Mailing-List: contact user-help@poi.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "POI Users List" <user@poi.apache.org>
Date: Fri, 4 Aug 2017 04:40:47 -0700 (MST)
From: Hehabr <hehabr@web.de>
To: user@poi.apache.org
Message-ID: <1501846847333-5728430.post@n5.nabble.com>
In-Reply-To: <CAM+TppJ+Q5PPyjp9-5PMkFDdE3PG4hZF7p3WLfWXr5-JmPnddg@mail.gmail.com>
References: <1501753335736-5728410.post@n5.nabble.com> <CABdJj55uD=xUG=OLaGYt+OSJ0j1XU4grBQYn6cWUHW=TR-w_FA@mail.gmail.com> <1501765649405-5728412.post@n5.nabble.com> <1501778244162-5728413.post@n5.nabble.com> <CAM+TppKZGeDZ46hJ1+LqdPOj6-C2SdLBJPbfWh8xRPrurY9wMg@mail.gmail.com> <1501832186798-5728427.post@n5.nabble.com> <CAM+TppJ+Q5PPyjp9-5PMkFDdE3PG4hZF7p3WLfWXr5-JmPnddg@mail.gmail.com>
Subject: Re: Apache POI: How to update excel file with many formulas?
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
archived-at: Fri, 04 Aug 2017 11:40:56 -0000

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?

package quicc.headliner.service;

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

}


--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-How-to-update-excel-file-with-many-formulas-tp5728410p5728430.html
Sent from the POI - User mailing list archive at Nabble.com.

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org