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

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

---

<!---【孟宪会之精彩世界】--->

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

<xmp>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 = "" &amp; Chr(10) &amp; "&amp;""楷体_GB2312,常规""&amp;10公司名称：" ' &amp; Gsmc .CenterHeader = "&amp;""楷体_GB2312,常规""公司人员情况表&amp;""宋体,常规""" &amp; Chr(10) &amp;_ "&amp;""楷体_GB2312,常规""&amp;10日 期：" .RightHeader = "" &amp; Chr(10) &amp; "&amp;""楷体_GB2312,常规""&amp;10单位：" .LeftFooter = "&amp;""楷体_GB2312,常规""&amp;10制表人：" .CenterFooter = "&amp;""楷体_GB2312,常规""&amp;10制表日期：" .RightFooter = "&amp;""楷体_GB2312,常规""&amp;10第&amp;P页 共&amp;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 </xmp>

参看本的帖子
http://community.csdn.net/Expert/topic/3417/3417163.xml?temp=.9710504

发布一个操作Excel类库 功能 excel文件创建修改 单元格数据格式编辑 图片 图表 插入控制 外部数据的添加 插入等

下载地址：http://www.journeychina.com/OfficeEditor/

ExcelEditor提供以下方法：

'创建一个Excel对象
Public Sub Creat()

'打开一个Excel对象
Public Sub Open(ByVal FileName As String)
参数描述：FileName ：excel文件路径

'获取一个工作表
Public Function GetSheet(ByVal SheetName As String) As Excel.Worksheet
参数描述：SheetName ：工作表名称

'添加一个工作表
Public Function AddSheet(ByVal SheetName As String) As Excel.Worksheet

'删除一个工作表
Public Function DelSheet(ByVal SheetName As String)

'重命名一个工作表

Public Function ReNameSheet(ByVal OldSheetName As String, ByVal NewSheetName As String) As

Excel.Worksheet

Public Function ReNameSheet(ByVal Sheet As Excel.Worksheet, ByVal NewSheetName As String) As

Excel.Worksheet

'设置一个单元格的值

Public Sub SetCellValue(ByVal ws As Excel.Worksheet, ByVal x As Integer, ByVal y As Integer, ByVal

value As Object)

参数说明 ws：要设值的工作表 X行Y列 value 值
Public Sub SetCellValue(ByVal ws As String, ByVal x As Integer, ByVal y As Integer, ByVal value As

Object)

' 设置一个单元格的属性 字体， 大小，颜色 ，对齐方式

Public Sub SetCellProperty(ByVal ws As Excel.Worksheet, ByVal Startx As Integer, ByVal Starty As

Integer, ByVal Endx As Integer, ByVal Endy As Integer, ByVal size As Integer, Optional ByVal name As

String = "宋体", Optional ByVal color As Excel.Constants = Excel.Constants.xlAutomatic, Optional ByVal

HorizontalAlignment As Excel.Constants = Excel.Constants.xlLeft)

参数说明 ：ws：要设值的工作表 Startx 起始单元格的行 Starty 起始单元格的列 Endx 结束单元格的行 Endy 结束

单元格的列 size 字体大小 name 字体 color 颜色 HorizontalAlignment 对齐方式

Public Sub SetCellProperty(ByVal wsn As String, ByVal Startx As Integer, ByVal Starty As Integer,

ByVal Endx As Integer, ByVal Endy As Integer, Optional ByVal size As Integer = 12, Optional ByVal name

As String = "宋体", Optional ByVal color As Excel.Constants = Excel.Constants.xlAutomatic, Optional

ByVal HorizontalAlignment As Excel.Constants = Excel.Constants.xlAutomatic.xlRight)

'合并单元格
Public Sub UniteCells(ByVal ws As Excel.Worksheet, ByVal Startx As Integer, ByVal Starty As Integer,

ByVal Endx As Integer, ByVal Endy As Integer)

参数说明 ：ws：要设值的工作表 Startx 起始单元格的行 Starty 起始单元格的列 Endx 结束单元格的行 Endy 结束

单元格的列

'将内存中数据表格插入到Excel指定工作表的指定位置 为在使用摩板时控制格式时使用
Public Sub InsertTable(ByVal dt As Data.DataTable, ByVal ws As String, ByVal startX As Integer,

ByVal startY As Integer)

Public Sub InsertTable(ByVal dt As Data.DataTable, ByVal ws As Excel.Worksheet, ByVal startX As

Integer, ByVal startY As Integer)

'将内存中数据表格添加到Excel指定工作表的指定位置
Public Sub AddTable(ByVal dt As Data.DataTable, ByVal ws As String, ByVal startX As Integer, ByVal

startY As Integer)
Public Sub AddTable(ByVal dt As Data.DataTable, ByVal ws As Excel.Worksheet, ByVal startX As Integer,

ByVal startY As Integer)

'插入图片操作"
Sub InsertPictures(ByVal Filename As String, ByVal ws As String)

Sub InsertPictures(ByVal Filename As String, ByVal ws As String, ByVal Height As Integer, ByVal Width

As Integer)

Sub InsertPictures(ByVal Filename As String, ByVal ws As String, ByVal left As Integer, ByVal top As

Integer, ByVal Height As Integer, ByVal Width As Integer)

插入图表操作"
Public Sub InsertActiveChart(ByVal ChartType As Excel.XlChartType, ByVal ws As String, ByVal

DataSourcesX1 As Integer, ByVal DataSourcesY1 As Integer, ByVal DataSourcesX2 As Integer, ByVal

DataSourcesY2 As Integer, Optional ByVal ChartDataType As Excel.XlRowCol = Excel.XlRowCol.xlColumns)

参数说明 ：ChartType 为图表类型 ws：要设值的工作表名 DataSourcesX1 图表数据源起始单元格的行

DataSourcesY1 图表数据源起始单元格的列 DataSourcesX2 图表数据源结束单元格的行 DataSourcesY2 图表数据源结

束单元格的列 ChartDataType 图表数据数据方式

' 保存文档
Public Function Save() As Boolean

' 文档另存为
Public Function SaveAs(ByVal FileName) As Boolean

' 关闭一个Excel对象，销毁对象
Public Sub Close()

这个一定要有！要不然 在系统进程中回有很EXCEL进程没有销毁！

使用方法：引用：VBExcelWordEditor.dll com引用： Microsoft Excel 10.0 Object Library

暂时只做了这些功能，慢慢扩展