|
很多时候可以利用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
没有相关教程
| 
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 
