在.NET環(huán)境下將報(bào)表導(dǎo)出EXCEL和WORDlihonggen0（原作）

在VB6開(kāi)發(fā)環(huán)境下，本人使用EXCEL作過(guò)報(bào)表，在.NET環(huán)境下開(kāi)發(fā)，本人使用水晶報(bào)表。但VB.NET同樣可以將報(bào)表導(dǎo)出到EXCEL和WORD進(jìn)行輸出，制作出專業(yè)水平的報(bào)表。

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

1．先創(chuàng)建一個(gè)DataTable，作為數(shù)據(jù)來(lái)源，也可以另將其它的數(shù)據(jù)源。

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.將表中的內(nèi)容導(dǎo)出到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 = "黑體"

'設(shè)標(biāo)題為黑體字

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

'標(biāo)題字體加粗

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

'設(shè)表格邊框樣式

End With

With xlSheet.PageSetup

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

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

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

.LeftFooter = "&""楷體_GB2312,常規(guī)""&10制表人："

.CenterFooter = "&""楷體_GB2312,常規(guī)""&10制表日期："

.RightFooter = "&""楷體_GB2312,常規(guī)""&10第&P頁(yè) 共&N頁(yè)"

End With

xlApp.Visible = True

3.將表中的內(nèi)容導(dǎo)出到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

總結(jié)：

E_mail:lihonggen0@163.com