用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