我想用delphi7写一个组件,功能是将数据库里的数据表导到Excel文件里来。请教各位大侠由有关这样的例子吗,或有这方面的代码,烦劳告知!谢谢
解决方案 »
- 求 dbnavigator和DBGrid的简单用法
- 文件菜单中的历史文件名有没有简便的处理方法
- 请问:如何删除TreeView节点以及其子节点
- 为何在InsideVCL中李维那个CallStack能列出一大串函数的调用过程而我的却始终只有那么可怜巴巴的几个?
- 甩女友的10条毒计---男人们请进
- 请教Esc的Ascll码是多少 ?
- 如何让两个ADOQuery中数据集一致?
- delphi 多线程死掉的问题
- 修改adodb.pas文件后如何编译该文件?
- 如何将电视卡某个频道的节目录下来
- 请高人指点;怎么解决这个问题Access Violation at address ×××× in module ****.DLL',Read of address 24010C016
- 我很在意BORLAND,金色的BORLAND,详见下文
请问在Delphi中我用query控件查找到几万条纪录并用DBGrid显示,怎样才能快速的把Query中的数据存成Excel文档(如PowerBuilder中的SaveAs一样)? A回答: 大致有下面几个办法:
1)存成CSV文件,这种文件可以被Excel打开。如:
uses ADOInt;
procedure TForm1.Button1Click(Sender: TObject);
var
Rs : string;
SL: TStringList;
begin
try
SL := TStringList.Create;
Rs := AdoQuery1.Recordset.GetString(adClipString, -1, #59,#13, '');
SL.Text := Rs;
SL.SaveToFile('C:\MyQry.csv');
finally
SL.Free;
end;
end;
2)利用Excel的数据库接口,这种方法需要一定的编程,而且不能控制格式:
with ADODataSet1 do
begin
CommandText := '$'; ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+ FileName
+ ';Extended Properties=Excel 8.0;Persist Security Info=False';
Open;
{dO SOME STUFF}
Close;
end;
3)借助Automation接口,这样可以控制输出的格式,但需要编写大量代码。可以参考
QA003964 "如何在DELPHI中控制EXCEL"。
4)使用商业控件,如SMExport component suite(http://www.scalabium.com/)和XLSReadWrite(http://www.axolot.com/components/index_g.htm)。
uses
windows, Variants, FileCtrl, dialogs, Controls, Forms, Sysutils, Grids, ComCtrls, stdctrls, classes, OleServer, Excel97;
function ExportStringGridToExcel(StringGrid: TStringGrid; FileName: string; Preview: Boolean = False): Boolean;
function ExportListViewToExcel(ListView: TListView; FileName: string; Preview: Boolean = False): Boolean;implementationfunction ExportListViewToExcel(ListView: TListView; FileName: string; Preview: Boolean = False): Boolean; overload;
var
StringGrid : TStringGrid;
i, j : integer;
begin
try
StringGrid := TStringGrid.Create(nil);
StringGrid.RowCount := ListView.Items.Count + 1;
StringGrid.ColCount := ListView.Columns.Count;
for i := 0 to StringGrid.ColCount - 1 do
begin
StringGrid.Cells[i, 0] := ListView.Columns[i].Caption;
end; for i := 0 to ListView.Items.Count - 1 do
begin
StringGrid.Cells[0, i + 1] := ListView.Items[i].Caption;
for j := 1 to ListView.Items[i].SubItems.Count do
begin
StringGrid.Cells[j, i + 1] := ListView.Items[i].SubItems[j - 1];
end;
end;
ExportStringGridToExcel(StringGrid, FileName, Preview);
finally
StringGrid.Free;
end;end;function ExportStringGridToExcel(StringGrid: TStringGrid; FileName: string; Preview: Boolean = False): Boolean;
var
SaveDialog : TSaveDialog;
ExcelApplication : TExcelApplication;
ExcelWorkBook : TExcelWorkbook;
ExcelWorkSheet : TExcelWorksheet;
Range : Variant; i, j, Row : integer;
RangeLeft, RangeRight: string;
begin
if FindWindow('XLMAIN', nil) <> 0 then
begin
Beep;
MessageDlg('' + #13 + #10 + '为了确保数据转换正确,请您关闭所有的Excel窗口!', mtWarning, [mbOK], 0);
Exit;
end; SaveDialog := TSaveDialog.Create(nil);
ExcelApplication := TExcelApplication.Create(nil);
ExcelWorkBook := TExcelWorkbook.Create(nil);
ExcelWorkSheet := TExcelWorksheet.Create(nil);
ExcelApplication.Visible[0] := False; SaveDialog.DefaultExt := '*.XLS';
SaveDialog.Filter := 'Excel 文件|*.XLS';
SaveDialog.FileName := 'Export.XLS';
SaveDialog.Options := [ofOverwritePrompt, ofHideReadOnly, ofEnableSizing]; if FileName = '' then
begin
if SaveDialog.Execute then
begin
FileName := SaveDialog.FileName;
end;
end; if FileName <> '' then
try
Screen.Cursor := crHourGlass;
ExcelApplication.Connect;
ExcelApplication.Workbooks.Add(Null, 0);
ExcelWorkBook.ConnectTo(ExcelApplication.Workbooks[1]);
ExcelWorkSheet.ConnectTo(ExcelWorkBook.Sheets[1] as _WorkSheet); for i := 0 to StringGrid.rowcount - 1 do
begin
for j := 0 to StringGrid.ColCount - 1 do
begin
ExcelWOrkSheet.Cells.Item[i + 1, j + 1] := '''' + StringGrid.Cells[j, i];
Application.ProcessMessages;
end;
end; RangeLeft := 'A1';
RangeRight := Chr(Ord('A') + StringGrid.ColCount - 1) + IntToStr(StringGrid.RowCount);
Range := ExcelWorkSheet.Range[RangeLeft, RangeRight];
Range.Borders.LineStyle := 1;
ExcelWorkSheet.Columns.AutoFit; ExcelWorkBook.SaveCopyAs(FileName);
Screen.Cursor := crDefault;
if Preview then
begin
ExcelApplication.Visible[0] := True;
ExcelWorkSheet.PrintPreview;
end;
finally
ExcelWOrkBook.Close(false);
ExcelApplication.Disconnect;
ExcelApplication.Quit;
ExcelApplication.Free;
ExcelWorkBook.Free;
ExcelWorkSheet.Free;
SaveDialog.Free;
Screen.Cursor := crDefault;
end;
end;
end.