Excel To SqlServer

王朝delphi·作者佚名  2006-01-09
窄屏简体版  字體: |||超大  

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;

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
© 2005- 王朝網路 版權所有  導航