Delphi读取excel数据

素颜马尾好姑娘i 2021-11-29 11:28 432阅读 0赞

从数据库导数据至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;  
   \}

转载于:https://www.cnblogs.com/tecsoon/archive/2009/03/25/1421383.html