Created attachment 26910 [details] Test class, which is used to create ConditionalFormula.xls The attached Java class creates a file called ConditionalFormula.xls. This file is basically identical with the attached file ConditionalFormula2.xls, with one important exception: The cells D3, D4, etc. have a conditional formula. When inspecting this formula in the first file, which is created by POI, then the formula is shown constant in Excel. In the second file, the formula is modified by Excel and the row number matches the cells row number. However, on the POI API level, there is no meaningful difference between the files, as can be shown by running the "Test" class. This class is used to extract the relevant information from the excel files.
Created attachment 26911 [details] Java class, which is used to inspect an Excel files conditional formulas
Created attachment 26912 [details] File, which has been created by POI using the Java class
Created attachment 26913 [details] File, which has been created by editing the conditional formula manually
Can you use BiffViewer (org.apache.poi.hssf.dev.BiffViewer) to spot what's different between POI and Excel? It's mostly the conditional formatting records that'll be of interest, are they set up differently? Are the formulas on them different?
Created attachment 26914 [details] Biff viewer output for POI file
Created attachment 26915 [details] Biff viewer output for manually created Excel file
I think the interesting part is this one: [CFHEADER] .id = 1b0 .numCF = 1 - .needRecalc = false + .needRecalc = true .enclosingCellRange= org.apache.poi.ss.util.CellRangeAddress [D3:D6] .cfranges=[org.apache.poi.ss.util.CellRangeAddress [D3:D6]] [/CFHEADER]
You should be able to get at the CFHeaderRecord from the sheet. Can you try changing the recalc flag on that, and seeing if that fixes your file? (If so we can alter the default value)
Created attachment 26919 [details] File, which has been created by POI using the Java class Updated file, which has been created with needRecalc set to true. Still, the problem stays the same.
Created attachment 26920 [details] Biff viewer output for POI file Updated Biff viewer output for file generated by POI.
I have modified my program to set needRecalc=true. Still, the problem remains the same.
Created attachment 26921 [details] Test class, which is used to create ConditionalFormula.xls Updated Java class to recreate Excel file using POI, with needRecalc=true.
Created attachment 26950 [details] File, which has been created by POI using the Java class Found another difference: The presence of a RECALCID record. Doesn't help either. Attaching updated Java class, which creates the RECALCID record as well.
Created attachment 26951 [details] Biff viewer output for POI file
Created attachment 26952 [details] File, which has been created by POI using the Java class
Created attachment 26953 [details] Test class, which is used to create ConditionalFormula.xls
I can't see anything obviously different in the cells or the CF stuff between the Excel and POI files The only thing I can see that might be different is the PLS block, which we don't currently seem to handle fully. You might want to look at [MS-XLS].pdf and see if that mentions anything about Conditional Formatting and that record?