如何把excel里是数据倒入到sql数据库中（在程序里实现）？
入题，请高手指教！最好有速度快的方法
[解决办法]
uses
ComObj, Grids, Db, DBTables, ADODB;
procedure load(rowCount,colCount:integer; fileName:String; var grid:TStringGrid);
//从Excel中读取数据到 Grid
var
v:variant;
i,j:integer;
begin
grid.RowCount:=rowCount;
grid.ColCount:=colCount;
v:=createoleobject( 'Excel.Application ');//创建OLE对象
try
V.workBooks.Open(fileName);
for i:=1 to rowCount do
for j:=1 to colCount do
grid.Cells[j-1,i-1]:=v.workbooks[1].sheets[1].cells[i,j];
v.workbooks[1].close;
finally
v.quit;
end
end;
procedure save(tableName:String;grid:TStringGrid);
// 将 Grid 中的数据保存到 SQL Server 数据表中
var
valuesStr:string;
i,j:integer;
begin
if not CreateTable(tableName,grid.ColCount) then
begin
showmessage( 'Error On CreateTable ');
exit;
end;
for i:=1 to grid.RowCount-1 do
begin
valuesStr:=inttostr(i)+ ', ';
for j:=0 to grid.ColCount-1 do
valuesStr:=valuesStr+Grid.Cells[j,i]+ ', ';
if not insertone(tableName,valuesStr) then
begin
showmessage( 'Error On Row( '+inttostr(i)+ ') ');
exit;
end;
end;
showmessage( '数据导入成功 ');
end;
function insertone(const tableName, ValuesStr: string): boolean;
// 插入一条记录
var
tmpstr,s:string;
p:integer;
begin
result:=true;
tmpstr:=ValuesStr;
with query1 do
begin
close;
sql.Clear;
sql.Add( 'insert into '+tableName+ ' values( ');
s:= ' ';
while tmpstr <> ' ' do
begin
p:=pos( ', ',tmpstr);
s:=s+ ' ' ' '+copy(tmpstr,1,p-1)+ ' ' ', ';
system.Delete(tmpstr,1,p);
end;
s:=copy(s,1,length(s)-1);
sql.Add(s);
sql.Add( ') ');
try
execsql;
except
result:=false;
end;
end;
end;
function CreateTable(const tableName:String; aFieldCount: integer): boolean;
// 创建表
var
tmpstr:string;
i:integer;
begin
result:=true;
tmpstr:= 'if exists (select * from sysobjects where Name= ' ' '
+tableName+ ' ' ') drop table '+tableName+ ' create table '+tableName+ '( ';
for i:=1 to aFieldCount do
tmpstr:=tmpstr+ 'F '+inttostr(i)+ ' varchar(50), ';
delete(tmpstr,length(tmpstr),1);
tmpstr:=tmpstr+ ') ';
with query1 do
begin
close;
sql.Clear;
sql.Add(tmpstr);
try
execsql;
except
result:=false;
end;
end;
end;
[解决办法]
直接用SQL语句:
SELECT *
FROM OpenDataSource( ’Microsoft.Jet.OLEDB.4.0’,
’Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0’)...xactions
c:\test.xls是EXCEL文件,XACTIONS是EXCEL文件中表名
