浅谈Excel导入和后台处理
1. 导入方法
procedure TfrmSalarySmallPartsRecords.btnExcelToSQLClick(Sender: TObject); var cntI: Integer; sTmptbname,sFileName, sSQL,checkSql: string; ExcelID, Sheet, errsheet: Variant; FFieldString: string; i, j, FCount, ExcelRowCount, errint: integer; errbl: boolean; vworkerID,vworkerName,vsortType,veffectiveDate:String;
qryTemp2:TADOQuery; begin inherited; sTmptbname:='##tmptb'+formatdatetime('yyyymmddhhMMsszzzz',now); with open1 do begin if Execute then sFileName := FileName else exit; end;
with qryInput do begin Close; SQL.Clear; SQL.Add('select * into '+sTmptbname+' from TargetTable(nolock) where 1<>1'); execsql; close; sql.Text:='select * from '+sTmptbname+''; open; end;
try ExcelID := CreateOleObject('Excel.Application'); ExcelID.Visible := False; except on E: Exception do begin ExcelID.Quit; ExcelID := Unassigned; MessageBox(Self.Handle, Pchar('创建Excel对象出错,原因为:' + e.Message), Pchar(AppliCation.Title), MB_OK + MB_ICONERROR); Exit; end; end; FFieldString := 'workerName;workerID;effectiveDate;styleno;remark;qty;sortType;sortName;bgprice'; FCount := CountChar(';', FFieldString); errbl:=false; try try ExcelID.WorkBooks.Open(sFileName); Sheet := ExcelID.WorkBooks[1].WorkSheets[1]; errsheet := ExcelID.WorkBooks[1].WorkSheets[2]; ExcelRowCount := ExcelID.WorkSheets[1].UsedRange.Rows.Count; //获得本数据表有多少行数据 errint := 1; //开始事务 for i := 2 to ExcelRowCount do //将要从哪行开始读 begin if length(trim(Sheet.Cells[i, 2].Value)) <= 0 then break; //如果第二个单元格(工号)为空,则提前结束循环,此处也可以用其它方式结束 with qryInput do begin Append; FieldByName('inDate').Value := GetNowTime; for j := 0 to FCount do begin if (j = 0) or (j = 7) then //姓名、配件名称 begin vworkerName:= Sheet.Cells[i,j+1].Value ; Continue; end else if j = 1 then //工号 begin // 判断工号是否存在 vworkerID vworkerID := Sheet.Cells[i,j+1].Value ; with qrytemp do begin Close; SQL.text:='select top 1 empid,empname from Employees where empid = ' + QuotedStr(vworkerID) ; Open; end; if qrytemp.IsEmpty then begin errsheet.Cells[errint,1]:='行'+inttostr(i)+' '+vartostr(Sheet.Cells[i,j+1])+' 找不到对应的工号!'; inc(errint); errbl:=true; end else begin if vworkerName<>qrytemp.FieldByName('empname').AsString then begin errsheet.Cells[errint,1]:='行'+inttostr(i)+' '+vartostr(Sheet.Cells[i,j+1])+' 工号与姓名不符合!'; inc(errint); errbl:=true; FieldByName('workerID').AsString:= vworkerID; FieldByName('workerName').AsString:= qrytemp.FieldByName('empname').AsString; end else begin FieldByName('workerID').AsString:= vworkerID; FieldByName('workerName').AsString:= qrytemp.FieldByName('empname').AsString; end; end; end else if j = 2 then //作业日期 veffectiveDate begin veffectiveDate:= Sheet.Cells[i,j+1].Value; if (veffectiveDate = '') OR (veffectiveDate = '1899-12-30') OR (veffectiveDate = '1900-01-01') OR (veffectiveDate = '1999-12-30') then begin FieldByName(CopySubStr(FFieldString,j)).Value := null; errsheet.Cells[errint,1]:='行'+inttostr(i)+' '+vartostr(Sheet.Cells[i,j+1])+' 作业日期不能为空!'; inc(errint); errbl:=true; FieldByName(CopySubStr(FFieldString,j)).Value := veffectiveDate; end else begin FieldByName(CopySubStr(FFieldString,j)).Value := veffectiveDate; end; end else if j = 5 then //件数/工时 begin if (Sheet.Cells[i,j+1].Value<=0) or (Sheet.Cells[i,j+1].Value>99999) then begin errsheet.Cells[errint,1]:='行'+inttostr(i)+' '+vartostr(Sheet.Cells[i,j+1])+' 件数/工时错误!'; inc(errint); errbl:=true; FieldByName(CopySubStr(FFieldString,j)).Value := Sheet.Cells[i,j+1].Value; end else begin FieldByName(CopySubStr(FFieldString,j)).Value := Sheet.Cells[i,j+1].Value; end; end else if j = 6 then //配件类型 vsortType begin vsortType := Sheet.Cells[i,j+1].Value ; with qrytemp do begin Close; SQL.text:='select top 1 sortType,sortDesc from tab_smallPartsSort where sortType = ' + QuotedStr(vsortType) ; Open; end; if qrytemp.IsEmpty then begin errsheet.Cells[errint,1]:='行'+inttostr(i)+' '+vartostr(Sheet.Cells[i,j+1])+' 找不到对应的配件类型!'; inc(errint); errbl:=true; end else begin FieldByName('sortType').AsString:= vsortType; FieldByName('sortName').AsString:= qrytemp.FieldByName('sortDesc').AsString; //TT001,TT002:计时类型 if (vsortType='TT001') or (vsortType='TT002') then FieldByName('priceType').AsString:= 'T' else FieldByName('priceType').AsString:= 'P'; end; end else begin FieldByName(CopySubStr(FFieldString, j)).Value := Sheet.Cells[i, j + 1].Value; end; end; if not errbl then post; end; end; {判断插入的记录是否有重复_begin} checkSql:='SELECT workerid,pricetype,effectivedate,sorttype,styleno,COUNT(*) cc FROM ' + sTmptbname + ' GROUP BY workerid,pricetype,effectivedate,sorttype,styleno ' + ' HAVING COUNT(*) >1';
try
qryTemp2:=TADOQuery.Create(nil);
qryTemp2.connection:=DbFrm.cn; with qrytemp2 do begin Close; SQL.Clear; sql.Add(checkSql); Open; end; if qrytemp2.RecordCount>0 then //有重复记录 begin MessageDlg('导入数据出错,款号记录'+qrytemp2.fieldbyname('styleno').AsString+'有重复！',mtWarning,[mbOK],0); with qryInput do begin Close; SQL.Clear; SQL.Add('drop table '+sTmptbname); execsql; end; exit; end; {判断插入的记录是否有重复_end} finnaly
qryTemp2.Free;
end; if errbl then begin ExcelID.DisplayAlerts:=false; messagedlg('导入数据出错,请看文件第二页,查看详细原因!',mtwarning,[mbok],0); ExcelID.save; with qryInput do begin Close; SQL.Clear; SQL.Add('drop table '+sTmptbname); execsql; end; exit; end; //ShowMesStr('导入数据成功完成!', '010'); except on E: Exception do begin MessageBox(Self.Handle, Pchar('系统提示您,数据导入失败,原因为:' + e.Message), Pchar(AppliCation.Title), MB_OK + MB_ICONERROR); with qryInput do begin Close; SQL.Clear; SQL.Add('drop table '+sTmptbname); execsql; end; exit; end; end finally ExcelID.WorkBooks[1].Close(false, ''); ExcelID.Quit; ExcelID := Unassigned; sheet := Unassigned; end;
//利用服务器存储过程处理导入数据 sSQL:= 'exec UpDemo :tmptb'; sSQL:=StringReplace(sSQL,':tmptb',sTmptbname,[rfIgnoreCase,rfReplaceAll]); with qrytemp do begin Close; SQL.Clear; SQL.Add(ssql); ExecSQL; end; ShowMesStr('导入更新数据成功完成,请重新查询!','010'); end;
2. 后台处理的存储过程（UpDemo）:如果数据存在且未审核就修改相关信息；如果不存在，则插入数据
CREATE PROCEDURE UpDemo @tmptbname varchar(30) AS BEGIN declare @sql varchar(4000), @sql2 VARCHAR(1000) SET @sql=' MERGE TargetTable t USING (SELECT b.workerID,a.workerName,a.priceType,a.effectiveDate,a.sortType,a.sortName,a.styleno,a.bgprice ,a.inDate,a.inUserID,a.inUserDate,a.qty,a.checkUserID,a.checkDate,a.isConfirm,a.remark from '+ @tmptbname +' a LEFT JOIN (SELECT empid AS workerID,empname FROM Employees) b ON b.empname=a.workerName WHERE NOT EXISTS (SELECT t.workerID,t.priceType,t.sortType,t.effectiveDate FROM TargetTable t WHERE t.workerID=a.workerID AND t.priceType=a.priceType AND t.sortType=a.sortType AND t.effectiveDate=a.effectiveDate AND t.styleno=a.styleno AND t.isConfirm=1 ) --过滤审核过的数据不可修改 ) s ON t.workerID=s.workerID and t.priceType=s.priceType AND t.styleno=s.styleno and t.sortType=s.sortType and t.effectiveDate=s.effectiveDate WHEN matched THEN UPDATE SET t.sortName=s.sortName,t.qty=s.qty,t.bgprice=s.bgprice, t.inUserID=s.inUserID,t.InDate=s.InDate,t.inUserDate=s.inUserDate,t.Remark=s.Remark WHEN NOT MATCHED THEN INSERT (workerID,workerName,priceType,effectiveDate,sortType,sortName,styleno,bgprice,inDate,inUserID,inUserDate,qty,checkUserID,checkDate,isConfirm,remark) VALUES(workerID,workerName,priceType,effectiveDate,sortType,sortName,styleno,bgprice,inDate,inUserID,inUserDate,qty,checkUserID,checkDate,isConfirm,remark); ' SET @sql2='' SET @sql2=@sql2+ ' drop table '+ @tmptbname print @sql PRINT @sql2 exec (@sql+@sql2) END
