Apache POI : delete the cached results from the file

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

Apache POI : delete the cached results from the file

Hehabr
This post was updated on .
I am trying to update an existing Excel file with many formulas.
I can't  use evaluate-function ( Excel-file contains a function not supported by Apache POI)
How can I delete the cached results from the file (after update), before I save new file?

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

    }


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


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

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

Re: Apache POI : delete the cached results from the file

Javen O'Neal-2
https://poi.apache.org/spreadsheet/eval-devguide.html#Appendix+A

IFERROR seems to be supported.
Even if it was not supported (such as a custom VBA macros), you can always
write Java code to define how the function behaves and register it with POI.

If you're using xlsx or xlsm files, you could edit the XML of the file by
hand to see if clearing or deleting the cached formula result element in
sheet#.xml resolves your issue. If that strategy works, then your code
would need to make the same file changes. You can use POI's XML DOM to edit
the XML via the CTCell class, which is loads easier than writing your own
XML transform code from scratch.


On Aug 7, 2017 01:05, "Hehabr" <[hidden email]> wrote:

I am trying to update an existing Excel file with many formulas.
I can't  use evaluate-function ( Excel-file contains a function not
supported by Apache POI)
How can I delete the cached results from the file, before I save new file?

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

    }


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


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

}



--
View this message in context: http://apache-poi.1045710.n5.
nabble.com/Apache-POI-delete-the-cached-results-from-the-file-tp5728446.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 : delete the cached results from the file

Hehabr
It is not IFERROR. It is formula SUMPRODUCT.
If I replace complex SUMPRODUCT-formulas with simple  =A1 → code works.

SUMPRODUCT- formulas example from Excel-file:

=SUMPRODUCT(('Sheet0'!$E$2:$E$300=$B$5)*('Sheet0'!$R$2:$R$300=$B50)*('Sheet0'!L$2:L$300))/SUMPRODUCT(('Sheet0'!$E$2:$E$300=$B$5)*('Sheet0'!$R$2:$R$300=$B50)*('Sheet0'!L$2:L$300>0)*(1))

=SUMPRODUCT(('Sheet0'!$E$2:$E$300=$B$5)*('Sheet0'!$R$2:$R$300=$B38)*('Sheet0'!H$2:H$300))

=IFERROR(SUMPRODUCT(('Sheet0'!$E$2:$E$300=$B$5)*('Sheet0'!$R$2:$R$300=$B45)*('Sheet0'!L$2:L$300))/SUMPRODUCT(('Sheet0'!$E$2:$E$300=$B$5)*('Sheet0'!$R$2:$R$300=$B45)*('Sheet0'!L$2:L$300>0)*(1));0)

=IF(D23="";SUMPRODUCT(('Sheet0'!$E$2:$E$300=$B$5)*('Sheet0'!$R$2:$R$300=$B74)*('Sheet0'!H$2:H$300));D23)
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Apache POI : delete the cached results from the file

Hehabr
Before I look at POIs XML DOM...
I want to try another idea:

“For each cell of type formula → temporarily set the cell type to something different than Cell.CELL_TYPE_FORMULA and then back to the original cell type”
Is this possible? An example of such code?
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Apache POI : delete the cached results from the file

Javen O'Neal-2
> Is this possible?
You could always try it.

https://poi.apache.org/spreadsheet/eval-devguide.html#Appendix+A
SUMPRODUCT is supported


On Aug 8, 2017 1:59 AM, "Hehabr" <[hidden email]> wrote:

Before I look at POIs XML DOM...
I want to try another idea:

“For each cell of type formula → temporarily set the cell type to something
different than Cell.CELL_TYPE_FORMULA and then back to the original cell
type”
Is this possible? An example of such code?



--
View this message in context: http://apache-poi.1045710.n5.
nabble.com/Apache-POI-delete-the-cached-results-from-the-
file-tp5728446p5728449.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 : delete the cached results from the file

Hehabr
A different opinion about SUMPRODUCT:
"... even if sumproduct looks like it is not an array formula, it is just a syntax sugar for one... array formulas are not fully supported yet.."

https://codexample.org/questions/250754/excel-formula-evaluation-using-apache-poi.c
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Apache POI : delete the cached results from the file

Hehabr
In addition, the Excel-file uses the formula AVERAGEIF
AVERAGEIF is not supported in Apache POI
So evaluation is not possible.

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

Re: Apache POI : delete the cached results from the file

Nick Burch-2
On Wed, 9 Aug 2017, Hehabr wrote:
> In addition, the Excel-file uses the formula AVERAGEIF AVERAGEIF is not
> supported in Apache POI So evaluation is not possible.

If there's a missing function that matters to you, we'd love an
implementation of it!

This short talk is from a few years ago, but is largely still all the
same, about how formula evaluation works and how to add new functions:
http://home.apache.org/~yegor/apachecon_us2010/Evaluation_Of_Excel_Formulas_In_POI.pptx

If you can, have a read of that, then contribute back a patch to implement
your missing function

Nick

---------------------------------------------------------------------
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 : delete the cached results from the file

Hehabr
I can delete the cached results from the file (after update), but I can't save new file with new cached results
For that i need to open the Excel in GUI mode. But i don't want to open the Excel Sheet and recalculation.

See uploaded excel-file :
1.xlsm

and code:

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

    }

    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.setForceFormulaRecalculation(true);

            cleenCach(workbook);

            OutputStream os = new FileOutputStream(excelFileNew);
            workbook.write(os);
            os.flush();
            os.close();
        }
        catch (Exception e) {
            e.printStackTrace();
        }
    }


    public static void cleenCach(XSSFWorkbook workbook) {
        for (Sheet sheet : workbook) {
            for (Row r : sheet) {
                for (Cell c : r) {
                    if (c.getCellTypeEnum() == CellType.FORMULA) {
                        String temp = c.getCellFormula();
                        c.setCellType(CellType.STRING);
                        c.setCellType(CellType.FORMULA);
                        c.setCellFormula(temp);
                    }
                }
            }
        }
    }


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

}

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

Output:

Formula is: C34
cell.getCachedFormulaResultType(): 0
0. case Cell.CELL_TYPE_NUMERIC --> Last evaluated as: 6.0
Formula is: C34
cell.getCachedFormulaResultType(): 0
0. case Cell.CELL_TYPE_NUMERIC --> Last evaluated as: 0.0
Loading...