旧的入库程序 MaterialOutStock 对excel文件操作

procedure TForm1.BitBtn1Click(Sender: TObject); //导入Excel文件
var
  OpenExcelfile: TOpenDialog;
  i, j: Integer;
  MsExcel: Variant;
  MsExcelWorkBook: Variant;
begin
  i :=1;
  OpenExcelfile := TOpenDialog.Create(self);  //创建打开对话框
  OpenExcelfile.InitialDir := ExtractFilePath(GetCurrentDir + '\');
  OpenExcelfile.DefaultExt := 'xls';
  OpenExcelfile.Filter := 'All Files(*.xls)|*.xls';
  if OpenExcelFile.Execute then
  begin
    try
      MsExcel := CreateOleObject('Excel.Application');  //使用CreateOleObject将启动Office，然后以Ole方式对Office进行控制
      MsExcelworkBook := MsExcel.WorkBooks.Open(OpenExcelFile.FileName);
      StringGrid1.Cells[0, 0] := Trim(MsExcelworkBook.Worksheets[1].Cells[11, 2]);
      StringGrid1.Cells[1, 0]:= Trim(MsExcelworkBook.Worksheets[1].Cells[11, 3]);
      StringGrid1.Cells[2, 0] := Trim(MsExcelworkBook.Worksheets[1].Cells[11, 7]);
      StringGrid1.Cells[3, 0] := Trim(MsExcelworkBook.Worksheets[1].Cells[11, 6]);
      StringGrid1.Cells[4, 0] := Trim(MsExcelworkBook.Worksheets[1].Cells[11, 8]);
      StringGrid1.Cells[5, 0] := Trim(MsExcelworkBook.Worksheets[1].Cells[11, 4]);
 
      for J := 2 to 1000 do
      begin

        if (Trim(MsExcelworkBook.Worksheets[1].Cells[J+11, 3]) = '')  then
          break
        else
        begin
         // sss:=StrToFloat(Trim(MsExcelworkBook.Worksheets[1].Cells[J+9, 9]));
          if Trim(MsExcelworkBook.Worksheets[1].Cells[J+11, 6])<>'0' then
          begin
            StringGrid1.Cells[0, i] := inttostr(i);
            StringGrid1.Cells[1, i] := Trim(MsExcelworkBook.Worksheets[1].Cells[J+11, 3]);
            StringGrid1.Cells[2, i] := Trim(MsExcelworkBook.Worksheets[1].Cells[J+11, 7]);
            StringGrid1.Cells[3, i] := Trim(MsExcelworkBook.Worksheets[1].Cells[J+11, 6]);
            StringGrid1.Cells[4, i] := Trim(MsExcelworkBook.Worksheets[1].Cells[J+11, 8]);
            StringGrid1.Cells[5, i] := Trim(MsExcelworkBook.Worksheets[1].Cells[J+11, 4]);
            i:=i+1;
          end;
        end;
      end;
      StringGrid1.RowCount := i ;
      i:=1;

      edt_InsertMo.Text:=Trim(MsExcelworkBook.Worksheets[1].Cells[3, 5]);
      lbl_model.Caption:=Trim(MsExcelworkBook.Worksheets[1].Cells[6, 5]);
      lbl_Count.Caption:=Trim(MsExcelworkBook.Worksheets[1].Cells[7, 5]);
      try
        MsExcel.Quit;
        MsExcel := 'null';
      except
      end

    except
      begin
        {MSNPopUp1.Title := '提示';
        MSNPopUp1.Text := 'Can''t Open Excel!!';
        MSNPopUp1.ShowPopUp; }
        PanelMSG.Caption:='Can''t Open Excel！';
      end;
    end;
  end;
  OpenExcelfile.Free;

end;

procedure TForm1.btnImportClick(Sender: TObject); //将StringGrid 数据插入到数据库
var
  i: integer;
  tempnowtime:TDateTime;

begin
  if edt_InsertMo.Text<>'mo' then
  begin
    Qry_in.Close;
    Qry_in.SQL.Clear;
    Qry_in.SQL.Add('select * from sfism4.r_sap_molist_t where MO_NAME=:mo_name ');
    Qry_in.ParamByName('mo_name').AsString :=edt_InsertMo.Text;
    Qry_in.Prepare;
    Qry_in.Open;
    if Qry_in.RecordCount = 0  then
    begin
       try
          database1.StartTransaction;
          tempnowtime:=Now;
          for i := 1 to StringGrid1.RowCount - 1 do
          begin
              Qry_in.Close;
              Qry_in.SQL.Clear;
              Qry_in.SQL.Add('insert into sfism4.r_sap_molist_t (mo_name,item,kp,sloc,qty,flag,emp_no,in_date,bun,description) ' +
                ' values (:MO_NAME,:ITEM,:KP,:SLOC,:QTY,''N'',:EMP_NO,:IN_DATE,:BUn,:DESCRIPTION) ');
              Qry_in.ParamByName('MO_NAME').AsString := trim(edt_InsertMo.Text);
              Qry_in.ParamByName('ITEM').AsInteger :=StrToInt(StringGrid1.Cells[0, i]) ;
              Qry_in.ParamByName('KP').AsString := StringGrid1.Cells[1, i];
              Qry_in.ParamByName('SLOC').AsString :=StringGrid1.Cells[2, i];
              Qry_in.ParamByName('QTY').AsFloat := StrToFloat(StringGrid1.Cells[3, i]);
              Qry_in.ParamByName('BUn').AsString :=StringGrid1.Cells[4, i];


              Qry_in.ParamByName('EMP_NO').AsString := trim(emp_no);
              Qry_in.ParamByName('IN_DATE').AsDateTime :=tempnowtime; //FormatDateTime('YYYYMMDDHHMM', NOW);
              Qry_in.ParamByName('description').AsString :=StringGrid1.Cells[5, i];
              Qry_in.ExecSQL;
              PanelMSG.Caption:='SAP 工单插入数据库成功 ';
          end;
          database1.Commit;
       except
          on e: exception do
          begin
            database1.Rollback;
            {MSNPopUp1.Title := '提示';
            MSNPopUp1.Text := e.Message;
            MSNPopUp1.ShowPopUp; }
            showmessage('插入异常！'+e.Message);
          end;
       end;
    end
    else
    begin
      //提示已经存在工单
      showmessage('此工单已经存在，请确认是否要修改');
    end;
  end
  else
    PanelMSG.Caption:='请先导入Excel工单！';
end;