很多時候可以利用excel的數(shù)據(jù)透視表導(dǎo)出你想要的報表格式。那么在.net下如何做呢？下面的代碼可以從數(shù)據(jù)庫中取出數(shù)據(jù)然后導(dǎo)入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     是服務(wù)器名或服務(wù)器的ip地址
        'database 是數(shù)據(jù)庫名
        'table           是表名

        try
            ' 開始導(dǎo)出
            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:="數(shù)據(jù)透視表1", defaultversion:=1)

            '準(zhǔn)備行字段
            rowfields(0) = "字段1"
            rowfields(1) = "字段2"
            rowfields(2) = "字段3"
            '準(zhǔn)備頁面字段
            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("數(shù)量")
            xpivotfield.orientation = 4

            ' 關(guān)閉工具條
            '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