Apache POI : Проблема с обновлением Excel

[Hehabr]

Apache POI : Проблема с обновлением Excel,
после того как в ячейки записываются новые значения :
java.lang.IllegalStateException: Cannot get a NUMERIC value from a STRING cell

Работаю с 5 файлами, 3 из них работают как надо, а 2 других не хотят.
Пока приходится эти 2 файла стартовать посредством
Runtime.getRuntime().exec("cmd /c start " + excel.getAbsolutePath());
чтобы они открылись и эксель всё там прорешал и только потом из них вычитывать.

В чём может быть проблема? Как исправить?

// ---------------------------------------------------------------------------
 
// Здесь в ячейки записываются новые значения
 
workbook.getCreationHelper().createFormulaEvaluator().evaluateAll();
workbook.setForceFormulaRecalculation(true);
 
OutputStream output = new FileOutputStream(excel.getAbsolutePath());
workbook.write(output);
output.flush();
output.close();
 
// Здесь из ячеек вычитываются новые значения,
после того как Excel перерешает с новыми значениями
 
// ---------------------------------------------------------------------------
 
// «Ломается» на ячейке в которой стоит: =C34
 
java.lang.IllegalStateException: Cannot get a NUMERIC value from a STRING cell
    at org.apache.poi.xssf.usermodel.XSSFCell.typeMismatch(XSSFCell.java:1050)
    at org.apache.poi.xssf.usermodel.XSSFCell.getNumericCellValue(XSSFCell.java:310)
    at quicc.excel.api.ExcelHandlerXSSF.handleCell(ExcelHandlerXSSF.java:275)
    at quicc.excel.api.ExcelHandlerXSSF.readCell(ExcelHandlerXSSF.java:251)

[wind]

Цитата Hehabr @ 09.07.17, 14:09

// «Ломается» на ячейке в которой стоит: =C34

Сам ответил на свой вопрос Подробности.

[Hehabr]

package poi.service;
 
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.OutputStream;
 
public class POITestEva {
 
    private String excelFilePath = "C:/Test/1.xlsm";
    private FileInputStream inputStream;
    private XSSFWorkbook workbook;
 
    public static void main(String[] args) {
        POITestEva pOITestEva = new POITestEva();
        pOITestEva.updateCell(3.0);
        System.out.println("D5 = " + pOITestEva.readCellTest("D5"));        // Line 23
    }
 
 
    public void updateCell(Double newData) {
        try {
            File excel = new File(excelFilePath);
            inputStream = new FileInputStream(excel);
            workbook = new XSSFWorkbook(inputStream);
            workbook.setForceFormulaRecalculation(true);
 
            Cell cell = getCell(1, "C8");
            if (cell != null) {
                cell.setCellValue(newData);
            }
 
            workbook.getCreationHelper().createFormulaEvaluator().evaluateAll();
            OutputStream output = new FileOutputStream(excel);
            workbook.write(output);
            output.flush();
            output.close();
            workbook.close();
            inputStream.close();
        }
        catch (Exception e) {
            e.printStackTrace();
        }
    }
 
 
    private Cell getCell(int sheetNr, String cellId) {
        CellReference ref = new CellReference(cellId);
        return getCell(sheetNr, ref.getCol(), ref.getRow());
    }
 
    private Cell getCell(int sheetNr, int col, int row) {
        XSSFSheet sheet = workbook.getSheetAt(sheetNr);
        if (sheet.getRow(row) != null
                && sheet.getRow(row).getCell(col) != null
                && !(sheet.getRow(row).getCell(col).getCellType() == Cell.CELL_TYPE_BLANK)) {
            return sheet.getRow(row).getCell(col);
        }
        return null;
    }
 
 
    public Double readCellTest(String cellId) {
        try {
            File excel = new File(excelFilePath);
            inputStream = new FileInputStream(excel);
            workbook = new XSSFWorkbook(inputStream);
            Double result = ( (Double) (readCell(cellId)) );            // Line 74
            if (workbook != null) {
                workbook.close();
            }
            if (inputStream != null) {
                workbook.close();
            }
            return result;
        }
        catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }
 
