Workbook.ReadFromFile

[seghele0]

I'm looking for the possibility (in this procedure) to open the worksheet in LibreOffice-Calc.

workbook.WriteToFile('Test.xlsx', sfOOXML, true);

The compiler doesn't give error, but LibreOffice doesn't open.

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

1.    
2.  ......
3.     workbook.WriteToFile('Test.xlsx', sfOOXML, true);
4.     // this (sfOOXML)is XLSX format.
5.   finally
6.     Workbook.ReadFromFile('Test.xlsx', sfOOXML);
7.     // Destroy the workbook after usage.
8.     workbook.Free;
9.   end;              

[wp]

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

[seghele0]

Thank you in advance.

[paweld]

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

1. uses
2.   LCLIntf;
3.  
4. ......
5.     workbook.WriteToFile('Test.xlsx', sfOOXML, true);
6.     // this (sfOOXML)is XLSX format.
7.   finally
8.     OpenDocument('Test.xlsx');
9.     // Destroy the workbook after usage.
10.     workbook.Free;
11.   end;    

[seghele0]

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

[wp]

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][+][-]

1. ...
2.     RunCommand('C:\Program Files\LibreOffice\program\scalc.exe', ['Test.xlsx'], outputStr);  // requires unit "process" in "uses".
3.   finally
4.     // Destroy the workbook after usage.
5.     workbook.Free;
6.   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]

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][+][-]

