这是我的数据导出代码,供你参考,adoTemp2是与DBGrid关联的数据集, procedure TfrmJS.btnExportClick(Sender: TObject); var i:Integer; eclApp,WorkBook:Variant; xlsFileName:string; begin if adoTemp2.RecordCount = 0 then Exit;
if dlgExport.Execute then xlsFileName := dlgExport.FileName else Exit; if xlsFileName = '' then begin Application.MessageBox('请输入文件名称','提示',0); Exit; end; try eclApp := CreateOleObject('Excel.Application'); WorkBook := CreateOleObject('Excel.Sheet'); Except Application.MessageBox('您的电脑中可能未安装Microsoft Excel!','错误',0); Exit; end; try workBook:=eclApp.workBooks.Add; eclApp.Cells(1,1):='零件名称'; eclApp.Cells(1,2):='零件代码'; eclApp.Cells(1,3):='车型'; eclApp.Cells(1,4):='产地'; eclApp.Cells(1,5):='数量'; eclApp.Cells(1,6):='单价'; adoTemp2.First; for i:=1 to adoTemp2.RecordCount do begin eclApp.Cells(i+1,1):= adotemp2.FieldByName('PJMC').AsString; eclApp.Cells(i+1,2):= adotemp2.FieldByName('PJDM').AsString; eclApp.Cells(i+1,3):= adotemp2.FieldByName('CX').AsString; eclApp.Cells(i+1,4):= adotemp2.FieldByName('CD').AsString; eclApp.Cells(i+1,5):= adotemp2.FieldByName('SL').AsString; eclApp.Cells(i+1,6):= adotemp2.FieldByName('DJ').AsString; adoTemp2.Next; end; WorkBook.saveas(xlsFileName); WorkBook.Close; eclApp.Quit; eclApp:=Unassigned; Application.MessageBox(PChar('数据已成功导出到“' + xlsFileName + '.XLS"'),'提示',0); except Application.MessageBox('导出失败!','提示',0); WorkBook.close; eclApp.Quit; eclApp:=Unassigned; end; end;
这个是导入的代码: procedure TfrmInputPJ.btnImportClick(Sender: TObject); var i,j:Integer; cbj:real; eclApp:Variant; xlsFileName,str:string; begin if dlgImport.Execute then xlsFileName := dlgImport.FileName else Exit; try eclApp := CreateOleObject('Excel.Application'); eclApp.WorkBooks.Open(xlsFileName); eclApp.Visible:=False; Except Application.MessageBox('您的电脑中可能未安装Microsoft Excel!','错误',0); Exit; end; try j := 0; for i:=2 to eclApp.ActiveSheet.UsedRange.Rows.Count do begin cbj := StrToFloat(eclApp.Cells[i,6].Value); with qryMain do begin Sql.Clear; Sql.Add('select * from T_0103 where PJMC='''+ eclApp.Cells[i,1].Value+''' and PJDM='''+eclApp.Cells[i,2].Value+''' AND CX='''+eclApp.Cells[i,3].Value+''' AND CD='''+eclApp.Cells[i,4].Value+''''); ExecSQL; Open; if RecordCount <> 0 then begin adoTemp1.Append; adoTemp1.FieldByName('RKDH').AsString := edtRKDH.Text; adoTemp1.FieldByName('PJBM').AsString := qryMain.FieldByName('PJBM').AsString; adoTemp1.FieldByName('SL').AsString := eclApp.Cells[i,5].Value; adoTemp1.FieldByName('CBJ').AsString := eclApp.Cells[i,6].Value; adoTemp1.FieldByName('LSJ').AsFloat := cbj * frmPJRK.adoMain.FieldByName('LSJXS').AsFloat; adoTemp1.FieldByName('QXJ').AsFloat := adoTemp1.FieldByName('LSJ').AsFloat; adoTemp1.FieldByName('PFJ').AsFloat := cbj * frmPJRK.adoMain.FieldByName('PFJXS').AsFloat; adoTemp1.Post; j := j + 1; end else begin str := eclApp.Cells[i,1].Value; Application.MessageBox(PChar('零件"' + str + '"未注册!'),'提示',0); end; end; end; CountTotal; Application.MessageBox(PChar('成功导入'+IntToStr(j)+'行'),'提示',0); Except Application.MessageBox('导入失败!','错误',0); end; eclApp.ActiveWorkBook.close; eclApp.Quit; eclApp:= Unassigned; end;
2.用跨实例的触发器,将Insert,Update,Delete做为触发点
文本文件比较好,文件小巧,而Excel文件会大一点,操作速度也会稍慢一点,而且如果要打开文件的话,有些数据可能无法正常显示,而且如果在其中修改并保存的话,甚至会丢失数据,比如001可能会变成1
procedure TfrmJS.btnExportClick(Sender: TObject);
var
i:Integer;
eclApp,WorkBook:Variant;
xlsFileName:string;
begin
if adoTemp2.RecordCount = 0 then Exit;
if dlgExport.Execute then
xlsFileName := dlgExport.FileName
else
Exit; if xlsFileName = '' then
begin
Application.MessageBox('请输入文件名称','提示',0);
Exit;
end; try
eclApp := CreateOleObject('Excel.Application');
WorkBook := CreateOleObject('Excel.Sheet');
Except
Application.MessageBox('您的电脑中可能未安装Microsoft Excel!','错误',0);
Exit;
end; try
workBook:=eclApp.workBooks.Add;
eclApp.Cells(1,1):='零件名称';
eclApp.Cells(1,2):='零件代码';
eclApp.Cells(1,3):='车型';
eclApp.Cells(1,4):='产地';
eclApp.Cells(1,5):='数量';
eclApp.Cells(1,6):='单价';
adoTemp2.First;
for i:=1 to adoTemp2.RecordCount do
begin
eclApp.Cells(i+1,1):= adotemp2.FieldByName('PJMC').AsString;
eclApp.Cells(i+1,2):= adotemp2.FieldByName('PJDM').AsString;
eclApp.Cells(i+1,3):= adotemp2.FieldByName('CX').AsString;
eclApp.Cells(i+1,4):= adotemp2.FieldByName('CD').AsString;
eclApp.Cells(i+1,5):= adotemp2.FieldByName('SL').AsString;
eclApp.Cells(i+1,6):= adotemp2.FieldByName('DJ').AsString;
adoTemp2.Next;
end;
WorkBook.saveas(xlsFileName);
WorkBook.Close;
eclApp.Quit;
eclApp:=Unassigned;
Application.MessageBox(PChar('数据已成功导出到“' + xlsFileName + '.XLS"'),'提示',0);
except
Application.MessageBox('导出失败!','提示',0);
WorkBook.close;
eclApp.Quit;
eclApp:=Unassigned;
end;
end;
procedure TfrmInputPJ.btnImportClick(Sender: TObject);
var
i,j:Integer;
cbj:real;
eclApp:Variant;
xlsFileName,str:string;
begin
if dlgImport.Execute then
xlsFileName := dlgImport.FileName
else
Exit; try
eclApp := CreateOleObject('Excel.Application');
eclApp.WorkBooks.Open(xlsFileName);
eclApp.Visible:=False;
Except
Application.MessageBox('您的电脑中可能未安装Microsoft Excel!','错误',0);
Exit;
end; try
j := 0;
for i:=2 to eclApp.ActiveSheet.UsedRange.Rows.Count do
begin
cbj := StrToFloat(eclApp.Cells[i,6].Value);
with qryMain do
begin
Sql.Clear;
Sql.Add('select * from T_0103 where PJMC='''+ eclApp.Cells[i,1].Value+''' and PJDM='''+eclApp.Cells[i,2].Value+''' AND CX='''+eclApp.Cells[i,3].Value+''' AND CD='''+eclApp.Cells[i,4].Value+'''');
ExecSQL;
Open;
if RecordCount <> 0 then
begin
adoTemp1.Append;
adoTemp1.FieldByName('RKDH').AsString := edtRKDH.Text;
adoTemp1.FieldByName('PJBM').AsString := qryMain.FieldByName('PJBM').AsString;
adoTemp1.FieldByName('SL').AsString := eclApp.Cells[i,5].Value;
adoTemp1.FieldByName('CBJ').AsString := eclApp.Cells[i,6].Value;
adoTemp1.FieldByName('LSJ').AsFloat := cbj * frmPJRK.adoMain.FieldByName('LSJXS').AsFloat;
adoTemp1.FieldByName('QXJ').AsFloat := adoTemp1.FieldByName('LSJ').AsFloat;
adoTemp1.FieldByName('PFJ').AsFloat := cbj * frmPJRK.adoMain.FieldByName('PFJXS').AsFloat;
adoTemp1.Post;
j := j + 1;
end
else
begin
str := eclApp.Cells[i,1].Value;
Application.MessageBox(PChar('零件"' + str + '"未注册!'),'提示',0);
end;
end;
end;
CountTotal;
Application.MessageBox(PChar('成功导入'+IntToStr(j)+'行'),'提示',0);
Except
Application.MessageBox('导入失败!','错误',0);
end;
eclApp.ActiveWorkBook.close;
eclApp.Quit;
eclApp:= Unassigned;
end;