DELPHI 导EXCEL数据至SQL(稍微改了下)

 

procedure TProductImpt.btn3Click(Sender: TObject);
var
  ExcelID, Sheet: Variant;
  sFileName, sproductname: string;
  i, ExcelRowCount, OkNum, FailNum: integer;
begin
//指定文件
  OkNum := 0; //导入成功的数据
  FailNum := 0; //导入失败的数据
  ProgressBar1.Min := 0;
  ProgressBar1.Position := 0;
  mmo_showEx.Lines.Clear;
    //指定要导入的EXCEL文件
  with MyOpenDialog do
  begin
    DefaultExt := 'Xls';
    Filter := 'Excel工作簿文件（*.xls）|*.Xls';
    if Execute then
      sFileName := FileName
    else
      exit;

  end;

  try
    ADOQuery1.Close;
    ADOQuery1.SQL.Clear;
    ADOQuery1.SQL.Add('TRUNCATE table PRODUCTMAINDOSSIERSP;');
   // ADOQuery1.SQL.Add('TRUNCATE table ShoppeSelfPrice;');
    ADOQuery1.ExecSQL;
    ExcelID := CreateOleObject('Excel.Application');
    ExcelID.Visible := False; //若为true,则将显示并打开将要导入的excel文件
  except
    on E: Exception do
    begin
      ExcelID.Quit;
      ExcelID := Unassigned;
      Application.Restore;
      Application.BringToFront;
      MessageBox(Self.Handle, Pchar('系统提示您,创建Excel对象出错,原因为:' + e.Message), Pchar(AppliCation.Title), MB_OK + MB_ICONERROR);
      Exit;
    end;
  end;

  try
    try
      ExcelID.WorkBooks.Open(sFileName);
      Sheet := ExcelID.WorkBooks[1].WorkSheets[1];
      ExcelRowCount := ExcelID.WorkSheets[1].UsedRange.Rows.Count; //获得本数据表有多少行数据
      if (Trim(string(Sheet.Cells[1, 5].Value)) <> '零售价') or (Trim(string(Sheet.Cells[1, 1].Value)) <> '商品代码') then
      begin
        Application.MessageBox('EXCEL数据格式错误，请参照格式', '错误',
          MB_OK + MB_ICONSTOP);
        Exit;
      end;
      ProgressBar1.Max := ExcelRowCount;
      ADOConnection1.BeginTrans; //开始事务
      for i := 2 to ExcelRowCount do //将要从哪行开始读,本程序为从第二行开始读,第一行为标题
      begin
        if length(trim(Sheet.Cells[i, 1].Value)) <= 0 then //如果第一个单元格为空,则提前结束循环,此处也可以用其它方式结束
        begin
          mmo_showEx.Lines.Add('EXCLE表格的第' + inttostr(i) + '行是空白数据!');
          ExcelRowCount := ExcelRowCount - 1;
          ProgressBar1.Max := ProgressBar1.Max - 1;
          Continue;
        end;
        with ADOQuery1 do
        begin
          close;
          SQL.Clear;
          SQL.Add('Select * from PRODUCTMAINDOSSIERSP where PRODUCTID = ''' + Sheet.Cells[i, 1].Value + ''''); //此处指定每行第一单元格的内容为关键字,不得有重复,若有重复,则该行的数据不导入
          Open;
          if ADOQuery1.RecordCount = 0 then
          begin
            try
              Close;
              SQL.Clear;
              sproductname := string(Sheet.Cells[i, 3].Value);
              sproductname := StringReplace(sproductname, '''', '', [rfReplaceAll]);//防止有引号导致插入错误
              SQL.Add('Insert into PRODUCTMAINDOSSIERSP(PRODUCTID, PRODUCTTYPE, PRODUCTNAME, COUNTRYBARCODE,RETAILPRICE)');
              sql.Add('values (:a,:b,:c,:d,:e)');
              Parameters.parambyname('a').Value := string(Sheet.Cells[i, 1].Value); //此种方法会速度加快点
              Parameters.parambyname('b').Value := string(Sheet.Cells[i, 2].Value);
              Parameters.parambyname('c').Value := sproductname;
              Parameters.parambyname('d').Value := string(Sheet.Cells[i, 4].Value);
              Parameters.parambyname('e').Value := string(Sheet.Cells[i, 5].Value);
              OkNum := OkNum + 1; //导入成功数加1
              ExecSQL;
              ProgressBar1.Position := ProgressBar1.Position + 1;
            except

              OkNum := OkNum - 1;
              FailNum := FailNum + 1;
              mmo_showEx.Lines.Add('货号' + string(Sheet.Cells[i, 1].Value) + '导入失败');
              ProgressBar1.Max := ProgressBar1.Max - 1;
            end;
          end
          else
          begin
            mmo_showEx.Lines.Add('货号' + string(Sheet.Cells[i, 1].Value) + '在EXCEL里数据重复');
            ProgressBar1.Max := ProgressBar1.Max - 1;
          end;
        end;
      end;

      with ADOQuery1 do
      begin
        Close;
        SQL.Clear;
        sql.Add('delete from PRODUCTMAINDOSSIER where PRODUCTID in (select  PRODUCTID from  PRODUCTMAINDOSSIERSP)');
        SQL.Add('insert PRODUCTMAINDOSSIER(PRODUCTID, PRODUCTTYPE, PRODUCTNAME, COUNTRYBARCODE,BASICMEASUREUNITS,CancelSign,SysUseProduct,SELLATTRIB,ShoppeID) ');
        SQL.Add('select PRODUCTID, PRODUCTTYPE, PRODUCTNAME, COUNTRYBARCODE,''01'',''N'',''N'',''01'',''' + Logininfo.ShoppeID + ''' from  PRODUCTMAINDOSSIERSP');
        sql.Add('delete from ShoppeSelfPrice where PRODUCTID in (select  PRODUCTID from  PRODUCTMAINDOSSIERSP)');
        SQL.Add('insert ShoppeSelfPrice(PRODUCTID,RETAILPRICE,UpShoppeID) ');
        SQL.Add('select PRODUCTID, RETAILPRICE, ''' + Logininfo.ShoppeID + ''' from  PRODUCTMAINDOSSIERSP');

        ExecSQL;
      end;

      ADOConnection1.CommitTrans; //提交事务
      with ADOQuery1 do
      begin
        Close;
        sql.Text := 'select count(*) from PRODUCTMAINDOSSIERSP';
        Open;
      end;
      MessageBox(Self.Handle, Pchar('系统提示您:' + #13 + 'EXCEL表格中共 ' + inttostr(ExcelRowCount) + '条信息' + #13
        + '系统共成功导入' + FloatToStr(OkNum + FailNum) + '条信息!' + #13
        + '其中重复数据:' + floattostr(ExcelRowCount - ADOQuery1.Fields[0].AsFloat) + '条信息' + #13
        + '未导入: ' + IntToStr(FailNum) + ' 条信息!'),
        Pchar(AppliCation.Title), MB_OK + MB_ICONINFORMATION);
      with ADOQuery1 do
      begin
        qry1.Close;
        qry1.Open;
      end;
      pgc1.ActivePage := ts2;

    except
      on E: Exception do
      begin
        ADOConnection1.RollbackTrans; //报错,回滚事务
        MessageBox(Self.Handle, Pchar('系统提示您,数据导入失败,原因为:' + e.Message),
          Pchar(AppliCation.Title), MB_OK + MB_ICONERROR);
      end;
    end
  finally
    ExcelID.WorkBooks[1].Close(false, '');
    ExcelID.Quit;
    ExcelID := Unassigned;
    sheet := Unassigned;
  end;