Forum > FPSpreadsheet
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 [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---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:
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 [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---... 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
Navigation
[0] Message Index
[#] Next page