Hello,
I try to evaluate an excel formula DATEVALUE() using a HSSFFormulaEvaluator.
What I'm trying to do is:
1) get the text string value which contain a Date, but in text format (like
2011-05-16)
2) put in cell the excel formula DATEVALUE("2011-05-16") to get the real
evaluated date int value
3) replace in cell this real date int value then format yyyy-mm-dd
_____________________________________________________________________
Here is the piece of code I'm using:
//open the Excel file
FileOutputStream fileOut;
POIFSFileSystem fs = new POIFSFileSystem(new
FileInputStream("INVOICE_THIERRY.xls"));
// creation workbook, sheet et Row 1
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
HSSFRow row2 = sheet.getRow(1);
.../...
// get the current Date String value like "2011-05-16"
// and replace in same cell with actual DATEVALUE formula
// the type of cell becomes FORMULA
// using this date value string
HSSFCell cellDate = row2.getCell(5);
System.out.println("cell date value string = " +
cellDate.getStringCellValue());
dateToModif = '"' + cellDate.getStringCellValue() + '"';
if (cellDate != null)
row2.removeCell(cellDate);
cellDate = row2.createCell(5);
cellDate.setCellType(HSSFCell.CELL_TYPE_FORMULA);
myFormula = "DATEVALUE(" + dateToModif + ")";
System.out.println("the formula = " + myFormula);
cellDate.setCellFormula(myFormula);
.../...
// create evaluator to replace in cell DATEVALUE formula by its
actual evaluation
HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb);
HSSFCell evaluateInCell = evaluator.evaluateInCell(cellDate);
_____________________________________________________________________
At this line the error is coming:
the formula = DATEVALUE("2011-05-16")
Exception in thread "main"
org.apache.poi.ss.formula.eval.NotImplementedException: Error evaluating
cell article2!F2
at
org.apache.poi.ss.formula.WorkbookEvaluator.addExceptionInfo(WorkbookEvaluator.java:321)
at
org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:288)
at
org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:221)
at
org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateFormulaCellValue(HSSFFormulaEvaluator.java:320)
at
org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateInCell(HSSFFormulaEvaluator.java:240)
at test.TestPOIModifs1.main(TestPOIModifs1.java:91)
Caused by: org.apache.poi.ss.formula.eval.NotImplementedException: DATEVALUE
at
org.apache.poi.hssf.record.formula.functions.NotImplementedFunction.evaluate(NotImplementedFunction.java:42)
at
org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:132)
at
org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:456)
at
org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:279)
... 4 more
_____________________________________________________________________
Java 1.6 and poi-3.7 is used.
Thank you for your help
Franck
--
View this message in context:
http://apache-poi.1045710.n5.nabble.com/HSSFFormulaEvaluator-DATEVALUE-is-not-evaluated-tp4658681p4658681.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]
