export grid to excel fast and wyswyg

<script type="text/javascript"> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>

<script type="text/javascript"> </script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>

export grid to excel quickly and wyswyg 2003-12-28 11:50:28.89
'**
' export grid to excel
Private Sub export excel( grid As Edit gridCtrlLib.Edit gridCtrl)
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 Go to 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.Print gridlines = 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
   这个是最快的了 可是局限性同上

<script type="text/javascript"> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>

<script type="text/javascript"> </script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>