Apache POI: How to update excel file with many formulas?

classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Apache POI: How to update excel file with many formulas?

Hehabr
What am I doing wrong?
Value of the cell D5 in file excelFileNew.xlsm is: #DIV/0!
Why?

-- 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();
        }
    }

}

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Apache POI: How to update excel file with many formulas?

Dominik Stadler
Hi,

Can you share the Excel file? Without that it is hard to state much... Can
also be a reduced file that only contains what is necessary to show the
behaviour.

Dominik.

On Thu, Aug 3, 2017 at 11:42 AM, Hehabr <[hidden email]> wrote:

> What am I doing wrong?
> Value of the cell D5 in file excelFileNew.xlsm is: #DIV/0!
> Why?
>
> -- 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();
>         }
>     }
>
> }
>
>
>
>
>
> --
> View this message in context: http://apache-poi.1045710.n5.
> nabble.com/Apache-POI-How-to-update-excel-file-with-many-
> formulas-tp5728410.html
> Sent from the POI - User mailing list archive at Nabble.com.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Apache POI: How to update excel file with many formulas?

Hehabr
Can you share the Excel file? -- No. Sorry. There are "hundreds" of formulas inside. The beginning of the chain looks like this:

D5 =C34

C34 =SUM(A8:A13)

A8 =IFERROR(SUM(B22);0)
A9    "is empty"
A10 =SUM(B24)
A11  "is empty"
A12 =SUM(B26)
A13  "is empty"

B22 =SUM(D12:F12)
B24 =SUM(D14:F14)
B26 =SUM(D16:F16)


D12 =SUM(D30:D40)
E12 =IF(H9="Yes";MAX(E18-(J18-J20)*SheetA!B2;SheetA!C2);MAX(E18-(J18-J20)*SheetA!F3;SheetA!F3)-IF(E9>0;F59;0))
F12 =F228/(D458+E58)*(D82+E82)

D14 =IF(H54>0;D40/H40/I40/J40*H64*I64*J64;0)
E14 =E40/H40*H64+E40/J40*(J64-J40)*0,3
F14 =F40/(D40+E40)*(D64+E64)

D16 =IF(H66>0;D42/H42/I42*H66*I66;0)
E16 =E32/H32*H66+E42/J42*(J66-J42)*0,3
F16 =F42/(D42+E42)*(D66+E66)

etc
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Apache POI: How to update excel file with many formulas?

Hehabr
Formulas in a Excel-file
:
AND, FALSE, IF, IFERROR, MAX, OR, SUM, SUMPRODUCT, VLOOKUP

Formulas in Program with System.out.println(FunctionEval.getSupportedFunctionNames());
:
[ABS, ACOS, ACOSH, ADDRESS, AND, ASIN, ASINH, ATAN, ATAN2, ATANH, AVEDEV, AVERAGE, CEILING, CHAR, CHOOSE, CLEAN, CODE, COLUMN, COLUMNS, COMBIN, CONCATENATE, COS, COSH, COUNT, COUNTA, COUNTBLANK, COUNTIF, DATE, DAY, DAYS360, DEGREES, DEVSQ, DGET, DMIN, DOLLAR, ERROR.TYPE, EVEN, EXACT, EXP, FACT, FALSE, FIND, FIXED, FLOOR, FV, HLOOKUP, HOUR, HYPERLINK, IF, INDEX, INDIRECT, INT, INTERCEPT, IPMT, IRR, ISBLANK, ISERR, ISERROR, ISLOGICAL, ISNA, ISNONTEXT, ISNUMBER, ISREF, ISTEXT, LARGE, LEFT, LEN, LN, LOG, LOG10, LOOKUP, LOWER, MATCH, MAX, MAXA, MEDIAN, MID, MIN, MINA, MINUTE, MIRR, MOD, MODE, MONTH, NA, NOT, NOW, NPER, NPV, ODD, OFFSET, OR, PERCENTILE, PI, PMT, POISSON, POWER, PPMT, PRODUCT, PROPER, PV, RADIANS, RAND, RANK, RATE, REPLACE, REPT, RIGHT, ROMAN, ROUND, ROUNDDOWN, ROUNDUP, ROW, ROWS, SEARCH, SECOND, SIGN, SIN, SINH, SLOPE, SMALL, SQRT, STDEV, SUBSTITUTE, SUBTOTAL, SUM, SUMIF, SUMPRODUCT, SUMSQ, SUMX2MY2, SUMX2PY2, SUMXMY2, T, TAN, TANH, TEXT, TIME, TODAY, TRIM, TRUE, TRUNC, UPPER, VALUE, VAR, VARP, VLOOKUP, WEEKDAY, YEAR]


