Recent

Author Topic: [FPSpreadsheet] Link to a cell in another worksheet. not saved  (Read 2659 times)

veb86

  • New Member
  • *
  • Posts: 10
[FPSpreadsheet] Link to a cell in another worksheet. not saved
« on: October 09, 2023, 12:35:50 am »
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

  • Hero Member
  • *****
  • Posts: 11995
Re: [FPSpreadsheet] Link to a cell in another worksheet. not saved
« Reply #1 on: October 09, 2023, 07:52:17 pm »
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

  • New Member
  • *
  • Posts: 10
Re: [FPSpreadsheet] Link to a cell in another worksheet. not saved
« Reply #2 on: October 09, 2023, 10:40:39 pm »
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

  • Hero Member
  • *****
  • Posts: 11995
Re: [FPSpreadsheet] Link to a cell in another worksheet. not saved
« Reply #3 on: October 09, 2023, 11:18:50 pm »
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

  • New Member
  • *
  • Posts: 10
Re: [FPSpreadsheet] Link to a cell in another worksheet. not saved
« Reply #4 on: October 09, 2023, 11:37:38 pm »
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

  • Hero Member
  • *****
  • Posts: 11995
Re: [FPSpreadsheet] Link to a cell in another worksheet. not saved
« Reply #5 on: October 09, 2023, 11:57:45 pm »
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

  • New Member
  • *
  • Posts: 10
Re: [FPSpreadsheet] Link to a cell in another worksheet. not saved
« Reply #6 on: October 10, 2023, 12:21:32 am »
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

  • Hero Member
  • *****
  • Posts: 11995
Re: [FPSpreadsheet] Link to a cell in another worksheet. not saved
« Reply #7 on: October 10, 2023, 01:19:22 am »
Fixed in SVN.

veb86

  • New Member
  • *
  • Posts: 10
Re: [FPSpreadsheet] Link to a cell in another worksheet. not saved
« Reply #8 on: October 10, 2023, 10:21:48 am »
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

  • Hero Member
  • *****
  • Posts: 11995
Re: [FPSpreadsheet] Link to a cell in another worksheet. not saved
« Reply #9 on: October 10, 2023, 01:03:17 pm »
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...

 

TinyPortal © 2005-2018