将数据导出到Excel的方法有多种，速度有快慢之分，我用过三种方法，速度都比较快，下面的一种利用Excel内置的功能，是三种之中最快的。其中最主要的是下面两句：
xlQuery := xlSheet.QueryTables.Add(ADOQExport.Recordset ,xlSheet.Range[''A3'']);
xlQuery.Refresh;
不过我这里稍为复杂一点，要通过某种条件完成分类汇总。
function ExportToExcel: Boolean;
var
xlApp, xlBook, xlSheet, xlQuery: Variant;
SQLCmd: String;
i, iNextRow: Integer;
//设定单元格默认格式
procedure ExcelSetDefaultFormat;
begin
xlSheet.Cells.Font.Name := ''宋体'';
xlSheet.Cells.Font.Size := 12;
xlSheet.Cells.VerticalAlignment := 2;
//xlSheet.Cells.RowHeight := 17.25;
xlSheet.Range[''C:D''].HorizontalAlignment := xlCenter;
end;
//输出标题
procedure ExcelSetHeader;
begin
xlSheet.Range[''A1''].Value := ''显示在报表第一行的标题'';
xlSheet.Range[''A1:F1''].HorizontalAlignment := 7;
xlSheet.Range[''1:1''].Font.Size := 18;
xlSheet.Range[''1:1''].Font.Bold := true;
xlSheet.Range[''A2''].Value := ''文件编号：WL/B 19'';
xlSheet.Range[''A2''].Font.Size := 11;
xlSheet.Range[''F2''].Value := ''记录编号：GZ-023'';
xlSheet.Range[''F2''].HorizontalAlignment := xlRight;
xlSheet.Range[''F2''].Font.Size := 11;
xlSheet.Range[''A3''].Value := ''XXXXX有限公司'';
xlSheet.Range[''F3''].Value := ''日期：2005-X-X'' ;
xlSheet.Range[''F3''].HorizontalAlignment := xlRight;
//输出字段名
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;
//设置页脚
procedure ExcelSetFooter;
begin
xlSheet.PageSetup.LeftFooter := ''制表：'' + DM.UserInfo.UserName;
xlSheet.PageSetup.CenterFooter := ''审核：'';
xlSheet.PageSetup.RightFooter := ''第 &P 页，共 &N 页'';
end;
//输出汇总数据
procedure ExcelSetSum;
begin
xlSheet.Range[Format(''A%d'', [iNextRow])].Value := ''条数合计(条)'';
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 := ''重量合计(kg)'';
xlSheet.Range[Format(''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;
//根据类别输出数据到Excel
procedure ExportData(DataType: Byte);
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
//标题
xlSheet.Range[Format(''A%d'', [iNextRow])].Value := DM.GetDataTypeStr(DataType);//将DataType转换为相应的文字显示
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) + ''合计(条)'';
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-ADOQExport.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(iNextRow);
end;
ProgressBar1.StepIt;
end;
begin
Result := true;
ShowProgress(0, cbbDataType.KeyItems.Count*2+2, 0); //调用前面例子中的函数显示进度面板
Screen.Cursor := crHourGlass;
try try
//建立OLE对象
xlApp := CreateOleObject(''Excel.Application'');
xlBook := xlApp.Workbooks.Add;
xlSheet := xlBook.Worksheets[''sheet1''];
xlApp.Visible := false;
ProgressBar1.StepIt;
//设置格式
ExcelSetDefaultFormat;
//输出标题内容
ExcelSetHeader;
ProgressBar1.StepIt;
//查询结果，导到EXCEL
for i:=0 to cbbDataType.KeyItems.Count-1 do //cbbDataType: TDBComboBoxEh
ExportData(StrToInt(cbbDataType.KeyItems.Strings[i]));
//输出汇总内容
ExcelSetSum;
//设置边框
xlSheet.Range[Format(''A4:F%d'', [iNextRow])].Borders.LineStyle := xlContinuous;
xlSheet.Cells.EntireColumn.AutoFit;
//输出页脚
ExcelSetFooter;
except
if not VarIsNull(xlApp) then
begin
xlApp.Quit;
xlApp.Disconnect;
xlApp := Unassigned;
xlApp := NULL;
end;
result := false;
Exit;
end;
finally
pnlShadow.Visible := false;
pnlProgress.Visible := false;
Screen.Cursor := crDefault;
xlSheet := Unassigned;
xlBook := Unassigned;
if not VarIsNull(xlApp) then begin
xlApp.Visible := true;
xlApp := Unassigned;
end;
if ADOQExport.Active then ADOQExport.Close;
end;
end;
|
