Hai Experts, While creating an Excel Template with POI 3.5-FINAL API the DATE() function works abnormally. I used the following function DATE(YEAR(A20),MONTH(A20)-1,DAY(A20)) ie., DATE(YEAR(previouscolumn value),MONTH(PreviousColumn Value)-1, DAY(PreviousCOlumn Value)) The above formula is working for some date ranges. If the previous column value is "February-09" it is correctly calculating the next value as "January-09" using the above formula. But it fails after that. After "January-09" the next evaluation of the formula is not working properly saying "#Value" in the column with out any error such as "Not Yet Implemented". If i keep the cursor in the particular cell and i press enter without changing the value or formula then the excel calculates the value correctly. Hope this is a bug in POI 3.5-Final API. How can i overcome this. Thanks Jaibabu
You seem to be describing unexpected behaviour from Excel, but we're going to need explicit details on how POI produced this workbook. Please upload some simple java code to produce this error (If relevant please add example input/output files). Another observation that may be useful: Does Excel fix the value if you force a recalculation of the whole sheet (instead of re-parsing the single cell as you described)?
Created attachment 24871 [details] Java file to generate Excel using POI3.5
Created attachment 24872 [details] Template Excel file that POI reads
Created attachment 24873 [details] Excel file created Using POI3.5
Hai Josh, Thanks for your response. I herewith attaching the Java file i used for writing Excel file using POI. (The java code reads a template excel file and generates the new Excel file with new values.) I am also attaching the "DemoTemplateFile.xlsx" which is used as a template file(from which the POI reads the format and formula) and "sampledemo.xlsx" file that POI generates using java code. In sampledemo.xlsx file please check the column "B12-B19" the Date formula is not evaluated correctly. But it evaluates the Date formula above that cell (B7-B11) correctly. The formula used in that column is DATE( YEAR( B6 ), MONTH( B6 )-1, DAY( B6 ) ) Assuming -1 is the problem in evaluation. If suppose if i use +1 instead it is evaluating correctly. Please refer Column (A7-A19). I used formula Evaluator to evaluate the formula cells in the Sheet. But i get the same result. Hope this may be useful for you. Thanks Jaibabu.J
Hai Experts, I hope the explanation i gave was enough. Please have a look in this issue. Thanks Jaibabu.J
Hai, Is there any thing need to explain about the problem more? Please reply. Thanks Jaibabu.J
This bug is still present in poi 3.7 final. Excel interprets carries in the date formula function, e.g. DATE(2012;MONTH(TODAY())-1;1) is evaluated to 2011-12-01 in January; DATE(2012;MONTH(TODAY())-13;1) is evaluated to 2010-12-01 then. It's the same with positive values and for carries at the date value.
Could someone please re-test with POI 3.8 beta 5 (the latest beta release), and confirm if the problem is fixed there or not?
I checked the SVN code. Implementation of Date function doesn't deal with any carry. SVN history (log) of DateFunc.java only mentions elimination of dependency on HSSF and reduction of eclipse warnings after 3.7 release.
Fixed in r1331796.