excel表导入数据库代码: uses :comobj,db; Procedure TForm1.Excel_2_db(str :string); var eclApp,WorkBook :variant ; xlsFileName :string; a_FiledCount:integer; //数据库表中的列数 b_filedCount:integer; //excel 文件中的 列数 b_row :integer; // excel 文件的行熟 i,j :integer; a_flag :boolean; begin Form1.OpenDialog1.Title :='Excel文件 导入到数据库'+str+'表'; Form1.OpenDialog1.InitialDir :=ExtractFilePath(Application.ExeName ); if (Form1.OpenDialog1.Execute ) then xlsFileName :=ExtractFileName(Form1.OpenDialog1.FileName) else exit; try eclApp := CreateOleObject('Excel.Application'); WorkBook :=CreateOleObject('Excel.Sheet'); except showmessage('您系统未安装MS-EXCEL'); exit; end; try workBook :=eclApp.WorkBooks.add ; eclApp.workBooks.open(Form1.OpenDialog1.FileName ); except on EOleException do begin WorkBook.close; eclApp.quit; eclApp:=Unassigned; exit; end; end; eclApp.visible :=false; try //try ..finally try //try ..except With Data_Mod.DataModule1.kcinfo_Tab do begin close ; TableName :=str; active :=true; a_FiledCount :=FieldCount; end; b_filedCount :=eclApp.ActiveSheet.UsedRange.columns.Count;//返回excel 表中的列数 b_row :=eclApp.activesheet.UsedRange.rows.count; //返回excel 表中的行数 if (a_FiledCount <>b_FiledCount) //当数据 表和导入的excel表中的列数不一样,说明导入的excel文件不是正确的 then begin showmessage('您选择导入的excel文件错误'+#13+#10+'请您重新选择'); WorkBook.close; eclApp.quit; eclApp:=Unassigned; exit; end else begin //列数正确,但是还要继续判断每列的字段名是否一致 for i :=1 to b_filedCount do begin //showmessage(eclApp.activesheet.cells.item[1,i].value); //showmessage(DataMod.ADO_basic.Fields.Fields[i-1].FieldName ); if eclApp.activesheet.cells.item[2,i].value<>Data_Mod.DataModule1.kcinfo_Tab.Fields[i-1].FieldName //判断字段名是否相等 //if eclApp.activesheet.cells.item[1,i].value<>DataMod.ADO_basic.Fields.Fields[i-1].FieldName //判断中文title.caption 是否相等 then begin showmessage('您选择导入的excel文件错误'+#13+#10+'请您重新选择'); WorkBook.close; eclApp.quit; eclApp:=Unassigned; Data_Mod.DataModule1.kcinfo_Tab.Close ; exit; end; end; //for i:=..... end; //end with else for i :=3 to b_row do //行 begin a_flag :=Data_Mod.DataModule1.kcinfo_Tab.Locate(eclApp.activesheet.cells.item[2,1],eclApp.activesheet.cells.item[i,1],[loCaseInsensitive]); if (a_flag =true) then begin showmessage('该记录已经存在'); Data_Mod.DataModule1.kcinfo_Tab.Next ; continue; end; With Data_Mod.DataModule1.kcinfo_Tab do begin close ; TableName :=str; active :=true; Append; end; For j :=1 to b_filedCount do //列 begin //开始导入数据库 //showmessage(eclApp.activesheet.cells.item[1,j]);//.Value); ////showmessage(eclApp.activesheet.cells.item[i,j].value); //showmessage(eclApp.activesheet.cells[i,j].value); Data_Mod.DataModule1.kcinfo_Tab.FieldByName(eclApp.activesheet.cells.item[2,j]).Value :=eclApp.activesheet.cells[i,j].value; end ; //end with For j :=1 to b_filedCount do Data_Mod.DataModule1.kcinfo_Tab.Post ; Data_Mod.DataModule1.kcinfo_Tab.Refresh ; end; showmessage('导入数据成功'); except WorkBook.close; eclApp.quit; eclApp:=Unassigned; Data_Mod.DataModule1.kcinfo_Tab.Close ; end; //end try except finally //操作错误,退出 WorkBook.close; eclApp.quit; eclApp:=Unassigned; Data_Mod.DataModule1.kcinfo_Tab.Close ; end; end;
导出为excel表: procedure TForm3.Excel4Click(Sender: TObject); //将联合查询的结构转为excel表 var xlsFilename :string; eclApp,WorkBook :variant ; a_filedNo,i,j :integer; begin a_filedNo :=Form3.DBGrid4.FieldCount ; xlsFileName :='关于学生成绩基本信息.xls'; try eclApp :=CreateOleObject('Excel.Application'); WorkBook :=CreateOleObject('Excel.Sheet'); except showmessage('您的系统没有安装MS EXCEL'); exit; end; try WorkBook :=eclApp.workBooks.add ; for i :=1 to a_FiledNo do //转化字段名; begin //eclApp.cells(1,i) :=Form3.DBGrid4.Columns[i-1].Title.caption ; eclApp.cells(1,i) :=Form3.DBGrid4.Fields[i-1].FieldName ; end; Form3.DBGrid4.DataSource.DataSet.First ; for i :=1 to Form3.a_recno do //Form3.a_recno begin for j :=1 to a_filedNo do //转化一个记录 begin eclApp.cells(i+1,j) :=Form3.DbGrid4.Fields[j-1].Value ; end; Form3.DBGrid4.DataSource.DataSet.Next ; end; try WorkBook.saveas(ExtractFilePath(Application.ExeName )+xlsFileName); WorkBook.close; showmessage('保存EXECL文件成功,路径为:'+ExtractFilePath(Application.ExeName )+xlsFileName); except showmessage('保存文件出错'); end; except showmessage('不能正确操作EXECL文件,可能该文件已经被其他程序占用或系统错误'); WorkBook.close; eclApp.quit; eclApp :=Unassigned; end; end;
uses :comobj,db;
Procedure TForm1.Excel_2_db(str :string);
var eclApp,WorkBook :variant ;
xlsFileName :string;
a_FiledCount:integer; //数据库表中的列数
b_filedCount:integer; //excel 文件中的 列数
b_row :integer; // excel 文件的行熟
i,j :integer;
a_flag :boolean;
begin
Form1.OpenDialog1.Title :='Excel文件 导入到数据库'+str+'表';
Form1.OpenDialog1.InitialDir :=ExtractFilePath(Application.ExeName );
if (Form1.OpenDialog1.Execute )
then xlsFileName :=ExtractFileName(Form1.OpenDialog1.FileName)
else exit; try
eclApp := CreateOleObject('Excel.Application');
WorkBook :=CreateOleObject('Excel.Sheet');
except
showmessage('您系统未安装MS-EXCEL');
exit;
end;
try
workBook :=eclApp.WorkBooks.add ;
eclApp.workBooks.open(Form1.OpenDialog1.FileName );
except
on EOleException do
begin
WorkBook.close;
eclApp.quit;
eclApp:=Unassigned;
exit;
end;
end;
eclApp.visible :=false;
try //try ..finally
try //try ..except
With Data_Mod.DataModule1.kcinfo_Tab do
begin
close ;
TableName :=str;
active :=true;
a_FiledCount :=FieldCount; end; b_filedCount :=eclApp.ActiveSheet.UsedRange.columns.Count;//返回excel 表中的列数
b_row :=eclApp.activesheet.UsedRange.rows.count; //返回excel 表中的行数 if (a_FiledCount <>b_FiledCount) //当数据 表和导入的excel表中的列数不一样,说明导入的excel文件不是正确的
then begin
showmessage('您选择导入的excel文件错误'+#13+#10+'请您重新选择');
WorkBook.close;
eclApp.quit;
eclApp:=Unassigned;
exit;
end
else begin //列数正确,但是还要继续判断每列的字段名是否一致
for i :=1 to b_filedCount do
begin
//showmessage(eclApp.activesheet.cells.item[1,i].value);
//showmessage(DataMod.ADO_basic.Fields.Fields[i-1].FieldName ); if eclApp.activesheet.cells.item[2,i].value<>Data_Mod.DataModule1.kcinfo_Tab.Fields[i-1].FieldName //判断字段名是否相等
//if eclApp.activesheet.cells.item[1,i].value<>DataMod.ADO_basic.Fields.Fields[i-1].FieldName //判断中文title.caption 是否相等
then begin
showmessage('您选择导入的excel文件错误'+#13+#10+'请您重新选择');
WorkBook.close;
eclApp.quit;
eclApp:=Unassigned;
Data_Mod.DataModule1.kcinfo_Tab.Close ;
exit;
end;
end; //for i:=.....
end; //end with else for i :=3 to b_row do //行
begin
a_flag :=Data_Mod.DataModule1.kcinfo_Tab.Locate(eclApp.activesheet.cells.item[2,1],eclApp.activesheet.cells.item[i,1],[loCaseInsensitive]);
if (a_flag =true)
then begin
showmessage('该记录已经存在');
Data_Mod.DataModule1.kcinfo_Tab.Next ;
continue;
end; With Data_Mod.DataModule1.kcinfo_Tab do
begin
close ;
TableName :=str;
active :=true;
Append;
end;
For j :=1 to b_filedCount do //列
begin //开始导入数据库
//showmessage(eclApp.activesheet.cells.item[1,j]);//.Value);
////showmessage(eclApp.activesheet.cells.item[i,j].value);
//showmessage(eclApp.activesheet.cells[i,j].value); Data_Mod.DataModule1.kcinfo_Tab.FieldByName(eclApp.activesheet.cells.item[2,j]).Value :=eclApp.activesheet.cells[i,j].value; end ; //end with For j :=1 to b_filedCount do
Data_Mod.DataModule1.kcinfo_Tab.Post ;
Data_Mod.DataModule1.kcinfo_Tab.Refresh ;
end;
showmessage('导入数据成功');
except
WorkBook.close;
eclApp.quit;
eclApp:=Unassigned;
Data_Mod.DataModule1.kcinfo_Tab.Close ;
end; //end try except
finally //操作错误,退出
WorkBook.close;
eclApp.quit;
eclApp:=Unassigned;
Data_Mod.DataModule1.kcinfo_Tab.Close ;
end;
end;
procedure TForm3.Excel4Click(Sender: TObject); //将联合查询的结构转为excel表
var xlsFilename :string;
eclApp,WorkBook :variant ;
a_filedNo,i,j :integer;
begin
a_filedNo :=Form3.DBGrid4.FieldCount ;
xlsFileName :='关于学生成绩基本信息.xls'; try
eclApp :=CreateOleObject('Excel.Application');
WorkBook :=CreateOleObject('Excel.Sheet');
except
showmessage('您的系统没有安装MS EXCEL');
exit;
end; try
WorkBook :=eclApp.workBooks.add ;
for i :=1 to a_FiledNo do //转化字段名;
begin
//eclApp.cells(1,i) :=Form3.DBGrid4.Columns[i-1].Title.caption ;
eclApp.cells(1,i) :=Form3.DBGrid4.Fields[i-1].FieldName ;
end; Form3.DBGrid4.DataSource.DataSet.First ;
for i :=1 to Form3.a_recno do //Form3.a_recno begin
for j :=1 to a_filedNo do //转化一个记录
begin
eclApp.cells(i+1,j) :=Form3.DbGrid4.Fields[j-1].Value ;
end;
Form3.DBGrid4.DataSource.DataSet.Next ;
end;
try
WorkBook.saveas(ExtractFilePath(Application.ExeName )+xlsFileName);
WorkBook.close;
showmessage('保存EXECL文件成功,路径为:'+ExtractFilePath(Application.ExeName )+xlsFileName);
except
showmessage('保存文件出错');
end;
except
showmessage('不能正确操作EXECL文件,可能该文件已经被其他程序占用或系统错误');
WorkBook.close;
eclApp.quit;
eclApp :=Unassigned;
end;
end;