[FPSpreadsheet] Link to a cell in another worksheet. not saved lazarus / 49-FPSpreadsheet ---------------------------------------------------------------------- veb86 · 2023-10-08 22:35 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i MyWorkbook.ReadFromFile('d:\4.xlsx', sfOOXML);MyWorkbook.WriteToFile('d:\444.xlsx', sfOOXML,true); ---------------------------------------------------------------------- wp · 2023-10-09 17:52 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/fpsprea dsheet/source/common/fpsexprparser.pas; I don't know whether this works with older fpspreadsheet versions, too. ---------------------------------------------------------------------- veb86 · 2023-10-09 20:40 Quote from: wp on October 09, 2023, 07:52:17 pmFixed; 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/fpsprea dsheet/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 · 2023-10-09 21:18 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 · 2023-10-09 21:37 Quote from: wp on October 09, 2023, 11:18:50 pmStrange... 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 · 2023-10-09 21:57 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i program test;uses  FPSpreadsheet, FPSTypes, FPSExprParser, XLSXOOXML, FPSOpenDocument;const  FILE_NAME = '4.xlsx';//  FILE_NAME = '4-R1C1.xlsx';//  FILE_NAME = '4.ods';//  FILE_NAME = 'link_to_other_sheet.xlsx';var  b: TsWorkbook;  sh: TsWorksheet;  f: PsFormula;begin  b := TsWorkbook.Create;  try    b.Options := [boReadFormulas];    b.ReadFromFile(FILE_NAME);    sh := b.GetFirstWorksheet;    for f in sh.Formulas do      WriteLn(f^.Text);    b.WriteToFile('444.xlsx', sfOOXML, true);  finally    b.Free;  end;   ReadLn;end. ---------------------------------------------------------------------- veb86 · 2023-10-09 22:21 Quote from: wp on October 09, 2023, 11:57:45 pmAh 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i program test;uses  FPSpreadsheet, FPSTypes, FPSExprParser, XLSXOOXML, FPSOpenDocument;const  FILE_NAME = '4.xlsx';//  FILE_NAME = '4-R1C1.xlsx';//  FILE_NAME = '4.ods';//  FILE_NAME = 'link_to_other_sheet.xlsx';var  b: TsWorkbook;  sh: TsWorksheet;  f: PsFormula;begin  b := TsWorkbook.Create;  try    b.Options := [boReadFormulas];    b.ReadFromFile(FILE_NAME);    sh := b.GetFirstWorksheet;    for f in sh.Formulas do      WriteLn(f^.Text);    b.WriteToFile('444.xlsx', sfOOXML, true);  finally    b.Free;  end;   ReadLn;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 . I made special codenames for work. there is a new file in the archive ---------------------------------------------------------------------- wp · 2023-10-09 23:19 Fixed in SVN. ---------------------------------------------------------------------- veb86 · 2023-10-10 08:21 Quote from: wp on October 10, 2023, 01:19:22 amFixed 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 · 2023-10-10 11:03 Quote from: veb86 on October 10, 2023, 10:21:48 amSmall 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...