procedure TForm1.Button1Click(Sender: TObject); var ExcelApp : Variant; //声明ExcelApp为Variant RecordCounts : Integer; i:Integer; begin try ExcelApp := CreateOleObject('Excel.Application'); //创建Excel.Application except ShowMessage('本机没有安装EXCEL!'); exit; end; ExcelApp.WorkBooks.Add;//新加一个工作簿 ExcelApp.WorkSheets[1].Activate; //激活第一个Sheet Query1.Close; Query1.Open; Query1.First; ExcelApp.Cells[1,1].value :='纳税人识别号'; ExcelApp.Cells[1,2].value :='单位名称'; RecordCounts := Query1.RecordCount; //取QUERY1的记录数用于循环 for i:=2 to RecordCounts+1 do begin if not Query1.Eof then begin ExcelApp.Cells[i,1].Value :=Query1.FieldByName('TAXNO').AsString; ExcelApp.Cells[i,2].Value :=Query1.FieldByName('UNITNAME').AsString; Query1.Next; end; end; ExcelApp.ActiveWorkBook.SaveAs('c:\list.xls'); if MessageDlg('文件 c:\list.xls 保存成功,是否要打开编辑此文件?',mtConfirmation,[mbYes,mbNo,mbCancel],0)= mrYes then ExcelApp.Visible := True //设置EXCEL窗口为可见 else begin ExcelApp.Visible := False; ExcelApp.Quit; end; end;用ADO和以上方法一样! 只要换成你用的DATASET即可!
贴个源码给你吧,这是我自己用的,参数是已经Open的ADOQuery: procedure ExportToExcel(ADOQry: TADOQuery); var MyExcel: Variant; WorkBook: OleVariant; WorkSheet: OleVariant; i,j:integer; begin try MyExcel:=CreateOleObject('Excel.Application'); MyExcel.Application.WorkBooks.Add; MyExcel.Caption:='将数据导入到EXCEL表中'; MyExcel.Application.Visible:=true; WorkBook:=MyExcel.Application.workbooks[1]; worksheet:=workbook.worksheets.item[1]; except showmessage('EXCEL不存在!'); end; i:=1; with ADOQry do begin for j:=0 to FieldCount-1 do worksheet.cells[1,j+1]:=Fields[j].FieldName; First; while not Eof do begin inc(i); for j:=0 to fieldcount-1 do begin worksheet.cells[i,j+1].NumberFormatLocal :='@'; worksheet.cells[i,j+1]:=Fields[j].asstring; end; next; end; end; end;
我是这样写的uses comobj; 定义一个常量 const xlCSV = 6; //不定义这个excelapp的saveas用不了var ExcelApp: Variant;f:textfile; fs:string; i,j:integer; adoquery1:tadoquery; begin if exportsavedialog.execute then begin fs:=exportsavedialog.filename;
ExcelApp := CreateOleObject( 'Excel.Application' ); ExcelApp.Visible := True; ExcelApp.Caption := '应用程序调用 Microsoft Excel'; ExcelApp.WorkBooks.Add; ExcelApp.WorkSheets[1].Activate; adoquery1:=Tadoquery.Create(nil); adoquery1.Connection:=custadoconnection; adoquery1.SQL.Clear; adoquery1.SQL.Add('select * from custdetails'); adoquery1.Open; if adoquery1.RecordCount > 0 then begin with adoquery1 do begin first; j:=1; while NOT EOF do begin for i:=1 to adoquery1.FieldCount-1 do begin excelapp.cells[j,i].value:=adoquery1.Fields[i].AsString; end; next; j:=j+1; end; end; end; ExcelApp.activeworkbook.Saveas(fs); ExcelApp.WorkBooks.Close; ExcelApp.Quit; end; end; end;
有没有EXCEL导入用ADO连接的SQL SERVER的代码啊,EXCEL用OLE连接,SQL用ADO连接 我想进行批量进行SQL SERVER,不能频繁的一条条的导入SQL SERVER 如何实现?
var
ExcelApp : Variant; //声明ExcelApp为Variant
RecordCounts : Integer;
i:Integer;
begin
try
ExcelApp := CreateOleObject('Excel.Application'); //创建Excel.Application
except
ShowMessage('本机没有安装EXCEL!');
exit;
end;
ExcelApp.WorkBooks.Add;//新加一个工作簿
ExcelApp.WorkSheets[1].Activate; //激活第一个Sheet
Query1.Close;
Query1.Open;
Query1.First;
ExcelApp.Cells[1,1].value :='纳税人识别号';
ExcelApp.Cells[1,2].value :='单位名称';
RecordCounts := Query1.RecordCount; //取QUERY1的记录数用于循环
for i:=2 to RecordCounts+1 do
begin
if not Query1.Eof then
begin
ExcelApp.Cells[i,1].Value :=Query1.FieldByName('TAXNO').AsString;
ExcelApp.Cells[i,2].Value :=Query1.FieldByName('UNITNAME').AsString;
Query1.Next;
end;
end;
ExcelApp.ActiveWorkBook.SaveAs('c:\list.xls');
if MessageDlg('文件 c:\list.xls 保存成功,是否要打开编辑此文件?',mtConfirmation,[mbYes,mbNo,mbCancel],0)= mrYes then
ExcelApp.Visible := True //设置EXCEL窗口为可见
else
begin
ExcelApp.Visible := False;
ExcelApp.Quit;
end;
end;用ADO和以上方法一样!
只要换成你用的DATASET即可!
procedure ExportToExcel(ADOQry: TADOQuery);
var
MyExcel: Variant;
WorkBook: OleVariant;
WorkSheet: OleVariant;
i,j:integer;
begin
try
MyExcel:=CreateOleObject('Excel.Application');
MyExcel.Application.WorkBooks.Add;
MyExcel.Caption:='将数据导入到EXCEL表中';
MyExcel.Application.Visible:=true;
WorkBook:=MyExcel.Application.workbooks[1];
worksheet:=workbook.worksheets.item[1];
except
showmessage('EXCEL不存在!');
end;
i:=1; with ADOQry do
begin
for j:=0 to FieldCount-1 do worksheet.cells[1,j+1]:=Fields[j].FieldName;
First;
while not Eof do
begin
inc(i);
for j:=0 to fieldcount-1 do
begin
worksheet.cells[i,j+1].NumberFormatLocal :='@';
worksheet.cells[i,j+1]:=Fields[j].asstring;
end;
next;
end;
end;
end;
定义一个常量
const xlCSV = 6; //不定义这个excelapp的saveas用不了var
ExcelApp: Variant;f:textfile;
fs:string;
i,j:integer;
adoquery1:tadoquery;
begin
if exportsavedialog.execute then
begin
fs:=exportsavedialog.filename;
ExcelApp := CreateOleObject( 'Excel.Application' );
ExcelApp.Visible := True;
ExcelApp.Caption := '应用程序调用 Microsoft Excel';
ExcelApp.WorkBooks.Add;
ExcelApp.WorkSheets[1].Activate; adoquery1:=Tadoquery.Create(nil);
adoquery1.Connection:=custadoconnection;
adoquery1.SQL.Clear;
adoquery1.SQL.Add('select * from custdetails');
adoquery1.Open;
if adoquery1.RecordCount > 0 then
begin
with adoquery1 do
begin
first;
j:=1;
while NOT EOF do
begin
for i:=1 to adoquery1.FieldCount-1 do
begin
excelapp.cells[j,i].value:=adoquery1.Fields[i].AsString;
end;
next;
j:=j+1;
end;
end;
end;
ExcelApp.activeworkbook.Saveas(fs);
ExcelApp.WorkBooks.Close;
ExcelApp.Quit;
end;
end;
end;
我想进行批量进行SQL SERVER,不能频繁的一条条的导入SQL SERVER
如何实现?
其实我只是需要用一下Excell传递一些文件!
要不就用TXT传递数据吧,这个总有吧。
以每条数据为一行,数据中加一个分隔符号就可以了