create a new worksheet (file) based on a template worksheet
lazarus / 49-FPSpreadsheet

----------------------------------------------------------------------
talitaedwiges · 2022-11-10 13:05

Hello! I need to create a new worksheet (file) based on a template
worksheet. I'm using CopyWorksheetFrom it worked, but the cell values
​​with formulas and links are not copied.

----------------------------------------------------------------------
wp · 2022-11-10 15:16

Please be more specific. The following code is working:

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
Project1; uses  fpspreadsheet, xlsxooxml; var  workbook: TsWorkbook; 
sheet1, sheet2: TsWorksheet;begin  workbook := TsWorkBook.Create; 
try    sheet1 := workbook.AddWorksheet('Original');   
sheet1.WriteNumber(0, 0, 1);    sheet1.WriteFormula(0, 1, 'A1');   
sheet1.WriteText(5, 0, 'Jump to cell B1');    sheet1.WriteHyperlink(5,
0, '#B1');     sheet2 := workbook.CopyWorksheetFrom(sheet1, true);   
sheet2.Name := 'Copy';     workbook.WriteToFile('test.xlsx', true); 
finally    workbook.Free;  end;end.
And, please, don't double-post.

----------------------------------------------------------------------
talitaedwiges · 2022-11-10 17:02

Copying occurs correctly but as cells containing formulas do not come.

 MyWorkbook:= TsWorkbook.Create;
  MyWorkbook2:= TsWorkbook.Create;
  TRY
    MyWorkbook.ReadFromFile('...\Modelo\Modelo Fluxo de Caixa.xlsx');

    for i:=0 to MyWorkbook.GetWorksheetCount-1 do
    begin
     MyWorksheet:= MyWorkbook.GetWorksheetByIndex(i);
     MyWorkbook2.CopyWorksheetFrom(MyWorksheet,false);
    end;

    t := Now;
    MyWorkbook2.WriteToFile('...\Fluxo de Caixa.xlsx', sfOOXML, true);
    t := Now - t;
    WriteLn(Format('Execution time: %.3f sec', [t*24*60*60]));
  finally
    MyWorkbook.Free;
    MyWorkbook2.Free;      


follows the model I'm trying to clone

----------------------------------------------------------------------
wp · 2022-11-10 19:29

Please check out the current version in the SVN repository (or
download the zipped snapshot from https://sourceforge.net/p/lazarus-
ccr/svn/HEAD/tree/components/fpspreadsheet/). It fixes handling of
hyperlinks when the referenced sheetname contains spaces.

Some formulas in your file are not supported by fpspreadsheet (INDEX,
LARGE, IFERROR), and it also does not support embedded comboboxes as
well as charts. You will miss these features when saving the workbook
copied from the template...