Creating an X x Y chart in Excel using C #

Question

I am trying to create a chart in Excel using C #. What I have done so far is to populate an Excel spreadsheet with the data I need to build. I have data in my table that looks like this:

TU 10 10 20 5 30 3,333333333 40 2,5 50 2 60 1,666666667 70 1,428571429 80 1,25 90 1,111111111 100 1 110 0,909090909 120 0,833333333 130 0,769230769 140 0,714285714 150 0,666666667 160 0,625 

I have data from row 1 to row 40. I want to create a chart in Excel like this:

Note that on the x-axis I want to put the values ​​(or some of the values) from column T and along the y-axis the values ​​from column U.

I created the following code to create the chart:

 object misValue = System.Reflection.Missing.Value; Microsoft.Office.Interop.Excel.Application xla = new Microsoft.Office.Interop.Excel.Application(); Workbook wb = xla.Workbooks.Add(XlSheetType.xlWorksheet); Worksheet ws = (Worksheet)xla.ActiveSheet; Microsoft.Office.Interop.Excel.Range chartRange; Microsoft.Office.Interop.Excel.ChartObjects xlCharts = (Microsoft.Office.Interop.Excel.ChartObjects)ws.ChartObjects(Type.Missing); Microsoft.Office.Interop.Excel.ChartObject myChart = (Microsoft.Office.Interop.Excel.ChartObject)xlCharts.Add(240, 90, 468, 315); Microsoft.Office.Interop.Excel.Chart chartPage = myChart.Chart; chartRange = ws.get_Range("U1", "U40"); chartPage.SetSourceData(chartRange, misValue); chartPage.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlLine; 

which gave me the following result:

which is close to what I need, but I could not set the values ​​for the X axis. Can someone tell me how to assign values ​​from the T1 range in T40 to the X axis? And if possible, how can I remove the “Série 1" label from the chart?

Answer 1

This is six months, so I assume you have already switched, but:

You already have a link to the chart ( chartPage ), so setting the x-axis and deleting the legend should be simple. You just need a link to the series.

 Excel.Series ser = (Excel.Series)chartPage.SeriesCollection(1); ser.xValues = ws.Range[ws.cells[row,col],ws.cells[row,col]]; chartPage.hasLegend = false; 

This was written under the assumption that you received the alias Microsoft.Office.Interop.Excel as follows:

 using Excel = Microsoft.Office.Interop.Excel; 

and that the series you are working on is actually the first. You can also set y values ​​the same way, instead of setting the source data.

 ser.Values = ws.Range[etc...]; 

Answer 2

I have to build another data range in Excel for a line chart ... with the code below, I can only build one data range. Is there a way to plot two datasets in one graph

  // Set chart range. var range = ws.get_Range("c4","c35"); var range1 = ws.get_Range("c74", "c105"); chart.SetSourceData(range); chart.SetSourceData(range1); // Set chart properties. chart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlLine; chart.ChartWizard(Source: range, Title: "s", CategoryTitle: "xAxis", ValueTitle: "yAxis"); chart.ChartWizard(Source: range1, Title: "s", CategoryTitle: "xAxis", ValueTitle: "yAxis");