DELPHI实现数据从EXCEL文件导入到SQLSERVER数据表中

DELPHI 专栏收录该内容
1 篇文章 0 订阅

 

实现数据从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 := 'Excel工作簿(*.xls;*.xlsx)|*.xls;*.xlsx';
    Execute;
    if FileName='' then
    begin
      messagebox(self.Handle,'已取消选择Excel文件','提示',MB_ICONINFORMATION+MB_OK+MB_DEFBUTTON1);
      exit;
    end;
  end;

  SaveFile:=extractfilepath(opendialog1.FileName)+changefileext(extractfilename(opendialog1.FileName),'')+formatdatetime('yyyymmddhhnnss',now)+extractfileext(opendialog1.FileName);
  edit2.Text:=savefile;

  try
    ExcelApplication1.Connect;
    ExcelApplication1.Visible[0]:=false;
    ExcelApplication1.Workbooks.Open(opendialog1.FileName,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,0);
    excelworkbook1.ConnectTo(excelapplication1.Workbooks.Item[1]);
    ExcelWorksheet1.ConnectTo(ExcelWorkbook1.Worksheets.Item[1] as _worksheet);
    excelworksheet1.Activate(0);
    //iusedcellcount:=ExcelWorksheet1.UsedRange[0].Cells.Count; //EXCEL有填写数据的单元格总数。EXCEL没有填写数据时的返回值也是:1
    BeginUsedRow:=ExcelWorksheet1.UsedRange[0].Cells.Row;   //EXCEL有填写数据的开始行。EXCEL没有填写数据时的返回值也是:1
    BeginUsedCol:=ExcelWorksheet1.UsedRange[0].Cells.Column; //EXCEL有填写数据的开始列。EXCEL没有填写数据时的返回值也是:1
    strcelldata:=excelworksheet1.UsedRange[0].Cells.Item[1,1]; //读取有填写数据的区域中第一个单元格数据。
    if strcelldata='' then
    begin
      messagebox(self.Handle,'Excel中没有数据','提示',MB_ICONINFORMATION+MB_OK+MB_DEFBUTTON1);
      exit;
    end;

    irowcount:=excelworksheet1.UsedRange[0].Rows.Count; //EXCEL文件中有填写数据的单元格的总行数。
    icolcount:=excelworksheet1.UsedRange[0].Columns.Count; //EXCEL文件中有填写数据的单元格的总列数。

    if icolcount>255 then
    begin
      messagebox(self.Handle,'Excel中栏位超出数据表所能接收的最大列数255列','提示',MB_ICONINFORMATION+MB_OK+MB_DEFBUTTON1);
      exit;
    end;

    try

      iRow := BeginRow;
      iCol := BeginCol;

      with ADOQuery1 do     //ADOQUERY1:接收从EXCEL导入数据的数据表
      begin
        close;
        sql.clear;
        sql.add('select * from '+ShuJuBBM);
        open;
      end;

      with ADOQuery2 do   //ADOQUERY2:从用户表XT_YONGHU中读取操作导入的用户ID
      begin
        close;
        sql.clear;
        sql.add('select YongHuID from XT_YongHu where YongHuM =:DaoRuYH');
        parameters.ParamByName('DaoRuYH').Value:=DaoRuYH;
        open;
        DaoRuRID:=adoquery2.FieldByName('yonghuid').Value;
      end;

      with ADOQuery2 do   //ADOQUERY2:从数据字典XT_SHUJUBZD中读取要接收导入数据的数据表字段字典:中文字段名与英文字段名对照,判断重复数据的条件,字段关联表
      begin
        close;
        sql.clear;
        sql.add('select a.* from XT_ShuJuBZD as a join XT_ShuJuB as b on a.shujubid=b.shujubid and b.shujub=:ShuJuBBM');
        parameters.ParamByName('ShuJuBBM').Value:=ShuJuBBM;
        open;
        ChongFuTJ_N:=0;
        first;
        while not eof do
        begin
          if fieldbyname('CanYuCFSJPD').AsBoolean then      //数据重复判断的字段数
            inc(ChongFuTJ_N);
          if fieldbyname('JiLuGUID').AsBoolean then         //当前字段是否为行记录的ID字段,仅有一个ID字段
            RecGuid:=adoquery2.FieldByName('ziduanywm').AsString;
          next;
        end;
      end;

      showmessage('开始导入');

      DaoRuSJ:=now;       //记录导入时间
      adoconnection1.BeginTrans;      //记录准备回滚撤消的起点

      WHILE iRow<=iRowCount Do
      begin
        keyfields:='';
        if ChongFuTJ_N>255 then
          setlength(keyvalues,255)
        else
          setlength(keyvalues,ChongFuTJ_N);//定义数据重复条件判断关键字段的动态数组元素个数

        for i := icol to icolcount do
        begin
          celldata:=excelworksheet1.UsedRange[0].Cells.Item[BeginRow-1,i];
          if adoquery2.Locate('GuanLianBZDExcelTitle',celldata,[loCaseInsensitive]) then   //关联表字段参与数据重复条件判断
          begin
            if adoquery2.FieldByName('CanYuCFSJPD').AsBoolean then   //当前字段是否参于数据重复条件判断的关键字段
            begin
              KeyFields:=KeyFields+adoquery2.FieldByName('ziduanywm').AsString+';'; //KEYFIELDS记录一个或多个关键字段名

              if not adoquery2.fieldbyname('GuanLianBZDSQLFileName').IsNull then
              begin
                celldata:=excelworksheet1.UsedRange[0].Cells.Item[irow,i];
                adoquery3.Close;
                adoquery3.SQL.Clear;
                adoquery3.SQL.LoadFromFile(adoquery2.fieldbyname('GuanLianBZDSQLFileName').AsString);
                adoquery3.parameters.ParamByName('GLBTJ').Value:=celldata;
                adoquery3.Open;
                KeyValues[i-1]:=adoquery3.FieldByName(adoquery2.FieldByName('GuanLianBZD').AsString).Value;
              end;
            end;
          end
          else
          begin
            if adoquery2.Locate('ZiDuanXSMC',celldata,[loCaseInsensitive]) then  //根据EXCEL栏位中文标题找出对应的英文字段名
            begin
              if adoquery2.FieldByName('CanYuCFSJPD').AsBoolean then   //当前字段是否参于数据重复条件判断的关键字段
              begin
                KeyFields:=KeyFields+adoquery2.FieldByName('ziduanywm').AsString+';'; //KEYFIELDS记录一个或多个关键字段名
                KeyValues[i-1]:=excelworksheet1.UsedRange[0].Cells.Item[irow,i]; //KEYVALUES数组中记录关键字段对应EXCEL中当前行相应栏位的数据
              end;
            end;
          end;
        end;

        if keyfields='' then   //数据字典中没有定义数据重复判断的关键字段
          ChongFuTJ:=false
        else
        begin
          ChongFuTJ:=true;
          delete(keyfields,length(keyfields),1); //去掉关键字段最后一个分号 ;字符
        end;

        with ADOQuery1 do
        begin

          if ChongFuTJ then
          begin
            if FuGaiDaoRu then  //覆盖方式导入
            begin
              if ChongFuTJ_N=1 then    //数据重复条件判断的关键字段只有1个,LOCATE函数不支持动态数组
              begin
                if Locate(keyfields,keyvalues[0],[loCaseInsensitive]) then
                begin
                  YNSave:=true;          //数据重复或填写错误,自动设置为需要保存导入结果,方便用户核对数据填写问题
                  excelworksheet1.Cells.Item[BeginUsedRow+iRow-1,BeginUsedCol+icolcount]:='重复数据,覆盖方式导入';
                  edit;        //重复,则更新原记录中其他栏位数据
                  fieldbyname('DaoRuSJ').AsDateTime:=DaoRuSJ;       //记录导入时间和操作用户ID
                  fieldbyname('DaoRuRID').Value:=DaoRuRID;
                end
                else
                begin
                  Append;       //否则,添加记录
                  fieldbyname('DaoRuSJ').AsDateTime:=DaoRuSJ;
                  fieldbyname('DaoRuRID').Value:=DaoRuRID;
                end;
              end
              else
              begin      //数据重复条件判断的关键字段有多个,LOCATE函数需采用动态数组
                if Locate(keyfields,vararrayof(keyvalues),[loCaseInsensitive]) then
                begin
                  YNSave:=true;
                  excelworksheet1.Cells.Item[BeginUsedRow+iRow-1,BeginUsedCol+icolcount]:='重复数据,覆盖方式导入';
                  edit;
                  fieldbyname('DaoRuSJ').AsDateTime:=DaoRuSJ;
                  fieldbyname('DaoRuRID').Value:=DaoRuRID;
                end
                else
                begin
                  Append;
                  fieldbyname('DaoRuSJ').AsDateTime:=DaoRuSJ;
                  fieldbyname('DaoRuRID').Value:=DaoRuRID;
                end;
              end;
            end
            else
            begin   //添加方式导入
              if ChongFuTJ_N=1 then
              begin
                if Locate(keyfields,keyvalues[0],[loCaseInsensitive]) then
                begin
                  YNSave:=true;
                  if YNWanZhengDR then         //整份EXCEL表数据需完整导入,则只要出现数据重复或填写错误,都会回滚撤消所有导入数据
                  begin
                    excelworksheet1.Cells.Item[BeginUsedRow+iRow-1,BeginUsedCol+icolcount]:='数据重复,整份数据不导入';
                    raise exception.Create('数据重复,整份数据不导入');
                  end
                  else
                    excelworksheet1.Cells.Item[BeginUsedRow+iRow-1,BeginUsedCol+icolcount]:='重复数据,没有导入';
                  inc(irow);
                  continue;        //出现数据重复,不导入EXCEL中当前行数据
                end
                else
                begin
                  Append;          //数据不重复,添加式导入
                  fieldbyname('DaoRuSJ').AsDateTime:=DaoRuSJ;
                  fieldbyname('DaoRuRID').Value:=DaoRuRID;
                end;
              end
              else
              begin
                if Locate(keyfields,vararrayof(keyvalues),[loCaseInsensitive]) then
                begin
                  YNSave:=true;
                  if YNWanZhengDR then
                  begin
                    excelworksheet1.Cells.Item[BeginUsedRow+iRow-1,BeginUsedCol+icolcount]:='数据重复,整份数据不导入';
                    raise exception.Create('数据重复,整份数据不导入');
                  end
                  else
                    excelworksheet1.Cells.Item[BeginUsedRow+iRow-1,BeginUsedCol+icolcount]:='重复数据,没有导入';
                  inc(irow);
                  continue;
                end
                else
                begin
                  Append;
                  fieldbyname('DaoRuSJ').AsDateTime:=DaoRuSJ;
                  fieldbyname('DaoRuRID').Value:=DaoRuRID;
                end;
              end;
            end;
          end
          else
          begin
            Append;  //数据字典中没有定义数据重复条件判断的字段,则直接添加
            fieldbyname('DaoRuSJ').AsDateTime:=DaoRuSJ;
            fieldbyname('DaoRuRID').Value:=DaoRuRID;
          end;

          if not (RecGUID='') then       //接收导入的数据表有记录行ID字段,则生成一个GUID号,每条记录行生成一次。没有这样的字段,就不生成
          begin
            if fieldbyname(recguid).IsNull then
              fieldbyname(RecGUID).Value:=CreateClassID;

            RecGUIDValue:=fieldbyname(RecGUID).Value;   //读取当前行ID值,为导入EXCEL当前行数据后,数据填写是否错误判断作准备
          end
          else
            RecGUIDValue:='';

          while icol<=icolcount do   //当前行各栏位数据依次导入
          begin
            //EXCEL模板中各栏位的标题必须在要导入数据的开始行倒退一行,即BeginRow-1,且只有一行。
            celldata:=excelworksheet1.UsedRange[0].Cells.Item[BeginRow-1,icol];

            //数据字典中字段GuanLianBZDExcelTitle定义EXCEL需转换关联表相关字段值的栏位标题
            if adoquery2.Locate('GuanLianBZDExcelTitle',celldata,[loCaseInsensitive]) then  //EXCEL中当前栏位的数据是否要转换成关联表中相关字段值
            begin
              if not adoquery2.fieldbyname('GuanLianBZDSQLFileName').IsNull then   //数据字典中字段GuanLianBZDSQLFileName定义读取关联表相关字段值的SQL复合语句的文件名及路径,SQL语句中必须带有:GLBTJ参数作为查找条件,:GLBTJ的值是EXCEL表中当前栏位填写的数据
              begin
                celldata:=excelworksheet1.UsedRange[0].Cells.Item[irow,icol];
                adoquery3.Close;
                adoquery3.SQL.Clear;
                adoquery3.SQL.LoadFromFile(adoquery2.fieldbyname('GuanLianBZDSQLFileName').AsString);
                adoquery3.parameters.ParamByName('GLBTJ').Value:=celldata;   //:GLBTJ是EXCEL中当前栏位中填写的数据
                adoquery3.Open;
                //关联表中相关字段值填写到接收导入数据表的当前字段中
                fieldbyname(adoquery2.FieldByName('ZiDuanYWM').AsString).Value:=adoquery3.FieldByName(adoquery2.FieldByName('GuanLianBZD').AsString).Value;
              end;
            end
            else
            begin
              //EXCEL模板中各栏位顺序允许被调整,没在数据字典中定义的多余列数据不会导入
              if adoquery2.Locate('ZiDuanXSMC',celldata,[loCaseInsensitive]) then  //EXCEL模板中各栏位的中文标题要与数据字典中文字段名相同,才会接收导入数据
              begin
                //记下需要做数据检验的栏位
                DataCheck[iCol,0]:= celldata;
                if not adoquery2.fieldbyname('ZiDuanDataCheckSQLFileName').IsNull then
                  DataCheck[iCol,1]:=adoquery2.fieldbyname('ZiDuanDataCheckSQLFileName').AsString;  //数据字典中字段ZiDuanDataCheckSQLFileName定义对当前栏位的数据做是否填写错误判断的SQL复合语句的文件名及路径,SQL语句中必须带有:RecGUIDValue参数作为查找条件,:RecGUIDValue的值为当前行的ID

                //导入EXCEL中各栏位数据
                celldata:=excelworksheet1.UsedRange[0].Cells.Item[irow,icol];
                fieldbyname(adoquery2.FieldByName('ZiDuanYWM').AsString).Value:=celldata;

              end;
            end;

            inc(icol);
          end;
          post; //保存导入数据
          adoquery1.Refresh;

          //对刚导入的当前行每列数据做填写是否错误的校验。SQL语句没法对读取的EXCEL各栏位数据作判断,所以只好对已导入的数据作一次列的循环检验,有点浪费机时,但也值得
          if YNWanZhengDR then
          begin
            if not (RecGUIDValue='') then   //不支持没有记录行ID的数据填写错误判断
            begin
              for I := 0 to icolcount-1 do
              begin
                if DataCheck[i,1]<>'' then
                begin
                  adoquery3.Close;
                  adoquery3.SQL.Clear;
                  adoquery3.SQL.LoadFromFile(DataCheck[i,1]);
                  adoquery3.parameters.ParamByName('RecGUIDValue').Value:=RecGUIDValue;
                  adoquery3.Open;
                  if ADOQUERY3.RecordCount>0 then
                  BEGIN
                    YNSave:=true;
                    excelworksheet1.Cells.Item[BeginUsedRow+iRow-1,BeginUsedCol+icolcount+1]:=DataCheck[i,0]+':栏位中数据填写错误';
                    RAISE EXCEPTION.Create('导入的数据存在填写错误');
                  END;
                end;
              end;
            end;
          end;

          setlength(keyvalues,0); //释放动态数组
          iCol:=BeginCol;
          inc(iRow);
        end;
      end;

      ADOQuery1.close ;
      adoquery2.Close;
      adoquery3.Close;
      adoconnection1.CommitTrans;    //提交本次所有导入的数据
      MessageBox(self.Handle, '导入成功', '提示', MB_OK +  MB_ICONInformation);

    except
      adoconnection1.RollbackTrans;   //回滚撤消本次所有导入数据
      MessageBox(self.Handle, '导入失败', '提示', MB_OK +  MB_ICONInformation);
    end;

    if YNSave then
    begin
      if fileexists(SaveFile) then
        deletefile(Savefile);
      excelworksheet1.SaveAs(SaveFile);
    end;

  finally
    if YNSave then
      if not excelworkbook1.Saved[0] then
        excelworkbook1.Saved[0]:=true;  //如果没有保存成功,则放弃保存

    excelworksheet1.Disconnect;
    excelworkbook1.Disconnect;
    ExcelApplication1.Disconnect;
    ExcelApplication1.Quit;
  end;
end;

procedure TForm1.Button3Click(Sender: TObject);
begin
  shellexecute(self.Handle,'open','excel.exe',pchar(edit2.Text),nil,sw_shownormal);
end;

procedure TForm1.CheckBox3Click(Sender: TObject);
begin
  button3.Enabled:=checkbox3.Checked;
end;

procedure TForm1.FormShow(Sender: TObject);
begin
  adoconnection1.Connected:=true;
end;

end.

  • 4
    点赞
  • 1
    评论
  • 4
    收藏
  • 打赏
    打赏
  • 扫一扫,分享海报

©️2022 CSDN 皮肤主题:大白 设计师:CSDN官方博客 返回首页

打赏作者

lv20030929

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值