You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Hehabr <he...@web.de> on 2017/07/09 15:32:47 UTC
Apache POI : Problem with Excel updating
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: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Apache POI : Problem with Excel updating
Posted by Hehabr <he...@web.de>.
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>
------------------------------------------------------------------------
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-Problem-with-Excel-updating-tp5728112p5728178.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
Re: Apache POI : Problem with Excel updating
Posted by Javen O'Neal <ja...@gmail.com>.
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.
Re: Apache POI : Problem with Excel updating
Posted by Hehabr <he...@web.de>.
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 quicc.headliner.service.POITestEva.handleCell(POITestEva.java:109)
at quicc.headliner.service.POITestEva.readCell(POITestEva.java:91)
at quicc.headliner.service.POITestEva.readCellTest(POITestEva.java:74)
at quicc.headliner.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
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-Problem-with-Excel-updating-tp5728112p5728177.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
Re: Apache POI : Problem with Excel updating
Posted by Bob Cochran <r2...@gmail.com>.
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 <jo...@gmail.com> 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 <on...@apache.org> 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" <he...@web.de> 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: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Apache POI : Problem with Excel updating
Posted by Jörn Franke <jo...@gmail.com>.
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 <on...@apache.org> 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" <he...@web.de> 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: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Apache POI : Problem with Excel updating
Posted by Javen O'Neal <on...@apache.org>.
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" <he...@web.de> 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: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org