Bug when opening xls with formulas

[rwkstgtd]

Hi there!

I'm trying to parse hundreds of old xls-Files to just extract numbers and text and do some stuff with the result.

Therefore I'm reading allways without boReadFormulas.

In most cases this works fine, original formulas beeing read as cells with type cctNumber. Value can then be accessed throug cell^.Numbervalue.

For some formula cells with have exactly the same formatting and similar contents as the rest of the formulas (no fancy stuff like conditional formats or anything) this fails without reason.

The result is still a cell with type cctFormula (boReadFormulas being turned off!). The formula can be accessed through ReadFormulaAsString(Cell) (resulting e.g. in "SUM(B7:M7)"). A call to ReadNumericValue(Cell,DValue) gives false (=no result). I somehow need to get the number value. Furthermore I suspect this might be a bug.

I would be happy to provide a sample file, but I can't size them down to the problem area, as altering the files in Excel 2019 solves the problem, even when saving back to xls. So the only way would be to provide a rather unhandy original file. Resaving all old files is no solution by the way, as we're looking at aprox. 500 files.

Maybe a solution would be to automatically convert all files to xlsx via FPSpreadsheet, but this is not a one time only action and I rather would implement a solution that is not just a work around, that maybe for future versions of FPSpreadsheet would be complete nonsense.

Thanks for any viable solution.

Kind regards
R. Köhler

[wp]

Thanks for this report. But without a file there's not much which I can do...

You could set a breakpoint on the formula calculation method (somewhere in fpspreadsheet.pas, TsWorksheet.CalcFormula or so) and check the call stack from where this is called in spite of the ignore formula option.

[rwkstgtd]

Sample is attached... If you care to look at it. Sorry it's all in German.

Problem exists in cell Summen!N7, that is worksheet 9, row 6, column 13 (zero based). Value after reading should be numerical 22.75, but is formula =SUM(B7:M7).

As mentioned I can't do any cleanup (macros, locked sheets,...) as this would break the example, sorry.

Thanks!

Roland Köhler

[wp]

BIFFExplorer shows that all cell results are present. But the faulty cell  is the first cell of a range with shared-formula records, a peculiarity of the BIFF data format. This indicates that your issue very probably is a bug. Let's see what I can do (although I am a bit clueless at the moment...).

[wp]

Please check out the new revision. I removed the cctFormula CellContentType which is a left-over from the old formula engine and erases the cell content of the first cell of a shared formula range. I hope this does not cause any other issues, all unit tests at least are still passed.

[rwkstgtd]

Hi there!

Wow that was fast!

If done a quick testing and for now it seems fine! I'm really happy with this! Have to implement a few things on my main project, so I can't compile this right now, but the separate testing project works perfect.

Thank you so much, above all for the quick solution. It's really great work you do with FPSpreadsheet.

Cheers and have a nice weekend,
Roland Köhler