CODEWITHSUNDEEP
exceldelphi
SIJO
SIJO

Reputation: 45

Delphi 6 - Create Excel chart from delphi application - data and chart on the same page

I need to create an excel chart in an excel page from a delphi program.

I am getting 'Member not found' error on reaching the line.

ch1.Chart.SeriesCollection.Item[0].Values := Sheets.Item['Delphi Data'].Range['E5:E15'];

Can you please help me to solve it

Below given is the code used.

procedure TForm1.ChartData;
var
     ARange,Sheets,ch1 : Variant; 
     SSeries : Series; 
     num : integer; 
     ChartAxis : Axis; 
     lcid :  Cardinal; 
begin 
  ch1 := XLApp.ActiveWorkBook.Sheets[1].ChartObjects.Add ( 500,100,400,200 ); // creates a new chart in the specified 
  Sheets := XLApp.Sheets; 
  ch1.Chart.ChartWizard ( 
                         Sheets.Item['Delphi Data'].Range['D5:D15'],   // 1  Source 
                         xlBarStacked, //  2  The chart type. 
                         8,            //  3  Format  
                         2,            //  4  PlotBy  
                         8,            //  5  CategoryLabels  
                         3,            //  6  SeriesLabels  
                         True,        //  7  HasLegend - 'true' to include a legend. 
                         'Sijos Report',  // 8  Title - The Chart control title text. 
                         'Y Legend',      // 9  CategoryTitle - The category axis title text. 
                         'X Legend',      // 10  ValueTitle - The value axis title text 
                         2                // 11  ExtraTitle - The series axis title for 3-D charts or the second value axis title for 2-D charts. 
                       ); 
ch1.Chart.SetSourceData(Sheets.Item['Delphi Data'].Range['D5:D15'],xlColumns); 

ch1.Chart.SeriesCollection.Item[0].Values := Sheets.Item['Delphi Data'].Range['E5:E15']; 
ch1.Chart.SeriesCollection.Item[0].XValues := Sheets.Item['Delphi Data'].Range['F5:F15'];  
End;

Upvotes: 2

Views: 4750

Answers (1)

David Heffernan
David Heffernan

Reputation: 612854

I think you need ch1.Chart.SeriesCollection(1) rather than ch1.Chart.SeriesCollection.Item[0] since Excel uses 1-based indexing.

I also could not get your code, using late bound COM to work at all when accessing the series object. But if you switch to using early bound COM then it is fine. You will need to add Excel2000, for example, to your uses clause.

var
  S: Series;
....
S := IUnknown(ch1.Chart.SeriesCollection(1)) as Series;
S.Values := Sheets.Item['Delphi Data'].Range['E5:E15']; 
S.XValues := Sheets.Item['Delphi Data'].Range['F5:F15'];

If I were you I would switch the whole code to early bound.

I think your code is based on this Delphi 3 example from Charlie Calvert. I could not get that to work on my Delphi 6. Perhaps Delphi changed. Perhaps Excel changed. No matter, the thing that made it work for me was to switch to early bound COM.

Upvotes: 4

Related Questions