从数据库导数据至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.Position := 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.Position := 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
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; }
|