作者:乔山办公网日期:
返回目录:excel表格制作
1、用数组方式快速导出MSFlexGrid表格数据到Excel表格中
Public Sub ExportToExcel(ByRef objGrid As MSHFlexGrid, ByVal strFileName As String, Optional StartRow As Long = 1, Optional StartColumn As Long = 1)
Dim objApp As Object
Dim objWorkbook As Object
Dim objWorksheet As Object
Dim objRange As Object
Dim CellsData() As String
Dim i As Long, j As Long
Dim nRows As Long, nColumns As Long
'构造二维数组
nRows = objGrid.Rows
nColumns = objGrid.Cols
ReDim CellsData(1 To nRows, 1 To nColumns)
For i = 1 To nRows
For j = 1 To nColumns
CellsData(i, j) = objGrid.TextMatrix(i - 1, j - 1)
Next
Next
'导出到Excel中
If StartRow < 1 Then StartRow = 1
If StartColumn < 1 Then StartColumn = 1
Set objApp = CreateObject("Excel.Application")
objApp.ScreenUpdating = False '禁止屏幕刷新
Set objWorkbook = objApp.Workbooks.Add
Set objWorksheet = objWorkbook.Sheets.Add
Set objRange = objWorksheet.Range(objWorksheet.Cells(StartRow, StartColumn), objWorksheet.Cells((StartRow - 1) + nRows, (StartColumn - 1) + nColumns))
objRange.Value = CellsData
objWorkbook.SaveAs strFileName '保存到指定文件
objWorkbook.Close
objApp.Quit '退出Excel
Set objRange = Nothing
Set objWorksheet = Nothing
Set objWorkbook = Nothing
Set objApp = Nothing
'销毁二维数组
Erase CellsData
End Sub
2、测试代码如下:
Option Explicit
Private Sub Form_Load()
Dim i As Long, j As Long
'填充测636f7079e799bee5baa6e997aee7ad94365试用数据
Me.MSHFlexGrid1.Rows = 2000
Me.MSHFlexGrid1.Cols = 10
For i = 0 To Me.MSHFlexGrid1.Rows - 1
For j = 0 To Me.MSHFlexGrid1.Cols - 1
Me.MSHFlexGrid1.TextMatrix(i, j) = i & "行" & j & "列"
Next
Next
Debug.Print Me.MSHFlexGrid1.TextArray(100)
End Sub
Private Sub cmdExport_Click()
ExportToExcel Me.MSHFlexGrid1, "d:/temp.xls"
Me.SetFocus
MsgBox "导出完毕"
End Sub
3、测试环境vb6企业版,excel2003
QQ:179623605 给你发一个实例...
加我时请注明: 百度VB数据库
可对Ole控件关联对象的Excel表格的单元格的Value 属性赋值来写入VB已读到的数据。
Value 属性,返回或设置一个Variant型数,它指定该属性之值,此属性可读/写。
以下是实现代码:
Option Explicit
Dim oBok As Object '定义oBok为Object 数据类型
Private Sub Command1_Click()
OLE1.CreateLink "C:\zhang2012.xls" '打开要写入的Excel表
Set oBok = OLE1.object '将对象引用赋给变量或属性。
oBok.Sheets(1).Range("A1").Value = "VB已读取到数据库数据" '将数据写入Excel表的A列第一行
Set oBok = Nothing '使用 Nothing 关键字被将对象变量从实际对象中7a64e78988e69d83362分离开来。
End Sub
Object 变量存储为 32 位(4 个字节)的地址形式,其为对象的引用。利用 Set 语句,声明为 Object 的变量可以赋值为任何对象的引用。
注意 虽然以 Object 类型声明的变量足以适应包含对各种对象的引用,但是绑定到变量引用的对象总是在晚期(运行时)绑定。要强迫在早期(编译时间)绑定的话,须将对象的引用赋值给用特定类名称声明的变量。
使用 Nothing 关键字被将对象变量从实际对象中分离开来。要使用
Set 语句将 Nothing 赋值给对象变量。例如:
Set MyObject = Nothing
Set 语句,将对象引用赋给变量或属性。
Adodc1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\source.xls;Extended Properties='Excel 8.0;HDR=yes;IMEX=1'"
Adodc1.RecordSource = "select * from [表名copy百$] where …度…"
Adodc1.Refresh
With Adodc1.Recordset
set MSHFlexGrid1.DataSource = Adodc1
MSHFlexGrid1.Refresh