There are many ways to export data to Excel, and the speed is slow. I have used three methods, the speed is faster, and one of the following uses Excel built-in function is the fastest. The most important thing is the following two sentences: xlquery: = xlsheet.querytables.add (adoqexport.recordset, xlsheet.Rreg "); xlquery.refresh; but I am a little more complicated here, to complete classification through some condition Summary.
function ExportToExcel: Boolean; var xlApp, xlBook, xlSheet, xlQuery: Variant; SQLCmd: String; i, iNextRow: Integer; // default cell format setting procedure ExcelSetDefaultFormat; begin xlSheet.Cells.Font.Name: = 'Arial' Xlsheet.cells.font.size: = 12; xlsheet.cells.vertiTicalAlignment: = 2; //xlsheet.cells.RowHeight: = 17.25; xlsheet.range ['c: d']. Horizontalalight physical: = xlcenter; end; // Output Title Procedure ExcelsetHeader; Begin Xlsheet.Range ['A1']. Value: = 'Shows the title' in the first line of the report '; Xlsheet.Range [' A1: F1 ']. Horizontalalignment: = 7; xlsheet.range ['1: 1']. Font.size: = 18; Xlsheet.Range ['1: 1']. Font.bold: = true; xlsheet.range ['a2']. Value: = 'file number: WL / B 19 '; Xlsheet.Range [' A2 ']. Font.size: = 11; xlsheet.range [' f2 ']. Value: =' record number: GZ-023 '; Xlsheet.Range [' F2 '] . HorizontalaLAlignment: = xlright; xlsheet.range ['f2']. Font.size: = 11; Xlsheet.Range ['A3']. Value: = 'xxxxx limited company'; xlsheet.range ['f3']. Value : = 'Date: 2005-xx'; xlsheet.range ['f3']. Horizontalalignment: = XLRIGHT; / / Output field name ADOQExport.SQL.Strings [4]: = 'where 1 = 0'; if ADOQExport.Active then ADOQExport.Requery else ADOQExport.Open; xlQuery: = xlSheet.QueryTables.Add (ADOQExport.Recordset, xlSheet.Range [ 'A4' ]); xlQuery.FieldNames: = true; xlQuery.RowNumbers: = False; xlQuery.FillAdjacentFormulas: = False; xlQuery.PreserveFormatting: = True; xlQuery.RefreshOnFileOpen: = False; xlQuery.BackgroundQuery: = True; xlQuery.RefreshStyle: =
xlOverwriteCells; // xlInsertDeleteCells; xlQuery.SavePassword: = True; xlQuery.SaveData: = True; xlQuery.AdjustColumnWidth: = True; xlQuery.RefreshPeriod: = 0; xlQuery.PreserveColumnInfo: = True; xlQuery.Refresh; iNextRow: = 5; End; // Set footage procedure excelsetfooter; begin xlsheet.pagesetup.Leftfooter: = 'Tab:' DM.Userinfo.uSername; xlsheet.pagesetup.centerfooter: = 'audit:'; xlsheet.pagesetup.rightfooter: = ' Page P Pages, Total & N Page '; END; // Output Summary Data Procedure ExcelsetSum; Begin Xlsheet.Range [Format (' a% d ', [INEXTROW])]. Value: =' bar counting (strip) '; Xlsheet.range [format ('a% d: b% 0: D', [inextrow])]. Horizontalalignment: = 7; xlsheet.range [format ('c% d', [inextrow])]. Value: = FLOATTOSTR (DBGRIDEH1.COLUMNS [6] .footer.sumvalue; xlsheet.range [format ('c% d: f% 0: D', [inextrow])]. Horizontalalignment: = 7; xlsheet.range [format (' A% D: f% 0: D ', [inextrow])]. Font.bold: = true; inc (inextrow); xlsheet.range [format (' a% d ', [inextrow])]. Value: = 'Weight of weight (kg)'; Xlsheet.range [Form AT ('a% d: b% 0: d', [inextrow])]. Horizontalalignment: = 7; xlsheet.range [format ('c% d', [inextrow])]. Value: = floattostr (DBGrideh1. Columns [7] .footer.sumvalue; xlsheet.range [format ('c% d: f% 0: d', [inextrow])]. Horizontalalignment: = 7; xlsheet.range [format ('a% d: F% 0: D ', [inextrow])]. Font.bold: = true; end; // According to the category output data to Excel Procedure ExportData; begin sqlcmd: = format (' where datatype =% D ', [Datatype]); adoqexport.sql.strings [4]: = SQLCMD;
if ADOQExport.Active then ADOQExport.Requery else ADOQExport.Open; ProgressBar1.StepIt; if not ADOQExport.IsEmpty then begin // title xlSheet.Range [Format ( 'A% d', [iNextRow])] Value:. = DM. GetDataTypeStr (Datatype); // converts DataType to the corresponding text display Xlsheet.Range [Format ('a% d: f% 0: D', [inextrow])]. Horizontalalignment: = 7; xlsheet.range [Format 'A% d: f% 0: D', [inextrow])]. Font.bold: = true; inc (inextrow); xlquery: = xlsheet.querytables.add (adoqexport.recordset, xlsheet.range [format (' A% d ', [inextrow])]); xlquery.fieldnames: = false; xlquery.refresh; inc (INEXTROW, AdoqExport.Recordcount); xlsheet.range [format (' a% d ', [inextrow])]. Value: = DM.GetDataTypeStr (Datatype) 'Total (strip)'; xlsheet.range [format ('a% d: b% 0: D', [inextrow])]. Horizontalalignment: = 7; xlsheet.range [ Format ('c% d', [inextrow])]. Value: = format ('= SUM (C% D: C% D)', [inextrow-adoquexport.recordcount, inextrow-1]); Xlsheet.Range [ Format ('D% D', [INEXTROW])]. Value : = Format ('= SUM (D% D: D% D)', [INEXTROW-AdoqExport.Recordcount, INEXTROW-1]); Xlsheet.Range [Format ('a% d: f% 0: D', [ INEXTROW])]. font.bold: = true; inc; projectbar1.stepit; end; begin result: = true; showProgress (0, cbbDataType.KeyItems.count * 2 2, 0); // Call the function display progress panel Screen.cursor: = CRHOURGLAS; TRY Try // Establish OLE object xlapp: = createoleObject ('Excel.Application'); xlbook: = xlapp.workbooks.add; xlsheet: = xlbook.worksheets ['Sheet1']; XLAPP.Visible: = false;
