[FPSpreadsheet] Link to a cell in another worksheet. not saved

[veb86]

Hello. I open a file containing a cell that references a cell on another worksheet, and save it under a different name. Having opened the saved file, I see that the link to a cell in another sheet disappears.

Code: Pascal  [Select][+][-]

1. MyWorkbook.ReadFromFile('d:\4.xlsx', sfOOXML);
2. MyWorkbook.WriteToFile('d:\444.xlsx', sfOOXML,true);  
3.  

[wp]

Fixed; the formula parser was going crazy when seeing UTF8 in the sheetname.

Please test the new version in SVN (https://sourceforge.net/p/lazarus-ccr/svn/HEAD/tree/components/fpspreadsheet/).

Or, if you update to the new OPM version 1.16 which was released yesterday, it should be possible to replace the fpsexprparser unit (it is in the source/common folder) by the one from https://sourceforge.net/p/lazarus-ccr/svn/HEAD/tree/components/fpspreadsheet/source/common/fpsexprparser.pas; I don't know whether this works with older fpspreadsheet versions, too.

[veb86]

Fixed; the formula parser was going crazy when seeing UTF8 in the sheetname.

Please test the new version in SVN (https://sourceforge.net/p/lazarus-ccr/svn/HEAD/tree/components/fpspreadsheet/).

Or, if you update to the new OPM version 1.16 which was released yesterday, it should be possible to replace the fpsexprparser unit (it is in the source/common folder) by the one from https://sourceforge.net/p/lazarus-ccr/svn/HEAD/tree/components/fpspreadsheet/source/common/fpsexprparser.pas; I don't know whether this works with older fpspreadsheet versions, too.

I've updated. But the problem did not go away
Below is a screenshot of the code working. The formula link to another worksheet is erased.

[wp]

Strange...

Please open unit fpsexprparser (in folder source/common of the FPSpreadsheet installation) and search for function TsExpressionScanner.DoTestExcelSheetName. If you don't find it something went wrong with your update process.

Why does your screenshot show the formula "=sheet1!B5"? In the test file that you posted in the first post the formula is "=Лист1!B5".

[veb86]

Strange...

Please open unit fpsexprparser (in folder source/common of the FPSpreadsheet installation) and search for function TsExpressionScanner.DoTestExcelSheetName. If you don't find it something went wrong with your update process.

Why does your screenshot show the formula "=sheet1!B5"? In the test file that you posted in the first post the formula is "=Лист1!B5".

I was doing a test and changed the name of the sheets to Latin and didn't pay attention to it. Basically it will be like this "=Лист1!B5".

I am attaching a screenshot of your function. The update was successful

[wp]

Ah maybe something different: Did you add the workbook option to read formulas? Otherwise formulas are ignored altogether...

This is my test program with which I tested my commit:

Code: Pascal  [Select][+][-]

1. program test;
2. uses
3.   FPSpreadsheet, FPSTypes, FPSExprParser, XLSXOOXML, FPSOpenDocument;
4. const
5.   FILE_NAME = '4.xlsx';
6. //  FILE_NAME = '4-R1C1.xlsx';
7. //  FILE_NAME = '4.ods';
8. //  FILE_NAME = 'link_to_other_sheet.xlsx';
9. var
10.   b: TsWorkbook;
11.   sh: TsWorksheet;
12.   f: PsFormula;
13. begin
14.   b := TsWorkbook.Create;
15.   try
16.     b.Options := [boReadFormulas];
17.     b.ReadFromFile(FILE_NAME);
18.     sh := b.GetFirstWorksheet;
19.     for f in sh.Formulas do
20.       WriteLn(f^.Text);
21.     b.WriteToFile('444.xlsx', sfOOXML, true);
22.   finally
23.     b.Free;
24.   end;
25.  
26.   ReadLn;
27. end.

[veb86]

Ah maybe something different: Did you add the workbook option to read formulas? Otherwise formulas are ignored altogether...

This is my test program with which I tested my commit:

Code: Pascal  [Select][+][-]

1. program test;
2. uses
3.   FPSpreadsheet, FPSTypes, FPSExprParser, XLSXOOXML, FPSOpenDocument;
4. const
5.   FILE_NAME = '4.xlsx';
6. //  FILE_NAME = '4-R1C1.xlsx';
7. //  FILE_NAME = '4.ods';
8. //  FILE_NAME = 'link_to_other_sheet.xlsx';
9. var
10.   b: TsWorkbook;
11.   sh: TsWorksheet;
12.   f: PsFormula;
13. begin
14.   b := TsWorkbook.Create;
15.   try
16.     b.Options := [boReadFormulas];
17.     b.ReadFromFile(FILE_NAME);
18.     sh := b.GetFirstWorksheet;
19.     for f in sh.Formulas do
20.       WriteLn(f^.Text);
21.     b.WriteToFile('444.xlsx', sfOOXML, true);
22.   finally
23.     b.Free;
24.   end;
25.  
26.   ReadLn;
27. end.

It worked. Thanks! b.Options := [boReadFormulas]; - was missing from my code. When I created the example, I thought that this was not important for saving.

There is one more bug, it’s not critical for me, but still. Check it out. It corrupts the file. If the sheets are named like this <light><panel>. I made special codenames for work.

there is a new file in the archive

[wp]

Fixed in SVN.

[veb86]

Fixed in SVN.

It worked. Thank you!!!

Small questions.
Data Validation Worksheet: List. Name ranges. Are these difficult questions? Are they on the roadmap?
Now I can do without them. Just very interesting

[wp]

Small questions.
Data Validation Worksheet: List. Name ranges. Are these difficult questions? Are they on the roadmap?

I don't know. Once I was close to starting with named cells and ranges, but there was some complication which demotivated me, and I moved this activity aside and forgot it. Glad that there is no roadmap for me...