export grid to excel quickly and wyswyg
2003-12-28 11:50:28.89
'**
'export grid to excel
Private Sub exportExcel(grid As EditGridCtrlLib.EditGridCtrl)
Dim xlApp As Object '*Excel.Application '
Dim xlBook As Object '*Excel.Workbook '
Dim xlSheet As Object '*Excel.Worksheet '
Dim cx As Long
Dim data() As String
Dim cnt As Integer ' visible column's count
Dim curCol As Long
Dim i As Integer
Dim j As Integer
' if no column need output,exit
With grid
cnt = 0
For i = 0 To .Cols - 1
If .ColWidth(i) < 0 Or .ColWidth(i) > 50 Then
cnt = cnt + 1
End If
Next i
End With
If cnt = 0 Then
Exit Sub
End If
cx = GetDeviceCaps(Me.hdc, LOGPIXELSY)
g_Utility.WaiterBegin
On Error GoTo err_proc
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)
xlApp.ScreenUpdating = False
' begin to fill
With Me.grdList
ReDim data(.Rows - 1, cnt - 1)
curCol = 0
For i = 0 To .Cols - 1
If .ColWidth(i) < 0 Or .ColWidth(i) > 50 Then
For j = 0 To .Rows - 1
data(j, curCol) = .TextMatrix(j, i)
Next j
xlSheet.Columns(curCol + 1).Select
If Fix(.ColAlignment(i) / 3) = 0 Then
xlApp.Selection.HorizontalAlignment = -4131 ' xlLeft
End If
If Fix(.ColAlignment(i) / 3) = 1 Then
xlApp.Selection.HorizontalAlignment = -4108 ' xlCenter
End If
If Fix(.ColAlignment(i) / 3) = 2 Then
xlApp.Selection.HorizontalAlignment = -4152 ' xlRight
End If
' resize column width
xlSheet.Columns(curCol + 1).ColumnWidth = .ColWidth(CLng(i)) / cx
curCol = curCol + 1
End If
Next i
End With
With xlSheet
.range(.cells(1, 1), .cells(Me.grdList.Rows, cnt)).value = data
End With
' colheader align center
xlSheet.Rows(1).Select
xlApp.Selection.HorizontalAlignment = -4108 ' xlCenter
xlApp.ActiveSheet.pagesetup.PrintGridlines = True
If Me.grdList.FixedRows > 0 Then
xlApp.ActiveSheet.pagesetup.PrintTitleRows = xlSheet.Rows(Me.grdList.FixedRows).Address
End If
If Me.grdList.FixedCols > 0 Then
xlApp.ActiveSheet.pagesetup.PrintTitleColumns = xlSheet.Columns(Me.grdList.FixedCols).Address
End If
xlApp.ScreenUpdating = True
xlApp.Visible = True
xlApp.ActiveWorkbook.printPreview
xlApp.DisplayAlerts = False
xlApp.ActiveWorkbook.Close False
xlApp.DisplayAlerts = True
xlApp.Quit
Set xlApp = Nothing
Set xlBook = Nothing
Set xlSheet = Nothing
g_Utility.WaitEnd
Exit Sub
err_proc:
g_Utility.WaitEnd
If Not xlApp Is Nothing Then
xlApp.Quit
Set xlApp = Nothing
End If
g_ErrLog.ShowMessage Err
End Sub
说明：
1、使用range.value一次性填充数据，可以极大地加快速度
2、ScreenUpdating 设为false可以加快速度
3、对不可见列不打印，并且根据grid来设置对齐方式
4、迟绑定可以减少去excel版本的依赖性
为什么使用这种方法而不是其它更快速的方法
1、copyformrecordset
这样的话就需要一个ado的结果集才可以操作，而且对列的对齐、列头文本、不可见列的操作都无法进行
2、querytable
由于在三层开发中客户端并没有办法直接访问数据库，同时它还存在着和上面一样的缺陷
3、bcp
这个是最快的了 可是局限性同上
