有好的东东,当然与大家一起分享了,大家一起学习了
'************************************
'功能：实现表数据输出到word.excel.txt
'时间：2007-07-11
'编写人：王朝东
'QQ：280685904
'操作：编写
'************************************
Imports System
Imports System.Data
Imports System.Drawing
Imports System.Data.SqlClient
Imports System.IO
Public Class DatasToClass DatasTo
Private Const WORDPOSTFIX = ".doc"
Private Const EXCELPOSTFIX = ".xls"
Private Const TXTPOSTFIX = ".txt"
Private Const DATADISTANCE = 5
Private Const TABDISTANCE = 8
''' <summary>
''' 将datatable里的数据导出到Excel文件
''' </summary>
''' <param name="dt">需要处理的数据表</param>
''' <param name="myFilename">包含有文件路径的文件名</param>
''' <param name="myCaption">生成文件的标题名</param>
''' <remarks></remarks>
Public Sub ToExcel()Sub ToExcel(ByVal dt As DataTable, ByVal myFilename As String, Optional ByVal myCaption As String = "")
If myCaption = "" Then
myCaption = dt.TableName.ToString
End If
Dim filename As String = myFilename
If filename.Length > 4 Then
If filename.Substring(filename.Length - 4, 4) <> ".xls" Then
filename = myFilename + EXCELPOSTFIX
End If
End If
Dim No As Object = System.Reflection.Missing.Value
Dim myExcel As Microsoft.Office.Interop.Excel.Application = New Microsoft.Office.Interop.Excel.ApplicationClass()
myExcel.Application.Workbooks.Add(No)
'把Dataset中的数据插入excel文件中
Dim totalCount As Integer = 0
Dim row As Integer = dt.Rows.Count
Dim column As Integer = dt.Columns.Count
Dim i As Integer
'通过循环添加列标题
For i = 0 To column - 1
myExcel.Cells(totalCount + 2, 1 + i) = dt.Columns(i).ColumnName
Next
'通过循环实现数据的填充
For i = 0 To row - 1
Dim j As Integer
For j = 0 To column - 1
myExcel.Cells(totalCount + 3 + i, 1 + j) = dt.Rows(i)(j).ToString()
Next
Next
totalCount = totalCount + row + 4
'设置表的样式
Dim CaptionRange As Microsoft.Office.Interop.Excel.Range '设置标题样式
CaptionRange = myExcel.Range(myExcel.Cells(1, 1), myExcel.Cells(1, column))
CaptionRange.Font.Name = "黑体" '设标题为黑体字
CaptionRange.Font.Bold = True '标题字体加粗
CaptionRange.MergeCells = True
CaptionRange.WrapText = False
CaptionRange.Orientation = 0
CaptionRange.AddIndent = False
CaptionRange.IndentLevel = 0
CaptionRange.ShrinkToFit = False
CaptionRange.FormulaR1C1 = myCaption
CaptionRange.HorizontalAlignment = 3 '1 常规 2 靠左 3表示居中对齐，8表示分散对齐
Dim BordersRange As Microsoft.Office.Interop.Excel.Range
BordersRange = myExcel.Range(myExcel.Cells(2, 1), myExcel.Cells(row + 2, column))
BordersRange.Borders.LineStyle = 0 '设表格边框样式 0没有边框
'保存excel文件到指定的目录下
myExcel.ActiveWorkbook._SaveAs(filename, No, No, No, No, No, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, No, No, No, No)
'让生成的excel文件可见
myExcel.Visible = True
End Sub
''' <summary>
''' 将datatable里的数据导出到Word文件
''' </summary>
''' <param name="dt">数据表</param>
''' <param name="myFilename">保存的文件名</param>
''' <param name="myCaption">默认为表的TableName</param>
''' <remarks></remarks>
Public Sub ToWord()Sub ToWord(ByVal dt As DataTable, ByVal myFilename As String, Optional ByVal myCaption As String = "")
If myCaption = "" Then
myCaption = dt.TableName.ToString
End If
Dim filename As String = myFilename
If filename.Length > 4 Then
If filename.Substring(filename.Length - 4, 4) <> ".doc" Then
filename = myFilename + WORDPOSTFIX
End If
End If
Dim tableBehavior = Microsoft.Office.Interop.Word.WdDefaultTableBehavior.wdWord9TableBehavior
Dim autoFitBehavior = Microsoft.Office.Interop.Word.WdAutoFitBehavior.wdAutoFitFixed
Dim unit = Microsoft.Office.Interop.Word.WdUnits.wdStory
Dim extend = System.Reflection.Missing.Value
Dim breakType = Int(Microsoft.Office.Interop.Word.WdBreakType.wdSectionBreakNextPage)
Dim count = 1
Dim character = Microsoft.Office.Interop.Word.WdUnits.wdCharacter
'no
Dim No = System.Reflection.Missing.Value
'把数据导入word文件中
Dim myWord As Microsoft.Office.Interop.Word.Application = New Microsoft.Office.Interop.Word.ApplicationClass()
Dim myDoc As Microsoft.Office.Interop.Word.Document = New Microsoft.Office.Interop.Word.DocumentClass()
myDoc = myWord.Documents.Add(No, No, No, No) 'no
myDoc.Activate()
'向把datatable中的表插入到word的文件中()
Dim totalTable As Integer
myWord.Application.Selection.TypeText(myCaption)
myWord.Application.Selection.ParagraphFormat.Alignment = Microsoft.Office.Interop.Word.WdParagraphAlignment.wdAlignParagraphCenter
myWord.Application.Selection.TypeParagraph()
myWord.Application.Selection.TypeParagraph()
Dim para As Microsoft.Office.Interop.Word.Range = myWord.Application.Selection.Range
myDoc.Tables.Add(para, dt.Rows.Count + 1, dt.Columns.Count, tableBehavior, autoFitBehavior)
'通过循环添加列标题
Dim column As Integer
Dim row As Integer
For column = 0 To dt.Columns.Count - 1
myDoc.Tables.Item(totalTable + 1).Cell(1, column + 1).Range.InsertBefore(dt.Columns(column).ColumnName.Trim)
Next
'通过循环实现数据的填充
For row = 0 To dt.Rows.Count - 1
For column = 0 To dt.Columns.Count - 1
myDoc.Tables.Item(totalTable + 1).Cell(row + 2, column + 1).Range.InsertBefore(dt.Rows(row)(column).ToString().Trim())
Next
Next
myWord.Application.Selection.EndKey(unit, extend)
myWord.Application.Selection.TypeParagraph()
myWord.Application.Selection.TypeParagraph()
myWord.Application.Selection.InsertBreak(breakType)
myWord.Application.Selection.TypeBackspace()
myWord.Application.Selection.Delete(character, count)
myWord.Application.Selection.HomeKey(unit, extend)
'保存word文件到指定的目录下，文件名 用系统时间生成精确到毫秒
myDoc.SaveAs(filename, No, No, No, No, No, No, No, No, No, No)
'让生成的word文件可见
myWord.Visible = True
End Sub
''' <summary>
''' 将datatable里的数据导出到Text文件
''' </summary>
''' <param name="dt">数据表</param>
''' <param name="myFilename">保存的文件名</param>
''' <param name="myCaption">默认为表的TableName</param>
''' <remarks></remarks>
Public Sub ToText()Sub ToText(ByVal dt As DataTable, ByVal myFilename As String, Optional ByVal myCaption As String = "")
If myCaption = "" Then
myCaption = dt.TableName.ToString
End If
Dim filename As String = myFilename
If filename.Length > 4 Then
If filename.Substring(filename.Length - 4, 4) <> ".txt" Then
filename = myFilename + TXTPOSTFIX
End If
End If
Dim file As FileInfo = New FileInfo(myFilename)
Dim textFile As StreamWriter
textFile = file.CreateText()
Dim row As Integer = dt.Rows.Count '统计datatable中当前表的行数
Dim column As Integer = dt.Columns.Count '统计datatable中当前表的列数
Dim totalLength As Integer = 0 '用于统计当前表中每列记录中字符数最长的字符串的长度之和
Dim titleLength As Integer = 0 '用于统计标题的长度（datatable中的表名的length+"表的数据如下"的length）
Dim columnLength(column) As Integer '统计每列记录中字符数最长的字符串的长度
Dim i As Integer
Dim j As Integer
Dim k As Integer
For i = 0 To column - 1
columnLength(i) = dt.Columns(i).ColumnName.ToString().Length
Next
For i = 0 To row - 1
For j = 0 To column - 1
If (dt.Rows(i)(j).ToString().Length > columnLength(j)) Then
columnLength(j) = dt.Rows(i)(j).ToString().Length
End If
Next
Next
For i = 0 To column - 1
totalLength = totalLength + columnLength(i) + DATADISTANCE
Next
totalLength = totalLength + 2 * TABDISTANCE - DATADISTANCE
'统计标题的长度（datatable的length）
titleLength = myCaption.Length
'把标题写入.txt文件中
For i = 0 To Int((totalLength - titleLength) / 2) - 1
textFile.Write(" ")
Next
textFile.Write(myCaption)
textFile.WriteLine()
For i = 0 To totalLength - 1
textFile.Write("*")
Next
textFile.WriteLine()
textFile.Write("\t")
'把datatbale中当前表的字段名写入.txt文件中
For i = 0 To column - 1
textFile.Write(dt.Columns(i).ColumnName.ToString())
For k = 0 To columnLength(i) - dt.Columns(i).ColumnName.ToString().Length + DATADISTANCE - 1
textFile.Write(" ")
Next
Next
textFile.WriteLine()
For i = 0 To totalLength - 1
textFile.Write("-")
Next
textFile.WriteLine()
textFile.Write("\t")
'把datatable中当前表的数据写入.txt文件中
For i = 0 To row - 1
For j = 0 To column - 1
textFile.Write(dt.Rows(i)(j).ToString())
For k = 0 To columnLength(j) - dt.Rows(i)(j).ToString().Length + DATADISTANCE - 1
textFile.Write(" ")
Next
Next
textFile.WriteLine()
textFile.Write("\t")
Next
textFile.WriteLine()
For i = 0 To totalLength - 1
textFile.Write("-")
Next
textFile.WriteLine()
textFile.WriteLine()
textFile.WriteLine()
'关闭当前的StreamWriter流
textFile.Close()
System.Diagnostics.Process.Start(file.FullName)
End Sub
End Class
学习的也比较浅,朝东献丑了,大家还是有什么意见或好的观点及时交流了