VB.NET开发语言为我们平时的开发方式带来了非常的改变。很多时候可以利用Excel的数据透视表导出你想要的报表格式。那么VB.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
开始实现VB.NET导出数据
- cnnsr = "ODBC;DRIVER=SQL
Server;SERVER=" + SERVER- cnnsrcnnsr = cnnsr + ";UID=;
APP=Report Tools;WSID=ReportClient;
DATABASE=" + DATABASE- cnnsrcnnsr = 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:=RowFields, PageFields
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
VB.NET导出数据的具体代码编写就为大家介绍到这里。
【编辑推荐】
