在.NET環境下將報表匯出EXCEL和WORD lihonggen0（原作）

excel|word|匯出excel 在.NET環境下將報表匯出EXCEL和WORD    lihonggen0（原作）  
  
在VB6開發環境下，本人使用EXCEL作過報表，在.NET環境下開發，本人使用水晶報表。但VB.NET同樣可以將報表匯出到EXCEL和WORD進行輸出，製作出專業水平的報表。

具體操作如下：(註：首先需添加引用，選擇COM-->選擇Microsoft Word 10.0 Object Library和Microsoft Excel 10.0 Object Library組件)

1．先建立一個DataTable，作為資料來源，也可以另將其它的資料來源。

    Private Function CreaTable() As DataTable

        Dim dt As New DataTable()

        dt.Columns.Add("列1", GetType(String))

        dt.Columns.Add("列2", GetType(Integer))

        dt.Columns.Add("列3", GetType(String))

        dt.Columns.Add("列4", GetType(String))

        Dim row, row1 As DataRow

        row = dt.NewRow()

        row!列1 = "行1"

        row!列2 = 1

        row!列3 = "d"

        row!列4 = "a"

        dt.Rows.Add(row)

        row1 = dt.NewRow()

        row1!列1 = "行2"

        row1!列2 = 12

        row1!列3 = "b"

        row1!列4 = "c"

        dt.Rows.Add(row1)

        Return dt

    End Function

2.將表中的內容匯出到Excel

                Dim xlApp As New Excel.Application()

                Dim xlBook As Excel.Workbook

                Dim xlSheet As Excel.Worksheet

                Dim rowIndex, colIndex As Integer

                rowIndex = 1

                colIndex = 0

                xlBook = xlApp.Workbooks().Add

                xlSheet = xlBook.Worksheets("sheet1")

                Dim Table As New DataTable()

                Table = CreaTable()

                '將所得到的表的列名,賦值給儲存格

                Dim Col As DataColumn

                Dim Row As DataRow

                For Each Col In Table.Columns

                    colIndex = colIndex + 1

                    xlApp.Cells(1, colIndex) = Col.ColumnName

                Next

                '得到的表所有行,賦值給儲存格

                For Each Row In Table.Rows

                    rowIndex = rowIndex + 1

                    colIndex = 0

                    For Each Col In Table.Columns

                        colIndex = colIndex + 1

                        xlApp.Cells(rowIndex, colIndex) = Row(Col.ColumnName)

                    Next

                Next

                With xlSheet

                    .Range(.Cells(1, 1), .Cells(1, colIndex)).Font.Name = "黑體"

                    '設標題為黑體字

                    .Range(.Cells(1, 1), .Cells(1, colIndex)).Font.Bold = True

                    '標題字型加粗

                    .Range(.Cells(1, 1), .Cells(rowIndex, colIndex)).Borders.LineStyle = 1

                    '設表格邊框樣式

                End With

                With xlSheet.PageSetup

                    .LeftHeader = "" & Chr(10) & "&""楷體_GB2312,常規""&10公司名稱："   ' & Gsmc

                    .CenterHeader = "&""楷體_GB2312,常規""公司人員情況表&""宋體,常規""" & Chr(10) & "&""楷體_GB2312,常規""&10日 期："

                    .RightHeader = "" & Chr(10) & "&""楷體_GB2312,常規""&10單位："

                    .LeftFooter = "&""楷體_GB2312,常規""&10製表人："

                    .CenterFooter = "&""楷體_GB2312,常規""&10製表日期："

                    .RightFooter = "&""楷體_GB2312,常規""&10第&P頁 共&N頁"

                End With

                xlApp.Visible = True

3.將表中的內容匯出到WORD

                Dim wordApp As New Word.Application()

                Dim myDoc As Word.Document

                Dim oTable As Word.Table

                Dim rowIndex, colIndex As Integer

                rowIndex = 1

                colIndex = 0

                wordApp.Documents.Add()

                myDoc = wordApp.ActiveDocument

                Dim Table As New DataTable()

                Table = CreaTable()

                oTable = myDoc.Tables.Add(Range:=myDoc.Range(Start:=0, End:=0), NumRows:=Table.Rows.Count + 1, NumColumns:=Table.Columns.Count)

                '將所得到的表的列名,賦值給儲存格

                Dim Col As DataColumn

                Dim Row As DataRow

                For Each Col In Table.Columns

                    colIndex = colIndex + 1

                    oTable.Cell(1, colIndex).Range.InsertAfter(Col.ColumnName)

                Next

                '得到的表所有行,賦值給儲存格

                For Each Row In Table.Rows

                    rowIndex = rowIndex + 1

                    colIndex = 0

                    For Each Col In Table.Columns

                        colIndex = colIndex + 1

                        oTable.Cell(rowIndex, colIndex).Range.InsertAfter(Row(Col.ColumnName))

                    Next

                Next

                oTable.Borders.InsideLineStyle = 1   

                oTable.Borders.OutsideLineStyle = 1

                wordApp.Visible = True

總結：

E_mail:lihonggen0@163.com