IFERROR is missing in Java... Why? (  My apache.poi.version is 3.16 )
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Apache POI: How to update excel file with many formulas?

Greg Woolsey
Like all open-source volunteer projects, the answer to "why isn't X
implemented?" is always "because no one needed it bad enough yet to write a
patch to do it."  That said, IFERROR() _IS_ supported by POI, but in the
AnalysisToolPak functions, not the FunctionEval list.  You can see the full
list of supported functions from
WorkbookEvaluator.getSupportedFunctionNames().

On Thu, Aug 3, 2017 at 9:37 AM Hehabr <[hidden email]> wrote:

> Formulas in a Excel-file
> :
> AND, FALSE, IF, IFERROR, MAX, OR, SUM, SUMPRODUCT, VLOOKUP
>
> Formulas in Program with
> System.out.println(FunctionEval.getSupportedFunctionNames());
> :
> [ABS, ACOS, ACOSH, ADDRESS, AND, ASIN, ASINH, ATAN, ATAN2, ATANH, AVEDEV,
> AVERAGE, CEILING, CHAR, CHOOSE, CLEAN, CODE, COLUMN, COLUMNS, COMBIN,
> CONCATENATE, COS, COSH, COUNT, COUNTA, COUNTBLANK, COUNTIF, DATE, DAY,
> DAYS360, DEGREES, DEVSQ, DGET, DMIN, DOLLAR, ERROR.TYPE, EVEN, EXACT, EXP,
> FACT, FALSE, FIND, FIXED, FLOOR, FV, HLOOKUP, HOUR, HYPERLINK, IF, INDEX,
> INDIRECT, INT, INTERCEPT, IPMT, IRR, ISBLANK, ISERR, ISERROR, ISLOGICAL,
> ISNA, ISNONTEXT, ISNUMBER, ISREF, ISTEXT, LARGE, LEFT, LEN, LN, LOG, LOG10,
> LOOKUP, LOWER, MATCH, MAX, MAXA, MEDIAN, MID, MIN, MINA, MINUTE, MIRR, MOD,
> MODE, MONTH, NA, NOT, NOW, NPER, NPV, ODD, OFFSET, OR, PERCENTILE, PI, PMT,
> POISSON, POWER, PPMT, PRODUCT, PROPER, PV, RADIANS, RAND, RANK, RATE,
> REPLACE, REPT, RIGHT, ROMAN, ROUND, ROUNDDOWN, ROUNDUP, ROW, ROWS, SEARCH,
> SECOND, SIGN, SIN, SINH, SLOPE, SMALL, SQRT, STDEV, SUBSTITUTE, SUBTOTAL,
> SUM, SUMIF, SUMPRODUCT, SUMSQ, SUMX2MY2, SUMX2PY2, SUMXMY2, T, TAN, TANH,
> TEXT, TIME, TODAY, TRIM, TRUE, TRUNC, UPPER, VALUE, VAR, VARP, VLOOKUP,
> WEEKDAY, YEAR]
>
>
> IFERROR is missing in Java... Why? (  My apache.poi.version is 3.16 )
>
>
>
>
> --
> View this message in context:
> http://apache-poi.1045710.n5.nabble.com/Apache-POI-How-to-update-excel-file-with-many-formulas-tp5728410p5728413.html
> Sent from the POI - User mailing list archive at Nabble.com.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Apache POI: How to update excel file with many formulas?

Javen O'Neal-2
In reply to this post by Hehabr
Based on what you said, it looks like POI hasn't implemented the IFERROR
function yet. Could you please open an enhancement request on bugzilla?

https://bz.apache.org/bugzilla/enter_bug.cgi?product=POI&component=SS%20Common

