Excel Interops - Want multiple datasources on one chart

[c0dergirl]

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 SeriesCollectio n class to
accomplish this.

Here's my code. Thanks in advance.


excel = new office.Applicat ion();
workbook = excel.Workbooks .Add(missing);
worksheet = (office.Workshe et)workbook.Act iveSheet;

office.ChartObj ects charts =
(office.ChartOb jects)worksheet .ChartObjects(m issing);
office.ChartObj ect chartObj = charts.Add(200, 20,800,300);
chartObj.Chart. ChartType = office.XlChartT ype.xlLine;
chartObj.Chart. HasLegend = true;

int counter=0;
foreach(ListIte m item in individualsList .Items)
{
if (item.Selected)
{
worksheet = (office.Workshe et)workbook.Wor ksheets.Add(mis sing,
missing, missing, missing);

System.Diagnost ics.Trace.Write Line("worksheet created");
string selectedItem = item.Text;
string selectedValue = item.Value;

// get the DataTable with the employees information
utilizationInfo = reportLogic.Get UtilizationTabl e(selectedValue );

// fill in the information
for (int p=0; p<utilizationIn fo.Rows.Count; p++)
{
worksheet.get_R ange("A" + (p+2), missing).Value2 =
utilizationInfo .Rows[p]["Date"].ToString();
double utilization = Double.Parse(ut ilizationInfo.R ows[p]
["Utilizatio n"].ToString()) * 100;
worksheet.get_R ange("B" + (p+2), missing).Value2 = (int)
(utilization + 0.5);
worksheet.get_R ange("C" + (p+2), missing).Value2 =
utilizationInfo .Rows[p]["TotalBillableH ours"].ToString();
worksheet.get_R ange("D" + (p+2), missing).Value2 =
utilizationInfo .Rows[p]["AvailableHours "].ToString();
}
range = worksheet.get_R ange("A1", "B1");
range.ColumnWid th = 12;

office.Range chartRange = worksheet.get_R ange("B2", "B40");
chartObj.Chart. SetSourceData(c hartRange, missing);
seriesCollectio n =
(office.SeriesC ollection)chart Obj.Chart.Serie sCollection(mis sing);

counter++;
}
}

/
*************** *************** *************** *************** *************** *************** ************/
// configure chart
/
*************** *************** *************** *************** *************** *************** ************/
// set the x-axis to be the period end dates
axis = (office.Axis)ch artObj.Chart.Ax es(office.XlAxi sType.xlCategor y,
office.XlAxisGr oup.xlPrimary);
axis.HasTitle = true;
axis.AxisTitle. Text = "Timesheet Period Date";
office.Range axisRange = worksheet.get_R ange("A2", "A40");
axis.CategoryNa mes = axisRange;

series = seriesCollectio n.Item(seriesCo llection.Count) ;

// show excel
excel.Visible = true;
workbook.Activa te();