将数据导出到Excel的方法有多种,速度有快慢之分,我用过三种方法,速度都比较快,下面的一种是利用临时文件进行,与前一种方法相比,在打开临时文件时可以指定字符集编码,因而可以保证中文内容正确导出。
function ExportByTmpFile: Boolean;var
List: TStringList;
FileName, TmpFile: string;
Buffer: array[0..MAX_PATH] of Char;
ASheet: Variant;
begin
//调用上例中的函数显示进度条
ShowProgress(0, ADOQuery1.RecordCount+3, 0);
Result := true;
FileName := 'C:/abc.xls'; //文件名根据实际情况修改,可作参数传入
//生成临时文件名
GetTempPath(MAX_PATH, Buffer);
TmpFile := Buffer;
if RightStr(TmpFile, 1)<>'/' then TmpFile := TmpFile + '/';
TmpFile := TmpFile + '~tmp' + IntToStr(GetTickCount) + '.txt';
List := TStringList.Create;
try try
//调用上例函数将数据读到List中,并保存到TmpFile
if GetDataList(List) then begin
List.SaveToFile(TmpFile);
ProgressBar1.StepIt;
end;
finally
List.Free;
end;
except
Result := false;
pnlProgress.Visible := false;
Exit;
end;
Excel.Connect; // 打开Excel(Excel: TExcelApplication控件)
try try
Excel.DisplayAlerts[0] := false;
Excel.Visible[0] := false;
Excel.Caption := 'XXXX标题(Excel)';
//Excel打开临时文件,指定内码为简体中文
Excel.Workbooks.OpenText(TmpFile, 936, 1, xlDelimited, xlDoubleQuote,
False, true, False, False, False, False, false, NULL, NULL, NULL, NULL, 0);
//Excel.Workbooks.Add(xlWBATWorksheet, 0);
ASheet := Excel.Worksheets.Item[1];
//Excel.Workbooks.Item[1].Activate(0);
//设定显示格式
ASheet.Cells.Font.Name := '宋体';
ASheet.Cells.Font.Size := 10;
ASheet.Cells.VerticalAlignment := 2;
ASheet.Range['A1', 'Z1'].HorizontalAlignment := 7;
ASheet.Range['A1', 'Z1'].Font.Size := 16;
ASheet.Range['A1', 'Z1'].RowHeight := 22;
ASheet.Range['A2', 'Z2'].HorizontalAlignment := 3;
ASheet.Range['A2', 'Z2'].Font.Bold := true;
Excel.Workbooks.Item[1].SaveAs(FileName, xlNormal, NULL, NULL, false, false, xlNoChange, xlLocalSessionChanges, false, NULL, NULL, 1);
Excel.Workbooks.Item[1].Close(false, FileName, 0, 0);
except
Result := false;
pnlProgress.Visible := false;
Exit;
end;
finally
Excel.Quit;
Excel.Disconnect;
DeleteFile(TmpFile);
end;
ProgressBar1.Position := ProgressBar1.Max;
MessageBox(Handle, PChar('数据成功导出到' + FileName), '导出数据', MB_ICONINFORMATION or MB_OK);
pnlProgress.Visible := false;
end;