.NET在Excel种画图（线形图）

网上很多种方法是定义ChartObject类或者Excel.Chart类，这样画出的图表是在一个大的Chart表中，而不是在Sheet表单中的图表。

http://support.microsoft.com/kb/302084?wa=wsignin1.0比如这个链接中所述的方法即是上面所说的。

 而我需要的则是在Sheet表中生成的小图，像下面这样：

我们先在Excel种用录制宏的方法来操作下生成线形图，来看看所生成的VBA代码如下：

Sub 宏6()
'
' 宏6 宏
'
       
'
    Columns("B:C").Select
    Range("B3968").Activate
    ActiveSheet.Shapes.AddChart2(227, xlLine).Select
    ActiveChart.SetSourceData Source:=Range("Sheet2!$B:$C")
End Sub

看出区别了，在Sheet表中的Chart应该是属于Shapes.Chart ， 而且用Sheet.Shapes.AddChart来生成（在.net中没有Sheet.Shapes.AddChart2方法）。

结合之前写的向Excel中快速写入大量数据，添加自动生成线形图的功能：

Private Sub SaveToXls(StrData As String, StrFileName As String, Optional StrChartRange As String = Nothing)
   
    'StrChartRange = "A:B" or "A1:B9" or "A:A,D:D"
   
    If String.IsNullOrEmpty(StrData) Or String.IsNullOrEmpty(StrFileName) Then
        Return
    Else
        Dim xlApp As Microsoft.Office.Interop.Excel.Application = New Microsoft.Office.Interop.Excel.Application
   
        If IsNothing(xlApp) Then
            MessageBox.Show("无法创建Excel对象，可能您的系统未安装Excel")
        End If
        xlApp.DefaultFilePath = ""
        xlApp.DisplayAlerts = True
        xlApp.SheetsInNewWorkbook = 1
        Dim xlBook As Microsoft.Office.Interop.Excel.Workbook = xlApp.Workbooks.Add(True)
        Dim xlSheet As Microsoft.Office.Interop.Excel.Worksheet = xlBook.Worksheets.Add()
        System.Windows.Forms.Clipboard.SetDataObject(StrData)
        xlSheet.Paste()
   
        If Not IsNothing(StrChartRange) Then
            LineChart(xlSheet, xlSheet.Range(StrChartRange))
        End If
   
        xlBook.SaveAs(StrFileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal,
            Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
            Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing)
        xlBook.Close()
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlSheet)
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBook)
        xlSheet = Nothing
        xlBook = Nothing
        xlApp.Quit()
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp)
        xlApp = Nothing
        GC.Collect()
    End If
   
End Sub
   
Private Sub LineChart(xlSheet As Microsoft.Office.Interop.Excel.Worksheet, oRange As Microsoft.Office.Interop.Excel.Range)
    Dim oShape As Microsoft.Office.Interop.Excel.Shape = xlSheet.Shapes.AddChart(Microsoft.Office.Interop.Excel.XlChartType.xlLine)
    Dim oChart As Microsoft.Office.Interop.Excel.Chart = oShape.Chart
    oChart.SetSourceData(oRange)
End Sub

以上代码即可产生第二幅图的图表效果！

转载于:https://www.cnblogs.com/jmpep/archive/2013/05/22/4486259.html