正在找execl与delphi的帖子,大家共同努力,给你一段刚才看到的帖子 ----------------------------------------------------------------------- 我的一段代码看对你是否有用 procedure Tf_fsxx.drexcel(filename,drcs,drbz,dxnr:string;ybt:boolean); var strcs:Tstringlist; i,j,k:integer; ks:array of integer; zdm,nr:array of string; str1,str2,tem,sqlstr,keyfield:string; adoquery:Tadoquery; mid:integer; ExcelApp: Variant; p:^Integer; begin with Tadoquery.create(nil) do begin connectionstring:=linkstr; sql.clear; sql.add('delete from xt_temdr'); execsql; free; end; strcs:=Tstringlist.create; strcs.text:=drcs; setlength(zdm,strcs.count); setlength(ks,strcs.count); setlength(nr,strcs.count); for i:=0 to strcs.count-1 do begin tem:=strcs.strings[i]; j:=pos(#9,tem); str1:=copy(tem,1,j-1); str2:=copy(tem,j+1,length(tem)-j); ks[i]:=strtoint(str1); zdm[i]:=str2; end; for i:=0 to high(zdm) do begin for j:=0 to 4 do begin if zdm[i]=yqxxarray[j,0] then begin zdm[i]:=yqxxarray[j,1]; break; end; end; end; if ybt then k:=1 else k:=0; ExcelApp:=CreateOleObject( 'Excel.Application' ); ExcelApp.WorkBooks.Open(FileName); ExcelApp.WorkSheets[1].Activate; createplan('正在导入数据……',ExcelApp.WorkSheets[1].UsedRange.Rows.Count-k); for i:=k+1 to ExcelApp.WorkSheets[1].UsedRange.Rows.Count do begin for j:=0 to high(ks) do begin nr[j]:=ExcelApp.cells[i,ks[j]]; end; with Tadoquery.create(nil) do begin connectionstring:=linkstr; sql.clear; sqlstr:='insert into xt_temdr('; for j:=0 to high(ks) do begin sqlstr:=sqlstr+zdm[j]+','; end; sqlstr:=copy(sqlstr,1,length(sqlstr)-1); sqlstr:=sqlstr+')values('; for j:=0 to high(ks) do begin sqlstr:=sqlstr+''''+nr[j]+''','; end; sqlstr:=copy(sqlstr,1,length(sqlstr)-1); sqlstr:=sqlstr+')'; sql.add(sqlstr); execsql; free; end; incplan; end; excelapp.workbooks.close; excelapp.quit; closeplan; keyfield:='gdzh'; for i:=0 to 4 do begin if yqxxarray[i,0]=drbz then begin keyfield:=yqxxarray[i,1]; end; end; with Tadoquery.create(nil) do begin connectionstring:=linkstr; sql.clear; sql.add('select * from xt_temdr,khzl where xt_temdr.'+keyfield+'=khzl.'+keyfield); open; createplan('正在写入发送库',recordcount); while not eof do begin tem:=dxnr; str1:=''; i:=pos('{*',tem); j:=pos('*}',tem); while (i>0)and(j>0) do begin str1:=str1+copy(tem,1,i-1); str2:=trim(copy(tem,i+2,j-i-2)); tem:=copy(tem,j+2,length(tem)-j); k:=0; for j:=0 to 4 do begin if yqxxarray[j,0]=str2 then begin str2:=yqxxarray[j,1]; k:=1; break; end; end; if (k=0)and(trim(str2)<>'bxhj') then str2:=str2+'_1'; str1:=str1+fieldbyname(str2).asstring; i:=pos('{*',tem); j:=pos('*}',tem); end; str1:=str1+tem; //内容 adoquery:=Tadoquery.create(nil); adoquery.connectionstring:=linkstr; mid:=incid('xt_fs','id'); adoquery.sql.clear; adoquery.sql.add('insert into xt_fs(id,fs_hm,fs_xm,fs_nr,fs_zt,fs_yxj,fs_yh,fs_drsj,fs_lx)values('); adoquery.sql.add(inttostr(mid)+','''+fieldbyname('sjhm').asstring+''','); adoquery.sql.add(''''+fieldbyname('xm_1').asstring+''','); adoquery.sql.add(''''+str1+''','); adoquery.sql.add('''未发送'','); adoquery.sql.add(current_loginqx+','); adoquery.sql.add(''''+current_loginname+''','); adoquery.sql.add(''''+datetostr(date)+' '+timetostr(time)+''','); adoquery.sql.add(inttostr(fslx)+')'); adoquery.execsql; with lv.Items.add do begin caption:=fieldbyname('xm_1').asstring; subitems.add(fieldbyname('sjhm').asstring); subitems.add('未发送'); subitems.add(str1); subitems.add(datetostr(date)+' '+timetostr(time)); subitems.add(''); subitems.add(''); subitems.add(current_loginqx); subitems.add(current_loginname); new(p); p^:=mid; data:=p; selected:=true; makevisible(true); end; adoquery.free; update; incplan; next; end; closeplan; free; end; strcs.free; end;
用静态字段,设置好标题名称 unit DataToExcel;interfaceuses classes, comctrls, stdctrls, windows, dialogs, controls, sysutils, db, forms, dbclient, comobj, Excel2000;//把数据集导入excelsheet的核心函数 function DatasetToExcelSheet ( DataSet: TDataSet; Sheet: olevariant; Caption: string): boolean;//实际使用的函数,内部调用了datasettoexcelsheet,在外面加入ui接口和错误处理 function DataSetToExcel ( DataSet: TDataSet; // 要转换的数据集 Caption: string = ''; Visible: boolean = True; // 是否让做转换工作的excel可见 ExcelFileName: string = '' // excel文件名,*.xls ): Boolean;implementationfunction DataSetToExcelSheet(DataSet: TDataSet; Sheet: olevariant; Caption: string): boolean; var row, col, FieldIndex: integer; bk: TBookMark; begin Result := false; if not DataSet.Active then exit; bk := DataSet.GetBookMark; DataSet.DisableControls; Sheet.Activate; try // 列标题 Row := 2; col := 1; Sheet.PageSetup.PrintGridLines := True; for FieldIndex := 0 to DataSet.FieldCount - 1 do begin if DataSet.Fields[FieldIndex].Visible then begin Sheet.Columns[FieldIndex + 1].NumberFormat := '@'; Sheet.Columns[FieldIndex + 1].Font.Name := '宋体'; Sheet.Columns[FieldIndex + 1].Font.Size := 10; Sheet.Columns[FieldIndex + 1].ColumnWidth := DataSet.Fields[FieldIndex].DisplayWidth; Sheet.Cells(row, col) := DataSet.Fields[FieldIndex].DisplayLabel; inc(col); end; end; Sheet.Range[Sheet.Cells[1, 1], Sheet.Cells[1, Col - 1]].Merge; Sheet.Range[Sheet.Cells[1, 1], Sheet.Cells[1, 1]].Font.Size := 14; Sheet.Range[Sheet.Cells[2, 1], sheet.Cells[2, Col - 1]].Font.Size := 11; Sheet.Range[Sheet.Cells[1, 1], Sheet.Cells[2, Col - 1]].Font.Bold := True; Sheet.Range[Sheet.Cells[1, 1], Sheet.Cells[2, Col - 1]].HorizontalAlignment := xlHAlignCenter; Sheet.Range[Sheet.Cells[1, 1], Sheet.Cells[2, Col - 1]].HorizontalAlignment := xlHAlignCenter; Sheet.Cells(1, 1) := Caption; // 表内容 DataSet.First; while not DataSet.Eof do begin row := row + 1; col := 1; for FieldIndex := 0 to DataSet.FieldCount - 1 do begin if DataSet.Fields[FieldIndex].Visible then begin Sheet.Cells(row, col) := DataSet.Fields[FieldIndex].AsString; inc(col); end; end; DataSet.next; end; Result := true; finally DataSet.GoToBookMark(bk); DataSet.EnableControls; end;end;function DataSetToExcel( DataSet: TDataSet; Caption: string = ''; visible: Boolean = True; ExcelFileName: string = ''): Boolean; var excelobj, excel, workbook, sheet: olevariant; OldCursor: TCursor; SaveDialog: TSaveDialog; begin Result := False; if not DataSet.Active then Exit; if (DataSet.Owner is TCustomForm) and (Caption = '') then Caption := TCustomForm(DataSet.Owner).Caption; OldCursor := Screen.Cursor; Screen.Cursor := crhourglass; try excelobj := createoleobject('Excel.Sheet'); excel := excelobj.application; excel.visible := visible; workbook := excel.workbooks.add; sheet := workbook.sheets[1]; except messagebox(getactivewindow, '无法调用MircorSoft Excel! ' + chr(13) + chr(10) + '请检查是否安装了MircorSoft Excel。', '提示', mb_ok + mb_iconinformation); Screen.Cursor := OldCursor; Exit; end; Result := DataSetToExcelSheet(DataSet, Sheet, Caption); if Result then if not visible then begin if ExcelFileName <> '' then WorkBook.Saveas(FileName := ExcelFileName) else begin SaveDialog := TSaveDialog.Create(nil); SaveDialog.Filter := 'MicroSoft Excel 文件|*.xls'; Result := SaveDialog.Execute; UpdateWindow(GetActiveWindow); if Result then Workbook.Saveas(FileName := SaveDialog.FileName); SaveDialog.Free; end; Excel.Quit; end; Screen.Cursor := OldCursor; end; end.
-----------------------------------------------------------------------
我的一段代码看对你是否有用
procedure Tf_fsxx.drexcel(filename,drcs,drbz,dxnr:string;ybt:boolean);
var
strcs:Tstringlist;
i,j,k:integer;
ks:array of integer;
zdm,nr:array of string;
str1,str2,tem,sqlstr,keyfield:string;
adoquery:Tadoquery;
mid:integer;
ExcelApp: Variant;
p:^Integer;
begin
with Tadoquery.create(nil) do begin
connectionstring:=linkstr;
sql.clear;
sql.add('delete from xt_temdr');
execsql;
free;
end;
strcs:=Tstringlist.create;
strcs.text:=drcs;
setlength(zdm,strcs.count);
setlength(ks,strcs.count);
setlength(nr,strcs.count);
for i:=0 to strcs.count-1 do begin
tem:=strcs.strings[i];
j:=pos(#9,tem);
str1:=copy(tem,1,j-1);
str2:=copy(tem,j+1,length(tem)-j);
ks[i]:=strtoint(str1);
zdm[i]:=str2;
end;
for i:=0 to high(zdm) do begin
for j:=0 to 4 do begin
if zdm[i]=yqxxarray[j,0] then begin
zdm[i]:=yqxxarray[j,1];
break;
end;
end;
end;
if ybt then k:=1
else k:=0; ExcelApp:=CreateOleObject( 'Excel.Application' );
ExcelApp.WorkBooks.Open(FileName);
ExcelApp.WorkSheets[1].Activate;
createplan('正在导入数据……',ExcelApp.WorkSheets[1].UsedRange.Rows.Count-k);
for i:=k+1 to ExcelApp.WorkSheets[1].UsedRange.Rows.Count do begin
for j:=0 to high(ks) do begin
nr[j]:=ExcelApp.cells[i,ks[j]];
end;
with Tadoquery.create(nil) do begin
connectionstring:=linkstr;
sql.clear;
sqlstr:='insert into xt_temdr(';
for j:=0 to high(ks) do begin
sqlstr:=sqlstr+zdm[j]+',';
end;
sqlstr:=copy(sqlstr,1,length(sqlstr)-1);
sqlstr:=sqlstr+')values(';
for j:=0 to high(ks) do begin
sqlstr:=sqlstr+''''+nr[j]+''',';
end;
sqlstr:=copy(sqlstr,1,length(sqlstr)-1);
sqlstr:=sqlstr+')';
sql.add(sqlstr);
execsql;
free;
end;
incplan;
end;
excelapp.workbooks.close;
excelapp.quit;
closeplan;
keyfield:='gdzh';
for i:=0 to 4 do begin
if yqxxarray[i,0]=drbz then begin
keyfield:=yqxxarray[i,1];
end;
end; with Tadoquery.create(nil) do begin
connectionstring:=linkstr;
sql.clear;
sql.add('select * from xt_temdr,khzl where xt_temdr.'+keyfield+'=khzl.'+keyfield);
open;
createplan('正在写入发送库',recordcount);
while not eof do begin
tem:=dxnr;
str1:='';
i:=pos('{*',tem);
j:=pos('*}',tem);
while (i>0)and(j>0) do begin
str1:=str1+copy(tem,1,i-1);
str2:=trim(copy(tem,i+2,j-i-2));
tem:=copy(tem,j+2,length(tem)-j);
k:=0;
for j:=0 to 4 do begin
if yqxxarray[j,0]=str2 then begin
str2:=yqxxarray[j,1];
k:=1;
break;
end;
end;
if (k=0)and(trim(str2)<>'bxhj') then str2:=str2+'_1';
str1:=str1+fieldbyname(str2).asstring;
i:=pos('{*',tem);
j:=pos('*}',tem);
end;
str1:=str1+tem; //内容
adoquery:=Tadoquery.create(nil);
adoquery.connectionstring:=linkstr;
mid:=incid('xt_fs','id');
adoquery.sql.clear;
adoquery.sql.add('insert into xt_fs(id,fs_hm,fs_xm,fs_nr,fs_zt,fs_yxj,fs_yh,fs_drsj,fs_lx)values(');
adoquery.sql.add(inttostr(mid)+','''+fieldbyname('sjhm').asstring+''',');
adoquery.sql.add(''''+fieldbyname('xm_1').asstring+''',');
adoquery.sql.add(''''+str1+''',');
adoquery.sql.add('''未发送'',');
adoquery.sql.add(current_loginqx+',');
adoquery.sql.add(''''+current_loginname+''',');
adoquery.sql.add(''''+datetostr(date)+' '+timetostr(time)+''',');
adoquery.sql.add(inttostr(fslx)+')');
adoquery.execsql;
with lv.Items.add do begin
caption:=fieldbyname('xm_1').asstring;
subitems.add(fieldbyname('sjhm').asstring);
subitems.add('未发送');
subitems.add(str1);
subitems.add(datetostr(date)+' '+timetostr(time));
subitems.add('');
subitems.add('');
subitems.add(current_loginqx);
subitems.add(current_loginname);
new(p);
p^:=mid;
data:=p;
selected:=true;
makevisible(true);
end;
adoquery.free;
update;
incplan;
next;
end;
closeplan;
free;
end;
strcs.free;
end;
unit DataToExcel;interfaceuses
classes, comctrls, stdctrls, windows, dialogs, controls, sysutils,
db, forms, dbclient, comobj, Excel2000;//把数据集导入excelsheet的核心函数
function DatasetToExcelSheet
(
DataSet: TDataSet;
Sheet: olevariant;
Caption: string): boolean;//实际使用的函数,内部调用了datasettoexcelsheet,在外面加入ui接口和错误处理
function DataSetToExcel
(
DataSet: TDataSet; // 要转换的数据集
Caption: string = '';
Visible: boolean = True; // 是否让做转换工作的excel可见
ExcelFileName: string = '' // excel文件名,*.xls
): Boolean;implementationfunction DataSetToExcelSheet(DataSet: TDataSet; Sheet: olevariant; Caption:
string): boolean;
var
row, col, FieldIndex: integer;
bk: TBookMark;
begin
Result := false;
if not DataSet.Active then exit;
bk := DataSet.GetBookMark;
DataSet.DisableControls; Sheet.Activate;
try // 列标题
Row := 2;
col := 1;
Sheet.PageSetup.PrintGridLines := True;
for FieldIndex := 0 to DataSet.FieldCount - 1 do
begin
if DataSet.Fields[FieldIndex].Visible then
begin
Sheet.Columns[FieldIndex + 1].NumberFormat := '@';
Sheet.Columns[FieldIndex + 1].Font.Name := '宋体';
Sheet.Columns[FieldIndex + 1].Font.Size := 10;
Sheet.Columns[FieldIndex + 1].ColumnWidth :=
DataSet.Fields[FieldIndex].DisplayWidth;
Sheet.Cells(row, col) := DataSet.Fields[FieldIndex].DisplayLabel; inc(col);
end;
end;
Sheet.Range[Sheet.Cells[1, 1], Sheet.Cells[1, Col - 1]].Merge;
Sheet.Range[Sheet.Cells[1, 1], Sheet.Cells[1, 1]].Font.Size := 14;
Sheet.Range[Sheet.Cells[2, 1], sheet.Cells[2, Col - 1]].Font.Size := 11;
Sheet.Range[Sheet.Cells[1, 1], Sheet.Cells[2, Col - 1]].Font.Bold := True;
Sheet.Range[Sheet.Cells[1, 1], Sheet.Cells[2, Col - 1]].HorizontalAlignment
:= xlHAlignCenter;
Sheet.Range[Sheet.Cells[1, 1], Sheet.Cells[2, Col - 1]].HorizontalAlignment
:= xlHAlignCenter;
Sheet.Cells(1, 1) := Caption;
// 表内容
DataSet.First;
while not DataSet.Eof do
begin
row := row + 1;
col := 1;
for FieldIndex := 0 to DataSet.FieldCount - 1 do
begin
if DataSet.Fields[FieldIndex].Visible then
begin
Sheet.Cells(row, col) := DataSet.Fields[FieldIndex].AsString;
inc(col);
end;
end;
DataSet.next;
end; Result := true;
finally
DataSet.GoToBookMark(bk);
DataSet.EnableControls;
end;end;function DataSetToExcel(
DataSet: TDataSet; Caption: string = ''; visible: Boolean = True;
ExcelFileName: string = ''): Boolean;
var
excelobj, excel, workbook, sheet: olevariant;
OldCursor: TCursor;
SaveDialog: TSaveDialog;
begin
Result := False;
if not DataSet.Active then
Exit;
if (DataSet.Owner is TCustomForm) and (Caption = '') then
Caption := TCustomForm(DataSet.Owner).Caption;
OldCursor := Screen.Cursor;
Screen.Cursor := crhourglass; try
excelobj := createoleobject('Excel.Sheet');
excel := excelobj.application;
excel.visible := visible;
workbook := excel.workbooks.add;
sheet := workbook.sheets[1];
except
messagebox(getactivewindow, '无法调用MircorSoft Excel! ' + chr(13) + chr(10)
+
'请检查是否安装了MircorSoft Excel。', '提示', mb_ok + mb_iconinformation);
Screen.Cursor := OldCursor;
Exit;
end; Result := DataSetToExcelSheet(DataSet, Sheet, Caption);
if Result then
if not visible then
begin
if ExcelFileName <> '' then
WorkBook.Saveas(FileName := ExcelFileName)
else begin
SaveDialog := TSaveDialog.Create(nil);
SaveDialog.Filter := 'MicroSoft Excel 文件|*.xls';
Result := SaveDialog.Execute;
UpdateWindow(GetActiveWindow);
if Result then
Workbook.Saveas(FileName := SaveDialog.FileName);
SaveDialog.Free;
end;
Excel.Quit;
end;
Screen.Cursor := OldCursor;
end;
end.