I am using JXL to write an Excel report. I need to use named areas for the report, and have formulas based off of them. However, when the Excel file loads, the values displayed are incorrect. If I click on the cell, and hit enter, the value changes and is correct. I can't figure out how to make the value appear correctly on load. Does anyone know a way to force Excel to re-evaluate, or to provide a default value for the Formula?
Below is a simplified version of my code:
excelWorkbook.addNameArea("NamedArea", excelSheet, column1, row1, column2, row2);
Formula formula = new Formula(columnNumber, rowNumber, "COUNT(NamedArea)");
excelSheet.addCell(formula);
I have also been having an issue with some formula fields not having the correct values, and have found no solution online. With some self investigation I found that if for instance you have the following formula on a cell
Formula frmla = new Formula(col2, row0, "SUM(A1+A2)");
excelSheet.addCell(frmla);
The following will not produce a result in the formula cell because of the use of Label:
Label lbl1 = new Label(col0, row0, "5", wrtbleCellFrmt);
Label lbl2 = new Label(col1, row0, "5", wrtbleCellFrmt);
The following will produce correct results because of the use of jxl.write.number:
jxl.write.Number number1 = new jxl.write.Number(col0, row0, 5, wrtbleCellFrmt);
jxl.write.Number number2 = new jxl.write.Number(col0, row0, 5, wrtbleCellFrmt);
Label takes a parameter String while Number takes in a Double, which probably is the cause for correct results.
Maybe this helps. Look for the "setAutomaticFormulaCalculation" method:
http://jexcelapi.sourceforge.net/resources/javadocs/2_6_10/docs/index.html
Sadly, it's looking like there is not really a fix for this. I think it's a problem in JXL. I think for some reason the named areas are not being set until after the formulas are evaluated.
Error #VALUE came up to me today too. It was this formula:
=COUNTIF('list1'!A9:'list1'!AE9,>0)
When I pressed F2 and enter on that cell it calculated it well but before it showed error: "A value used in this formula is of the wrong data type"
I used Number class to input numbers into that area but I found out that if there is only one empty space in that range (if you don't give value to all in the range) it will show that error in spreadsheet later.
Solution:
I moved formula to that same list1, into cell AF9:
=COUNTIF(A9:AE9,>0)
to calculate it first) and then copied only calculated value of AF9 to other list
='list1'!AF9
It worked ok after that and no more #VALUE showed up.