    private Object readCell(String cellId) {
        Cell cell = getCell(1, cellId);
        return handleCell(cell.getCellType(), cell);                    // Line 91
    }
 
 
    @SuppressWarnings("deprecation")
    private Object handleCell(int type, Cell cell) {
        switch (type) {
            case XSSFCell.CELL_TYPE_STRING:
                return cell.getStringCellValue();
            case XSSFCell.CELL_TYPE_NUMERIC:
                return cell.getNumericCellValue();
            case XSSFCell.CELL_TYPE_BOOLEAN:
                return cell.getBooleanCellValue();
            case XSSFCell.CELL_TYPE_BLANK:
                return null;
            case XSSFCell.CELL_TYPE_ERROR:
                return null;
            case XSSFCell.CELL_TYPE_FORMULA:
                return cell.getNumericCellValue();                  // Line 109
            default:
                return null;
        }
    }
 
}
 
-------------------------------------------------------------------------
 
"C:\Program Files\Java\jdk1.8.0_51\bin\java" -Didea.launcher.port=... ...java.lang.IllegalStateException: Cannot get a NUMERIC value from a STRING cell
D5 = null
    at org.apache.poi.xssf.usermodel.XSSFCell.typeMismatch(XSSFCell.java:1050)
    at org.apache.poi.xssf.usermodel.XSSFCell.getNumericCellValue(XSSFCell.java:310)
    at service.POITestEva.handleCell(POITestEva.java:109)
    at service.POITestEva.readCell(POITestEva.java:91)
    at service.POITestEva.readCellTest(POITestEva.java:74)
    at service.POITestEva.main(POITestEva.java:23)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at com.intellij.rt.execution.application.AppMain.main(AppMain.java:147)
 
Process finished with exit code 0

Добавлено 10.07.17, 20:49
<apache.poi.version>3.16</apache.poi.version>

------------------------------------------------------------------------

D5 =SUM(C18:C20)

C18 =IFERROR(SUM(C31:32)
C19 =SUM(C33:34)
C20 =SUM(C35:36)

C31-C36 → next Formel etc.

-----------------------------------------------------------------------------

Добавлено 10.07.17, 21:07
Конвертировать строку в число не представляется возможным, потому что эта строка - это текст формулы в ячейке. До обновления одной из других ячеек из проблемной ячейки D5 вычитывается число, получающееся там по этой формуле, а после обновления нет.

Сообщение отредактировано: Hehabr - 10.07.17, 21:07

[wind]

Цитата Hehabr @ 10.07.17, 20:43

    case XSSFCell.CELL_TYPE_FORMULA:
        return cell.getNumericCellValue();

???

Что непонятного в тексте по ссылке из моего предыдущего сообщения? Там же описано, как посчитать формулу, примеры кода есть

[Hehabr]

Нижеследующий код выдает результат в консоли:
....................
D5 - C34
C34
....................
Почему выводится сама формула (=C34) , а не число, которое видно в ячейке D5, если открыть сам файл?
Как сделать чтобы выводилось само число?

import org.apache.poi.ss.usermodel.*;
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 POITest {
 
    public static void main(String[] args) throws IOException {
        FileInputStream fis = new FileInputStream("C:/Test/1.xlsm");
        XSSFWorkbook workbook = new XSSFWorkbook(fis);
        XSSFSheet sheet = workbook.getSheetAt(1);
        CellReference ref = new CellReference("D5");
        int row = ref.getRow();
        int col = ref.getCol();
        Cell cell = sheet.getRow(row).getCell(col);
        gettingTheCellContents(ref, cell);
        fis.close();
        workbook.close();
    }
 
    // https://poi.apache.org/spreadsheet/quick-guide.html#CellContents
    private static void gettingTheCellContents(CellReference cellRef, Cell cell) {
 
        DataFormatter formatter = new DataFormatter();
        System.out.print(cellRef.formatAsString());
        System.out.print(" - ");
 
        // get the text that appears in the cell by getting the cell value and applying any data formats (Date, 0.00, 1.23e9, $1.23, etc)
        String text = formatter.formatCellValue(cell);
        System.out.println(text);
 
        // Alternatively, get the value and format it yourself
        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(cell.getCellFormula());
                break;
            case BLANK:
                System.out.println();
                break;
            default:
                System.out.println();
        }
    }
    
}