VB2005输出Word和Excel报表

 

Public Class Form1
    Inherits System.Windows.Forms.Form

#Region " Windows 窗体设计器生成的代码 "

    Public Sub New()
        MyBase.New()

        '该调用是 Windows 窗体设计器所必需的。
        InitializeComponent()

        '在 InitializeComponent() 调用之后添加任何初始化

    End Sub

    '窗体重写 dispose 以清理组件列表。
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub

    'Windows 窗体设计器所必需的
    Private components As System.ComponentModel.IContainer

    '注意: 以下过程是 Windows 窗体设计器所必需的
    '可以使用 Windows 窗体设计器修改此过程。
    '不要使用代码编辑器修改它。
    Friend WithEvents Button1 As System.Windows.Forms.Button
    Friend WithEvents Button2 As System.Windows.Forms.Button
    Private Sub InitializeComponent()
        Me.Button1 = New System.Windows.Forms.Button
        Me.Button2 = New System.Windows.Forms.Button
        Me.SuspendLayout()
        '
        'Button1
        '
        Me.Button1.Location = New System.Drawing.Point(40, 56)
        Me.Button1.Name = "Button1"
        Me.Button1.Size = New System.Drawing.Size(104, 40)
        Me.Button1.TabIndex = 0
        Me.Button1.Text = "输出Word报表"
        '
        'Button2
        '
        Me.Button2.Location = New System.Drawing.Point(224, 56)
        Me.Button2.Name = "Button2"
        Me.Button2.Size = New System.Drawing.Size(104, 40)
        Me.Button2.TabIndex = 1
        Me.Button2.Text = "输出Excel报表"
        Me.Button2.TextAlign = System.Drawing.ContentAlignment.MiddleRight
        '
        'Form1
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(6, 14)
        Me.ClientSize = New System.Drawing.Size(384, 149)
        Me.Controls.Add(Me.Button2)
        Me.Controls.Add(Me.Button1)
        Me.MaximizeBox = False
        Me.MinimizeBox = False
        Me.Name = "Form1"
        Me.Text = "输出Word和Excel报表"
        Me.ResumeLayout(False)

    End Su

 

b

#End Region
    Private Function CreaTable() As DataTable
        Dim dt As New DataTable
        dt.Columns.Add("姓名", GetType(String))
        dt.Columns.Add("性别", GetType(String))
        dt.Columns.Add("年龄", GetType(Integer))
        dt.Columns.Add("毕业学校", GetType(String))
        Dim row, row1 As DataRow
        row = dt.NewRow()
        row 姓名 = "小李"
        row 性别 = "男"
        row 年龄 = 22
        row 毕业学校 = " 清华大学"
        dt.Rows.Add(row)
        row1 = dt.NewRow()
        row1 姓名 = "小黄"
        row1 性别 = "女"
        row1 年龄 = 24
        row1 毕业学校 = "西安交通大学"
        dt.Rows.Add(row1)
        Return dt
    End Function

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        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
    End Sub

  
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        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
    End Sub
End Class