View Issue Details

IDProjectCategoryView StatusLast Update
0038762Lazarus CCRFPSpreadsheetpublic2021-04-16 22:39
ReporterMAndreato Assigned Towp  
Status resolvedResolutionwon't fix 
Summary0038762: Formulas with references to sheet names with underscore
DescriptionFormulas with references to sheet names with underscore are well managed on a TsWorksheetGrid but not if TsWorkbookSource is used.
Steps To ReproduceCreate 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: "_"
TagsNo tags attached.
Attached Files



2021-04-16 12:33

developer   ~0130403

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.


2021-04-16 12:52

reporter   ~0130404

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)


2021-04-16 17:28

developer   ~0130408

Last edited: 2021-04-16 17:29

View 2 revisions

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"

program cmdline_project;

  sysUtils, fpspreadsheet, fpstypes, xlsxooxml;

  wb: TsWorkbook;

  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.


2021-04-16 22:39

reporter   ~0130416

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!

Issue History

Date Modified Username Field Change
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