Set x and y axis on an Excel scatter plot in C#

Question

I have data like this in an Excel sheet:

15 0.5   25 0.36
20 0.32  37 0.54
35 0.33  19 0.24

The data is generated by my code and can expand in the way that I add more series. I want to do a scatterplot of the data with x and y coordinates.

using Excel = Microsoft.Office.Interop.Excel;

    public void OpenExcel(string folder, string filename)
    {
        this.folder = folder;
        this.filename = filename;
        path = folder + @"\" + filename;

        if (File.Exists(path) != true) //if the output file does not exists, create it
        {
            var app = new Microsoft.Office.Interop.Excel.Application();
            var temp = app.Workbooks.Add();
            temp.SaveAs(path);
            temp.Close();
        }

        wb = excel.Workbooks.Open(path);
    }

    public void MakeScatterPlot(int numberofseries, int rowlenght) //we already have the data in the sheet at this point
    {
        Excel.Shape chart_shape = workSheet.Shapes.AddChart(Excel.XlChartType.xlXYScatter, 5, 5, 900, 450);
        Excel.Chart chart = chart_shape.Chart;
        Excel.Range chart_range = (Excel.Range)workSheet.Range[workSheet.Cells[1, 1], workSheet.Cells[rowlenght, (numberofseries * 2)]].Cells;
        chart.SetSourceData(chart_range, Excel.XlRowCol.xlColumns);

        for (int i = 1; i <= numberofseries; i++)
        {
           chart.SeriesCollection(i).XValues = (Excel.Range)workSheet.Range[workSheet.Cells[1, 1 + 2 * (i-1)], workSheet.Cells[rowlenght, 1]].Cells; //1, 3, 5...
           chart.SeriesCollection(i).Values = (Excel.Range)workSheet.Range[workSheet.Cells[1, 2 + 2 * (i-1)], workSheet.Cells[rowlenght, 1]].Cells; //2, 4, 6...
        }

    }

The line chart.SeriesCollection(i).XValues seems to work fine, but I get an error and the program stops when I try to set the y-axis I get an error: System.Runtime.InteropServices.COMException: 'Exception from HRESULT: 0x800A03EC'. How do I set the y-axis values?

KGB91 KGB91 · Accepted Answer · 2020-04-23T10:30:32

I seem to just have messed up the cols and rows a bit:

        int xcol = 1 + 2 * (i- 1); //1, 3, 5, 6
        int ycol = xcol++; //2, 5, 7
        chart.SeriesCollection(currentarm).XValues = (Excel.Range)workSheet.Range[workSheet.Cells[1, xcol], workSheet.Cells[rowlenght, **xcol**]].Cells;
        chart.SeriesCollection(currentarm).Values = (Excel.Range)workSheet.Range[workSheet.Cells[1, ycol], workSheet.Cells[rowlenght, **ycol**]].Cells;

Set x and y axis on an Excel scatter plot in C#

1 Answers