![>](style_images/1/nav_m.gif)
![]() |
Наши проекты:
Журнал · Discuz!ML · Wiki · DRKB · Помощь проекту |
|
ПРАВИЛА | FAQ | Помощь | Поиск | Участники | Календарь | Избранное | RSS |
[3.15.34.105] |
![]() |
|
Сообщ.
#1
,
|
|
|
Что я делаю не так?
Почему в новом файле значение ячейки D5: #DIV/0! -- Program output: Formula is: C34 1. case Cell.CELL_TYPE_NUMERIC --> Last evaluated as: 407.2521754511886 Formula is: C34 -- Cell values: Value of the cell D5 in file excelFileOrig.xlsm is: 407,25 Value of the cell D5 in file excelFileNew.xlsm is: #DIV/0! Value of the cell C8 in file excelFileOrig.xlsm is: 5,0 Value of the cell D5 in file excelFileNew.xlsm is: 15,0 -- Code: ![]() ![]() import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.DateUtil; import org.apache.poi.ss.util.CellReference; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import java.io.*; public class POITestRB { static String excelFileOrig = "C:/Test/excelFileOrig.xlsm"; static String excelFileNew = "C:/Test/excelFileNew.xlsm"; static FileInputStream fis; static XSSFWorkbook workbook; public static void main(String[] args) throws IOException { fis = new FileInputStream(excelFileOrig); workbook = new XSSFWorkbook(fis); gettingCellContents(workbook, "D5"); updateCell(workbook, 15.0); fis.close(); workbook.close(); fis = new FileInputStream(excelFileNew); workbook = new XSSFWorkbook(fis); gettingCellContents(workbook, "D5"); } private static void gettingCellContents(XSSFWorkbook workbook, String cellId) { XSSFSheet sheet = workbook.getSheetAt(1); CellReference ref = new CellReference(cellId); int row = ref.getRow(); int col = ref.getCol(); Cell cell = sheet.getRow(row).getCell(col); switch (cell.getCellTypeEnum()) { case STRING: System.out.println(cell.getRichStringCellValue().getString()); break; case NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { System.out.println(cell.getDateCellValue()); } else { System.out.println(cell.getNumericCellValue()); } break; case BOOLEAN: System.out.println(cell.getBooleanCellValue()); break; case FORMULA: System.out.println("Formula is: " + cell.getCellFormula()); switch(cell.getCachedFormulaResultType()) { case Cell.CELL_TYPE_NUMERIC: System.out.println("1. case Cell.CELL_TYPE_NUMERIC --> Last evaluated as: " + cell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.println("2. case Cell.CELL_TYPE_STRING --> Last evaluated as \"" + cell.getRichStringCellValue() + "\""); break; } break; case BLANK: System.out.println(); break; default: System.out.println(); } } public static void updateCell(XSSFWorkbook workbook, Double newData) { try { XSSFSheet sheet = workbook.getSheetAt(1); CellReference ref = new CellReference("C8"); int row = ref.getRow(); int col = ref.getCol(); Cell cell = sheet.getRow(row).getCell(col); if (cell != null) { cell.setCellValue(newData); } workbook.getCreationHelper().createFormulaEvaluator().evaluateAll(); OutputStream os = new FileOutputStream(excelFileNew); workbook.write(os); os.flush(); os.close(); } catch (Exception e) { e.printStackTrace(); } } } |
Сообщ.
#2
,
|
|
|
Может быть можно как-то удалить кэшированные результаты из файла?
Чтобы в новом файле все результаты пересчитались по новому, с учётом внесённых изменений... |
Сообщ.
#3
,
|
|
|
С помощью workbook.setForceFormulaRecalculation(true); новый файл сохраняется с новыми значениями всех ячеек.
Проблема теперь с выводом значения ячейки в новом файле: ------------------------------------------------------------------------------------------------- Formula is: C34 cell.getCachedFormulaResultType(): 0 0. case Cell.CELL_TYPE_NUMERIC --> Last evaluated as: 407.2521754511886 Formula is: C34 cell.getCachedFormulaResultType(): 5 5. case Cell.CELL_TYPE_ERROR --> ------------------------------------------------------------------------------------------------- Почему выводит CELL_TYPE_ERROR ? Новый файл хранит новые значениями всех ячеек. Что надо ещё добавить в коде? ![]() ![]() import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.DateUtil; import org.apache.poi.ss.util.CellReference; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import java.io.*; public class POITestRB { static String excelFileOrig = "C:/Test/1.xlsm"; static String excelFileNew = "C:/Test/excelFileNew.xlsm"; static FileInputStream fis; static XSSFWorkbook workbook; public static void main(String[] args) throws IOException { fis = new FileInputStream(excelFileOrig); workbook = new XSSFWorkbook(fis); gettingCellContents(workbook, "D5"); updateCell(workbook, 10.0); fis.close(); workbook.close(); fis = new FileInputStream(excelFileNew); workbook = new XSSFWorkbook(fis); gettingCellContents(workbook, "D5"); fis.close(); workbook.close(); } private static void gettingCellContents(XSSFWorkbook workbook, String cellId) { workbook.setForceFormulaRecalculation(true); XSSFSheet sheet = workbook.getSheetAt(1); CellReference ref = new CellReference(cellId); int row = ref.getRow(); int col = ref.getCol(); Cell cell = sheet.getRow(row).getCell(col); switch (cell.getCellTypeEnum()) { case STRING: System.out.println(cell.getRichStringCellValue().getString()); break; case NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { System.out.println(cell.getDateCellValue()); } else { System.out.println(cell.getNumericCellValue()); } break; case BOOLEAN: System.out.println(cell.getBooleanCellValue()); break; case FORMULA: System.out.println("Formula is: " + cell.getCellFormula()); System.out.println("cell.getCachedFormulaResultType(): " + cell.getCachedFormulaResultType()); switch(cell.getCachedFormulaResultType()) { case Cell.CELL_TYPE_NUMERIC: System.out.println("0. case Cell.CELL_TYPE_NUMERIC --> Last evaluated as: " + cell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.println("4. case Cell.CELL_TYPE_STRING --> Last evaluated as \"" + cell.getRichStringCellValue() + "\""); break; case Cell.CELL_TYPE_ERROR: System.out.println("5. case Cell.CELL_TYPE_ERROR --> "); break; } break; case BLANK: System.out.println(); break; default: System.out.println("default"); } } public static void updateCell(XSSFWorkbook workbook, Double newData) { try { XSSFSheet sheet = workbook.getSheetAt(1); CellReference ref = new CellReference("C8"); int row = ref.getRow(); int col = ref.getCol(); Cell cell = sheet.getRow(row).getCell(col); if (cell != null) { cell.setCellValue(newData); } workbook.getCreationHelper().createFormulaEvaluator().clearAllCachedResultValues(); workbook.getCreationHelper().createFormulaEvaluator().evaluateAll(); OutputStream os = new FileOutputStream(excelFileNew); workbook.write(os); os.flush(); os.close(); } catch (Exception e) { e.printStackTrace(); } } } |