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;
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;
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文件中表名
select 班级,学号,姓名,性别 from [Excel 5.0; DATABASE=;HDR=YES;IMEX=1;].[]
通过在ACCESS里面运行该SQL把EXCEL导入到MSSQL