用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