我用DELPHI+ACCESS做了一个小应用，发生了一件怪事： 订单数据明明插入到数据库了，可以正常进行下一步操作，并且可以正常导出，但软件关闭后，今天操作的订单数据会丢失，以前的数据不会丢失。后续的操作数据也会留下来。
下面贴出我导入EXCEL订单的代码。
procedure TfrmFPimport.btnOpenClick(Sender: TObject);
var
ExcelApp: Variant;
sl,subsl:TStringList;
i,j:integer;
filename,strSql,sql_mx:string;
fapiao,yearmonth:string;
cmd: TadoCommand;
e: exception;
kprq,bz,rq1,rq2,bizhi: string;
code,number,capital_hz:string;
hl,yfbf,dj,zj,dj_rmb,zj_rmb,rmb_hz,usd_hz,usd_hz_rmb,chayi,hangshu:double;
road:string;
begin
dlgOpen1.FileName:='';
dlgOpen1.Execute;
filename:=dlgOpen1.FileName;
if filename <>'' then
begin
try
sl:=TStringList.Create;
subsl:=TStringList.Create;
btnOpen.Enabled :=false;
ADOConnection_Excel.Connected:=False;
ADOConnection_Excel.ConnectionString:='Provider=Microsoft.Jet.OLEDB.4.0;Password="";Data Source="'+
filename+'";Extended Properties="Excel 8.0;IMEX=1";Persist Security Info=False';
ADOConnection_Excel.Connected:=True;
ADOConnection_Excel.GetTableNames(sl);
if (sl.Count>0) and (sl.IndexOf('sheet1$')<>-1) then
begin
road:=ExtractFileDir(Application.Exename);
ADOConnection1.ConnectionString:='Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source='+road+'\database\wxfp.mdb;Mode=Share Deny None;Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Database Password=nbeport2010;Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=1;';
ADOConnection1.Connected:=True;
ADOConnection1.BeginTrans;
cmd := TadoCommand.Create(application);
cmd.Connection := dmdCustom.cnnCustom;
// cmd.ParamCheck := false;
ADOQuery_Excel.Close;
ADOQuery_Excel.Sql.Text:='SELECT * FROM [sheet1$]';
ADOQuery_Excel.Open;
ADOQuery_Excel.First;
for i:=1 to ADOQuery_Excel.RecordCount do
begin
//判断是否要发票已经开过了
qry_RunSql.Close;
qry_RunSql.SQL.text:='select bgd_no from wxInvoice where bgd_no=:HGBH and zfflag='''+'0'+'''';
qry_RunSql.Parameters.ParamByName('HGBH').Value:= ADOQuery_Excel.fieldbyname('内部发票号').AsString;
qry_RunSql.Open;
if not qry_RunSql.Eof then
begin
showmessage('内部发票号：'+ADOQuery_Excel.fieldbyname('内部发票号').AsString+'已经开具！请在excel文件中删除');
ADOConnection_Excel.Connected:=False;
exit;
end;
end;
ADOQuery_Excel.Close;
ADOQuery_Excel.Sql.Text:='SELECT * FROM [sheet1$]';
ADOQuery_Excel.Open;
if ADOQuery_Excel.RecordCount>0 then
begin
ADOQuery_Excel.First;
fapiao:= '';
advprgrsbr1.Min:=1;
advprgrsbr1.Max:= ADOQuery_Excel.RecordCount-1;
for i:=1 to ADOQuery_Excel.RecordCount do
begin
advprgrsbr1.Position:=i;
Application.ProcessMessages;
if ADOQuery_Excel.fieldbyname('内部发票号').AsString = fapiao then
begin
// 多行的情况下，判断行数
qry_excel2.Close;
qry_excel2.SQL.text:='SELECT * FROM [sheet1$] where 内部发票号=:fp';
qry_excel2.Parameters.ParamByName('fp').Value:= fapiao;
qry_excel2.Open;
hangshu:= qry_excel2.RecordCount;
//处理明细行
//匹配国税编码及名称
qry.Close;
qry.SQL.Text:='select * from tblCmcode where code=:code';
qry.Parameters.ParamByName('code').value:=ADOQuery_Excel.fieldbyname('HS编码').AsString;
qry.Open;
//只考虑FOB情况
zj:= ADOQuery_Excel.fieldbyname('报关金额').AsFloat;
usd_hz:=usd_hz +strtofloat(format('%0.2f', [zj]));
zj_rmb:= hl * zj;
zj_rmb:= strtofloat(format('%0.2f', [zj_rmb]));
dj_rmb:= zj_rmb / ADOQuery_Excel.fieldbyname('报关数量').AsFloat;
rmb_hz:= rmb_hz + zj_rmb;
usd_hz_rmb:=strtofloat(format('%0.2f', [usd_hz* hl])); //汇总美元金额 直接 乘以 汇率
if j = hangshu then
begin
chayi:= usd_hz_rmb - rmb_hz;
chayi:= strtofloat(format('%0.2f', [chayi]));
end;
sql_mx:= 'insert into wxInvoiceDetail(invCode,invNumber,hscode,goodsName,model,unit,price,quantity,amount,'+
'gscode,gsname,depname,rmb_dj,jsAmount,XH) values(''' +
code + ''',''' +
number + ''',''' +
ADOQuery_Excel.fieldbyname('HS编码').AsString + ''',''' +
ADOQuery_Excel.fieldbyname('报关品名').AsString + ''',''' +
ADOQuery_Excel.fieldbyname('规格型号').AsString + ''',''' +
'个' + ''',' +
ADOQuery_Excel.fieldbyname('报关单价').AsString + ',' +
ADOQuery_Excel.fieldbyname('报关数量').AsString + ',' +
format('%0.2f', [zj]) + ',''' +
qry.fieldbyname('GScode').AsString + ''',''' +
qry.fieldbyname('name').AsString + ''',''' +
qry.fieldbyname('depname').AsString + ''',' +
format('%0.6f', [dj_rmb]) + ',' +
format('%0.2f', [zj_rmb+chayi]) + ',''' +
inttostr(j) + ''')' ;
j:=j+1;
// sql_mx := StringReplace(sql_mx, '''','''''', [rfReplaceAll]);
cmd.CommandText := sql_mx;
cmd.Execute();
//处理人民币合计 更新到表头里
capital_hz:= MoneyConvert(usd_hz_rmb);
sql_mx:='update wxInvoice set rmb=:renminbi,capital=:capital,amt=:amt where bgd_no=:bgd_no';
cmd.CommandText:= sql_mx;
cmd.Parameters.ParamByName('renminbi').Value:= formatfloat('0.00',usd_hz_rmb);
cmd.Parameters.ParamByName('capital').Value:= capital_hz;
cmd.Parameters.ParamByName('amt').Value:= usd_hz;
cmd.Parameters.ParamByName('bgd_no').Value:= ADOQuery_Excel.FieldValues['内部发票号'];
cmd.Execute();
fapiao:= ADOQuery_Excel.FieldValues['内部发票号'];
end
else
begin
if trim(ADOQuery_Excel.fieldbyname('内部发票号').AsString)='' then break;
//初始化需要累加的值
j:=1;
rmb_hz :=0;
usd_hz:=0;
qry.Close;
//获取发票号码 代码
strSql := 'select startNumber,pcs,remain,invCode,id from wxFpkc' +
' where remain >0 ' +
' order by id';
qry.SQL.Text := strSql;
qry.Open;
if qry.Eof then
begin
qry.Close;
showmessage('无可用的发票库存信息，请导入发票！');
exit;
end;
Number := formatfloat('00000000',strtoint(qry.Fields[0].AsString) +qry.Fields[1].AsInteger - qry.Fields[2].AsInteger);
code:= qry.Fields[3].AsString;
//判断汇率有没有设置过
kprq:= ADOQuery_Excel.fieldbyname('开票日期').AsString;
kprq:= StringReplace (kprq, '.', '-', [rfReplaceAll]);
yearmonth:=FormatdateTime('yyyymm',strtodatetime(kprq));
qry_RunSql.Close;
qry_RunSql.SQL.text:='select * from tblBicode where code=:code and VALIDMONTH=:VALIDMONTH';
qry_RunSql.Parameters.ParamByName('VALIDMONTH').Value:= yearmonth;
qry_RunSql.Parameters.ParamByName('code').Value:= ADOQuery_Excel.fieldbyname('币别').AsString;
qry_RunSql.Open;
if qry_RunSql.Eof then
begin
showmessage(yearmonth+'汇率没有设置，请设置！');
exit;
end
else
hl:= qry_RunSql.fieldbyname('hl').asfloat/100;
//处理明细行
//匹配国税编码及名称
qry.Close;
qry.SQL.Text:='select * from tblCmcode where code=:code';
qry.Parameters.ParamByName('code').value:=ADOQuery_Excel.fieldbyname('HS编码').AsString;
qry.Open;
//只考虑FOB情况
zj:= ADOQuery_Excel.fieldbyname('报关金额').AsFloat;
usd_hz:=usd_hz +strtofloat(format('%0.2f', [zj]));
zj_rmb:= hl * zj;
zj_rmb:= strtofloat(format('%0.2f', [zj_rmb]));
dj_rmb:= zj_rmb / ADOQuery_Excel.fieldbyname('报关数量').AsFloat;
rmb_hz:= rmb_hz + zj_rmb;
//处理汇总差异
usd_hz_rmb:=strtofloat(format('%0.2f', [usd_hz* hl])); //汇总美元金额 直接 乘以 汇率
if j = hangshu then
begin
chayi:= usd_hz_rmb - rmb_hz;
chayi:= strtofloat(format('%0.2f', [chayi]));
end;
//处理人民币合计
capital_hz:= MoneyConvert(usd_hz_rmb);
sql_mx:= 'insert into wxInvoiceDetail(invCode,invNumber,hscode,goodsName,model,unit,price,quantity,amount,'+
'gscode,gsname,depname,rmb_dj,jsAmount,XH) values(''' +
code + ''',''' +
number + ''',''' +
ADOQuery_Excel.fieldbyname('HS编码').AsString + ''',''' +
ADOQuery_Excel.fieldbyname('报关品名').AsString + ''',''' +
ADOQuery_Excel.fieldbyname('规格型号').AsString + ''',''' +
'个' + ''',' +
ADOQuery_Excel.fieldbyname('报关单价').AsString + ',' +
ADOQuery_Excel.fieldbyname('报关数量').AsString + ',' +
format('%0.2f', [zj]) + ',''' +
qry.fieldbyname('GScode').AsString + ''',''' +
qry.fieldbyname('name').AsString + ''',''' +
qry.fieldbyname('depname').AsString + ''',' +
format('%0.6f', [dj_rmb]) + ',' +
format('%0.2f', [zj_rmb+chayi]) + ',''' +
inttostr(j) + ''')' ;
j:=j+1;
cmd.CommandText := sql_mx;
cmd.Execute();
//处理表头信息
qry1.Close;
qry1.SQL.Text:='select top 1 * from tblCompany';
qry1.Open;
strSql:= 'insert into wxInvoice(taxNumber,invType,invCode,invNumber,kpDate,bgd_no,clientCode,CustName,amt,operator,payee,fhr,remark,'+
'crny,exchange,rmb,contractNo,jckrq,capital) values(''' +
'91330201610258252F.1' + ''',''' +
'27310' + ''',''' +
code + ''',''' +
Number + ''',''' +
kprq + ''',''' +
ADOQuery_Excel.fieldbyname('内部发票号').AsString + ''',''' +
ADOQuery_Excel.fieldbyname('客户名称').AsString + ''',''' +
ADOQuery_Excel.fieldbyname('客户名称').AsString + ''',' +
formatfloat('0.00',usd_hz) + ',''' +
qry1.fieldbyname('kpr').AsString + ''',''' +
qry1.fieldbyname('skr').AsString + ''',''' +
qry1.fieldbyname('fhr').AsString + ''',''' +
ADOQuery_Excel.fieldbyname('备注').AsString + ''',''' +
ADOQuery_Excel.fieldbyname('币别').AsString + ''',' +
floattostr(hl*100) + ',''' +
formatfloat('0.00',usd_hz_rmb) + ''',''' +
ADOQuery_Excel.fieldbyname('合同号').AsString + ''',''' +
kprq + ''',''' +
capital_hz + ''')' ;
//strSql := StringReplace(strSql, '''','''''', [rfReplaceAll]);
cmd.CommandText := strSql;
cmd.Execute();
//更新库存，并以此为基准更新国税库存
strSql := 'update wxFpkc set remain=remain-1';
cmd.CommandText := strSql;
cmd.Execute();
strSql := 'update wxFpkc ' +
' set crtNumber= right( ''00000000'' +cstr( cdbl(endNumber )-remain +1) ,8) ';
cmd.CommandText := strSql;
cmd.Execute();
strSql := 'delete from wxFpkc where remain=0';
cmd.CommandText := strSql;
cmd.Execute();
fapiao:= ADOQuery_Excel.FieldValues['内部发票号'];
end;
ADOQuery_Excel.Next;
// 更新发票库存
end;
ADOConnection1.CommitTrans; //*******//提交到后台数据库
end;
end;
except
ADOConnection1.RollbackTrans;
showmessage(e.Message);
ADOConnection1.Connected:=false;
ADOConnection_Excel.Connected:=False;
end;
ADOConnection_Excel.Connected:=False;
// ADOConnection1.endtrans;
ADOConnection1.Connected:=false;
showmessage('导入成功！');
btnOpen.Enabled :=true;
end;
end;
