注:首先需添加引用,选择COM-->选择Microsoft Excel Object Library组件
1、建立内存表结构 createMemoryTable
2、把表结构写入excel中
代码如下:
Public Class Form1
Inherits System.Windows.Forms.Form
Dim sqlDs As New Data.DataSet '数据在内存中的缓存
Dim sqlTables As New Data.DataTable("sqltables") '数据表
#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 DataGrid1 As System.Windows.Forms.DataGrid
Friend WithEvents Button2 As System.Windows.Forms.Button
Friend WithEvents Button3 As System.Windows.Forms.Button
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
Me.Button1 = New System.Windows.Forms.Button
Me.DataGrid1 = New System.Windows.Forms.DataGrid
Me.Button2 = New System.Windows.Forms.Button
Me.Button3 = New System.Windows.Forms.Button
CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).BeginInit()
Me.SuspendLayout()
'
'Button1
'
Me.Button1.Location = New System.Drawing.Point(152, 224)
Me.Button1.Name = "Button1"
Me.Button1.Size = New System.Drawing.Size(168, 32)
Me.Button1.TabIndex = 0
Me.Button1.Text = "写入excel"
'
'DataGrid1
'
Me.DataGrid1.DataMember = ""
Me.DataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText
Me.DataGrid1.Location = New System.Drawing.Point(0, 8)
Me.DataGrid1.Name = "DataGrid1"
Me.DataGrid1.Size = New System.Drawing.Size(448, 152)
Me.DataGrid1.TabIndex = 1
'
'Button2
'
Me.Button2.Location = New System.Drawing.Point(152, 176)
Me.Button2.Name = "Button2"
Me.Button2.Size = New System.Drawing.Size(168, 32)
Me.Button2.TabIndex = 2
Me.Button2.Text = "浏览内存表"
'
'Button3
'
Me.Button3.Location = New System.Drawing.Point(16, 280)
Me.Button3.Name = "Button3"
Me.Button3.Size = New System.Drawing.Size(424, 32)
Me.Button3.TabIndex = 3
Me.Button3.Text = "在datagrid中修改数据后,再重新写入excel,利用excel显示图例,汇总"
'
'Form1
'
Me.AutoScaleBaseSize = New System.Drawing.Size(6, 14)
Me.ClientSize = New System.Drawing.Size(456, 341)
Me.Controls.Add(Me.Button3)
Me.Controls.Add(Me.Button2)
Me.Controls.Add(Me.DataGrid1)
Me.Controls.Add(Me.Button1)
Me.MaximizeBox = False
Me.Name = "Form1"
Me.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen
Me.Text = "Form1"
CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).EndInit()
Me.ResumeLayout(False)
End Sub
#End Region
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
End Sub
'函数功能:在内存中建立一个表
'输入参数:
'输出参数:
Private Sub createMemoryTable()
sqlTables.Clear()
'给数据表加入行标头
sqlTables.Columns.Add("ID", GetType(String))
sqlTables.Columns.Add("Name", GetType(String))
sqlTables.Columns.Add("Age", GetType(Int16))
'加入每一行数据
Dim rowData As Data.DataRow
rowData = sqlTables.NewRow
'插入数据
rowData("ID") = "1"
rowData("Name") = "Loken"
rowData("Age") = 24
sqlTables.Rows.Add(rowData)
'插入加一条记录
rowData = sqlTables.NewRow
rowData("ID") = "2"
rowData("Name") = "Amada"
rowData("Age") = 26
sqlTables.Rows.Add(rowData)
'把表插入到DataSet中
sqlDs.Tables.Add(sqlTables)
End Sub
'将表中的内容写到excel中
Private Sub writeExcel()
Dim xlApp As New Excel.Application
Dim xlbook As Excel.Workbook
Dim xlsheet As Excel.Worksheet
Dim rowIndex As Int16 = 1
Dim colIndex As Int16 = 0
xlbook = xlApp.Workbooks.Add
xlsheet = xlApp.Sheets.Add
'把列标头加入excel中
Dim row As Data.DataRow
Dim col As DataColumn
For Each col In sqlDs.Tables("sqltables").Columns
colIndex = colIndex + 1
xlApp.Cells(1, colIndex) = col.ColumnName
Next
'把内容加入到excel中
For Each row In sqlDs.Tables("sqltables").Rows
rowIndex = rowIndex + 1
colIndex = 0
For Each col In sqlDs.Tables("sqltables").Columns
colIndex = colIndex + 1
xlApp.Cells(rowIndex, colIndex) = row.Item(colIndex - 1)
'下列语句也可
'xlApp.Cells(rowIndex, colIndex) = row(col.ColumnName)
Next
Next
Try
'设定风格
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
Catch
MessageBox.Show(Err.Description)
End Try
xlApp.Visible = True
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
writeExcel()
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
'初始化表
createMemoryTable()
DataGrid1.SetDataBinding(sqlDs, "sqltables")
End Sub
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
'打开excel
Dim xlApplication As New Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim filePath As String
filePath = Application.StartupPath & "/book1.xls"
'打开一个已经建立好的模板文件
xlBook = xlApplication.Workbooks.Open(filePath)
'将当前工作薄的第一个工作表赋给XSHEET对象
xlSheet = xlBook.Sheets(1)
xlApplication.Visible = True
'把DataGrid中的数据写入
Dim i As Int16
Dim j As Int16
For i = 0 To sqlDs.Tables("sqltables").Rows.Count - 1
For j = 0 To sqlDs.Tables("sqltables").Columns.Count - 1
xlApplication.Cells(i + 2, j + 1) = DataGrid1.Item(i, j) '从第二行开始写
Next
Next
End Sub
Private Sub Form1_Closing(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles MyBase.Closing
Application.Exit()
End Sub
End Class