export grid to excel fast and wyswyg

<script type="text/javascript"><!-- google_ad_client = "pub-2947489232296736"; /* 728x15, 创建于 08-4-23MSDN */ google_ad_slot = "3624277373"; google_ad_width = 728; google_ad_height = 15; //--> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
<script type="text/javascript"><!-- google_ad_client = "pub-2947489232296736"; /* 160x600, 创建于 08-4-23MSDN */ google_ad_slot = "4367022601"; google_ad_width = 160; google_ad_height = 600; //--> </script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
export grid to excel quickly and wyswyg2003-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
   这个是最快的了 可是局限性同上

<script type="text/javascript"><!-- google_ad_client = "pub-2947489232296736"; /* 728x15, 创建于 08-4-23MSDN */ google_ad_slot = "3624277373"; google_ad_width = 728; google_ad_height = 15; //--> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
<script type="text/javascript"><!-- google_ad_client = "pub-2947489232296736"; /* 160x600, 创建于 08-4-23MSDN */ google_ad_slot = "4367022601"; google_ad_width = 160; google_ad_height = 600; //--> </script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
阅读更多
个人分类: vb
想对作者说点什么? 我来说一句

export grid view with images.

2011年11月29日 2.97MB 下载

没有更多推荐了,返回首页

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