在.NET环境下将报表导出Excel和Word

新客网 XKER.COM 2004-11-03 来源： 收藏本文

在VB.NET同样可以将报表导出到Excel和Word进行输出，制作出专业水平的报表。具体操作如下：(注：首先需添加引用，选择COM-->选择Microsoft Word Object Library和Microsoft Excel Object Library组件)

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 As Integer = 1
Dim colIndex As Integer = 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

• ·身份证函数 查看身份证地区信息
• ·VS2008 第一次安装心得及使用
• ·ASP.NET 2.0跨网页提交的三法
• ·编程实例 WebGroupBox(Aspx控件)
• ·asp.net mvc脚手架代码生成工具
• ·用独立的DLL来存储图片(资源文件)
• ·ASP.NET中多国语言的实现方法
• ·实例 .net生成静态页方法总结
• ·ASP.NET控件学习笔记之ViewState
• ·用递归在TreeView价节点
• ·经验总结 关于.NET 中的Event机制
• ·.NET应用程序开发标准化(z)
• ·在DataTable中查询应该注意的问题
• ·LINQ 中调用存储过程自动绑定列名
• ·如何用.NET技术在线生成网站LOGO
• ·对于访问IIS元数据库失败的解决