View Issue Details

IDProjectCategoryView StatusLast Update
0038762Lazarus CCRFPSpreadsheetpublic2021-04-16 22:39
ReporterMAndreato Assigned Towp  
PrioritynormalSeverityminorReproducibilityalways
Status resolvedResolutionwon't fix 
Platformx86-64OSWindows 
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.
Widgetset
Attached Files

Activities

wp

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.

MAndreato

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)

wp

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

<sheetData>
  <row r="1" spans="1:1" x14ac:dyDescent="0.25">
    <c r="A1" t="str">
      <f>_xlfn.TEXTJOIN(",",TRUE,a_b!A1:A2)</f>
      <v>a,b</v>
    </c>
  </row>
  </sheetData>

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;

uses
  sysUtils, fpspreadsheet, fpstypes, xlsxooxml;

var
  wb: TsWorkbook;

begin
  wb := TsWorkbook.Create;
  try
    wb.Options := [boReadFormulas, boAutoCalc];
    try
      wb.ReadFromFile('test.xlsx');
      if wb.ErrorMsg <> '' then
        WriteLn(wb.ErrorMsg);
    except
      on E: Exception do
        WriteLn(E.Message);
    end;
  finally
    wb.Free;
  end;
end.

Beyond that I am not planning to further fix this issue because it was never intended to support ALL of Excel's formulas.

MAndreato

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