You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by FranckF <fr...@orange.fr> on 2011/08/02 15:21:17 UTC
HSSFFormulaEvaluator DATEVALUE is not evaluated
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: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: HSSFFormulaEvaluator DATEVALUE is not evaluated
Posted by FranckF <fr...@orange.fr>.
Hello Nick,
Thanks for your advices, it works fine now with the following. I get inside
my cell date in a good format.
Franck
_________________________________________________________________
try {
//ouverture du fichier Excel
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);
DateFormat dateformat = new SimpleDateFormat("yyyy-MM-dd");
HSSFDataFormat df = wb.createDataFormat();
HSSFCellStyle cs = wb.createCellStyle();
//System.out.println("index of format: " +
df.getFormat("yyyy/MM/dd"));
cs.setDataFormat(df.getFormat("d-mmm-yy"));
HSSFCell cellDateM = row2.getCell(5);
System.out.println("cell date value string = " +
cellDateM.getStringCellValue());
try {
Date theDate = dateformat.parse(cellDateM.getStringCellValue());
System.out.println("theDate = " + dateformat.format(theDate));
cellDateM.setCellValue(theDate);
cellDateM.setCellStyle(cs);
} catch (ParseException e) {
e.printStackTrace();
}
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/HSSFFormulaEvaluator-DATEVALUE-is-not-evaluated-tp4658681p4661480.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: HSSFFormulaEvaluator DATEVALUE is not evaluated
Posted by Nick Burch <ni...@alfresco.com>.
On Tue, 2 Aug 2011, FranckF wrote:
> Maybe I was not so clear. I have at the begining a string value "" that I
> would like to convert in a real excel Date format but I don't want to keep
> in the cell DATEVALUE().
> So getting the string value of my date and give it to DATEVALUE is working
> very well and when you open excel sheet you can see real date. My problem is
> that I still have in cell the formula.
> So I wanted to evaluate this formula in place of keeping it and at the end I
> have in my xls all date in text replaced with actual excel date.
So in a cell you currently have a string like "2011-01-02", and you want
to instead store a date? Simply grab the string, parse it to a
DateFormatter to get the Date object, set that into the cell, and apply a
suitable format.
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: HSSFFormulaEvaluator DATEVALUE is not evaluated
Posted by FranckF <fr...@orange.fr>.
Thanks Nick for quick answer.
Maybe I was not so clear. I have at the begining a string value "" that I
would like to convert in a real excel Date format but I don't want to keep
in the cell DATEVALUE().
So getting the string value of my date and give it to DATEVALUE is working
very well and when you open excel sheet you can see real date. My problem is
that I still have in cell the formula.
So I wanted to evaluate this formula in place of keeping it and at the end I
have in my xls all date in text replaced with actual excel date.
But maybe I should go a different way and just change the format. I'm new
with POI.
Cheers
Franck
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/HSSFFormulaEvaluator-DATEVALUE-is-not-evaluated-tp4658681p4658860.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: HSSFFormulaEvaluator DATEVALUE is not evaluated
Posted by Nick Burch <ni...@alfresco.com>.
On Tue, 2 Aug 2011, FranckF wrote:
> I try to evaluate an excel formula DATEVALUE() using a HSSFFormulaEvaluator.
It looks like the DATEVALUE function hasn't been implemented. If you
really wanted to go down this route, you'd need to submit a patch to add
in this function. Shout if you're interested and I can point you at some
guideance
> 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)
Why don't you just change the format of the cell to be whatever format you
want the date in, then ask the Data Formatter to format it to a string for
you?
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org