SpreadSheet Add Chart Line

[eldonfsr]

Hello i crate a workbook and add worksheet fine but not chart don't shoe any error but don't show graph i don't know what i doing wrong..

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

1. procedure TFormCharts.BtnToExcellClick(Sender: TObject);
2. Var I,s:integer;
3.     SourceSeries:TLIneSeries;
4.     MyWorkbook: TsWorkbook;
5.     MyWorksheet: TsWorksheet;
6.     ch: TsChart;
7.     cser: TsLineSeries;
8.     ser: TsChartSeries;
9. begin
10.    MyWorkbook := TsWorkbook.Create;
11.    MyWorkbook.SetDefaultFont('Calibri', 12);
12.    MyWorkbook.Options := MyWorkbook.Options + [boCalcBeforeSaving];
13.    For s:=0 to  Chart1.SeriesCount-1 do begin
14.      SourceSeries:=TLineSeries(ChartListbox1.Series[s]);
15.      if( SourceSeries.Count > 0) then begin
16.       MyWorksheet := MyWorkbook.AddWorksheet('Datos '+inttostr(s+1));
17.       ch := MyWorkSheet.AddChart(4, 4, 300, 300);
18.       ch.Title.Caption := 'Datas '+inttostr(s+1);
19.       MyWorksheet.WriteText(0, 1, 'Axe X');
20.       MyWorksheet.WriteText(0, 2, 'Axe Y');
21.       ChartDataDb.Filtered:=false;
22.       ChartDataDb.Filter:='serie='+''+inttostr(s)+'';
23.       ChartDataDb.Filtered:=true;
24.       for i := 0 to SourceSeries.Count - 1 do  begin
25.          if(ChartDataDb.Locate('serie;id', VarArrayOf([s,i]),[loCaseInsensitive,loPartialKey])) then begin
26.            MyWorksheet.WriteNumber(i+1, 1, ChartDataDb.FieldByName('XValue').AsInteger); //  GetXValue(i));
27.            MyWorksheet.WriteNumber(i+1, 2, ChartDataDb.FieldByName('YValue').AsInteger);
28.          end;
29.       end;
30. //      if( SourceSeries.Count > 0 ) then begin
31.         cser:= TsLineSeries.Create(ch);
32.         ch.Title.Font.Style := [fssBold];
33.         ch.Title.Font.Color := scBlue;
34. //        cser.SetTitleAddr(1, 1);              // series 1 title in cell A1
35.         cser.SetYRange(1, 2, i+1, 2);
36.         cser.SetXRange(2, 2, i+1, 2);
37. //        ser.SetYRange(3, 1, 10, 1);          // series 1 y values in B4:B11
38.  
39. //        cser.XRange:= 'B1:B'+IntToStr(i+1)+'';
40. //        cser.XRange:='C1:C'+Inttostr(i+1)+'';
41. //        ch.Title.Caption := 'Graph '+inttostr(s+1);
42. //        cser:=nil;
43. //      end;
44.       ChartDataDb.Filtered:=false;
45.     end;
46.  
47.    end;
48.    MyWorkbook.WriteToFile(GetCurrentDir + '\testfrafica.xls', sfExcel8, true);
49.    MyWorkbook.Free;
50.  
51. end;
52.  
53.  

attache excel file generated... and form i get datas for the  chart
 

[Dankan1890]

Have you tried saving using the Excel OOXML or OpenDocument format (xlsxOOXML, fpsopendocument, or fpsallformats) and see if that works?

[eldonfsr]

Not but let get documentation about that and try
 

[eldonfsr]

i change to save ooxml and still same don't show chart just values of columns

[eldonfsr]

can be the problem be here
what options must be to have to save all datas
 
MyWorkbook.Options := MyWorkbook.Options + [boCalcBeforeSaving];

[wp]

What do you want to achieve? I don't understand: Why do you iterate over the series of a pre-existing chart? How is that related to the spreadsheet to be created? And there is a database from which you seen to get the data to be put into the spreadsheet and spreadsheet chart. How is that related to the pre-existing chart over which you iterate?

