问题描述:

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.

相关阅读:
Top