Delphi读取excel数据

从数据库导数据至excel 容易，但要是想把excel 里的数据通过delphi写的程式导入数据库就没那么简单了，并且网上讲的都不全面，没有几个完整可行的方案，以下是我收集的个人认为比较可行的方案，不敢独享，故拿出来供大家学习参考之用!

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

form2.show;

form2.ProgressBar1. 0;

form2.ProgressBar1.Max := 65535;

V.workBooks.Open(fileName);

//for i:=1 to rowCount do

for i:=1 to 65535 do

for j:=1 to colCount do

//if grid.Cells[j-1,i-1] = '' then break;

form2.ProgressBar1. i;

grid.Cells[j-1,i-1]:=v.workbooks[1].sheets[1].cells[i,j];

v.workbooks[1].close;

finally

v.quit;

form2.close;

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 '

+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;

}