用VB.Net匯出資料到樞紐分析表

來源:互聯網
上載者:User
匯出資料


很多時候可以利用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


 



相關文章

Beyond APAC's No.1 Cloud

19.6% IaaS Market Share in Asia Pacific - Gartner IT Service report, 2018

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。