如何使用c＃在Excel中创建点图，折线图？

I'm use these namespaces: Excel = Microsoft.Office.Interop.Excel; and Microsoft.Office.Tools.Excel;

我使用这些命名空间：Excel = Microsoft.Office.Interop.Excel;和Microsoft.Office.Tools.Excel;

I need to create a line chart with many short lines, which all will be parallel to the x axis.

我需要创建一个包含许多短行的折线图，这些短行都将与x轴平行。

I already have written all needed points, which should be connected with line.

我已经写了所有需要的点，应该用线连接。

y axis values for the several type of lines are the same, not they aren't numbers, they are a sort of name. Eventually this should looks this way:

几种类型的线的y轴值是相同的，不是它们不是数字，它们是一种名称。最终这应该是这样的：

I tried to do this in my way, but faced some problems seriesCollection doesn`t work as should, he draws 3 lines, but I can see only last one, 2 previous line become same dot. Here is the code:

我试图以我的方式做到这一点，但遇到一些问题，seriesCollection不能正常工作，他绘制了3行，但我只能看到最后一行，2前一行成为同一个点。这是代码：

        Excel.Workbook xlWorkBook;
        Excel.Worksheet xlWorkSheet;
        object misValue = System.Reflection.Missing.Value;
        Excel._Application xlApp = new Excel.Application();
        xlWorkBook = xlApp.Workbooks.Add(misValue);
        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

        //add data 
        xlWorkSheet.Cells[1, 1] = 13;
        xlWorkSheet.Cells[1, 2] = 27;
        xlWorkSheet.Cells[1, 3] = 22;
        xlWorkSheet.Cells[1, 4] = 22;

        xlWorkSheet.Cells[2, 1] = 42    ;
        xlWorkSheet.Cells[2, 2] = 35;
        xlWorkSheet.Cells[2, 3] = 22;
        xlWorkSheet.Cells[2, 4] = 22;

        xlWorkSheet.Cells[3, 1] = 1;
        xlWorkSheet.Cells[3, 2] = 10;
        xlWorkSheet.Cells[3, 3] = 4;
        xlWorkSheet.Cells[3, 4] = 4;


        Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
        Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(10, 80, 300, 250);
        Excel.Chart chartPage = myChart.Chart;
        myChart.Select();

        chartPage.ChartType = Excel.XlChartType.xlXYScatterLines;
        Microsoft.Office.Interop.Excel.Application xla = new Microsoft.Office.Interop.Excel.Application();
        Excel.SeriesCollection seriesCollection = chartPage.SeriesCollection();

        Excel.Series series1 = seriesCollection.NewSeries();
        series1.XValues = xlWorkSheet.get_Range("A1", "B1"); ;
        series1.Values = xlWorkSheet.get_Range("C1", "D1");

        Excel.Series series2 = seriesCollection.NewSeries();
        series2.XValues = xlWorkSheet.get_Range("A2", "B2"); ;
        series2.Values = xlWorkSheet.get_Range("C2", "D2");

        Excel.Series series3 = seriesCollection.NewSeries();
        series3.XValues = xlWorkSheet.get_Range("A3", "B3"); ;
        series3.Values = xlWorkSheet.get_Range("C3", "D3"); 

Don't know how to solve this problem. I think y-axis can't be a text, only numbers. Each line should be another series, I guess.

不知道如何解决这个问题。我认为y轴不能是文字，只能是数字。我猜，每一行应该是另一个系列。

P.S. Numbers can be represented in any required ways to simplify the work.

附：数字可以用任何所需的方式表示，以简化工作。

UPD: This is how I created this dot chart, I have recorded macros

UPD：这就是我创建这个点图的方法，我记录了宏

Sub Dot_chart()
'
'
'

'
    ActiveSheet.Shapes.AddChart2(240, xlXYScatterLines).Select
    ActiveWindow.SmallScroll Down:=6
    ActiveSheet.Shapes("Chart 2").IncrementLeft 120.8823622047
    ActiveSheet.Shapes("Chart 2").IncrementTop 132.3529133858
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.FullSeriesCollection(1).Name = "=List1!$B$23"
    ActiveChart.FullSeriesCollection(1).XValues = "=List1!$D$22:$D$23"
    ActiveChart.FullSeriesCollection(1).Values = "=List1!$D$24:$D$25"
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.FullSeriesCollection(2).Name = "=List1!$B$23"
    ActiveChart.FullSeriesCollection(2).XValues = "=List1!$E$22:$E$23"
    ActiveChart.FullSeriesCollection(2).Values = "=List1!$E$24:$E$25"
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.FullSeriesCollection(3).Name = "=List1!$B$28"
    ActiveChart.FullSeriesCollection(3).XValues = "=List1!$D$27:$D$28"
    ActiveChart.FullSeriesCollection(3).Values = "=List1!$D$29:$D$30"
End Sub

List1 sheet`s name.

List1表的名称。

1 个解决方案

#1

After days of searching I found the solution, hope it will be useful for someone.

经过几天的搜索，我找到了解决方案，希望它对某人有用。

            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            object misValue = System.Reflection.Missing.Value;
            Excel._Application xlApp = new Excel.Application();

            xlWorkBook = xlApp.Workbooks.Add(misValue);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

        //add data 
        xlWorkSheet.Cells[1, 1] = 13;
        xlWorkSheet.Cells[1, 2] = 27;
        xlWorkSheet.Cells[1, 3] = 22;
        xlWorkSheet.Cells[1, 4] = 22;

        xlWorkSheet.Cells[2, 1] = 42    ;
        xlWorkSheet.Cells[2, 2] = 35;
        xlWorkSheet.Cells[2, 3] = 22;
        xlWorkSheet.Cells[2, 4] = 22;

        xlWorkSheet.Cells[3, 1] = 1;
        xlWorkSheet.Cells[3, 2] = 10;
        xlWorkSheet.Cells[3, 3] = 4;
        xlWorkSheet.Cells[3, 4] = 4;


        Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
        Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(10, 80, 300, 250);
        Excel.Chart chartPage = myChart.Chart;
        myChart.Select();

        chartPage.ChartType = Excel.XlChartType.xlXYScatterLines;
        Microsoft.Office.Interop.Excel.Application xla = new Microsoft.Office.Interop.Excel.Application();
        Excel.SeriesCollection seriesCollection = chartPage.SeriesCollection();

        Excel.Series series1 = seriesCollection.NewSeries();
        series1.XValues = xlWorkSheet.get_Range("A1", "B1"); ;
        series1.Values = xlWorkSheet.get_Range("C1", "D1");

        Excel.Series series2 = seriesCollection.NewSeries();
        series2.XValues = xlWorkSheet.get_Range("A2", "B2"); ;
        series2.Values = xlWorkSheet.get_Range("C2", "D2");

        Excel.Series series3 = seriesCollection.NewSeries();
        series3.XValues = xlWorkSheet.get_Range("A3", "B3"); ;
        series3.Values = xlWorkSheet.get_Range("C3", "D3"); 



        xlWorkBook.SaveAs(@"C:\ProgramData\RadiolocationQ\Text.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
        xlWorkBook.Close(true, misValue, misValue);
        xlApp.Quit();

        releaseObject(xlWorkSheet);
        releaseObject(xlWorkBook);
        releaseObject(xlApp);


        Process.Start(@"C:\ProgramData\RadiolocationQ\Text.xls");