Apache POI:Как обновить Excel-файл со многими формулами

[Hehabr]

Что я делаю не так?
Почему в новом файле значение ячейки 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();
        }
    }
 
}

[Hehabr]

Может быть можно как-то удалить кэшированные результаты из файла?
Чтобы в новом файле все результаты пересчитались по новому, с учётом внесённых изменений...

[Hehabr]

С помощью 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();
        }
    }
 
}

Сообщение отредактировано: Hehabr - 04.08.17, 11:59