By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,680 Members | 2,139 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,680 IT Pros & Developers. It's quick & easy.

Excel Interops - Want multiple datasources on one chart

P: n/a
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();

Mar 1 '07 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.