IDC资讯中心
很多时候可以利用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