1. unit Unit1;
2. {$mode objfpc}{$H+}
3. interface
4. uses
5.   Classes, SysUtils, Forms, Controls, Graphics, Dialogs, Grids,
6.   ExtCtrls, StdCtrls, Buttons,
7.   fpSpreadsheet, fpsTypes, xlsxOOXML, DateUtils,
8. [b]  Process;[/b]
9. type
10.  
11.   { TForm1 }
12.  
13.   TForm1 = class(TForm)
14.     Button1: TButton;
15.     Panel1: TPanel;
16.     StringGrid1: TStringGrid;
17.     procedure Button1Click(Sender: TObject);
18.  
19.   private
20.   public
21.  
22.   end;
23.  
24. var
25.   Form1: TForm1;
26.  
27. implementation
28. {$R *.lfm}
29.  
30. function MyStrToFloat(s: String): Double;
31. var
32.   fs: TFormatSettings;
33. begin
34.   fs := FormatSettings;
35.   fs.DecimalSeparator := '.';
36.   if not TryStrToFloat(s, Result, fs) then
37.   begin
38.     fs.DecimalSeparator := ',';
39.     Result := StrToFloat(s, fs);
40.   end;
41. end;
42.  
43. function MyStrToDate(s: String): TDate;
44. begin
45.   Result := ScanDateTime('yyyy"/"mm"/"dd', s);
46. end;
47.  
48. function MyStrToCurr(s: String): Double;
49. begin
50.   Result := MyStrToFloat(Copy(s, 1, Length(s)-2));
51. end;
52.  
53. procedure TForm1.Button1Click(Sender: TObject);
54. var
55.   workbook: TsWorkbook;
56.   worksheet: TsWorksheet;
57.   r, c: Integer;
58. begin
59.   // Create a workbook
60.   workbook := TsWorkbook.Create;
61.   try
62.     // Add a worksheet to the workbook
63.     worksheet := workbook.AddWorksheet('Export from grid');
64.     // Write the title row
65.     for c := 0 to StringGrid1.ColCount-1 do
66.       worksheet.WriteText(0, c, StringGrid1.Cells[c, 0]);
67.     // Write the data cells
68.     for r := 1 to StringGrid1.RowCount-1 do
69.     begin
70.       for c := 0 to StringGrid1.ColCount-1 do
71.         case c of
72.           0: worksheet.WriteNumber(r, c,
73.                   StrToInt(StringGrid1.cells[c, r]), nfFixed, 0);
74.           1: worksheet.WriteNumber(r, c,
75.                   MyStrToFloat(StringGrid1.Cells[c, r]), nfGeneral);
76.           2: worksheet.WriteText(r, c,
77.                   StringGrid1.Cells[c, r]);
78.           3: worksheet.WriteDateTime(r, c,
79.                   MyStrToDate(StringGrid1.Cells[c, r]), nfShortDate);
80.           4: worksheet.WriteCurrency(r, c,
81.                   MyStrToCurr(StringGrid1.Cells[c, r]), nfCurrency, 2, '$');
82.         end;
83.     end;
84.     // Save the workbook as xlsx file
85.     workbook.SetDefaultFont('Arial',13);
86.     // worksheet.DefaultRowHeight:= 1.3; => depricated (no longer in use)
87.     worksheet.WriteDefaultRowHeight(1.3, suLines);
88.     //Worksheet.WriteDefaultColWidth(10, suLines); not needed !!!!
89.     //****************************************
90.     Worksheet.WriteHorAlignment(0,0,haCenter);
91.     Worksheet.WriteHorAlignment(0,1,haCenter);
92.     Worksheet.WriteHorAlignment(0,2,haCenter);
93.     Worksheet.WriteHorAlignment(0,3,haCenter);
94.     Worksheet.WriteHorAlignment(0,4,haCenter);
95.     //****************************************
96.     Worksheet.WriteVertAlignment(0,0,vaCenter);
97.     Worksheet.WriteVertAlignment(0,1,vaCenter);
98.     Worksheet.WriteVertAlignment(0,2,vaCenter);
99.     Worksheet.WriteVertAlignment(0,3,vaCenter);
100.     Worksheet.WriteVertAlignment(0,4,vaCenter);
101.     //****************************************
102.     for r := 0 to StringGrid1.RowCount -1 do
103.     worksheet.WriteHorAlignment(r, 1, haCenter);
104.     for r := 0 to StringGrid1.RowCount -1 do
105.     worksheet.WriteHorAlignment(r, 2, haCenter);
106.     Worksheet.PageLayout.Options := Worksheet.PageLayout.Options + [poFitPages];
107.     Worksheet.PageLayout.FitHeightToPages := 0;  // use as many pages as needed
108.     //****************************************
109.     for r := 0 to StringGrid1.RowCount -1 do
110.     Worksheet.WriteBorders(r, 0, [cbEast,cbWest,cbNorth,cbSouth]); // A1: row 0, column 0
111.     for r := 0 to StringGrid1.RowCount -1 do
112.     Worksheet.WriteBorders(r, 1, [cbEast,cbWest,cbNorth, cbSouth]); // B1: row 0, column 1
113.     for r := 0 to StringGrid1.RowCount -1 do
114.     Worksheet.WriteBorders(r, 2, [cbEast,cbWest,cbNorth, cbSouth]); //
115.     for r := 0 to StringGrid1.RowCount -1 do
116.     Worksheet.WriteBorders(r, 3, [cbEast,cbWest,cbNorth, cbSouth]); //
117.     for r := 0 to StringGrid1.RowCount -1 do
118.     Worksheet.WriteBorders(r, 4, [cbEast,cbWest,cbNorth, cbSouth]); //
119.     //****************************************
120.     workbook.WriteToFile('Test.xlsx', sfOOXML, true);
121.     // this (sfOOXML)is XLSX format.
122.  [b]   RunCommand('C:\Windows\system32\calc.exe', ['Test.xlsx'], outputStr);[/b]
123.     // requires unit "process" in "uses".
124.   finally
125.     // Destroy the workbook after usage.
126.     workbook.Free;
127.   end;
128. end;
129.  
130. end.                          

[wp]

This 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][+][-]

1. procedure TForm1.Button1Click(Sender: TObject);
2. var
3.   ...
4.   outputstr: String;
5. begin
6.   ...
7.     workbook.WriteToFile('Test.xlsx', sfOOXML, true);
8.     // this (sfOOXML)is XLSX format.
9.    RunCommand('C:\Program Files\LibreOffice\program\scalc.exe', ['Test.xlsx'], outputStr);
10.   finally
11.     // Destroy the workbook after usage.
12.     workbook.Free;
13.   end;
14. end;

Should 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]

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.