Question

如何在DELPHI中将EXCEL中的数据导入到SQL数据库

Answer 1

用这个方法吧，好理解且不容易出错。
var
nu,barcode1,da,na,ena,com,spec:string;
ExcelApp,WorkBook: Olevariant;
ExcelRowCount,i :Integer;
begin
dbgrid2.Enabled:=false;
try
if SaveDialog1.Execute then
begin

ExcelApp := CreateOleObject('Excel.Application');
WorkBook := ExcelApp.WorkBooks.Open(savedialog1.FileName);
ExcelApp.Visible := false;
ExcelRowCount := WorkBook.WorkSheets[1].UsedRange.Rows.Count;

for i := 2 to excelrowcount + 1 do
begin
nu := excelapp.Cells[i,1].Value;
if trim(excelapp.cells[i,2].value)='' then
na:=' '
else begin
na:=excelapp.Cells[i,2].Value;
end;
if trim(excelapp.cells[i,3].value)='' then
ena:=' '
else
ena:= excelapp.Cells[i,3].Value;
if trim(excelapp.cells[i,4].value)='' then
com:=' '
else
com:= trim(excelapp.Cells[i,4].Value);
if trim(excelapp.cells[i,5].value)='' then
spec:=' '
else
spec:= excelapp.Cells[i,5].Value;

if ((excelapp.Cells[i,1].Value = '') and (ExcelApp.Cells[i,3].Value = '')) then //指定excel档的第 i 行 ,第 1,2(看情况而定)行如果为空就退出,这样的设定,最好是你的档案力这两行//对应数据库中不能为空的数据
exit
else
with data.product do
begin
close;
sql.clear;
sql.Add('select * from twoven where number='''+nu+'''');
open;
if recordcount>=1 then begin
if MessageBox(handle,'此记录已存在，是否覆盖原记录？','提示',MB_IconQuestion+MB_YesNo)=IDYES then
BEGIN
Delete;
sql.Clear;
sql.add('insert into twoven(number,name,ename,wf_comp,wf_spec)');
sql.add(' values(:number,:name,:ename,:wf_comp,:wf_spec)');
Parameters.ParamByName('number').Value :=trim(nu);//excel档的第一列插入到aa表的 a 栏位;
Parameters.ParamByName('name').Value := trim(na);//excel档的第二列插入到aa表的 b 栏位;
Parameters.ParamByName('ename').Value := trim(ena);//excel档的第一列插入到aa表的 a 栏位;
Parameters.ParamByName('wf_comp').Value := trim(com);//excel档的第一列插入到aa表的 a 栏位;
Parameters.ParamByName('wf_spec').Value := trim(spec);//excel档的第二列插入到aa表的 b 栏位;
execsql;
END;
end;=

end;
showmessage('数据导入成功！');
WorkBook.Close;
ExcelApp.Quit;
ExcelApp := Unassigned;
WorkBook := Unassigned;

ExcelWorkBook1.Close(false);
ExcelApplication1.Disconnect;
ExcelApplication1.Quit;
Screen.Cursor:=crDefault;
end;