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: [email protected]
For additional commands, e-mail: [email protected]
