Recent

Author Topic: Adding a chart to a spreadsheet in a console app  (Read 1787 times)

jollytall

  • Sr. Member
  • ****
  • Posts: 320
Adding a chart to a spreadsheet in a console app
« on: January 23, 2024, 03:38:31 pm »
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

  • Hero Member
  • *****
  • Posts: 12030
Re: Adding a chart to a spreadsheet in a console app
« Reply #1 on: January 23, 2024, 04:30:35 pm »
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.
« Last Edit: January 23, 2024, 05:15:28 pm by wp »

jollytall

  • Sr. Member
  • ****
  • Posts: 320
Re: Adding a chart to a spreadsheet in a console app
« Reply #2 on: January 23, 2024, 05:35:53 pm »
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

  • Hero Member
  • *****
  • Posts: 12030
Re: Adding a chart to a spreadsheet in a console app
« Reply #3 on: January 23, 2024, 06:04:54 pm »
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

  • Sr. Member
  • ****
  • Posts: 320
Re: Adding a chart to a spreadsheet in a console app
« Reply #4 on: January 23, 2024, 06:42:15 pm »
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

  • Hero Member
  • *****
  • Posts: 12030
Re: Adding a chart to a spreadsheet in a console app
« Reply #5 on: January 23, 2024, 08:19:36 pm »
Try again now. But be prepared that there will be more bugs of this kind at the moment...

jollytall

  • Sr. Member
  • ****
  • Posts: 320
Re: Adding a chart to a spreadsheet in a console app
« Reply #6 on: January 23, 2024, 09:00:37 pm »
Significant improvement. It ran twice. :D
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

  • Hero Member
  • *****
  • Posts: 12030
Re: Adding a chart to a spreadsheet in a console app
« Reply #7 on: January 23, 2024, 10:33:49 pm »
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

  • Sr. Member
  • ****
  • Posts: 320
Re: Adding a chart to a spreadsheet in a console app
« Reply #8 on: January 24, 2024, 07:50:19 am »
Now it works as intended. Thank you!

jollytall

  • Sr. Member
  • ****
  • Posts: 320
Re: Adding a chart to a spreadsheet in a console app
« Reply #9 on: January 24, 2024, 11:21:07 am »
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

  • Hero Member
  • *****
  • Posts: 12030
Re: Adding a chart to a spreadsheet in a console app
« Reply #10 on: January 25, 2024, 12:38:44 am »
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

  • Sr. Member
  • ****
  • Posts: 320
Re: Adding a chart to a spreadsheet in a console app
« Reply #11 on: January 25, 2024, 09:45:56 am »
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,

 

TinyPortal © 2005-2018