Using the excel COM api from Microsoft.Office.Interop.Excel
, I want to create a Excel table with line graph. In general, this works. But I need to add a second data source (line) to the graph. In Excel, this can be done by a right click on the graph and add a data source with the range. My idea was to do the same in C# and call chart.setDataSource()
twice, but that's not working.
My current testing code:
var excel = new Application() {Visible = true
};
var workbook = excel.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
var worksheet = (Worksheet)workbook.Sheets.Add();
// Demo Data: First column
worksheet.Range["A1", "A1"].Value = "Duration";
worksheet.Range["A2", "A2"].Value = 10;
worksheet.Range["A3", "A3"].Value = 35;
worksheet.Range["A4", "A4"].Value = 3;
// Demo Data: Second column
worksheet.Range["B1", "B1"].Value = "Elements Count";
worksheet.Range["B2", "B2"].Value = 30;
worksheet.Range["B3", "B3"].Value = 580;
worksheet.Range["B4", "B4"].Value = 5;
// Add chart
ChartObjects xlCharts = (ChartObjects)worksheet.ChartObjects(Type.Missing);
ChartObject chartObject = xlCharts.Add(Left: 0, Top: 200, Width: 600, Height: 300);
Chart chart = chartObject.Chart;
// Define source data for chart
Range firstRowRange = worksheet.get_Range("A1", "A4");
Range secondRowRange = worksheet.get_Range("B1", "B4");
chart.SetSourceData(firstRowRange, Missing.Value);
chart.ChartType = XlChartType.xlLine;
This generates a line chart based on the first column (Duration). But does not include the second column (Elements Count). As mentioned above, I added the second column as second data source manually in Excel to demonstrate how it should look like: Example-Screenshots how it should look
The current code generates only the blue line, but not the red. How can this be done in C#?
I found only the workaround to place both columns parallel, and pass a range like A1:B4 to the SetSourceData
method. But this is not an ideal solution as my productive table is bigger and the overview is not optimal when I place all columns which are needed for the grap parallel.