Adding a chart to a spreadsheet in a console app

[jollytall]

I have a console app that creates and manipulates spreadsheets. Once it is done, I open the spreadsheet in OpenOffice and can easily add a chart to it to see my data.
Now, I would like to simplify my life and add the chart to the spreadsheet when it is generated, so by the time I open it in OpenOffice, the chart is already there.
Is there an easy way to do it?

[wp]

Get yourself the current development version from CCR (https://sourceforge.net/p/lazarus-ccr/svn/HEAD/tree/components/fpspreadsheet/, use svn or click the "download snapshot" button). There are a variety of sample projects in the folder examples/other/chart of the unzipped directory.

There is no documentation, yet. Just as a guide, this is the basic code from one of the samples, it creates a chart with two bar series:

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

1. var
2.   book: TsWorkbook;
3.   sheet: TsWorksheet;
4.   ch: TsChart;
5.   ser: TsChartSeries;
6.   fn: String;
7. begin
8.   fn := FILE_NAME;
9.  
10.   book := TsWorkbook.Create;
11.   try
12.     // worksheet
13.     sheet := book.AddWorksheet('bar_series');
14.  
15.     // Enter data
16.     sheet.WriteText( 0, 0, 'School Grades');
17.     sheet.WriteFont( 0, 0, '', 12, [fssBold], scBlack);
18.     sheet.WriteText( 2, 0, '');          sheet.WriteText  ( 2, 1, 'Student 1'); sheet.WriteText  ( 2, 2, 'Student 2');
19.     sheet.WriteText( 3, 0, 'Biology');   sheet.WriteNumber( 3, 1, 12);          sheet.WriteNumber( 3, 2, 15);
20.     sheet.WriteText( 4, 0, 'History');   sheet.WriteNumber( 4, 1, 11);          sheet.WriteNumber( 4, 2, 13);
21.     sheet.WriteText( 5, 0, 'French');    sheet.WriteNumber( 5, 1, 16);          sheet.WriteNumber( 5, 2, 11);
22.     sheet.WriteText( 6, 0, 'English');   sheet.WriteNumber( 6, 1, 18);          sheet.WriteNumber( 6, 2, 11);
23.     sheet.WriteText( 7, 0, 'Sports');    sheet.WriteNumber( 7, 1, 16);          sheet.WriteNumber( 7, 2,  7);
24.     sheet.WriteText( 8, 0, 'Maths');     sheet.WriteNumber( 8, 1, 10);          sheet.WriteNumber( 8, 2, 17);
25.     sheet.WriteText( 9, 0, 'Physics');   sheet.WriteNumber( 9, 1, 12);          sheet.WriteNumber( 9, 2, 19);
26.     sheet.WriteText(10, 0, 'Computer');  sheet.WriteNumber(10, 1, 16);          sheet.WriteNumber(10, 2, 18);
27.  
28.     // Create chart: left/top in cell D4, 160 mm x 100 mm
29.     ch := book.AddChart(sheet, 2, 3, 160, 100);
30.  
31.     // Chart properties
32.     ch.Border.Style := clsNoLine;
33.     ch.Title.Caption := 'School Grades';
34.     ch.Title.Font.Style := [fssBold];
35.     ch.Legend.Border.Style := clsNoLine;
36.     ch.XAxis.Title.Caption := '';
37.     ch.YAxis.Title.Caption := 'Grade points';
38.     ch.YAxis.AxisLine.Color := scSilver;
39.     ch.YAxis.MajorTicks := [];
40.  
41.     // Add 1st bar series ("Student 1")
42.     ser := TsBarSeries.Create(ch);
43.     ser.SetTitleAddr(2, 1);              // series 1 title in cell B3
44.     ser.SetLabelRange(3, 0, 10, 0);      // series 1 x labels in A4:A11
45.     ser.SetYRange(3, 1, 10, 1);          // series 1 y values in B4:B11
46.     ser.Line.Color := scDarkRed;
47.     ser.Fill.Style := cfsSolidHatched;
48.     ser.Fill.Hatch := ch.Hatches.AddLineHatch('Crossed', chsDouble, scDarkRed, 2, 0.1, 45);
49.     ser.Fill.Color := scRed;
50.  
51.     // Add 2nd bar series ("Student 2")
52.     ser := TsBarSeries.Create(ch);
53.     ser.SetTitleAddr(2, 2);              // series 2 title in cell C3
54.     ser.SetLabelRange(3, 0, 10, 0);      // series 2 x labels in A4:A11
55.     ser.SetYRange(3, 2, 10, 2);          // series 2 y values in C4:C11
56.     ser.Line.Color := scDarkBlue;
57.     ser.Fill.Style := cfsSolidHatched;
58.     ser.Fill.Hatch := ch.Hatches.AddLineHatch('Forward', chsSingle, scWhite, 1.5, 0.1, 45);
59.     ser.Fill.Color := scBlue;
60.  
61.     book.WriteToFile(fn + '.ods', true);
62.     WriteLn('Data saved with chart in ', fn, '.ods');
63.   finally
64.     book.Free;
65.   end;
66. end.

But note: this is still in active development, and it is possible that some properties or commands may change. Charts in ods files (Libre/OpenOffice) are supported for reading and writing. Excel xlsx files are 80% complete for reading, but I don't know whether writing will be feasible (that file structure is a pain...), old Excel xls probably will not be supported.

[jollytall]

Thanks, it is a great help.

I downloaded the most recent version (I see you even updated it 40 minutes before I did it) and used that. Your example worked well.
Once!
If the spreadsheet had this chart in it, it already failed to save it again, even if I did not do anything with it. I tried to delete the whole sheet containing the chart, I tried to add a chart to another sheet, but nothing. If the file had once a chart, it could not be saved again.
However if I read it into OpenOffice, deleted the chart, saved the spreadsheet, then no problem, the chart can be added normally.

Can you try to reproduce it? Is it only my program, or something discrepancy between the load and save parts?

Thanks,

[wp]

All this is still pretty much experimental...

What exactly are you doing? Send a mini-project so that I can see what needs to be done to make this happen.

[jollytall]

Simply added few lines to your above example. Just before the end. line:

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

1. ...
2.     book.Free;
3.   end;  
4.  
5. // new code
6. book := TsWorkbook.Create;
7. try
8.   book.ReadFromFile(fn + '.ods');
9.   book.WriteToFile(fn + '.ods', true);
10. except
11.   on E:Exception do writeln(E.Message); // <-- Access violation
12.   end;
13. book.free;
14. // end of new code
15.  
16. end.

The error is in fpsOpenDocumentChart at line 2676

[wp]

Try again now. But be prepared that there will be more bugs of this kind at the moment...

[jollytall]

Significant improvement. It ran twice.
It failed for the third time

The code is, based on your demo:

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

1. program test;
2.  
3. uses
4.   Classes, Sysutils,
5.   fpspreadsheet, fpsTypes, fpsopendocument, fpsChart;
6.  
7. const
8.   FILE_NAME = 'test.ods';
9.  
10. var
11.   book: TsWorkbook;
12.   dummy, sheet: TsWorksheet;
13.   ch: TsChart;
14.   ser: TsChartSeries;
15.   fn: String;
16. begin
17.   fn := FILE_NAME;
18.  
19.   book := TsWorkbook.Create;
20.   try
21.     if FileExists(fn) then
22.       begin
23.       book.ReadFromFile(fn);
24.       dummy := book.GetWorksheetByName('dummy');
25.       if not assigned(dummy) then
26.         dummy := book.AddWorksheet('dummy');
27.       sheet := book.GetWorksheetByName('bar_series');
28.       if assigned(sheet) then
29.         book.RemoveWorksheet(sheet);
30.       end;
31.     // worksheet
32.     sheet := book.AddWorksheet('bar_series');
33.  
34.  
35.     // Enter data
36.     sheet.WriteText( 0, 0, 'School Grades');
37.     sheet.WriteFont( 0, 0, '', 12, [fssBold], scBlack);
38.     sheet.WriteText( 2, 0, '');          sheet.WriteText  ( 2, 1, 'Student 1'); sheet.WriteText  ( 2, 2, 'Student 2');
39.     sheet.WriteText( 3, 0, 'Biology');   sheet.WriteNumber( 3, 1, 12);          sheet.WriteNumber( 3, 2, 15);
40.     sheet.WriteText( 4, 0, 'History');   sheet.WriteNumber( 4, 1, 11);          sheet.WriteNumber( 4, 2, 13);
41.     sheet.WriteText( 5, 0, 'French');    sheet.WriteNumber( 5, 1, 16);          sheet.WriteNumber( 5, 2, 11);
42.     sheet.WriteText( 6, 0, 'English');   sheet.WriteNumber( 6, 1, 18);          sheet.WriteNumber( 6, 2, 11);
43.     sheet.WriteText( 7, 0, 'Sports');    sheet.WriteNumber( 7, 1, 16);          sheet.WriteNumber( 7, 2,  7);
44.     sheet.WriteText( 8, 0, 'Maths');     sheet.WriteNumber( 8, 1, 10);          sheet.WriteNumber( 8, 2, 17);
45.     sheet.WriteText( 9, 0, 'Physics');   sheet.WriteNumber( 9, 1, 12);          sheet.WriteNumber( 9, 2, 19);
46.     sheet.WriteText(10, 0, 'Computer');  sheet.WriteNumber(10, 1, 16);          sheet.WriteNumber(10, 2, 18);
47.  
48.     // Create chart: left/top in cell D4, 160 mm x 100 mm
49.     ch := book.AddChart(sheet, 2, 3, 160, 100);
50.  
51.     // Chart properties
52.     ch.Border.Style := clsNoLine;
53.     ch.Title.Caption := 'School Grades';
54.     ch.Title.Font.Style := [fssBold];
55.     ch.Legend.Border.Style := clsNoLine;
56.     ch.XAxis.Title.Caption := '';
57.     ch.YAxis.Title.Caption := 'Grade points';
58.     ch.YAxis.AxisLine.Color := scSilver;
59.     ch.YAxis.MajorTicks := [];
60.  
61.     // Add 1st bar series ("Student 1")
62.     ser := TsBarSeries.Create(ch);
63.     ser.SetTitleAddr(2, 1);              // series 1 title in cell B3
64.     ser.SetLabelRange(3, 0, 10, 0);      // series 1 x labels in A4:A11
65.     ser.SetYRange(3, 1, 10, 1);          // series 1 y values in B4:B11
66.     ser.Line.Color := scDarkRed;
67.     ser.Fill.Style := cfsSolidHatched;
68.     ser.Fill.Hatch := ch.Hatches.AddLineHatch('Crossed', chsDouble, scDarkRed, 2, 0.1, 45);
69.     ser.Fill.Color := scRed;
70.  
71.     // Add 2nd bar series ("Student 2")
72.     ser := TsBarSeries.Create(ch);
73.     ser.SetTitleAddr(2, 2);              // series 2 title in cell C3
74.     ser.SetLabelRange(3, 0, 10, 0);      // series 2 x labels in A4:A11
75.     ser.SetYRange(3, 2, 10, 2);          // series 2 y values in C4:C11
76.     ser.Line.Color := scDarkBlue;
77.     ser.Fill.Style := cfsSolidHatched;
78.     ser.Fill.Hatch := ch.Hatches.AddLineHatch('Forward', chsSingle, scWhite, 1.5, 0.1, 45);
79.     ser.Fill.Color := scBlue;
80.  
81.     book.WriteToFile(fn, true);
82.     WriteLn('Data saved with chart in ', fn);
83.   finally
84.     book.Free;
85.   end;
86. end.

What I think happens is as follows:
First time, there is no file, so it creates the two sheets (dummy is only needed, so the real one can be deleted - the last one can never be deleted, what is an improvement request, if I may) and adds the data and the chart, and finally saves it. The file is perfect.
Second time it deletes the sheet but probably not the chart. Then it adds the sheet and the chart and saves it. The file is not perfect any more, it has actually two charts in it, but OpenOffice can open it. If you drag the chart away, under it there is another one.
Third time the program cannot open it, with an unmatched tag error.

Do I need to delete the Chart manually? I would think that as the chart is linked to the sheet, once the sheet is deleted the chart should go with it.

Thanks,

P.S. I am very well used to and prepared to accept more bugs, as long as you give this excellent and quick support. My programs are more buggy anyway...

[wp]

Try again. Yes, a worksheet must destroy the charts linked to it. I had focused on the more difficult things. As I said: work in progress...

[jollytall]

Now it works as intended. Thank you!

[jollytall]

Sorry to say but it again worked only for a while. It worked as long as I deleted the sheet - and recreated the chart if I wanted - before saving it again.
Once I load and save a file with a chart, it corrupts somehow and next time it cannot be loaded.
In the following example I added the second try..finally block and simplified a bit above. The problem is with the second try. If the exit; is in it, it works perfectly. Once you comment/remove the exit; and thus you load and save the file without changing it, at the next run it crashes again.

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

1. program test;
2.  
3. uses
4.   Classes, Sysutils,
5.   fpspreadsheet, fpsTypes, fpsopendocument, fpsChart;
6.  
7. const
8.   FILE_NAME = 'test.ods';
9.  
10. var
11.   book: TsWorkbook;
12.   dummy, sheet: TsWorksheet;
13.   ch: TsChart;
14.   ser: TsChartSeries;
15.   fn: String;
16. begin
17.   fn := FILE_NAME;
18.  
19.   book := TsWorkbook.Create;
20.   try
21.     if FileExists(fn) then
22.       book.ReadFromFile(fn);
23.  
24.     dummy := book.GetWorksheetByName('dummy');
25.     if not assigned(dummy) then
26.       dummy := book.AddWorksheet('dummy');
27.     sheet := book.GetWorksheetByName('bar_series');
28.     if assigned(sheet) then
29.       book.RemoveWorksheet(sheet);
30.     sheet := book.AddWorksheet('bar_series');
31.  
32.     // Enter data
33.     sheet.WriteText( 0, 0, 'School Grades');
34.     sheet.WriteFont( 0, 0, '', 12, [fssBold], scBlack);
35.     sheet.WriteText( 2, 0, '');          sheet.WriteText  ( 2, 1, 'Student 1'); sheet.WriteText  ( 2, 2, 'Student 2');
36.     sheet.WriteText( 3, 0, 'Biology');   sheet.WriteNumber( 3, 1, 12);          sheet.WriteNumber( 3, 2, 15);
37.     sheet.WriteText( 4, 0, 'History');   sheet.WriteNumber( 4, 1, 11);          sheet.WriteNumber( 4, 2, 13);
38.     sheet.WriteText( 5, 0, 'French');    sheet.WriteNumber( 5, 1, 16);          sheet.WriteNumber( 5, 2, 11);
39.     sheet.WriteText( 6, 0, 'English');   sheet.WriteNumber( 6, 1, 18);          sheet.WriteNumber( 6, 2, 11);
40.     sheet.WriteText( 7, 0, 'Sports');    sheet.WriteNumber( 7, 1, 16);          sheet.WriteNumber( 7, 2,  7);
41.     sheet.WriteText( 8, 0, 'Maths');     sheet.WriteNumber( 8, 1, 10);          sheet.WriteNumber( 8, 2, 17);
42.     sheet.WriteText( 9, 0, 'Physics');   sheet.WriteNumber( 9, 1, 12);          sheet.WriteNumber( 9, 2, 19);
43.     sheet.WriteText(10, 0, 'Computer');  sheet.WriteNumber(10, 1, 16);          sheet.WriteNumber(10, 2, 18);
44.  
45.     // Create chart: left/top in cell D4, 160 mm x 100 mm
46.     ch := book.AddChart(sheet, 2, 3, 160, 100);
47.  
48.     // Chart properties
49.     ch.Border.Style := clsNoLine;
50.     ch.Title.Caption := 'School Grades';
51.     ch.Title.Font.Style := [fssBold];
52.     ch.Legend.Border.Style := clsNoLine;
53.     ch.XAxis.Title.Caption := '';
54.     ch.YAxis.Title.Caption := 'Grade points';
55.     ch.YAxis.AxisLine.Color := scSilver;
56.     ch.YAxis.MajorTicks := [];
57.  
58.     // Add 1st bar series ("Student 1")
59.     ser := TsBarSeries.Create(ch);
60.     ser.SetTitleAddr(2, 1);              // series 1 title in cell B3
61.     ser.SetLabelRange(3, 0, 10, 0);      // series 1 x labels in A4:A11
62.     ser.SetYRange(3, 1, 10, 1);          // series 1 y values in B4:B11
63.     ser.Line.Color := scDarkRed;
64.     ser.Fill.Style := cfsSolidHatched;
65.     ser.Fill.Hatch := ch.Hatches.AddLineHatch('Crossed', chsDouble, scDarkRed, 2, 0.1, 45);
66.     ser.Fill.Color := scRed;
67.  
68.     book.WriteToFile(fn, true);
69.     WriteLn('Data saved with chart in ', fn);
70.   finally
71.     book.Free;
72.     end;
73.  
74.   exit; // <- TRY TO COMMENT IT
75.  
76.   // This is the problem block
77.   book := TsWorkbook.Create;
78.   try
79.     book.ReadFromFile(fn);
80.     book.WriteToFile(fn, true);
81.   finally
82.     book.free;
83.     end;
84.  
85. end.

[wp]

Should be fixed now. Good that it crashed, otherwise it would have been hard to find why the hatch pattern was gone in the second run.

[jollytall]

Thanks. As a first try it works OK, but for few days I will have no time to test it further. If I find anything more later, I will let you know.
Thanks again,