export grid to excel fast and wyswyg

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

这个是最快的了 可是局限性同上