fpSpread - unsupported formula

[kjteng]

I have an excel workbooks (contains 100 sheets) which can be opened if I exclude boReadFormulas from the workbook options i.e. the workbook contains formulas which are not supported by fpSpread.  I tried to find  the problematic cell by repeatedly delete cells in Excel and then re-open it the file in fpSpread (with the use of macro in excel), After trying for about an hour only I managed to locate the cells which contain invlaid function 'ROW()'.
.

My question: Is there a better way to locate the cell(s) which contains invalid formula in the fpSpread?

[kjteng]

After further test, I found that normally fpspread will read the value if a cell contains an invalid or undefined formula.
The problem mention above may be due to the some abonormal behavious of Excel which can be seen in the attached file:

1.  rowfunc.xlsx - the formula was typde in manually in A1, A2, A3. This file can be opened in fpspread
2.  rowfunc1.xlsx -the formula was typed in manually in A1 then copy to A2 and A3. This file cannot be read properly in fpspread
 


There is no problem if I use LibreOffice to open rowfunc1.xlsx. Save the file again in Libreoffice, file size reduced to from 11k to 7k bytes and fpspread can read the file after that!

[wp]

Is there a better way to locate the cell(s) which contains invalid formula in the fpSpread?

After loading you should check whether the string Workbook.ErrorMsg is empty or not. When you display it in a messagebox you get a list of all errors that occurred during reading. Unfortunately, formula errors were not logged so far. In the new revision the error list contains also formula errors along with sheet name and cell address where they occured.

invalid function 'ROW()'.

Since this is a very simple function I added it to the built-in formulas, along with COLUMN(). While it works nicely for xlsx and ods files there is a problem for xls files (the old binary Excel files of Excel97): This is because the argument of these functions is a "cell reference", but fpspreadsheet does not support this type, it writes it with the code of a "cell value". Therefore, Excel has problems with these formulas in xls files; it can decode them, but cannot calculate them and thus displays the result #VALUE in the cell. However, since the formula is correctly decoded you can go to the edit bar and just press ENTER.

[kjteng]

Tested with IRR functions. Works. Now it reads the value if the formula is undefined and Workbook.ErrorMsg show the info that I need.
Good work. Thank you very much.

[wp]

I forgot to mention: There is one discrepancy of the ROW() and COLUMN() formulas in relation to Excel. Excel allows to call these function without an argument to return the row/colum of the cell with the formula. This is not allowed in fpspreadsheet which returns an argument error in this case.