unit_importdata.pas

一个不错的源程序DELPHI开发的,功能比较好的客户管理系统

//------------------------------------------------------------
//      作者:曾庆顺
//      模块:通用数据导入窗口
//      时间：2002.09.12
//      功能介绍：1、对于txt,excel文件可以任意对应字段，导入指定的表里
//                2、待确定再加上，允许别的窗口调用，传如参数：
//                            调用这个窗体时传入的表名InTableName
//                            调用这个窗体时传入的字段名InFieldName
//                            调用这个窗体时传入的显示的字段名称InFieldLabel
//                            调用这个窗体时传入的对应的数值InFieldValue
//-----------------------------------------------------------
unit Unit_ImportData;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  StdCtrls, ExtCtrls, ComCtrls, Db, DBTables, Buttons, OleCtnrs,ComObj;

type
  TDataCode = class
    COLUMN_NAME :string;//字段名
    DATA_TYPE   :string;//字段类型
    DATA_LENGTH :string;//字段长度
    COMMENTS    :string;//字段说明
    LinkCol     :string;//连接的列数
    NULLABLE    :string;//非空标志，N为非空，Y为可以空
  end;
  TRecordLink =record
    DataCode :TDataCode;

    end;
  //用来加表名的选择下拉框
  TDataCode1 = class
    Code :string;//字段名
    end;
  TFrm_ImportData = class(TForm)
    PageControl1: TPageControl;
    TabSheet1: TTabSheet;
    GroupBox1: TGroupBox;
    Panel1: TPanel;
    Memo1: TMemo;
    Panel2: TPanel;
    Edit1: TEdit;
    Label1: TLabel;
    Button1: TButton;
    Query1: TQuery;
    Button3: TButton;
    OpenDialog1: TOpenDialog;
    Button4: TButton;
    Panel4: TPanel;
    Bevel2: TBevel;
    Bevel1: TBevel;
    Label6: TLabel;
    SpeedButton1: TSpeedButton;
    SpeedButton2: TSpeedButton;
    SpeedButton3: TSpeedButton;
    SpeedButton4: TSpeedButton;
    SpeedButton5: TSpeedButton;
    GroupBox4: TGroupBox;
    Label7: TLabel;
    Label8: TLabel;
    yhzd_list: TListBox;
    divi_edit: TEdit;
    col_edit: TEdit;
    UpDown1: TUpDown;
    GroupBox5: TGroupBox;
    xtzd_list: TListBox;
    link_list: TListBox;
    Panel5: TPanel;
    SpeedButton6: TSpeedButton;
    Label9: TLabel;
    Query2: TQuery;
    PnlPrint: TPanel;
    Label146: TLabel;
    Label147: TLabel;
    Label2: TLabel;
    CheckBox1: TCheckBox;
    Splitter1: TSplitter;
    GroupBox2: TGroupBox;
    GroupBox3: TGroupBox;
    Panel3: TPanel;
    ComboBox: TComboBox;
    Label3: TLabel;
    Memo2: TMemo;
    Button2: TButton;
    ListView1: TListView;
    procedure Button1Click(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure Button3Click(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure Panel4DragOver(Sender, Source: TObject; X, Y: Integer;
      State: TDragState; var Accept: Boolean);
    procedure FormDragOver(Sender, Source: TObject; X, Y: Integer;
      State: TDragState; var Accept: Boolean);
    procedure FormDragDrop(Sender, Source: TObject; X, Y: Integer);
    procedure Button4Click(Sender: TObject);
    procedure SpeedButton4Click(Sender: TObject);
    procedure SpeedButton5Click(Sender: TObject);
    procedure SpeedButton1Click(Sender: TObject);
    procedure SpeedButton2Click(Sender: TObject);
    procedure SpeedButton3Click(Sender: TObject);
    procedure col_editChange(Sender: TObject);
    procedure CheckBox1Click(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure Button2Click(Sender: TObject);
    procedure SpeedButton6Click(Sender: TObject);
    procedure PageControl1DragOver(Sender, Source: TObject; X, Y: Integer;
      State: TDragState; var Accept: Boolean);
    procedure PageControl1DragDrop(Sender, Source: TObject; X, Y: Integer);
    procedure GroupBox1DragOver(Sender, Source: TObject; X, Y: Integer;
      State: TDragState; var Accept: Boolean);
    procedure GroupBox2DragOver(Sender, Source: TObject; X, Y: Integer;
      State: TDragState; var Accept: Boolean);
    procedure GroupBox1DragDrop(Sender, Source: TObject; X, Y: Integer);
    procedure GroupBox2DragDrop(Sender, Source: TObject; X, Y: Integer);
  private
    { Private declarations }
    P_Divi,tablename :string;
    P_Add,P_TxtOrExcel,P_Title :boolean;
    P_LinkCount :integer;
    P_Link :array of TRecordLink;

    P_InTableName:string;
    P_InFieldName,P_InFieldLabel,P_InFieldValue :string;

    procedure SetInTableName(values:string);
    procedure SetInFieldName(values:string);
    procedure SetInFieldLabel(values:string);
    procedure SetInFieldValue(values:string);

    //释放内存
    Procedure FreeNodeListBox(ListBox:TListBox);
    //释放内存
    Procedure FreeNodeCombobox(Combobox:TCombobox);
    //构造插入语句
    Function ConstrucSql:string;
    //根据Code 得到索引号
    Function GetCodeIndex(Combobox:TCombobox;pCode:string):integer;
  public
    { Public declarations }
    // 调用这个窗体时传入的表名
    property InTableName: string read P_InTableName write SetInTableName;
    // 调用这个窗体时传入的字段名
    property InFieldName: string read P_InFieldName write SetInFieldName;
    // 调用这个窗体时传入的显示的字段名称
    property InFieldLabel: string read P_InFieldLabel write SetInFieldLabel;
    // 调用这个窗体时传入的对应的数值
    property InFieldValue: string read P_InFieldValue write SetInFieldValue;
  end;

var
  Frm_ImportData: TFrm_ImportData;

implementation

uses dmmain;

{$R *.DFM}
(*var
 WorkBook:Variant;*)
 
procedure TFrm_ImportData.Button1Click(Sender: TObject);
var
  i,j,rowcount,colcount :integer;
  eclApp,WorkBook,Sheet:Variant;
  LMsg :string;
  ListColumn :TListColumn;
  V_Bud:TListItem;
begin
  If Not OpenDialog1.Execute Then
  begin
    edit1.text :='';
    memo1.Clear;
    ListView1.Columns.Clear;
    ListView1.Items.Clear;
    ListView1.Refresh;
    exit;
  end
    else
      edit1.text :=trim(OpenDialog1.FileName);
  If not FileExists(edit1.text) Then
  begin
    messagedlg('文件名不存在！',mtInformation	,[mbok],0);
    exit;
  end;
  case OpenDialog1.FilterIndex of
    1://txt
      begin
        P_TxtOrExcel :=false;
        memo1.Visible :=true;
        ListView1.Visible :=false;
        if trim(edit1.text)<>'' then
        begin
          memo1.Clear;
          PnlPrint.Visible :=true;
          PnlPrint.Refresh;
          try
            memo1.Lines.LoadFromFile(edit1.text);
          except
            messagedlg('载入文件出错！',mtInformation	,[mbok],0);
            PnlPrint.Visible :=false;
            exit;
          end;
          PnlPrint.Visible :=false;
        end;
      end;
    2:begin
        if trim(edit1.text)<>'' then
        begin
          P_TxtOrExcel :=true;
          memo1.Visible :=false;
          ListView1.Visible :=true;
          ListView1.Columns.Clear;
          ListView1.Items.Clear;
          ListView1.Refresh;
          PnlPrint.Visible :=true;
          PnlPrint.Refresh;
          //**************************************************
           Try
              eclApp:=CreateOleObject('Excel.Application');
           Except
              Application.MessageBox('您的机器里未安装Microsoft Excel。','提示',MB_ICONINFORMATION);
              PnlPrint.Visible :=false;
              Exit;
           End;
           try
             eclApp.WorkBooks.Open(Edit1.text);
             WorkBook :=eclApp.WorkBooks[1];
             Sheet :=WorkBook.WorkSheets[1];
           except
              LMsg:='不能正确操作Excel文件。可能是该文件已被其他程序打开, 或系统错误。';
              Application.MessageBox(Pchar(LMsg),'提示',MB_ICONINFORMATION);
              PnlPrint.Visible :=false;
              try
                for i:=1 to eclApp.WorkBooks.count  do
                eclApp.WorkBooks[i].Close;
              finally
                eclApp.Quit;
              end;
           end;
           rowcount :=eclApp.ActiveSheet.UsedRange.Rows.Count;
           colcount :=eclApp.ActiveSheet.UsedRange.Columns.Count;
           //加列名
           ListView1.Columns.Clear;
           if P_Title then
           begin
             for i:=1 to colcount do
             begin
               ListColumn :=ListView1.Columns.add;
               ListColumn.Caption :=trim(Sheet.cells[1,i]);
               ListColumn.Width :=100;
             end;
           end
             else
               begin
                 for i:=1 to colcount do
                 begin
                   ListColumn :=ListView1.Columns.add;
                   ListColumn.Caption :='第'+inttostr(i)+'列';
                 end;
               end;
           //一行一行地导入
           try
               for i:=1 to rowcount do
               begin
                   //跳过字段说明行
                   if P_Title then
                   if i=1 then
                   begin
                     continue;
                   end;
                   //一行的每格存到数组里
                   for j:=1 to colcount  do
                   begin
                     if j=1 then
                     begin
                       V_Bud:=ListView1.Items.Add;
                       V_Bud.Caption :=trim(Sheet.cells[i,j]);
                       continue;
                     end;
                     V_Bud.SubItems.Add(trim(Sheet.cells[i,j]));
                   end;
               end;
          except
            messagedlg('载入文件出错！',mtInformation	,[mbok],0);
            PnlPrint.Visible :=false;
            exit;
          end;
          //******************************************************************
          try
            for i:=1 to eclApp.WorkBooks.count  do
            eclApp.WorkBooks[i].Close;
          finally
            eclApp.Quit;
          end;
          PnlPrint.Visible :=false;
        end;
      end;
  end;
end;

procedure TFrm_ImportData.FormDestroy(Sender: TObject);
begin
  FreeNodeListBox(xtzd_list);
  FreeNodeListBox(link_list);
  FreeNodeCombobox(Combobox);
end;

procedure TFrm_ImportData.Button3Click(Sender: TObject);
var
  i,j,k,m,rowcount,colcount:integer;
  str,StrField,StrValues :string;
  Values :Array[0..100] Of String;//用动态数组不好做
  LBz,LBz1 :boolean;
  V_Bud:TListItem;
begin

  LBz :=false;
  LBz1 :=false;
  if P_Add =false then
  begin
    messagedlg('请选择连接！',mtInformation	,[mbok],0);
    exit;
  end;
  for i:=0 to 99 do
  Values[i] :='';
  PnlPrint.Visible :=true;
  PnlPrint.Refresh;
  Memo2.Clear;

  if P_TxtOrExcel=false then
  begin
     //txt
     //做导入
     for i:=0 to memo1.Lines.Count-1 do
     begin
         //跳过字段说明行
         if P_Title then
         if i=0 then
         begin
           continue;
         end;
         //一行一行地导入
         Str:=memo1.Lines.Strings[i];
         //先把一行的字串存到一个数组里
         j:=pos(P_divi,str);
         if j<=0 then
         begin
           messagedlg('分隔符有错！',mtInformation	,[mbok],0);
           PnlPrint.Visible :=false;
           button3.Enabled :=false;
           exit;
         end;
         k:=0;
         while j>0 do
         begin
           Values[k] :=copy(str,1,j-1);
           Str:=copy(str,j+1,length(str));
           j:=pos(P_divi,str);
           k :=k+1;
           //得到最后一个数值
           if j<=0 then
           Values[k] :=trim(Str);
         end;
         //判断连接数大于实际有的列数
         if P_LinkCount >k+1 then
         begin
           memo2.Lines.add('第'+inttostr(i+1)+'行'+'第'+inttostr(k+1)+'列'
              +',数值为空，而字段为非空！');
           continue;
         end;
         //*****用这个数组进行插入表 ,要判断数据的合法性*******
         query1.close;
         Str :='insert into '+tablename+' (';
         StrField :='';
         StrValues :='';
         for k:=0 to P_LinkCount -1 do
         begin
           StrField :=StrField+P_Link[k].DataCode.COLUMN_NAME+',';
           StrValues :=StrValues+':'+P_Link[k].DataCode.COLUMN_NAME+',';
         end;
         Str :=Str+StrField;
         Str :=copy(Str,1,length(Str)-1)+')';
         Str :=Str+' values('+StrValues;