Workbook.ReadFromFile
lazarus / 61-Beginners

----------------------------------------------------------------------
seghele0 · 2022-03-04 14:15

I'm looking for the possibility (in this procedure) to open the
worksheet in LibreOffice-Calc.
Quoteworkbook.WriteToFile('Test.xlsx', sfOOXML, true);The compiler
doesn't give error, but LibreOffice doesn't open.

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    
 ......    workbook.WriteToFile('Test.xlsx', sfOOXML, true);    //
this (sfOOXML)is XLSX format.  finally   
Workbook.ReadFromFile('Test.xlsx', sfOOXML);    // Destroy the
workbook after usage.    workbook.Free;  end;

----------------------------------------------------------------------
wp · 2022-03-04 14:32

Please post a full compilable project (but please remove everything
which is not needed to demonstrate the issue).

----------------------------------------------------------------------
seghele0 · 2022-03-04 14:49

Thank you in advance.

----------------------------------------------------------------------
paweld · 2022-03-04 14:56

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 uses 
LCLIntf; ......    workbook.WriteToFile('Test.xlsx', sfOOXML, true); 
 // this (sfOOXML)is XLSX format.  finally   
OpenDocument('Test.xlsx');    // Destroy the workbook after usage.   
workbook.Free;  end;

----------------------------------------------------------------------
seghele0 · 2022-03-04 15:32

This makes no difference.
Still NO errors.
LibreOffice doesn't open.

----------------------------------------------------------------------
wp · 2022-03-04 16:45

This is not a full compilable project... But anyway, I recreated the
missing .lpr file and took the .pas from the previous post, compiled
and ran your demo. Opened the generated file in Excel - correct.
Opened the file in LibreOffice Calc - correct. So, what is the
problem? Do you want to open LibreOffice Calc from your application?
Is this the reason for the mysterious
"Workbook.ReadFromFile('Test.xlsx', sfOOXML);" at the end? This line
does not open LibreOffice, it simply loads the created file into your
workbook variable (where it still was...). Use the RunCommand
procedure to execute an external program:

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 ...   
RunCommand('C:\Program Files\LibreOffice\program\scalc.exe',
['Test.xlsx'], outputStr);  // requires unit "process" in "uses". 
finally    // Destroy the workbook after usage.    workbook.Free; 
end;
As you can see I would not put it into the "finally" section because
then Calc will always be opened even if the file was not created
successfully. A better place is before the "finally" because this is
reached only when the file was created and written successfully.

----------------------------------------------------------------------
seghele0 · 2022-03-05 08:47

WP, there's probably still something missing.
This is not for my programming level, sorry.
Should the full path be indicated or just 'calc.exe'?

ERROR: identifier not found "outputStr"

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 unit
Unit1;{$mode objfpc}{$H+}interfaceuses  Classes, SysUtils, Forms,
Controls, Graphics, Dialogs, Grids,  ExtCtrls, StdCtrls, Buttons, 
fpSpreadsheet, fpsTypes, xlsxOOXML, DateUtils,[b]  Process;[/b]type  
{ TForm1 }   TForm1 = class(TForm)    Button1: TButton;    Panel1:
TPanel;    StringGrid1: TStringGrid;    procedure Button1Click(Sender:
TObject);   private  public   end; var  Form1:
TForm1; implementation{$R *.lfm} function MyStrToFloat(s: String):
Double;var  fs: TFormatSettings;begin  fs := FormatSettings; 
fs.DecimalSeparator := '.';  if not TryStrToFloat(s, Result, fs) then 
begin    fs.DecimalSeparator := ',';    Result := StrToFloat(s, fs); 
end;end; function MyStrToDate(s: String): TDate;begin  Result :=
ScanDateTime('yyyy"/"mm"/"dd', s);end; function MyStrToCurr(s:
String): Double;begin  Result := MyStrToFloat(Copy(s, 1,
Length(s)-2));end; procedure TForm1.Button1Click(Sender: TObject);var 
workbook: TsWorkbook;  worksheet: TsWorksheet;  r, c: Integer;begin 
// Create a workbook  workbook := TsWorkbook.Create;  try    // Add a
worksheet to the workbook    worksheet :=
workbook.AddWorksheet('Export from grid');    // Write the title row 
 for c := 0 to StringGrid1.ColCount-1 do      worksheet.WriteText(0,
c, StringGrid1.Cells[c, 0]);    // Write the data cells    for r := 1
to StringGrid1.RowCount-1 do    begin      for c := 0 to
StringGrid1.ColCount-1 do        case c of          0:
worksheet.WriteNumber(r, c,                 
StrToInt(StringGrid1.cells[c, r]), nfFixed, 0);          1:
worksheet.WriteNumber(r, c,                 
MyStrToFloat(StringGrid1.Cells[c, r]), nfGeneral);          2:
worksheet.WriteText(r, c,                  StringGrid1.Cells[c, r]); 
       3: worksheet.WriteDateTime(r, c,                 
MyStrToDate(StringGrid1.Cells[c, r]), nfShortDate);          4:
worksheet.WriteCurrency(r, c,                 
MyStrToCurr(StringGrid1.Cells[c, r]), nfCurrency, 2, '$');       
end;    end;    // Save the workbook as xlsx file   
workbook.SetDefaultFont('Arial',13);    //
worksheet.DefaultRowHeight:= 1.3; => depricated (no longer in use)   
worksheet.WriteDefaultRowHeight(1.3, suLines);   
//Worksheet.WriteDefaultColWidth(10, suLines); not needed !!!!   
//****************************************   
Worksheet.WriteHorAlignment(0,0,haCenter);   
Worksheet.WriteHorAlignment(0,1,haCenter);   
Worksheet.WriteHorAlignment(0,2,haCenter);   
Worksheet.WriteHorAlignment(0,3,haCenter);   
Worksheet.WriteHorAlignment(0,4,haCenter);   
//****************************************   
Worksheet.WriteVertAlignment(0,0,vaCenter);   
Worksheet.WriteVertAlignment(0,1,vaCenter);   
Worksheet.WriteVertAlignment(0,2,vaCenter);   
Worksheet.WriteVertAlignment(0,3,vaCenter);   
Worksheet.WriteVertAlignment(0,4,vaCenter);   
//****************************************    for r := 0 to
StringGrid1.RowCount -1 do    worksheet.WriteHorAlignment(r, 1,
haCenter);    for r := 0 to StringGrid1.RowCount -1 do   
worksheet.WriteHorAlignment(r, 2, haCenter);   
Worksheet.PageLayout.Options := Worksheet.PageLayout.Options +
[poFitPages];    Worksheet.PageLayout.FitHeightToPages := 0;  // use
as many pages as needed    //**************************************** 
  for r := 0 to StringGrid1.RowCount -1 do   
