I'm having some problems using the SUMIF() formula - if I generate a cell using it and then open the workbook in Excel 97 (or 2000) the displayed cell value is #VALUE!. However, if I look at the contents of the cell, the formula appears to be set correctly, and if I double-click on the cell, or hit the function button followed by OK, the cell then recalculates correctly. I should have said that if I do a manual recalculation within Excel ( tools | options | calculation | manual, F9) the cell is still not updated - I have to double click on it as described below. I'm using 1.7-dev and JDK 1.3.1 with Excel 97. Attachments are: poi.xls, poi.out - xls and BiffViewer from POI hand1.xls, hand1.out - xls and BiffViewer after loading into Excel 97 and saving unchanged hand2.xls, hand2.out - xls and BiffViewer after loading hand1.xls, double- clicking on formula and resaving. diff between hand1.out and hand2.out: $ diff hand1.out hand2.out 1687c1687 < .value = NaN --- > .value = 0.0 1702c1702 < 00000000 45 00 00 01 00 00 C0 00 C0 --- > 00000000 25 00 00 01 00 00 C0 00 C0 1717c1717 < 00000000 45 00 00 01 00 01 C0 01 C0 --- > 00000000 25 00 00 01 00 01 C0 01 C0 In context: ============================================ Offset 0xa41 (2625) recordid = 0x6, size =47 [FORMULA] .row = 2 .column = 3 .xf = f .value = 0.0 .options = 0 .zero = -52428800 .expressionlength= 25 .numptgsinarray = 4 Formula 0=AreaPtg firstRow = 0 lastRow = 1 firstCol = 0 lastCol = 0 firstColRowRel= true lastColRowRel = true firstColRel = true lastColRel = true 00000000 25 00 00 01 00 00 C0 00 C0 %........ Formula 1=org.apache.poi.hssf.record.formula.IntPtg@60be38 00000000 1E B8 0B ... Formula 2=AreaPtg firstRow = 0 lastRow = 1 firstCol = 1 lastCol = 1 firstColRowRel= true lastColRowRel = true firstColRel = true lastColRel = true 00000000 25 00 00 01 00 01 C0 01 C0 %........ Formula 3=<FunctionPtg> field_1_num_args=3 name =SUMIF field_2_fnc_index=345 </FunctionPtg> 00000000 42 03 59 01 B.Y. [/FORMULA] ============================================
Created attachment 2759 [details] Generation from POI
Created attachment 2760 [details] Generation from POI
Created attachment 2761 [details] Ater saving unchanged in Excel 97
Created attachment 2762 [details] After double clicking on formula in hand1.xls and saving.
Created attachment 2763 [details] BiffView of poi.xls
Created attachment 2764 [details] BiffView of hand1.xls
Created attachment 2765 [details] BiffView of hand2.xls
attach_id 2759 added in error - ignore!
Created attachment 3718 [details] Patch: Changed VALUE/REF token on SUMIF()