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 =
utilizationInfo.Rows[p]["Date"].ToString();
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 =
utilizationInfo.Rows[p]["TotalBillableHours"].ToString();
worksheet.get_Range("D" + (p+2), missing).Value2 =
utilizationInfo.Rows[p]["AvailableHours"].ToString();
}
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);
counter++;
}
}
/
************************************************** ************************************************** **/
// configure chart
/
************************************************** ************************************************** **/
// set the x-axis to be the period end dates
axis = (office.Axis)chartObj.Chart.Axes(office.XlAxisType .xlCategory,
office.XlAxisGroup.xlPrimary);
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;
workbook.Activate();