View Issue Details
|ID||Project||Category||View Status||Date Submitted||Last Update|
|0038762||Lazarus CCR||FPSpreadsheet||public||2021-04-16 09:17||2021-04-16 22:39|
|Summary||0038762: Formulas with references to sheet names with underscore|
|Description||Formulas with references to sheet names with underscore are well managed on a TsWorksheetGrid but not if TsWorkbookSource is used.|
|Steps To Reproduce||Create an Excel (XLSX) document and fill in the following cells of the Sheet1:|
- A1: "a"
- B1: "b"
Rename Sheet1 as "a_b" (without quotes).
Add a new sheet and fill its A1 with the formula: =TEXTJOIN(",";TRUE;a_b!A1:A2)
On Form1, drop a TsWorksheetGrid, set ReadFormulas to True and WorkbookSource.Filename to filename/path of the Excel document --> it is OK.
Add a TsWorkbookSource and connect sWorksheetGrid1.WorkbookSource to it, then set sWorkbookSource1.Filename to the document --> Formula error in cell ... Unknown character at pos 1: "_"
|Tags||No tags attached.|
AFAIK - and I checked unit fpsfunc in which the supported formulas are implemented - fpspreadsheet does not support the TEXTJOIN() formula, and looking for it in the internet I see that it belongs to Excel365 and Excel2019 to which I do not have access. Therefore I cannot test this case. Because when I enter this formula in my Excel 2016 it seems to be stored as plain text, and this is a completely different situation. Could you please post a simple file containing this formula so that I can investigate further?
When I replace the TEXTJOIN() by a straightforward "=a_b!A1&a_b!B1" then I notice that fpspreadsheet reads and analyzes the formula correctly. This tells me that the underscore in the sheetname is not a problem.
See the attached test.xlsx. However I've to specify that I'm using the italian localization of Excel (I don't know if it does matter), so I translated "TESTO.UNISCI" to "TEXTJOIN" for testcase replication purposes.
test.xlsx (9,275 bytes)
Thanks for the file. Peeking into the xml structure of the file I see that the formula cell contains
<row r="1" spans="1:1" x14ac:dyDescent="0.25">
<c r="A1" t="str">
Do you see the "_xlfn." before the TEXTJOIN? It is the leading '_' which create the error message, not the one in the sheet name.
To get a decent error message I added the underscore to the list of allowed identifier characters. The following code now displays the error message "Formula error in cell "Foglio1!A1": Unknown identifier: _XLFN.TEXTJOIN"
sysUtils, fpspreadsheet, fpstypes, xlsxooxml;
wb := TsWorkbook.Create;
wb.Options := [boReadFormulas, boAutoCalc];
if wb.ErrorMsg <> '' then
on E: Exception do
Beyond that I am not planning to further fix this issue because it was never intended to support ALL of Excel's formulas.
Googling for "_XLFN" it seems a prefix for functions added on most recent versions of Excel; mine is 2019.
If TEXTJOIN is not supported by FPSpreadsheet, I'll try to work around it.
Thanks anyway for this great tool!
|2021-04-16 09:17||MAndreato||New Issue|
|2021-04-16 11:58||wp||Assigned To||=> wp|
|2021-04-16 11:58||wp||Status||new => assigned|
|2021-04-16 12:33||wp||Note Added: 0130403|
|2021-04-16 12:52||MAndreato||Note Added: 0130404|
|2021-04-16 12:52||MAndreato||File Added: test.xlsx|
|2021-04-16 17:28||wp||Note Added: 0130408|
|2021-04-16 17:29||wp||Note Edited: 0130408||View Revisions|
|2021-04-16 17:29||wp||Status||assigned => resolved|
|2021-04-16 17:29||wp||Resolution||open => won't fix|
|2021-04-16 22:39||MAndreato||Note Added: 0130416|