Apache POI : Problem with Excel updating

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

Apache POI : Problem with Excel updating

Hehabr
This post was updated on .
CONTENTS DELETED
The author has deleted this message.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Apache POI : Problem with Excel updating

Javen O'Neal-2
What version of POI are you using?

What is handleCell doing with the Cell? Are you trying to read the numeric
value from a cell containing number stored as text?

Have you taken a look at:
https://poi.apache.org/spreadsheet/quick-guide.html#Getting+the+cell+contents


On Jul 9, 2017 5:40 PM, "Hehabr" <[hidden email]> wrote:

Apache POI : Problem with Excel updating,
after the new values are written to the cells :
java.lang.IllegalStateException: Cannot get a NUMERIC value from a STRING
cell

What could be the problem? How to fix it?


I work with 5 files, 3 of them work as they should, and 2 others do not.
Workaround for 2 bad files:
Runtime.getRuntime().exec("cmd /c start " + excel.getAbsolutePath());



//
---------------------------------------------------------------------------

// Here: new values are written into the cells

workbook.getCreationHelper().createFormulaEvaluator().evaluateAll();
workbook.setForceFormulaRecalculation(true);

OutputStream output = new FileOutputStream(excel.getAbsolutePath());
workbook.write(output);
output.flush();
output.close();

// Here, new values are subtracted from the cells,
after Excel resolves with new values


//
---------------------------------------------------------------------------

// Value in bad Cell: =B24

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)





--
View this message in context: http://apache-poi.1045710.n5.
nabble.com/Apache-POI-Problem-with-Excel-updating-tp5728112.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 : Problem with Excel updating

Jörn Franke
You need to provide more source code so that we can help you...
Sometimes excel formulates formula results as General/Text and not number. This would be one possible out of several explanations.

> On 9. Jul 2017, at 17:51, Javen O'Neal <[hidden email]> wrote:
>
> What version of POI are you using?
>
> What is handleCell doing with the Cell? Are you trying to read the numeric
> value from a cell containing number stored as text?
>
> Have you taken a look at:
> https://poi.apache.org/spreadsheet/quick-guide.html#Getting+the+cell+contents
>
>
> On Jul 9, 2017 5:40 PM, "Hehabr" <[hidden email]> wrote:
>
> Apache POI : Problem with Excel updating,
> after the new values are written to the cells :
> java.lang.IllegalStateException: Cannot get a NUMERIC value from a STRING
> cell
>
> What could be the problem? How to fix it?
>
>
> I work with 5 files, 3 of them work as they should, and 2 others do not.
> Workaround for 2 bad files:
> Runtime.getRuntime().exec("cmd /c start " + excel.getAbsolutePath());
>
>
>
> //
> ---------------------------------------------------------------------------
>
> // Here: new values are written into the cells
>
> workbook.getCreationHelper().createFormulaEvaluator().evaluateAll();
> workbook.setForceFormulaRecalculation(true);
>
> OutputStream output = new FileOutputStream(excel.getAbsolutePath());
> workbook.write(output);
> output.flush();
> output.close();
>
> // Here, new values are subtracted from the cells,
> after Excel resolves with new values
>
>
> //
> ---------------------------------------------------------------------------
>
> // Value in bad Cell: =B24
>
> 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)
>
>
>
>
>
> --
> View this message in context: http://apache-poi.1045710.n5.
> nabble.com/Apache-POI-Problem-with-Excel-updating-tp5728112.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 : Problem with Excel updating

Robert Cochran
I agree that you need to provide your source code and perhaps also an example workbook. That will help everyone understand what is causing the problem. It is hard to diagnose from merely the error message.

Thanks a ton

Bob


> On Jul 9, 2017, at 11:54 AM, Jörn Franke <[hidden email]> wrote:
>
> You need to provide more source code so that we can help you...
> Sometimes excel formulates formula results as General/Text and not number. This would be one possible out of several explanations.
>
>> On 9. Jul 2017, at 17:51, Javen O'Neal <[hidden email]> wrote:
>>
>> What version of POI are you using?
>>
>> What is handleCell doing with the Cell? Are you trying to read the numeric
>> value from a cell containing number stored as text?
>>
>> Have you taken a look at:
>> https://poi.apache.org/spreadsheet/quick-guide.html#Getting+the+cell+contents
>>
>>
>> On Jul 9, 2017 5:40 PM, "Hehabr" <[hidden email]> wrote:
>>
>> Apache POI : Problem with Excel updating,
>> after the new values are written to the cells :
>> java.lang.IllegalStateException: Cannot get a NUMERIC value from a STRING
>> cell
>>
>> What could be the problem? How to fix it?
>>
>>
>> I work with 5 files, 3 of them work as they should, and 2 others do not.
>> Workaround for 2 bad files:
>> Runtime.getRuntime().exec("cmd /c start " + excel.getAbsolutePath());
>>
>>
>>
>> //
>> ---------------------------------------------------------------------------
>>
>> // Here: new values are written into the cells
>>
>> workbook.getCreationHelper().createFormulaEvaluator().evaluateAll();
>> workbook.setForceFormulaRecalculation(true);
>>
>> OutputStream output = new FileOutputStream(excel.getAbsolutePath());
>> workbook.write(output);
>> output.flush();
>> output.close();
>>
>> // Here, new values are subtracted from the cells,
>> after Excel resolves with new values
>>
>>
>> //
>> ---------------------------------------------------------------------------
>>
>> // Value in bad Cell: =B24
>>
>> 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)
>>
>>
>>
>>
>>
>> --
>> View this message in context: http://apache-poi.1045710.n5.
>> nabble.com/Apache-POI-Problem-with-Excel-updating-tp5728112.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]
>

---------------------------------------------------------------------
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 : Problem with Excel updating

Hehabr
This post was updated on .
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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Apache POI : Problem with Excel updating

Hehabr
D5 =SUMME(C18:C20)

C18 =WENNFEHLER(SUMME(C31:32)
C19 =SUMME(C33:34)
C20 =SUMME(C35:36)

C31-C36 → next Formel etc.


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

<apache.poi.version>3.16</apache.poi.version>

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

Re: Apache POI : Problem with Excel updating

Javen O'Neal
In reply to this post by Hehabr
And here's the problem:

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

Cell.getXCellValue will not convert between data types. If the cell isn't
numeric, trying to get a numeric value will raise an exception.

Refer to the Quick Guide that I referenced in a prior email.
Loading...