建议分两步走,先将lxx的excel导入到一个临时表temp_sfbba中,然后再从temp_sfbba中取出相应字段导入到sfbba select * into temp_sfbba from OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 8.0;HDR=YES;DATABASE=Lxx.xls',sheet1$) 然后从temp_sfbba中的相应字段中取值导入到sfbba中 insert into sfbba(你的相应字段) values select (temp_sfbba的对应字段) from temp_sfbba
看一下这个
select * into temp_sfbba from OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 8.0;HDR=YES;DATABASE=Lxx.xls',sheet1$)
然后从temp_sfbba中的相应字段中取值导入到sfbba中
insert into sfbba(你的相应字段) values select (temp_sfbba的对应字段) from temp_sfbba
var
i,j:Integer;
cbj:real;
eclApp:Variant;
xlsFileName,pjmc,pjdm,cx,cd:string;
begin
if dlgImport.Execute then
xlsFileName := dlgImport.FileName
else
Exit; try
eclApp := CreateOleObject('Excel.Application');
eclApp.WorkBooks.Open(xlsFileName);
eclApp.Visible:=False;
Except
Application.MessageBox('您的电脑中可能未安装Microsoft Excel!','错误',0);
Exit;
end; try
j := 0;
for i:=2 to eclApp.ActiveSheet.UsedRange.Rows.Count do
begin
cbj := StrToFloat(eclApp.Cells[i,6].Value);
with qryMain do
begin
pjmc := eclApp.Cells[i,1].Value;
pjdm := eclApp.Cells[i,2].Value;
cx := eclApp.Cells[i,3].Value;
cd := eclApp.Cells[i,4].Value;
Sql.Clear;
Sql.Add('select * from T_0103 where PJMC='''+pjmc+''' and PJDM='''+pjdm+''' AND CX='''+cx+''' AND CD='''+cd+'''');
ExecSQL;
Open;
if RecordCount <> 0 then
begin
adoTemp1.Append;
adoTemp1.FieldByName('RKDH').AsString := edtRKDH.Text;
adoTemp1.FieldByName('PJBM').AsString := qryMain.FieldByName('PJBM').AsString;
adoTemp1.FieldByName('SL').AsString := eclApp.Cells[i,5].Value;
adoTemp1.FieldByName('CBJ').AsString := eclApp.Cells[i,6].Value;
adoTemp1.FieldByName('LSJ').AsFloat := cbj * frmPJRK.adoMain.FieldByName('LSJXS').AsFloat;
adoTemp1.FieldByName('QXJ').AsFloat := adoTemp1.FieldByName('LSJ').AsFloat;
adoTemp1.FieldByName('PFJ').AsFloat := cbj * frmPJRK.adoMain.FieldByName('PFJXS').AsFloat;
adoTemp1.Post;
j := j + 1;
end
else
begin
Application.MessageBox(PChar('零件"' + pjmc + '"未注册!'),'提示',0);
end;
end;
end;
CountTotal;
Application.MessageBox(PChar('成功导入'+IntToStr(j)+'行'),'提示',0);
Except
Application.MessageBox('导入失败!','错误',0);
eclApp.ActiveWorkBook.close;
eclApp.Quit;
eclApp:= Unassigned;
end;
eclApp.ActiveWorkBook.close;
eclApp.Quit;
eclApp:= Unassigned;
end;
Sql.Add('select * from T_0103 where PJMC='''+pjmc+''' and PJDM='''+pjdm+''' AND CX='''+cx+''' AND CD='''+cd+'''');
ExecSQL;
Open;
if RecordCount <> 0 then
begin
adoTemp1.Append;
adoTemp1.FieldByName('RKDH').AsString := edtRKDH.Text;
adoTemp1.FieldByName('PJBM').AsString := qryMain.FieldByName('PJBM').AsString;
adoTemp1.FieldByName('SL').AsString := eclApp.Cells[i,5].Value;
adoTemp1.FieldByName('CBJ').AsString := eclApp.Cells[i,6].Value;
adoTemp1.FieldByName('LSJ').AsFloat := cbj * frmPJRK.adoMain.FieldByName('LSJXS').AsFloat;
adoTemp1.FieldByName('QXJ').AsFloat := adoTemp1.FieldByName('LSJ').AsFloat;
adoTemp1.FieldByName('PFJ').AsFloat := cbj * frmPJRK.adoMain.FieldByName('PFJXS').AsFloat;
adoTemp1.Post;
j := j + 1;
Sql.Clear;
Sql.Add('select * from T_0103 where PJMC='''+pjmc+''' and PJDM='''+pjdm+''' AND CX='''+cx+''' AND CD='''+cd+'''');
ExecSQL;
Open;
if RecordCount <> 0 then
begin
adoTemp1.Append;
adoTemp1.FieldByName('RKDH').AsString := edtRKDH.Text;
adoTemp1.FieldByName('PJBM').AsString := qryMain.FieldByName('PJBM').AsString;
adoTemp1.FieldByName('SL').AsString := eclApp.Cells[i,5].Value;
adoTemp1.FieldByName('CBJ').AsString := eclApp.Cells[i,6].Value;
adoTemp1.FieldByName('LSJ').AsFloat := cbj * frmPJRK.adoMain.FieldByName('LSJXS').AsFloat;
adoTemp1.FieldByName('QXJ').AsFloat := adoTemp1.FieldByName('LSJ').AsFloat;
adoTemp1.FieldByName('PFJ').AsFloat := cbj * frmPJRK.adoMain.FieldByName('PFJXS').AsFloat;
adoTemp1.Post;
j := j + 1;
select * from
OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="Data Source";Extended Properties="Excel 8.0;HDR=Yes;";Persist Security Info=False'')'...sheet1$--Data Source 数据源
--sheet1$ excel的标签名称
--你可以查一下excel导入到sql2000的例子
在delphi中操作:
通过数据控件以数据表的方式打开excel,获得数据集后插入到sql数据库
procedure TForm1.Button2Click(Sender: TObject);
var
i,Cel,Row: Integer;
EParam,DocuType,wkBk : OleVariant;
begin
try
self.ExcelApplication1.Connect;
except
ShowMessage('打开Excel失败');
exit;
end;
opendialog1.Execute;
self.ExcelApplication1.Visible[0] := False;
self.ExcelApplication1.Caption := 'excelToDb试验';
EParam:=EmptyParam;
DocuType:=0;
try
wkBk:=ExcelApplication1.Workbooks.Add(EParam, DocuType); wkBk:=ExcelApplication1.WorkBooks.Open(opendialog1.FileName,EmptyParam,EmptyParam,
EmptyParam,EmptyParam,EmptyParam,EmptyParam,
EmptyParam,EmptyParam,
EmptyParam,EmptyParam,DocuType,EmptyParam,DocuType);
except
begin
ExcelApplication1.Disconnect;//出现异常情况时关闭
ExcelApplication1.Quit;
showmessage('请选择EXCEL数据表格!');
exit;
end;
end;
ExcelWorkbook1.ConnectTo(ExcelApplication1.ActiveWorkbook);//ExcelWorkbook1与Eexcelapplication1建立连接
ExcelWorksheet1.ConnectTo(ExcelWorkbook1.Worksheets[1] as _Worksheet);//Excelworksheet1与Excelworkbook1建立连接
self.ADOQuery1.Close;
self.ADOQuery1.SQL.Clear;
self.ADOQuery1.SQL.Text := 'select * from TB2';
self.ADOQuery1.Open;
for i := 1 to 4 do
begin
if trim(excelworksheet1.cells.item[i+1,1])<>'' then
begin
self.ADOQuery1.Append;
self.ADOQuery1.FieldByName('Name').AsString := ExcelWorksheet1.Cells.Item [i+1,1];
self.ADOQuery1.FieldByName('Age').AsString := ExcelWorksheet1.Cells.Item [i+1,2];
self.ADOQuery1.FieldByName('Memo').AsString := ExcelWorksheet1.Cells.Item [i+1,3];
ADOQuery1.Post;
end;
end; ExcelApplication1.Disconnect;
ExcelApplication1.Quit;
showmessage('数据转换完毕!');
end;