用VB.Net导出数据到数据透视表

很多时候可以利用Excel的数据透视表导出你想要的报表格式。那么在.Net下如何做呢？下面的代码可以从数据库中取出数据然后导入Excel。

        Dim excel As Excel.Application
        Dim xBk As Excel._Workbook
        Dim xSt As Excel._Worksheet
        Dim xRange As Excel.Range
        Dim xPivotCache As Excel.PivotCache
        Dim xPivotTable As Excel.PivotTable
        Dim xPivotField As Excel.PivotField
        Dim cnnsr As String, sql As String
        Dim RowFields() As String = {"", "", ""}
        Dim PageFields() As String = {"", "", "", "", "", ""}

        'SERVER     是服务器名或服务器的IP地址
        'DATABASE 是数据库名
        'Table           是表名

        Try
            ' 开始导出
            cnnsr = "ODBC;DRIVER=SQL Server;SERVER=" + SERVER 
            cnnsr = cnnsr + ";UID=;APP=Report Tools;WSID=ReportClient;DATABASE=" + DATABASE
            cnnsr = cnnsr + ";Trusted_Connection=Yes"

            excel = New Excel.ApplicationClass
            xBk = excel.Workbooks.Add(True)
            xSt = xBk.ActiveSheet

            xRange = xSt.Range("A4")
            xRange.Select()

            ' 开始
            xPivotCache = xBk.PivotCaches.Add(SourceType:=2)
            xPivotCache.Connection = cnnsr
            xPivotCache.CommandType = 2

            sql = "select * from " + Table

            xPivotCache.CommandText = sql
            xPivotTable = xPivotCache.CreatePivotTable(TableDestination:="Sheet1!R3C1", TableName:="数据透视表1", DefaultVersion:=1)

            '准备行字段
            RowFields(0) = "字段1"
            RowFields(1) = "字段2"
            RowFields(2) = "字段3"
            '准备页面字段
            PageFields(0) = "字段4"
            PageFields(1) = "字段5"
            PageFields(2) = "字段6"
            PageFields(3) = "字段7"
            PageFields(4) = "字段8"
            PageFields(5) = "字段9"
            xPivotTable.AddFields(RowFields:=RowFields, PageFields:=PageFields)

            xPivotField = xPivotTable.PivotFields("数量")
            xPivotField.Orientation = 4

            ' 关闭工具条
            'xBk.ShowPivotTableFieldList = False
            'excel.CommandBars("PivotTable").visible = False

            excel.Visible = True

        Catch ex As Exception
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            xBk.Close(0)
            excel.Quit()
            MessageBox.Show(ex.Message, "报表工具", MessageBoxButtons.OK, MessageBoxIcon.Warning)
        End Try