procedure TIMCSFormTop.ExportToExcel(const sExcelFileName: string; const cxgvMain: TcxGridDBTableView); var eclApp,WorkBook: Variant; nRowCount, nColumnCount, i,j,RowIND: integer; begin try eclApp := CreateOleObject('Excel.Application'); WorkBook := CreateOleObject('Excel.Sheet'); WorkBook := eclApp.WorkBooks.add; nColumnCount := cxgvMain.ColumnCount; for i := 0 to nColumnCount - 1 do eclApp.Cells(1,i + 1) := cxgvMain.Columns[i].Caption; RowIND := 0; with cxgvMain.ViewData do begin nRowCount := RowCount; for i := 0 to nRowCount - 1 do begin if cxgvMain.ViewData.Rows[i].IsData then begin for j := 1 to nColumnCount do eclApp.Cells(RowIND + 2,j) :=Rows[i].DisplayTexts[j - 1]; RowIND := RowIND + 1 ; end; end; end; WorkBook.Saveas(sExcelFileName); WorkBook.close; Showmessage('Save Success!'); except ShowMessage('Save to Excel Failed!'); end;以上程序是將cxgrid的内容輸出到excel中,你將那些Rows[i].DisplayTexts[j - 1]改爲ADOQuery.Fields[i].value就差不多了,自己再測試一下
这是我用的方法,要用到剪帖板,这样在导出大量数据时很快,普通方法非常慢。看你好像是个新手,先把面向对象的编程技术简要学习一下吧。 procedure WriteExcel(AdsData: TCustomADODataSet; sName, Title: string); var ExcelApplication1: TExcelApplication; i, j,k: integer; filename: string; ColoumName:string; s:string; tsList:TStringList; asheet:Variant; begin if Application.MessageBox('是否将记录转换成Excel文件?','转换提醒',MB_YESNO)=IDYES thenbegin if ADsData.Active=False then abort; Screen.Cursor:= crSQLWait; try ExcelApplication1 := TExcelApplication.Create(Application); ExcelApplication1.Connect;
ExcelApplication1.Workbooks.Add(xlWBATWorksheet,0); aSheet:=ExcelApplication1.Worksheets.Item[1]; except Application.Messagebox('Excel 没有安装!', '操作提示', MB_IConERROR + mb_Ok); Abort; end; try tsLIst:=TStringLIst.Create; with ADSData do begin First; s:=''; for i:=0 to FieldCount-1 do if Fields[i].Visible=True then s:=s+Fields[i].DisplayLabel+#9; tsList.Add(s); While Not Eof do begin S := ''; for I := 0 to FieldCount -1 do begin If fields[I].visible=true then S := S + #9 + trim(Fields[I].AsString); end; System.Delete(S, 1, 1); tsList.Add(S); Next; end; end; Clipboard.AsText:=tsList.Text; finally tsList.Free; end; ExcelApplication1.Disconnect; with AdsData do begin k:=AdsData.RecordCount; for i:=0 to FieldCount-1 do if ((AdsData.Fields[i].DataType=ftString)or (AdsData.Fields[i].DataType=ftWideString)) and (AdsData.Fields[i].Visible=True) then asheet.Range[chr(65+i)+'1',chr(65+i)+IntToStr(k+1)].NumberFormat:='@'; end; asheet.paste; clipboard.Clear; asheet.Columns.AutoFit; ExcelApplication1.Visible[0]:=True; ExcelApplication1.Free; Screen.Cursor:= crDefault;end; end;
const cxgvMain: TcxGridDBTableView);
var
eclApp,WorkBook: Variant;
nRowCount, nColumnCount, i,j,RowIND: integer;
begin
try
eclApp := CreateOleObject('Excel.Application');
WorkBook := CreateOleObject('Excel.Sheet');
WorkBook := eclApp.WorkBooks.add; nColumnCount := cxgvMain.ColumnCount;
for i := 0 to nColumnCount - 1 do
eclApp.Cells(1,i + 1) := cxgvMain.Columns[i].Caption; RowIND := 0;
with cxgvMain.ViewData do
begin
nRowCount := RowCount;
for i := 0 to nRowCount - 1 do
begin
if cxgvMain.ViewData.Rows[i].IsData then
begin
for j := 1 to nColumnCount do
eclApp.Cells(RowIND + 2,j) :=Rows[i].DisplayTexts[j - 1];
RowIND := RowIND + 1 ;
end;
end;
end;
WorkBook.Saveas(sExcelFileName);
WorkBook.close;
Showmessage('Save Success!');
except
ShowMessage('Save to Excel Failed!');
end;以上程序是將cxgrid的内容輸出到excel中,你將那些Rows[i].DisplayTexts[j - 1]改爲ADOQuery.Fields[i].value就差不多了,自己再測試一下
procedure WriteExcel(AdsData: TCustomADODataSet; sName, Title: string);
var
ExcelApplication1: TExcelApplication; i, j,k: integer;
filename: string;
ColoumName:string;
s:string;
tsList:TStringList;
asheet:Variant;
begin
if Application.MessageBox('是否将记录转换成Excel文件?','转换提醒',MB_YESNO)=IDYES thenbegin
if ADsData.Active=False then abort;
Screen.Cursor:= crSQLWait;
try
ExcelApplication1 := TExcelApplication.Create(Application);
ExcelApplication1.Connect;
ExcelApplication1.Workbooks.Add(xlWBATWorksheet,0);
aSheet:=ExcelApplication1.Worksheets.Item[1];
except
Application.Messagebox('Excel 没有安装!', '操作提示', MB_IConERROR + mb_Ok);
Abort;
end;
try
tsLIst:=TStringLIst.Create;
with ADSData do
begin
First;
s:='';
for i:=0 to FieldCount-1 do
if Fields[i].Visible=True then
s:=s+Fields[i].DisplayLabel+#9;
tsList.Add(s);
While Not Eof do
begin
S := '';
for I := 0 to FieldCount -1 do
begin
If fields[I].visible=true then
S := S + #9 + trim(Fields[I].AsString);
end;
System.Delete(S, 1, 1);
tsList.Add(S);
Next;
end;
end;
Clipboard.AsText:=tsList.Text;
finally
tsList.Free;
end;
ExcelApplication1.Disconnect; with AdsData do
begin
k:=AdsData.RecordCount;
for i:=0 to FieldCount-1 do
if ((AdsData.Fields[i].DataType=ftString)or (AdsData.Fields[i].DataType=ftWideString)) and (AdsData.Fields[i].Visible=True) then
asheet.Range[chr(65+i)+'1',chr(65+i)+IntToStr(k+1)].NumberFormat:='@';
end; asheet.paste;
clipboard.Clear;
asheet.Columns.AutoFit;
ExcelApplication1.Visible[0]:=True;
ExcelApplication1.Free;
Screen.Cursor:= crDefault;end;
end;
dxDBGrid1.SaveToXLS(FileName,True);
所见即所得。