The documentation for the IFERROR function is
https://support.office.com/en-us/article/IFERROR-function-c526fd07-caeb-47b8-8bb6-63f3e417f611

On Aug 3, 2017 09:37, "Hehabr" <[hidden email]> wrote:

> Formulas in a Excel-file
> :
> AND, FALSE, IF, IFERROR, MAX, OR, SUM, SUMPRODUCT, VLOOKUP
>
> Formulas in Program with
> System.out.println(FunctionEval.getSupportedFunctionNames());
> :
> [ABS, ACOS, ACOSH, ADDRESS, AND, ASIN, ASINH, ATAN, ATAN2, ATANH, AVEDEV,
> AVERAGE, CEILING, CHAR, CHOOSE, CLEAN, CODE, COLUMN, COLUMNS, COMBIN,
> CONCATENATE, COS, COSH, COUNT, COUNTA, COUNTBLANK, COUNTIF, DATE, DAY,
> DAYS360, DEGREES, DEVSQ, DGET, DMIN, DOLLAR, ERROR.TYPE, EVEN, EXACT, EXP,
> FACT, FALSE, FIND, FIXED, FLOOR, FV, HLOOKUP, HOUR, HYPERLINK, IF, INDEX,
> INDIRECT, INT, INTERCEPT, IPMT, IRR, ISBLANK, ISERR, ISERROR, ISLOGICAL,
> ISNA, ISNONTEXT, ISNUMBER, ISREF, ISTEXT, LARGE, LEFT, LEN, LN, LOG, LOG10,
> LOOKUP, LOWER, MATCH, MAX, MAXA, MEDIAN, MID, MIN, MINA, MINUTE, MIRR, MOD,
> MODE, MONTH, NA, NOT, NOW, NPER, NPV, ODD, OFFSET, OR, PERCENTILE, PI, PMT,
> POISSON, POWER, PPMT, PRODUCT, PROPER, PV, RADIANS, RAND, RANK, RATE,
> REPLACE, REPT, RIGHT, ROMAN, ROUND, ROUNDDOWN, ROUNDUP, ROW, ROWS, SEARCH,
> SECOND, SIGN, SIN, SINH, SLOPE, SMALL, SQRT, STDEV, SUBSTITUTE, SUBTOTAL,
> SUM, SUMIF, SUMPRODUCT, SUMSQ, SUMX2MY2, SUMX2PY2, SUMXMY2, T, TAN, TANH,
> TEXT, TIME, TODAY, TRIM, TRUE, TRUNC, UPPER, VALUE, VAR, VARP, VLOOKUP,
> WEEKDAY, YEAR]
>
>
> IFERROR is missing in Java... Why? (  My apache.poi.version is 3.16 )
>
>
>
>
> --
> View this message in context: http://apache-poi.1045710.n5.
> nabble.com/Apache-POI-How-to-update-excel-file-with-many-
> formulas-tp5728410p5728413.html
> Sent from the POI - User mailing list archive at Nabble.com.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Apache POI: How to update excel file with many formulas?

Robert Cochran
Hehabr, if you do open an enhancement request,  can you please post the request number so I can follow it. I am not skilled with the POI, but wish to learn it and perhaps contribute in the future.


Thank you!

Bob

