delphi excel导入SQL

Question

delphi ,excel导入SQL表A，表A字段expno为主键，导入时判断表中是否存在，如果存在就更新expno以外的字段值，如果不存在就插入记录。整批导入表A，不加判断已经实现，如果根据expno判断是否存在，更新和插入（excel中expno在表A中存在，还有不存在的），同时进行更新和插入。
请大家给思路或示例，谢

Answer 1

还是一条一条读Excel稳定，操作方式也比较灵活

Answer 2

打开excel表，逐条读取并判断sqlserver表中是否存在，不存在就写入，存在就更新

Answer 3

楼主，我的导入代码是，帮忙改造下，提示下如何加判断条件插入或更新procedure Tmain_fm.open_btnClick(Sender: TObject);
var
  i:Integer;
  MyExcelFile:string;
begin
   dlgOpen1.Title:= '请选择相应的Excel文件';
   dlgOpen1.Filter:='Excel(*.xls)|*.xls';
      if  dlgOpen1.Execute   then
      begin
      MyExcelFile :=dlgOpen1.FileName;
      dm.con2.Connected:=False;
      dm.con2.Close;
      dm.con2.ConnectionString:='Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+MyExcelFile+';Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";Persist Security Info=False';
      dm.con2.Connected:=true;
      dm.ds1.DataSet:=dm.qry1;
      dm.qry1.Close;
      dm.qry1.SQL.Clear;
      dm.qry1.SQL.Add('SELECT epxno,epxname,epxsex FROM [sheet1$]');
      dm.qry1.Open;
      for i:=1 to dm.qry1.RecordCount   do
     begin
       Application.ProcessMessages;
       self.refresh;
       dm.searth_qry.SQL.Clear;
       dm.searth_qry.SQL.Add('insert into madmpeo (epxno,epxname,epxsex)');
       dm.searth_qry.SQL.Add('values ('''+format('%.8d',[strtointdef(dm.qry1.fieldbyName(epxno).AsString,0)])+''','
                           +''+QuotedStr(dm.qry1.fieldbyName(epxname).AsString)+','+QuotedStr(dm.qry1.fieldbyName(epxsex).AsString)+')');
       ShowMessage(dm.searth_qry.SQL.text);
       try
         dm.searth_qry.ExecSQL;
       except
       end;
         Application.ProcessMessages;
         dm.qry1.Next;      end;
end;
end;

Answer 4

procedure Tmain_fm.open_btnClick(Sender: TObject);
var
  i:Integer;
  MyExcelFile:string;
begin
   dlgOpen1.Title:= '请选择相应的Excel文件';
   dlgOpen1.Filter:='Excel(*.xls)|*.xls';
      if  dlgOpen1.Execute   then
      begin
      MyExcelFile :=dlgOpen1.FileName;
      dm.con2.Connected:=False;
      dm.con2.Close;
      dm.con2.ConnectionString:='Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+MyExcelFile+';Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";Persist Security Info=False';
      dm.con2.Connected:=true;
      dm.ds1.DataSet:=dm.qry1;
      dm.qry1.Close;
      dm.qry1.SQL.Clear;
      dm.qry1.SQL.Add('SELECT epxno,epxname,epxsex FROM [sheet1$]');
      dm.qry1.Open;
      for i:=1 to dm.qry1.RecordCount   do
     begin
       Application.ProcessMessages;
       self.refresh;
       //这里加判断
       dm.searth_qry.SQL.Clear;
       dm.searth_qry.SQL.Add(select * from madmpeo where epxno=:s1);
       dm.search_qry.Parameters.ParamByName('s1').Value:=format('%.8d',[strtointdef(dm.qry1.fieldbyName(epxno).AsString,0)]);
       dm.search_qry.open;
       if dm.search_qry.recordcount=1 then
       begin
         //这里更新数据，自己写吧
         dm.search_qry.edit;
       end
       else
       begin
       //这里也可以用append
       dm.searth_qry.SQL.Clear;
       dm.searth_qry.SQL.Add('insert into madmpeo (epxno,epxname,epxsex)');
       dm.searth_qry.SQL.Add('values ('''+format('%.8d',[strtointdef(dm.qry1.fieldbyName(epxno).AsString,0)])+''','
                           +''+QuotedStr(dm.qry1.fieldbyName(epxname).AsString)+','+QuotedStr(dm.qry1.fieldbyName(epxsex).AsString)+')');
       ShowMessage(dm.searth_qry.SQL.text);
       try
         dm.searth_qry.ExecSQL;
       except
       end;
       end;
         Application.ProcessMessages;
         dm.qry1.Next;      end;
end;
end;

Answer 5

if dm.search_qry.recordcount=1 then
       begin
         //这里更新数据，自己写吧
         dm.search_qry.edit;
       end
       else
       begin
       //这里也可以用append
       dm.searth_qry.SQL.Clear;
       dm.searth_qry.SQL.Add('insert into madmpeo (epxno,epxname,epxsex)');
       dm.searth_qry.SQL.Add('values ('''+format('%.8d',[strtointdef(dm.qry1.fieldbyName(epxno).AsString,0)])+''','
                           +''+QuotedStr(dm.qry1.fieldbyName(epxname).AsString)+','+QuotedStr(dm.qry1.fieldbyName(epxsex).AsString)+')');
       ShowMessage(dm.searth_qry.SQL.text);
       try
         dm.searth_qry.ExecSQL;
       except
       end;
       end;
是这样改的？那你在else后设断点，看看是否有dm.search_qry.recordcount=0的情况

Answer 6

是这样修改的，设置断点后出F8执行到
      for i:=1 to dm.qry1.RecordCount   do
出错：BOF或EOF中有一个为真，或。

Answer 7

把这个
for i:=1 to dm.qry1.RecordCount   do
换成
while not dm.qry1.eof do另外，要保证循环中不能再用dm.qry1了

Answer 8

怀具啊，excel表中同时存在插入和更新的数据，要不只能插入，要不只能更新，不能两者兼得，折磨人

Answer 9

procedure Tmain_fm.in_btnClick(Sender: TObject);
var
  i:Integer;
  MyExcelFile:string;
begin
   dlgOpen1.Title:= '请选择相应的Excel文件';
   dlgOpen1.Filter:='Excel(*.xls)|*.xls';
      if  dlgOpen1.Execute   then
      begin
      MyExcelFile :=dlgOpen1.FileName;
      dm.con2.Connected:=False;
      dm.con2.Close;
      dm.con2.ConnectionString:='Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+MyExcelFile+';Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";Persist Security Info=False';
      dm.con2.Connected:=true;
      dm.ds1.DataSet:=dm.qry1;
      //dm.qry1.Close;
      dm.qry1.SQL.Clear;
      dm.qry1.SQL.Add('SELECT 编号 as peono,姓名 as peoname,性别 as peosex,公司 as peocompany,本部 as peoshiyebu,部门 as peodept,科室 as peosection,身份 as peoid,状态 as peostatus FROM [sheet1$]');
      dm.qry1.Open;
      for i:=1 to dm.qry1.RecordCount   do
      begin
       Application.ProcessMessages;
       self.refresh;
      //这里加判断
       dm.searth_qry.SQL.Clear;
       dm.searth_qry.SQL.Add('select * from madmpeo where peono=:s1');
       dm.searth_qry.Parameters.ParamByName('s1').Value:=format('%.8d',[strtointdef(dm.qry1.fieldbyName('peono').AsString,0)]);
       dm.searth_qry.Open;
       if dm.searth_qry.recordcount=1 then
       with dm.searth_qry do
       begin
         //这里更新数据，自己写吧
        // ShowMessage('update');
           dm.searth_qry.SQL.Clear;
           dm.searth_qry.SQL.Add('update madmpeo set peoname='+QuotedStr(dm.qry1.fieldbyName('peoname').AsString)+','
                               +'peosex='+QuotedStr(dm.qry1.fieldbyName('peosex').AsString)+','
                               +'peocompany='+QuotedStr(dm.qry1.fieldbyName('peocompany').AsString)+','
                               +'peoshiyebu='+QuotedStr(dm.qry1.fieldbyName('peoshiyebu').AsString)+','
                               +'peodept='+QuotedStr(dm.qry1.fieldbyName('peodept').AsString)+','
                               +'peosection='+QuotedStr(dm.qry1.fieldbyName('peosection').AsString)+','
                               +'peoid='+QuotedStr(dm.qry1.fieldbyName('peoid').AsString)+','
                               +'peostatus='+QuotedStr(dm.qry1.fieldbyName('peostatus').AsString)+' '
                               +'where peono='''+format('%.8d',[strtointdef(dm.qry1.fieldbyName('peono').AsString,0)])+'''');
           dm.searth_qry.ExecSQL;
         //dm.searth_qry.edit;
       end
       else
       begin
       //这里也可以用append
       dm.add_qry.Append;
       dm.add_qry.SQL.Clear;
       dm.add_qry.SQL.Add('insert into madmpeo (peono,peoname,peosex,peocompany,peoshiyebu,peodept,peosection,peoid,peostatus)');
       dm.add_qry.SQL.Add('values ('''+format('%.8d',[strtointdef(dm.qry1.fieldbyName('peono').AsString,0)])+''','
                           +''+QuotedStr(dm.qry1.fieldbyName('peoname').AsString)+','+QuotedStr(dm.qry1.fieldbyName('peosex').AsString)+','
                           +''+QuotedStr(dm.qry1.fieldbyName('peocompany').AsString)+','+QuotedStr(dm.qry1.fieldbyName('peoshiyebu').AsString)+','
                           +''+QuotedStr(dm.qry1.fieldbyName('peodept').AsString)+','+QuotedStr(dm.qry1.fieldbyName('peosection').AsString)+','
                           +''+QuotedStr(dm.qry1.fieldbyName('peoid').AsString)+','+QuotedStr(dm.qry1.fieldbyName('peostatus').AsString)+')');
       ShowMessage(dm.add_qry.SQL.text);
       dm.add_qry.Post;
       try
         dm.add_qry.ExecSQL;
       except
       end;
         Application.ProcessMessages;
         dm.qry1.Next;      end;
      end;
end;
end;大哥，这是完整的导入代码，帮忙修正下。
思路：导入的excel数据，qry1读取并导入数据库表，然后放在dbgrideh中显示

Answer 10

这段不对吧,改成这样试试//这里也可以用append
       //dm.add_qry.Append;
       dm.add_qry.close;
       dm.add_qry.connection:=dm.con2;
       dm.add_qry.SQL.Clear;
       dm.add_qry.SQL.Add('insert into madmpeo (peono,peoname,peosex,peocompany,peoshiyebu,peodept,peosection,peoid,peostatus)');
       dm.add_qry.SQL.Add('values ('''+format('%.8d',[strtointdef(dm.qry1.fieldbyName('peono').AsString,0)])+''','
                           +''+QuotedStr(dm.qry1.fieldbyName('peoname').AsString)+','+QuotedStr(dm.qry1.fieldbyName('peosex').AsString)+','
                           +''+QuotedStr(dm.qry1.fieldbyName('peocompany').AsString)+','+QuotedStr(dm.qry1.fieldbyName('peoshiyebu').AsString)+','
                           +''+QuotedStr(dm.qry1.fieldbyName('peodept').AsString)+','+QuotedStr(dm.qry1.fieldbyName('peosection').AsString)+','
                           +''+QuotedStr(dm.qry1.fieldbyName('peoid').AsString)+','+QuotedStr(dm.qry1.fieldbyName('peostatus').AsString)+')');
       ShowMessage(dm.add_qry.SQL.text);
       //dm.add_qry.Post;
       //try
         dm.add_qry.ExecSQL;
       //except
       //end;

Answer 11

感觉你对ado不是很熟悉啊

Answer 12

非常惭愧，对delphi编程也是一知半解，摸索中按照刚才您修正的代码又调试一下，在判断后，更新数据的执行前加入：
ShowMessage(dm.searth_qry.SQL.text);
弹出的语句中，全部显示excel表中第一条记录的更新语句，然后就结束了

Answer 13

dm.qry1.Next;的位置要在
while not dm.qry1.eof do
begin
end;
这层begin..end中

Answer 14

大哥，添加了，错误是：
帮忙，添加next在begin...end之间，记录总是从excel表第二行开始读取，最后一行会重复多次；excel表中还是不允许出现要插入的数据，同时存在更新和插入会报错，帮忙啊大哥