我用delphi做的程序将excel文件导入sql (用 adoquery1.FieldByName('').AsString:=ADOquery2.FieldValues[''];),当excel的单元格为空时不能导入,在查询分析器用insert into 表....select... from OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE=d:\文件。 却能够完整导入excel文件。当我在在excel文件的空值单元格里加上“‘”,永delphi就能导入,但是这样比较麻烦,谁有别的好方法。
if ADOQuery2.FieldValues['Test'] = '' then //''为空字串,也许可以用Null或Nil。
ADOQuery1.FieldByName('Test').AsString := ' ' //加入空格。
else
ADOQuery1.FieldByName('Test').AsString := ADOQuery2.FiledValues['Test'];
procedure TForm3.Button4Click(Sender: TObject);
var
a_cc: TStrings;
ExcelApp: Variant;
begin
if OpenDialog1.Execute then begin
{打开保存一次EXCEL文件,为何要保存一次呢?如果是其它程序用第三方控件导出的EXCEL文件,最好做此步,否则容发生错误}
ExcelApp := CreateOleObject( 'Excel.Application' );
ExcelApp.WorkBooks.Open(OpenDialog1.FileName);
ExcelApp.ActiveWorkBook.Save;
ExcelApp.Quit;
varclear(ExcelApp);
a_cc :=TStringList.Create;
{ADOConn是数据库连接控件ADOConnection}
ADOConnection2.ConnectionString :=
'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' + OpenDialog1.FileName + ';Extended Properties=excel 8.0;Persist Security Info=false';
//'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\admin\桌面\222\geshi2.xls;Extended Properties=Excel 8.0;Persist Security Info=False'
ADOConnection2.Connected := True;
ADOConnection2.GetTableNames(a_cc);{将EXCEL文件中的表格名单赋值于a_cc变量}
with adoquery2 do
begin
ADOquery2.Close;
ADOquery2.sql.text:='select * from ['+a_cc[0]+']';//得到EXCEL文件中的第一个表格的数据
ADOquery2.open; ADOquery2.RecordCount ;
adoquery1.Open ;
while not eof do
begin;
adoquery1.Append;
adoquery1.FieldByName('voucher_no').AsString:=ADOquery2.FieldValues['voucher_no'];
adoquery1.FieldByName('vouc_type').AsString:=ADOquery2.FieldValues['vouc_type'];
adoquery1.FieldByName('vouctypeno').AsString:=ADOquery2.FieldValues['vouctypeno'];
adoquery1.FieldByName('date_vouch').AsString:=ADOquery2.FieldValues['date_vouch'];
adoquery1.FieldByName('cnt_attach').AsString:=ADOquery2.FieldValues['cnt_attach'];
adoquery1.FieldByName('usr_prepar').AsString:=ADOquery2.FieldValues['usr_prepar'];
adoquery1.FieldByName('usr_check').AsString:=ADOquery2.FieldValues['usr_check'];
adoquery1.FieldByName('usr_post').AsString:=ADOquery2.FieldValues['usr_post'];
adoquery1.FieldByName('tot_debit').AsString:=ADOquery2.FieldValues['tot_debit'];
adoquery1.FieldByName('tot_credit').AsString:=ADOquery2.FieldValues['tot_credit'];
adoquery1.FieldByName('flag_check').AsString:=ADOquery2.FieldValues['flag_check'];
adoquery1.FieldByName('flag_post').AsString:=ADOquery2.FieldValues['flag_post'];
adoquery1.FieldByName('cnt_dtl').AsString:=ADOquery2.FieldValues['cnt_dtl'];
adoquery1.FieldByName('cnt_dj').AsString:=ADOquery2.FieldValues['cnt_dj'];
adoquery1.FieldByName('desc_djfg').AsString:=ADOquery2.FieldValues['desc_djfg'];
adoquery1.post;
next;
end;
close;
end;
ADOConnection2.Connected:=false;
a_cc.Free;
messagebox(handle,'数据处理完毕','提示',MB_OK+MB_ICONINFORMATION);
end;
end;
end.
begin
ADOquery2.Close;
ADOquery2.sql.text:='select * from ['+a_cc[0]+']';//得到EXCEL文件中的第一个表格的数据
ADOquery2.open; //ADOquery2.RecordCount ;這條語句你打算作什麼?可以不要。 adoquery1.Open ;
while not eof do
begin;
adoquery1.Append;
if FieldValues['voucher_no']=Null then
AdoQuery1.FieldByName('voucher_no').AsString := ' '
else
adoquery1.FieldByName('voucher_no').AsString:=ADOquery2.FieldValues['voucher_no'];
if FieldValues['vouc_type'] = Null then
AdoQuery1.FieldByName('vouc_type').AsString := ' '
else
adoquery1.FieldByName('vouc_type').AsString:=ADOquery2.FieldValues['vouc_type'];
//以下可以參照上面做法。 adoquery1.FieldByName('vouctypeno').AsString:=ADOquery2.FieldValues['vouctypeno'];
adoquery1.FieldByName('date_vouch').AsString:=ADOquery2.FieldValues['date_vouch'];
adoquery1.FieldByName('cnt_attach').AsString:=ADOquery2.FieldValues['cnt_attach'];
adoquery1.FieldByName('usr_prepar').AsString:=ADOquery2.FieldValues['usr_prepar'];
adoquery1.FieldByName('usr_check').AsString:=ADOquery2.FieldValues['usr_check'];
adoquery1.FieldByName('usr_post').AsString:=ADOquery2.FieldValues['usr_post'];
adoquery1.FieldByName('tot_debit').AsString:=ADOquery2.FieldValues['tot_debit'];
adoquery1.FieldByName('tot_credit').AsString:=ADOquery2.FieldValues['tot_credit'];
adoquery1.FieldByName('flag_check').AsString:=ADOquery2.FieldValues['flag_check'];
adoquery1.FieldByName('flag_post').AsString:=ADOquery2.FieldValues['flag_post'];
adoquery1.FieldByName('cnt_dtl').AsString:=ADOquery2.FieldValues['cnt_dtl'];
adoquery1.FieldByName('cnt_dj').AsString:=ADOquery2.FieldValues['cnt_dj'];
adoquery1.FieldByName('desc_djfg').AsString:=ADOquery2.FieldValues['desc_djfg'];
adoquery1.post;
next;
end;
close;
end;
請看以上紅色標記。
AdoQuery1.FieldByName('voucher_no').AsString := ''//这里加入空格的话提示不能导入
else
adoquery1.FieldByName('voucher_no').AsString:=ADOquery2.FieldValues['voucher_no'];