Adding a chart to a spreadsheet in a console app lazarus / 49-FPSpreadsheet ---------------------------------------------------------------------- jollytall · 2024-01-23 14:38 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 · 2024-01-23 15:30 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i var  book: TsWorkbook;  sheet: TsWorksheet;  ch: TsChart;  ser: TsChartSeries;  fn: String;begin  fn := FILE_NAME;   book := TsWorkbook.Create;  try  // worksheet    sheet := book.AddWorksheet('bar_series');     // Enter data    sheet.WriteText( 0, 0, 'School Grades');    sheet.WriteFont( 0, 0, '', 12, [fssBold], scBlack);    sheet.WriteText( 2, 0, '');          sheet.WriteText  ( 2, 1, 'Student 1'); sheet.WriteText  ( 2, 2, 'Student 2');    sheet.WriteText( 3, 0, 'Biology');   sheet.WriteNumber( 3, 1, 12);          sheet.WriteNumber( 3, 2, 15);    sheet.WriteText( 4, 0, 'History'); sheet.WriteNumber( 4, 1, 11);          sheet.WriteNumber( 4, 2, 13);  sheet.WriteText( 5, 0, 'French');    sheet.WriteNumber( 5, 1, 16);        sheet.WriteNumber( 5, 2, 11);    sheet.WriteText( 6, 0, 'English');   sheet.WriteNumber( 6, 1, 18);          sheet.WriteNumber( 6, 2, 11);    sheet.WriteText( 7, 0, 'Sports');    sheet.WriteNumber( 7, 1, 16);          sheet.WriteNumber( 7, 2,  7);    sheet.WriteText( 8, 0, 'Maths');     sheet.WriteNumber( 8, 1, 10);        sheet.WriteNumber( 8, 2, 17);    sheet.WriteText( 9, 0, 'Physics');   sheet.WriteNumber( 9, 1, 12);          sheet.WriteNumber( 9, 2, 19);    sheet.WriteText(10, 0, 'Computer');  sheet.WriteNumber(10, 1, 16);          sheet.WriteNumber(10, 2, 18);     // Create chart: left/top in cell D4, 160 mm x 100 mm    ch := book.AddChart(sheet, 2, 3, 160, 100);     // Chart properties    ch.Border.Style := clsNoLine;    ch.Title.Caption := 'School Grades';    ch.Title.Font.Style := [fssBold];    ch.Legend.Border.Style := clsNoLine;    ch.XAxis.Title.Caption := '';    ch.YAxis.Title.Caption := 'Grade points';    ch.YAxis.AxisLine.Color := scSilver;    ch.YAxis.MajorTicks := [];     // Add 1st bar series ("Student 1")    ser := TsBarSeries.Create(ch);    ser.SetTitleAddr(2, 1);              // series 1 title in cell B3    ser.SetLabelRange(3, 0, 10, 0);      // series 1 x labels in A4:A11    ser.SetYRange(3, 1, 10, 1);        // series 1 y values in B4:B11    ser.Line.Color := scDarkRed;    ser.Fill.Style := cfsSolidHatched;    ser.Fill.Hatch := ch.Hatches.AddLineHatch('Crossed', chsDouble, scDarkRed, 2, 0.1, 45);    ser.Fill.Color := scRed;     // Add 2nd bar series ("Student 2")    ser := TsBarSeries.Create(ch);    ser.SetTitleAddr(2, 2);              // series 2 title in cell C3    ser.SetLabelRange(3, 0, 10, 0);      // series 2 x labels in A4:A11    ser.SetYRange(3, 2, 10, 2);        // series 2 y values in C4:C11    ser.Line.Color := scDarkBlue;    ser.Fill.Style := cfsSolidHatched;    ser.Fill.Hatch := ch.Hatches.AddLineHatch('Forward', chsSingle, scWhite, 1.5, 0.1, 45);    ser.Fill.Color := scBlue;     book.WriteToFile(fn + '.ods', true);  WriteLn('Data saved with chart in ', fn, '.ods');  finally    book.Free;  end;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 · 2024-01-23 16:35 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 · 2024-01-23 17:04 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 · 2024-01-23 17:42 Simply added few lines to your above example. Just before the end. line: 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 ...    book.Free;  end;   // new codebook := TsWorkbook.Create;try  book.ReadFromFile(fn + '.ods');  book.WriteToFile(fn + '.ods', true);except  on E:Exception do writeln(E.Message); // <-- Access violation  end;book.free;// end of new code end. The error is in fpsOpenDocumentChart at line 2676 ---------------------------------------------------------------------- wp · 2024-01-23 19:19 Try again now. But be prepared that there will be more bugs of this kind at the moment... ---------------------------------------------------------------------- jollytall · 2024-01-23 20:00 Significant improvement. It ran twice. It failed for the third time The code is, based on your demo: 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 test; uses  Classes, Sysutils,  fpspreadsheet, fpsTypes, fpsopendocument, fpsChart; const  FILE_NAME = 'test.ods'; var  book: TsWorkbook;  dummy, sheet: TsWorksheet;  ch: TsChart;  ser: TsChartSeries;  fn: String;begin  fn := FILE_NAME;   book := TsWorkbook.Create;  try    if FileExists(fn) then      begin      book.ReadFromFile(fn);      dummy := book.GetWorksheetByName('dummy');      if not assigned(dummy) then      dummy := book.AddWorksheet('dummy');      sheet := book.GetWorksheetByName('bar_series');      if assigned(sheet) then      book.RemoveWorksheet(sheet);      end;    // worksheet    sheet := book.AddWorksheet('bar_series');      // Enter data    sheet.WriteText( 0, 0, 'School Grades');    sheet.WriteFont( 0, 0, '', 12, [fssBold], scBlack);    sheet.WriteText( 2, 0, '');          sheet.WriteText  ( 2, 1, 'Student 1'); sheet.WriteText  ( 2, 2, 'Student 2');    sheet.WriteText( 3, 0, 'Biology');   sheet.WriteNumber( 3, 1, 12);          sheet.WriteNumber( 3, 2, 15);  sheet.WriteText( 4, 0, 'History');   sheet.WriteNumber( 4, 1, 11);        sheet.WriteNumber( 4, 2, 13);    sheet.WriteText( 5, 0, 'French');    sheet.WriteNumber( 5, 1, 16);          sheet.WriteNumber( 5, 2, 11);    sheet.WriteText( 6, 0, 'English'); sheet.WriteNumber( 6, 1, 18);          sheet.WriteNumber( 6, 2, 11);  sheet.WriteText( 7, 0, 'Sports');    sheet.WriteNumber( 7, 1, 16);        sheet.WriteNumber( 7, 2,  7);    sheet.WriteText( 8, 0, 'Maths');     sheet.WriteNumber( 8, 1, 10);          sheet.WriteNumber( 8, 2, 17);    sheet.WriteText( 9, 0, 'Physics'); sheet.WriteNumber( 9, 1, 12);          sheet.WriteNumber( 9, 2, 19);  sheet.WriteText(10, 0, 'Computer');  sheet.WriteNumber(10, 1, 16);        sheet.WriteNumber(10, 2, 18);     // Create chart: left/top in cell D4, 160 mm x 100 mm    ch := book.AddChart(sheet, 2, 3, 160, 100);     // Chart properties    ch.Border.Style := clsNoLine;    ch.Title.Caption := 'School Grades';    ch.Title.Font.Style := [fssBold];    ch.Legend.Border.Style := clsNoLine;    ch.XAxis.Title.Caption := '';    ch.YAxis.Title.Caption := 'Grade points';    ch.YAxis.AxisLine.Color := scSilver;    ch.YAxis.MajorTicks := [];     // Add 1st bar series ("Student 1")    ser := TsBarSeries.Create(ch);    ser.SetTitleAddr(2, 1);              // series 1 title in cell B3    ser.SetLabelRange(3, 0, 10, 0);      // series 1 x labels in A4:A11    ser.SetYRange(3, 1, 10, 1);        // series 1 y values in B4:B11    ser.Line.Color := scDarkRed;    ser.Fill.Style := cfsSolidHatched;    ser.Fill.Hatch := ch.Hatches.AddLineHatch('Crossed', chsDouble, scDarkRed, 2, 0.1, 45);    ser.Fill.Color := scRed;     // Add 2nd bar series ("Student 2")    ser := TsBarSeries.Create(ch);    ser.SetTitleAddr(2, 2);              // series 2 title in cell C3    ser.SetLabelRange(3, 0, 10, 0);      // series 2 x labels in A4:A11    ser.SetYRange(3, 2, 10, 2);        // series 2 y values in C4:C11    ser.Line.Color := scDarkBlue;    ser.Fill.Style := cfsSolidHatched;    ser.Fill.Hatch := ch.Hatches.AddLineHatch('Forward', chsSingle, scWhite, 1.5, 0.1, 45);    ser.Fill.Color := scBlue;     book.WriteToFile(fn, true);    WriteLn('Data saved with chart in ', fn);  finally    book.Free;  end;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 · 2024-01-23 21:33 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 · 2024-01-24 06:50 Now it works as intended. Thank you! ---------------------------------------------------------------------- jollytall · 2024-01-24 10:21 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i program test; uses  Classes, Sysutils,  fpspreadsheet, fpsTypes, fpsopendocument, fpsChart; const  FILE_NAME = 'test.ods'; var  book: TsWorkbook;  dummy, sheet: TsWorksheet;  ch: TsChart;  ser: TsChartSeries;  fn: String;begin  fn := FILE_NAME;   book := TsWorkbook.Create;  try    if FileExists(fn) then      book.ReadFromFile(fn);     dummy := book.GetWorksheetByName('dummy');    if not assigned(dummy) then      dummy := book.AddWorksheet('dummy');    sheet := book.GetWorksheetByName('bar_series');    if assigned(sheet) then    book.RemoveWorksheet(sheet);    sheet := book.AddWorksheet('bar_series');     // Enter data    sheet.WriteText( 0, 0, 'School Grades');    sheet.WriteFont( 0, 0, '', 12, [fssBold], scBlack);    sheet.WriteText( 2, 0, '');          sheet.WriteText  ( 2, 1, 'Student 1'); sheet.WriteText  ( 2, 2, 'Student 2');    sheet.WriteText( 3, 0, 'Biology');   sheet.WriteNumber( 3, 1, 12);        sheet.WriteNumber( 3, 2, 15);    sheet.WriteText( 4, 0, 'History');   sheet.WriteNumber( 4, 1, 11);          sheet.WriteNumber( 4, 2, 13);    sheet.WriteText( 5, 0, 'French');    sheet.WriteNumber( 5, 1, 16);          sheet.WriteNumber( 5, 2, 11);    sheet.WriteText( 6, 0, 'English');   sheet.WriteNumber( 6, 1, 18);        sheet.WriteNumber( 6, 2, 11);    sheet.WriteText( 7, 0, 'Sports');    sheet.WriteNumber( 7, 1, 16);          sheet.WriteNumber( 7, 2,  7);    sheet.WriteText( 8, 0, 'Maths');   sheet.WriteNumber( 8, 1, 10);          sheet.WriteNumber( 8, 2, 17);  sheet.WriteText( 9, 0, 'Physics');   sheet.WriteNumber( 9, 1, 12);        sheet.WriteNumber( 9, 2, 19);    sheet.WriteText(10, 0, 'Computer');  sheet.WriteNumber(10, 1, 16);          sheet.WriteNumber(10, 2, 18);     // Create chart: left/top in cell D4, 160 mm x 100 mm    ch := book.AddChart(sheet, 2, 3, 160, 100);   // Chart properties    ch.Border.Style := clsNoLine;    ch.Title.Caption := 'School Grades';    ch.Title.Font.Style := [fssBold];    ch.Legend.Border.Style := clsNoLine;    ch.XAxis.Title.Caption := '';    ch.YAxis.Title.Caption := 'Grade points';    ch.YAxis.AxisLine.Color := scSilver;    ch.YAxis.MajorTicks := [];     // Add 1st bar series ("Student 1")    ser := TsBarSeries.Create(ch);    ser.SetTitleAddr(2, 1);              // series 1 title in cell B3    ser.SetLabelRange(3, 0, 10, 0);      // series 1 x labels in A4:A11    ser.SetYRange(3, 1, 10, 1);        // series 1 y values in B4:B11    ser.Line.Color := scDarkRed;    ser.Fill.Style := cfsSolidHatched;    ser.Fill.Hatch := ch.Hatches.AddLineHatch('Crossed', chsDouble, scDarkRed, 2, 0.1, 45);    ser.Fill.Color := scRed;     book.WriteToFile(fn, true);    WriteLn('Data saved with chart in ', fn);  finally    book.Free;    end;   exit; // <- TRY TO COMMENT IT   // This is the problem block  book := TsWorkbook.Create;  try    book.ReadFromFile(fn);    book.WriteToFile(fn, true);  finally    book.free;    end; end. ---------------------------------------------------------------------- wp · 2024-01-24 23:38 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 · 2024-01-25 08:45 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,