关于execl数据导入sql的问题
关于execl数据导入sql的问题或是execl通过delphi导入sql搜索了很多,都没有成功,有以下三种,很疑惑,到底有没有导入的方法呢?
1.procedure TForm1.Button1Click(Sender: TObject);
var
sSql, mdbpath, xlspath: string;
begin
if (trim(edit1.Text) = '') or (trim(edit2.Text) = '') then
begin
MessageBox(GetActiveWindow(), '请正确选择相关路径!', '警告', MB_OK + MB_ICONWARNING);
exit;
end;
mdbpath := trim(edit1.Text); //MDB路径
xlspath := trim(edit2.Text); //Excel路径
ADOConnection1.Connected := False;
try
ADOConnection1.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' + mdbpath + ';Persist Security Info=False';
ADOConnection1.Connected := true;
try
//删除所有的记录
adoquery1.Close;
adoquery1.SQL.Clear;
sSql :='Delete * from Classes';
adoquery1.SQL.Text := sSql;
adoquery1.Execsql;
//添加记录
adoquery1.Close;
adoquery1.SQL.Clear;
sSql :='Insert into Classes (名次, 姓名, 性别, 总分)' + ' SELECT' + ' 名次, 姓名, 性别, 总分' + ' FROM [excel 8.0;database=' + xlspath + '].[Classes$]';
adoquery1.Parameters.Clear;
adoquery1.ParamCheck := false;
adoquery1.SQL.Text := sSql;
adoquery1.Execsql;
MessageBox(GetActiveWindow(), '批量导入EXCEL成功!', '警告', MB_OK + MB_ICONWARNING);
except
MessageBox(GetActiveWindow(), '批量导入EXCEL失败!', '警告', MB_OK + MB_ICONWARNING);
end;
except
MessageBox(GetActiveWindow(), '连接ACCESS失败!', '警告', MB_OK + MB_ICONWARNING);
end;
end;
2.SELECT * INTO ww1.dbo.ee
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\ee.xls',
3.SELECT * FROM [ee$]') SELECT *
FROM OpenDataSource( ’Microsoft.Jet.OLEDB.4.0’,
’Data Source="xls文件的地址";User ID=Admin;Password=;Extended properties=Excel 5.0’)...工作区的名称