在.NET环境下将报表导出EXCEL和WORD

在VB6开发环境下，本人使用EXCEL作过报表，在.NET环境下开发，本人使用水晶报表。但VB.NET同样可以将报表导出到EXCEL和WORD进行输出，制作出专业水平的报表。

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