我想用delphi7写一个组件,功能是将数据库里的数据表导到Excel文件里来。请教各位大侠由有关这样的例子吗,或有这方面的代码,烦劳告知!谢谢
解决方案 »
- 错误运行?
- 如何在一个过程中调用另一个过程中的函数?(delphi)
- 函数quotedStr是什么意思?
- 两台机器同时输入不同的数据,然后将这个两个数据库合并!
- 我怎样拷贝文件夹用代码写?
- 怎样在DBGrid中创建列表头
- 如何将access和excel中的数据导入到delphi中,在delphi中能直观的看到里面的数据
- 下面这条语句应该怎样写?
- BackSpace键(后退键)的键位是多少?(即key=#?)
- Access中有没有类似于Oracle中substr这样的函数?
- 请高人指点;怎么解决这个问题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.