实现数据从EXCEL文件导入到SQLSERVER数据表中,可以数据字典XT_SHUJUBZD数据表中定义各种导入规则,适应各种导入要求。定义的规则越简单,导入过程速度会越快。
以下是本人自己编写的完整程序源代码:
unit ExcelImportToSql;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs,comobj, StdCtrls, DB, ADODB, ExcelXP, OleServer,registry, Grids,
DBGrids,shellapi;
type
TForm1 = class(TForm)
OpenDialog1: TOpenDialog;
ADOQuery1: TADOQuery;
ExcelApplication1: TExcelApplication;
ExcelWorksheet1: TExcelWorksheet;
ExcelWorkbook1: TExcelWorkbook;
Button2: TButton;
ADOQuery2: TADOQuery;
ADOConnection1: TADOConnection;
ADOQuery3: TADOQuery;
Label1: TLabel;
Edit1: TEdit;
CheckBox2: TCheckBox;
CheckBox3: TCheckBox;
Edit2: TEdit;
Label2: TLabel;
Button3: TButton;
RadioButton1: TRadioButton;
RadioButton2: TRadioButton;
Label3: TLabel;
Memo1: TMemo;
procedure Button2Click(Sender: TObject);
procedure FormShow(Sender: TObject);
procedure Button3Click(Sender: TObject);
procedure CheckBox3Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;
var
Form1: TForm1;
implementation
{$R *.dfm}
procedure TForm1.Button2Click(Sender: TObject);
const
BeginRow = 2; //要导入数据的开始行,倒退一行为各栏位标题行
BeginCol = 1; //要导入数据的开始列。
var
reg:TRegistry;
BeginUsedRow,BeginUsedCol:integer;
//iusedcellcount:integer;
irow,icol,irowcount,icolcount:integer;
strcelldata:string;
celldata:variant;
ShuJubBM,KeyFields,SaveFile,RecGUID:string;
FuGaiDaoRu:boolean;
KeyValues:array of variant;
ChongFuTJ,YNSave,YNWanZhengDR:boolean;
i,j,ChongFuTJ_N:integer;
DaoRuSJ:TDatetime;
RecGUIDValue,DaoRuRID:Variant;
DaoRuYH:string;
DataCheck:array[0..255,0..1] of string;
begin
ShuJubBM:=edit1.Text;
FuGaiDaoRu:=radiobutton1.Checked;
YNWanZhengDR:=checkbox2.Checked;
YNSave:=checkbox3.Checked;
// ShuJubBM:='XT_SHUJUBZD'; //接收导入的数据表
// FuGaiDaoRu:=true; //覆盖方式导入true,添加方式导入false
// YNWanZhengDR:=true; //是否保证excel数据完整导入,添加方式导入出现重复就不导入,EXCEL中各栏位数据填写错误就不导入
// YNSave:=true; //是否保存导入结果,导入的数据存在重复或填写错误时,自动设置为TRUE
SaveFile:=''; //保存导入结果的excel文件名
DaoRuYH:='admin'; //导入操作的用户名
//DaoRuSJ:=now; //所有导入数据确认为同一时间,接收导入的数据表中需有DaoRuSJ,DaoRuRID字段,方便对导入数据的记录和删除处理
//DaoRuRID:=''; //记录导入的操作用户ID
reg:=nil; //用于判断是否安装EXCEL
KeyFields:=''; //存储用于判断数据重复的多个关键字段
RecGUID:=''; //存储数据表记录行ID字段名
if (BeginRow<2) or (BeginCol<1) then
begin
messagebox(self.Handle,'要导入数据的开始行不能小于2,开始列不能小于1','提示',MB_ICONINFORMATION+MB_OK+MB_DEFBUTTON1);
exit;
end;
try
reg:=TRegistry.Create;
reg.RootKey:=HKEY_CLASSES_ROOT;
if not reg.OpenKey('Excel.Application\CLSID',false) then
begin
messagebox(self.Handle,'Excel应用软件还没安装,安装后再导入','提示',MB_ICONINFORMATION+MB_OK+MB_DEFBUTTON1);
exit;
end;
finally
reg.CloseKey;
reg.Free;
end;
with OpenDialog1 do
begin
Title := '选择要导入的excel文件';
Filter := '