Don't use the xls file format (sfBiff8) when your spreadsheet is supposed to contain charts. Spreadsheet charts have been implemented only for xlsx and ods.

For examples how to create spreadsheet charts take a look at the folder examples/other/chart of your fpspreadsheet installation.

[eldonfsr]

ok thanks for your time and attention, what i need is generate 10 lineseries chart for that i create table to keep values and put on excell so that i need is for each serie create a chart on each worksheet, work book goin to have 10 worksheets..

can be possible or is only one chart per book..

Thanks
 

[wp]

Here's a small project creating a workbook with 5 worksheets and a chart on each worksheet.

[eldonfsr]

Well after playing with fpspreadsheed  finale i got chart and datas exporte to excel here you have my process if some body need ....is not perfect but is working for me..

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

1. procedure TFormCharts.BtnToExcellClick(Sender: TObject);
2. Var I,s,rs:integer;
3.     SourceSeries:TLIneSeries;
4.     MWB: TsWorkbook;
5.     MWS,MWS1,MWS2,MWS3,MWS4,MWS5,MWS6,MWS7,MWS8,MWS9: TsWorksheet;
6.     ch,ch1,ch2,ch3,ch4,ch5,ch6,ch7,ch8,ch9: TsChart;
7.     cser,cser1,cser2,cser3,cser4,cser5,cser6,cser7,cser8,cser9: TsLineSeries;
8. begin
9.    MWB := TsWorkbook.Create;
10.    rs:=0;
11. //   MWB.SetDefaultFont('Calibri', 12);
12. //   MWB.Options := MWB.Options + [boCalcBeforeSaving,boEntireDocument];
13.    For s:=0 to  Chart1.SeriesCount-1 do begin
14.      SourceSeries:=TLineSeries(ChartListbox1.Series[s]);
15.      if( SourceSeries.Count > 0) then begin
16.       case s of
17.       0: begin
18.           MWS := MWB.AddWorksheet('Datos '+inttostr(s+1));
19.           MWS.WriteText(0, 1, 'Axe X');
20.           MWS.WriteText(0, 2, 'Axe Y');
21.           ch := MWS.AddChart( 200, 100,4, 4);
22.           ch.Title.Caption := 'Datas '+inttostr(s+1);
23.           ch.Title.Font.Style := [fssBold];
24.           ch.Title.Font.Color := scBlue;
25.           ch.StackMode := csmStacked;
26.           cser:= TsLineSeries.Create(ch);
27.         end;
28.       1: begin
29.            MWS1:= MWB.AddWorksheet('Datos '+inttostr(s+1));
30.            MWS1.WriteText(0, 1, 'Axe X');
31.            MWS1.WriteText(0, 2, 'Axe Y');
32.            ch1 := MWS1.AddChart( 200, 200,4, 4);
33.            ch1.Title.Caption := 'Datas '+inttostr(s+1);
34.            ch1.Title.Font.Style := [fssBold];
35.            ch1.Title.Font.Color := scBlue;
36.            cser1:= TsLineSeries.Create(ch1);
37.          end;
38.       2: begin
39.          MWS2 := MWB.AddWorksheet('Datos '+inttostr(s+1));
40.          MWS2.WriteText(0, 1, 'Axe X');
41.          MWS2.WriteText(0, 2, 'Axe Y');
42.          ch2 := MWS2.AddChart( 200, 200,4, 4);
43.          ch2.Title.Caption := 'Datas '+inttostr(s+1);
44.          ch2.Title.Font.Style := [fssBold];
45.          ch2.Title.Font.Color := scBlue;
46.          cser2:= TsLineSeries.Create(ch2);
47.         end;
48.       3: begin
49.            MWS3 := MWB.AddWorksheet('Datos '+inttostr(s+1));
50.            MWS3.WriteText(0, 1, 'Axe X');
51.            MWS3.WriteText(0, 2, 'Axe Y');
52.            ch3 := MWS3.AddChart( 200, 200,4, 4);
53.            ch3.Title.Caption := 'Datas '+inttostr(s+1);
54.            ch3.Title.Font.Style := [fssBold];
55.            ch3.Title.Font.Color := scBlue;
56.            cser3:= TsLineSeries.Create(ch3);
57.         end;
58.       4: begin
59.         MWS4 := MWB.AddWorksheet('Datos '+inttostr(s+1));
60.         MWS4.WriteText(0, 1, 'Axe X');
61.         MWS4.WriteText(0, 2, 'Axe Y');
62.         ch4 := MWS4.AddChart( 200, 200,4, 4);
63.         ch4.Title.Caption := 'Datas '+inttostr(s+1);
64.         ch4.Title.Font.Style := [fssBold];
65.         ch4.Title.Font.Color := scBlue;
66.           cser4:= TsLineSeries.Create(ch4);
67.  
68.          end;
69.       5: begin
70.         MWS5 := MWB.AddWorksheet('Datos '+inttostr(s+1));
71.         MWS5.WriteText(0, 1, 'Axe X');
72.         MWS5.WriteText(0, 2, 'Axe Y');
73.         ch5 := MWS5.AddChart( 200, 200,4, 4);
74.         ch5.Title.Caption := 'Datas '+inttostr(s+1);
75.         ch5.Title.Font.Style := [fssBold];
76.         ch5.Title.Font.Color := scBlue;
77.         cser5:= TsLineSeries.Create(ch5);
78.         end;
79.       6: begin
80.         MWS6 := MWB.AddWorksheet('Datos '+inttostr(s+1));
81.         MWS6.WriteText(0, 1, 'Axe X');
82.         MWS6.WriteText(0, 2, 'Axe Y');
83.         ch6 := MWS6.AddChart( 200, 200,4, 4);
84.         ch6.Title.Caption := 'Datas '+inttostr(s+1);
85.         ch6.Title.Font.Style := [fssBold];
86.         ch6.Title.Font.Color := scBlue;
87.         cser6:= TsLineSeries.Create(ch6);
88.         end;
89.       7: begin
90.         MWS7 := MWB.AddWorksheet('Datos '+inttostr(s+1));
91.         MWS7.WriteText(0, 1, 'Axe X');
92.         MWS7.WriteText(0, 2, 'Axe Y');
93.         ch7 := MWS7.AddChart( 200, 200,4, 4);
94.         ch7.Title.Caption := 'Datas '+inttostr(s+1);
95.         ch7.Title.Font.Style := [fssBold];
96.         ch7.Title.Font.Color := scBlue;
97.         cser7:= TsLineSeries.Create(ch7);
98.         end;
99.       8: begin
100.         MWS8 := MWB.AddWorksheet('Datos '+inttostr(s+1));
101.         MWS8.WriteText(0, 1, 'Axe X');
102.         MWS8.WriteText(0, 2, 'Axe Y');
103.         ch8 := MWS8.AddChart(200, 200,4, 4);
104.         ch8.Title.Caption := 'Datas '+inttostr(s+1);
105.         ch8.Title.Font.Style := [fssBold];
106.         ch8.Title.Font.Color := scBlue;
107.         cser8:= TsLineSeries.Create(ch8);
108.         end;
109.       9: begin
110.         MWS9 := MWB.AddWorksheet('Datos '+inttostr(s+1));
111.         MWS9.WriteText(0, 1, 'Axe X');
112.         MWS9.WriteText(0, 2, 'Axe Y');
113.         ch9 := MWS9.AddChart( 200, 200,4, 4);
114.         ch9.Title.Caption := 'Datas '+inttostr(s+1);
115.         ch9.Title.Font.Style := [fssBold];
116.         ch9.Title.Font.Color := scBlue;
117.         cser9:= TsLineSeries.Create(ch9);
118.         end;
119.  
120.       end;
121.       ChartDataDb.Filtered:=false;
122.       ChartDataDb.Filter:='serie='+''+inttostr(s)+'';
123.       ChartDataDb.Filtered:=true;
124.       rs:=Round(ChartDataDb.RecordCount/ (FormMAIN.ChartCount+1));
125.       if rs>0 then  rs:=(rs*S);
126.  
127.       for i := 1 to SourceSeries.Count - 1 do  begin
128.  
129.          if(ChartDataDb.Locate('serie;id', VarArrayOf([s,(i+rs)]),[loCaseInsensitive,loPartialKey])) then begin
130.           CASE s OF
131.           0: begin MWS.WriteNumber(i, 1, ChartDataDb.FieldByName('XValue').AsInteger); //  GetXValue(i));
132.                    MWS.WriteNumber(i, 2, ChartDataDb.FieldByName('YValue').AsInteger);
133.              end;
134.           1: begin MWS1.WriteNumber(i, 1, ChartDataDb.FieldByName('XValue').AsInteger); //  GetXValue(i));
135.                    MWS1.WriteNumber(i, 2, ChartDataDb.FieldByName('YValue').AsInteger);
136.              end;
137.           2: begin MWS2.WriteNumber(i, 1, ChartDataDb.FieldByName('XValue').AsInteger); //  GetXValue(i));
138.                    MWS2.WriteNumber(i, 2, ChartDataDb.FieldByName('YValue').AsInteger);
139.              end;
140.            3: begin MWS3.WriteNumber(i, 1, ChartDataDb.FieldByName('XValue').AsInteger); //  GetXValue(i));
141.                     MWS3.WriteNumber(i, 2, ChartDataDb.FieldByName('YValue').AsInteger);
142.              end;
143.            4: begin MWS4.WriteNumber(i, 1, ChartDataDb.FieldByName('XValue').AsInteger); //  GetXValue(i));
144.                    MWS4.WriteNumber(i, 2, ChartDataDb.FieldByName('YValue').AsInteger);
145.              end;
146.            5: begin MWS5.WriteNumber(i, 1, ChartDataDb.FieldByName('XValue').AsInteger); //  GetXValue(i));
147.                     MWS5.WriteNumber(i, 2, ChartDataDb.FieldByName('YValue').AsInteger);
148.               end;
149.            6: begin MWS6.WriteNumber(i, 1, ChartDataDb.FieldByName('XValue').AsInteger); //  GetXValue(i));
150.                    MWS6.WriteNumber(i, 2, ChartDataDb.FieldByName('YValue').AsInteger);
151.              end;
152.            7: begin MWS7.WriteNumber(i, 1, ChartDataDb.FieldByName('XValue').AsInteger); //  GetXValue(i));
153.                    MWS7.WriteNumber(i, 2, ChartDataDb.FieldByName('YValue').AsInteger);
154.              end;
155.             8: begin MWS8.WriteNumber(i, 1, ChartDataDb.FieldByName('XValue').AsInteger); //  GetXValue(i));
156.                    MWS8.WriteNumber(i, 2, ChartDataDb.FieldByName('YValue').AsInteger);
157.              end;
158.             9: begin MWS9.WriteNumber(i, 1, ChartDataDb.FieldByName('XValue').AsInteger); //  GetXValue(i));
159.                      MWS9.WriteNumber(i, 2, ChartDataDb.FieldByName('YValue').AsInteger);
160.                end;
161.  
162.           end;
163.          end;
164.       end;
165.      case s of
166.         0: begin
167.             ChartDataDb.First;
168.             ch.XAxis.Min := ChartDataDb.FieldByName('XValue').AsInteger;
169.             ch.YAxis.Min := 0;
170.             ChartDataDb.last;
171.             ch.XAxis.Max :=ChartDataDb.FieldByName('XValue').AsInteger;
172.             ch.YAxis.Max := ChartDataDb.FieldByName('YValue').AsInteger;
173.             ch.YAxis.Title.Caption:='Y Axe';
174.             cser.SetTitleAddr(0, 1);              // series 1 title in cell A1
175.             cser.SetYRange(2, 2, i+1, 2);
176.             cser.SetXRange(2, 1, i+1, 1);
177.         end;
178.      1: begin
179. //         cser1:= TsLineSeries.Create(ch1);
180.          ChartDataDb.First;
181.          ch1.XAxis.Min := ChartDataDb.FieldByName('XValue').AsInteger;
182.          ch1.YAxis.Min := 0;
183.          ChartDataDb.last;
184.          ch1.XAxis.Max :=ChartDataDb.FieldByName('XValue').AsInteger;
185.          ch1.YAxis.Max := ChartDataDb.FieldByName('YValue').AsInteger;
186.          ch1.YAxis.Title.Caption:='Y Axe';
187.          ch1.Title.Font.Style := [fssBold];
188.          ch1.Title.Font.Color := scBlue;
189.          ch.YAxis.Title.Caption:='Y Axe';
190.  
191.          cser1.SetTitleAddr(0, 1);              // series 1 title in cell A1
192.          cser1.SetYRange(2, 2, i+1, 2);
193.          cser1.SetXRange(1, 2, i+1, 2);
194.      end;
195.      2: begin
196. //         cser2:= TsLineSeries.Create(ch2);
197.        ChartDataDb.First;
198.        ch2.XAxis.Min := ChartDataDb.FieldByName('XValue').AsInteger;
199.        ch2.YAxis.Min := 0;
200.        ChartDataDb.last;
201.        ch2.XAxis.Max :=ChartDataDb.FieldByName('XValue').AsInteger;
202.        ch2.YAxis.Max := ChartDataDb.FieldByName('YValue').AsInteger;
203.        ch2.YAxis.Title.Caption:='Y Axe';
204.        cser2.SetTitleAddr(0, 1);              // series 1 title in cell A1
205.        cser2.SetYRange(2, 2, i+1, 2);
206.        cser2.SetXRange(1, 2, i+1, 2);
207.      end;
208.      3: begin
209. //         cser3:= TsLineSeries.Create(ch3);
210.        ChartDataDb.First;
211.        ch3.XAxis.Min := ChartDataDb.FieldByName('XValue').AsInteger;
212.        ch3.YAxis.Min := 0;
213.        ChartDataDb.last;
214.        ch3.XAxis.Max :=ChartDataDb.FieldByName('XValue').AsInteger;
215.        ch3.YAxis.Max := ChartDataDb.FieldByName('YValue').AsInteger;
216.        ch3.YAxis.Title.Caption:='Y Axe';
217.          cser3.SetTitleAddr(0, 1);              // series 1 title in cell A1
218.          cser3.SetYRange(2, 2, i+1, 2);
219.          cser3.SetXRange(1, 2, i+1, 2);
220.      end;
221.      4: begin
222. //         cser4:= TsLineSeries.Create(ch4);
223.        ChartDataDb.First;
224.        ch4.XAxis.Min := ChartDataDb.FieldByName('XValue').AsInteger;
225.        ch4.YAxis.Min := 0;
226.        ChartDataDb.last;
227.        ch4.XAxis.Max :=ChartDataDb.FieldByName('XValue').AsInteger;
228.        ch4.YAxis.Max := ChartDataDb.FieldByName('YValue').AsInteger;
229.        ch4.YAxis.Title.Caption:='Y Axe';
230.          cser4.SetTitleAddr(0, 1);              // series 1 title in cell A1
231.          cser4.SetYRange(2, 2, i+1, 2);
232.          cser4.SetXRange(1, 2, i+1, 2);
233.      end;
234.      5: begin
235. //         cser5:= TsLineSeries.Create(ch5);
236.        ChartDataDb.First;
237.        ch5.XAxis.Min := ChartDataDb.FieldByName('XValue').AsInteger;
238.        ch5.YAxis.Min := 0;
239.        ChartDataDb.last;
240.        ch5.XAxis.Max :=ChartDataDb.FieldByName('XValue').AsInteger;
241.        ch5.YAxis.Max := ChartDataDb.FieldByName('YValue').AsInteger;
242.        ch5.YAxis.Title.Caption:='Y Axe';
243.          cser5.SetTitleAddr(0, 1);              // series 1 title in cell A1
244.          cser5.SetYRange(2, 2, i+1, 2);
245.          cser5.SetXRange(1, 2, i+1, 2);
246.      end;
247.      6: begin
248. //         cser6:= TsLineSeries.Create(ch6);
249.        ChartDataDb.First;
250.        ch6.XAxis.Min := ChartDataDb.FieldByName('XValue').AsInteger;
251.        ch6.YAxis.Min := 0;
252.        ChartDataDb.last;
253.        ch6.XAxis.Max :=ChartDataDb.FieldByName('XValue').AsInteger;
254.        ch6.YAxis.Max := ChartDataDb.FieldByName('YValue').AsInteger;
255.        ch6.YAxis.Title.Caption:='Y Axe';
256.          cser6.SetTitleAddr(0, 1);              // series 1 title in cell A1
257.          cser6.SetYRange(2, 2, i+1, 2);
258.          cser6.SetXRange(1, 2, i+1, 2);
259.      end;
260.      7: begin
261. //         cser7:= TsLineSeries.Create(ch7);
262.        ChartDataDb.First;
263.        ch7.XAxis.Min := ChartDataDb.FieldByName('XValue').AsInteger;
264.        ch7.YAxis.Min := 0;
265.        ChartDataDb.last;
266.        ch7.XAxis.Max :=ChartDataDb.FieldByName('XValue').AsInteger;
267.        ch7.YAxis.Max := ChartDataDb.FieldByName('YValue').AsInteger;
268.        ch7.YAxis.Title.Caption:='Y Axe';
269.          cser7.SetTitleAddr(0, 1);              // series 1 title in cell A1
270.          cser7.SetYRange(2, 2, i+1, 2);
271.          cser7.SetXRange(1, 2, i+1, 2);
272.      end;
273.      8: begin
274. //         cser8:= TsLineSeries.Create(ch8);
275.        ChartDataDb.First;
276.        ch8.XAxis.Min := ChartDataDb.FieldByName('XValue').AsInteger;
277.        ch8.YAxis.Min := 0;
278.        ChartDataDb.last;
279.        ch8.XAxis.Max :=ChartDataDb.FieldByName('XValue').AsInteger;
280.        ch8.YAxis.Max := ChartDataDb.FieldByName('YValue').AsInteger;
281.        ch8.YAxis.Title.Caption:='Y Axe';
282.          cser8.SetTitleAddr(0, 1);              // series 1 title in cell A1
283.          cser8.SetYRange(2, 2, i+1, 2);
284.          cser8.SetXRange(1, 2, i+1, 2);
285.      end;
286.      9: begin
287. //         cser9:= TsLineSeries.Create(ch9);
288.        ChartDataDb.First;
289.        ch9.XAxis.Min := ChartDataDb.FieldByName('XValue').AsInteger;
290.        ch9.YAxis.Min := 0;
291.        ChartDataDb.last;
292.        ch9.XAxis.Max :=ChartDataDb.FieldByName('XValue').AsInteger;
293.        ch9.YAxis.Max := ChartDataDb.FieldByName('YValue').AsInteger;
294.        ch9.YAxis.Title.Caption:='Y Axe';
295.          cser9.SetTitleAddr(0, 1);              // series 1 title in cell A1
296.          cser9.SetYRange(2, 2, i+1, 2);
297.          cser9.SetXRange(1, 2, i+1, 2);
298.      end;
299.  
300.      end;
301.  
302.       ChartDataDb.Filtered:=false;
303.     end;
304.  
305.    end;
306.    MWB.WriteToFile(GetCurrentDir + '\testfrafica.xlsx', true);
307.  
308.    MWB.Free;
309.  
310. end;
311.  
312.  

thanks all send me some tips and ideas...