JXL Named Area Formulas Display the Incorrect Value

Question

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);

Manassas

I don't know what jxl is, but in Excel/Vba you'd use excelSheet.Calculate – Sulla

Thanks for the info. Sadly, I have to use JXL. – Manassas

Answer 1

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.

Vasquez

Answer 2

Maybe this helps. Look for the "setAutomaticFormulaCalculation" method:

http://jexcelapi.sourceforge.net/resources/javadocs/2_6_10/docs/index.html

Sulla

No luck. I tried every possible combination of the methods setAutomaticFormulaCalculation and setRecalculateFormulasBeforeSave. I am beginning to think this is just an issue with JXL. – Manassas

Thanks for the tip though. I am guessing those methods are what "should" fix the issue. But I think there are just issues somewhere. I am wondering if it has to do with the timing of the area naming versus formula calculations. – Manassas

Answer 3

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.

Manassas

Answer 4

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.

Armalla