Export Grid To Excel Quickly and Wyswyg2003-12-28 11: 50: 28.89
'**
'Export Grid to Excel
Private Sub ExportExcel (Grid AS EditGridCtrlIb.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 colorn 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 (I) / 3) = 0 THEN
XLapp.Serection.horizontalaLAlignment = -4131 'XLLEFT
END IF
IF fix (I) / 3) = 1 THEN
XLapp.Serection.horizontalaLAlignment = -4108 'XLCenter
END IF
IF FIX (.COLALIGNMENT (I) / 3) = 2 THEN
XLapp.Serection.horizontalaLAlignment = -4152 'XLRIGHT
END IF
'Resize Column Width
Xlsheet.Columns (Curcol 1) .ColumnWidth = .COLWIDTH (CLNG (i)) / cxcurcol = 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.Serection.horizontalaLAlignment = -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
Description: 1. Use Range.Value disposable to fill the data, which can be greatly accelerated. The speed is set to FALSE to speed up the speed 3. If you do not print it, it is not printed, and the alignment is set according to Grid, which can be reduced. Why is the dependency of the Excel version to use this method rather than other faster methods 1. If you need a result of the ADO's result settlement, and the column is aligned, the list text, the unacceptable manner Unable to perform 2, queryTable Due to the passage of the client directly accessed directly in the three-story development, it still has the same defects as above 3, BCP is the fastest, but the limit is the same.