> On Aug 3, 2017, at 1:18 PM, Javen O'Neal <[hidden email]> wrote:
>
> Based on what you said, it looks like POI hasn't implemented the IFERROR
> function yet. Could you please open an enhancement request on bugzilla?
>
> https://bz.apache.org/bugzilla/enter_bug.cgi?product=POI&component=SS%20Common
>
> The documentation for the IFERROR function is
> https://support.office.com/en-us/article/IFERROR-function-c526fd07-caeb-47b8-8bb6-63f3e417f611
>
>> On Aug 3, 2017 09:37, "Hehabr" <[hidden email]> wrote:
>>
>> Formulas in a Excel-file
>> :
>> AND, FALSE, IF, IFERROR, MAX, OR, SUM, SUMPRODUCT, VLOOKUP
>>
>> Formulas in Program with
>> System.out.println(FunctionEval.getSupportedFunctionNames());
>> :
>> [ABS, ACOS, ACOSH, ADDRESS, AND, ASIN, ASINH, ATAN, ATAN2, ATANH, AVEDEV,
>> AVERAGE, CEILING, CHAR, CHOOSE, CLEAN, CODE, COLUMN, COLUMNS, COMBIN,
>> CONCATENATE, COS, COSH, COUNT, COUNTA, COUNTBLANK, COUNTIF, DATE, DAY,
>> DAYS360, DEGREES, DEVSQ, DGET, DMIN, DOLLAR, ERROR.TYPE, EVEN, EXACT, EXP,
>> FACT, FALSE, FIND, FIXED, FLOOR, FV, HLOOKUP, HOUR, HYPERLINK, IF, INDEX,
>> INDIRECT, INT, INTERCEPT, IPMT, IRR, ISBLANK, ISERR, ISERROR, ISLOGICAL,
>> ISNA, ISNONTEXT, ISNUMBER, ISREF, ISTEXT, LARGE, LEFT, LEN, LN, LOG, LOG10,
>> LOOKUP, LOWER, MATCH, MAX, MAXA, MEDIAN, MID, MIN, MINA, MINUTE, MIRR, MOD,
>> MODE, MONTH, NA, NOT, NOW, NPER, NPV, ODD, OFFSET, OR, PERCENTILE, PI, PMT,
>> POISSON, POWER, PPMT, PRODUCT, PROPER, PV, RADIANS, RAND, RANK, RATE,
>> REPLACE, REPT, RIGHT, ROMAN, ROUND, ROUNDDOWN, ROUNDUP, ROW, ROWS, SEARCH,
>> SECOND, SIGN, SIN, SINH, SLOPE, SMALL, SQRT, STDEV, SUBSTITUTE, SUBTOTAL,
>> SUM, SUMIF, SUMPRODUCT, SUMSQ, SUMX2MY2, SUMX2PY2, SUMXMY2, T, TAN, TANH,
>> TEXT, TIME, TODAY, TRIM, TRUE, TRUNC, UPPER, VALUE, VAR, VARP, VLOOKUP,
>> WEEKDAY, YEAR]
>>
>>
>> IFERROR is missing in Java... Why? (  My apache.poi.version is 3.16 )
>>
>>
>>
>>
>> --
>> View this message in context: http://apache-poi.1045710.n5.
>> nabble.com/Apache-POI-How-to-update-excel-file-with-many-
>> formulas-tp5728410p5728413.html
>> Sent from the POI - User mailing list archive at Nabble.com.
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [hidden email]
>> For additional commands, e-mail: [hidden email]
>>
>>

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Apache POI: How to update excel file with many formulas?

Hehabr
In reply to this post by Javen O'Neal-2
Idea: delete the cached results from the file.
Is it possible?
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Apache POI: How to update excel file with many formulas?

Javen O'Neal
If you don't trust POI's formula evaluator, you can ask Excel to
recalculate all formulas when it opens the workbook.
https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFWorkbook.html#setForceFormulaRecalculation(boolean)
This won't clear the cached results from the file, and it's up to your
spreadsheet program to honor the formula recalculation request

Otherwise you could for-loop over all formula cells and delete the cached
result.

But based on what Greg said, the IFERROR function is already implemented in
POI under the Analysis ToolPak and it's only a few lines of code to make
these functions available to the FormulaEvaluator.

On Fri, Aug 4, 2017 at 12:36 AM, Hehabr <[hidden email]> wrote:

> Idea: delete the cached results from the file.
> Is it possible?
>
>
>
> --
> View this message in context: http://apache-poi.1045710.n5.
> nabble.com/Apache-POI-How-to-update-excel-file-with-many-
> formulas-tp5728410p5728427.html
> Sent from the POI - User mailing list archive at Nabble.com.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Apache POI: How to update excel file with many formulas?

Hehabr
This post was updated on .
With workbook.setForceFormulaRecalculation(true); is new file correctly saved. All cells have new values!

Next problem: bad output for new file.
-------------------------------------------------------------------------------------------------
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 -->
-------------------------------------------------------------------------------------------------

Why CELL_TYPE_ERROR ? All cells have new values in new file...
What needs to be changed in 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/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();
        }
    }

}
Loading...