Worksheet.WriteBorders(r, 0, [cbEast,cbWest,cbNorth,cbSouth]); // A1:
row 0, column 0    for r := 0 to StringGrid1.RowCount -1 do   
Worksheet.WriteBorders(r, 1, [cbEast,cbWest,cbNorth, cbSouth]); // B1:
row 0, column 1    for r := 0 to StringGrid1.RowCount -1 do   
Worksheet.WriteBorders(r, 2, [cbEast,cbWest,cbNorth, cbSouth]); //   
for r := 0 to StringGrid1.RowCount -1 do    Worksheet.WriteBorders(r,
3, [cbEast,cbWest,cbNorth, cbSouth]); //    for r := 0 to
StringGrid1.RowCount -1 do    Worksheet.WriteBorders(r, 4,
[cbEast,cbWest,cbNorth, cbSouth]); //   
//****************************************   
workbook.WriteToFile('Test.xlsx', sfOOXML, true);    // this
(sfOOXML)is XLSX format. [b]  
RunCommand('C:\Windows\system32\calc.exe', ['Test.xlsx'],
outputStr);[/b]    // requires unit "process" in "uses".  finally   
// Destroy the workbook after usage.    workbook.Free;  end;end; end.

----------------------------------------------------------------------
wp · 2022-03-05 10:24

Quote from: seghele0 on March 05, 2022, 09:47:25 amThis is not for my
programming level, sorry.
Nonsense. It's easy, really. Did you search for "Lazarus RunCommand"?
At the top of the hit list, there is https://www.freepascal.org/docs-
html/fcl/process/runcommand.html, and this shows you that the last
parameter, outputstring, of RunCommand  is "String containing the
output of the process". You only must declare it:
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 procedure
TForm1.Button1Click(Sender: TObject);var  ...  outputstr:
String;begin  ...    workbook.WriteToFile('Test.xlsx', sfOOXML,
true);    // this (sfOOXML)is XLSX format.   RunCommand('C:\Program
Files\LibreOffice\program\scalc.exe', ['Test.xlsx'], outputStr); 
finally    // Destroy the workbook after usage.    workbook.Free; 
end;end;
Quote from: seghele0 on March 05, 2022, 09:47:25 amShould the full
path be indicated or just 'calc.exe'? Why do you want to pass the
generated xlsx file to the Windows calculator? This program has
nothing to do with spreadsheet files. Your wrote that you wanted to
open the file in LibreOffice Calc. The spreadsheet application of this
office suite named "scalc.exe" (note the leading "s"). Search for it
on your HD, it will probably be in the same path that I specified.
Then use this path in the RunCommand procedure. You probably need the
full path unless you had added the path to the LibreOffice binaries to
the Windows PATH setting. (Just try it without the path and see what
happens - you cannot damage anything, just revert if it does not
work).

----------------------------------------------------------------------
seghele0 · 2022-03-05 14:10

Thank you very much for your patience.
Everything works now.

At the age of 72 things don't always go the way I want them to.

I love this forum.