470,614 Members | 1,576 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,614 developers. It's quick & easy.

Excel Interops - Want multiple datasources on one chart

This c# program allows you to select multiple items from a listbox.
For each selected item, a worksheet is created in the workbook with
some information. Right now I create a chart that plots some
information from one of the sheets. What I want to do is have a line
on the graph for every sheet. I assume I use multiple datasources to
do this, but I don't know how to use the SeriesCollection class to
accomplish this.

Here's my code. Thanks in advance.
excel = new office.Application();
workbook = excel.Workbooks.Add(missing);
worksheet = (office.Worksheet)workbook.ActiveSheet;

office.ChartObjects charts =
(office.ChartObjects)worksheet.ChartObjects(missin g);
office.ChartObject chartObj = charts.Add(200,20,800,300);
chartObj.Chart.ChartType = office.XlChartType.xlLine;
chartObj.Chart.HasLegend = true;

int counter=0;
foreach(ListItem item in individualsList.Items)
if (item.Selected)
worksheet = (office.Worksheet)workbook.Worksheets.Add(missing,
missing, missing, missing);

System.Diagnostics.Trace.WriteLine("worksheet created");
string selectedItem = item.Text;
string selectedValue = item.Value;

// get the DataTable with the employees information
utilizationInfo = reportLogic.GetUtilizationTable(selectedValue);

// fill in the information
for (int p=0; p<utilizationInfo.Rows.Count; p++)
worksheet.get_Range("A" + (p+2), missing).Value2 =
double utilization = Double.Parse(utilizationInfo.Rows[p]
["Utilization"].ToString()) * 100;
worksheet.get_Range("B" + (p+2), missing).Value2 = (int)
(utilization + 0.5);
worksheet.get_Range("C" + (p+2), missing).Value2 =
worksheet.get_Range("D" + (p+2), missing).Value2 =
range = worksheet.get_Range("A1", "B1");
range.ColumnWidth = 12;

office.Range chartRange = worksheet.get_Range("B2", "B40");
chartObj.Chart.SetSourceData(chartRange, missing);
seriesCollection =
(office.SeriesCollection)chartObj.Chart.SeriesColl ection(missing);


************************************************** ************************************************** **/
// configure chart
************************************************** ************************************************** **/
// set the x-axis to be the period end dates
axis = (office.Axis)chartObj.Chart.Axes(office.XlAxisType .xlCategory,
axis.HasTitle = true;
axis.AxisTitle.Text = "Timesheet Period Date";
office.Range axisRange = worksheet.get_Range("A2", "A40");
axis.CategoryNames = axisRange;

series = seriesCollection.Item(seriesCollection.Count);

// show excel
excel.Visible = true;

Mar 1 '07 #1
0 3447

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

13 posts views Thread by Allison Bailey | last post: by
1 post views Thread by Jaime Lucci | last post: by
6 posts views Thread by Thelma Lubkin | last post: by
3 posts views Thread by toffee | last post: by
9 posts views Thread by AGP | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.