xls.pas

xls2sql 将Microsoft的Excel文件导入MSSQL中

unit xls;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  DBTables, Grids, DBGrids, Db, ADODB, StdCtrls;

type
  TForm1 = class(TForm)
    ADOConnection1: TADOConnection;
    ADOQuery1: TADOQuery;
    DataSource1: TDataSource;
    DBGrid1: TDBGrid;
    whdb: TDatabase;
    qu_tb: TQuery;
    DataSource2: TDataSource;
    DBGrid2: TDBGrid;
    Button1: TButton;
    OpenDialog1: TOpenDialog;
    Button2: TButton;
    Label2: TLabel;
    Edit1: TEdit;
    tb_tran: TQuery;
    procedure Button1Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure Edit1Exit(Sender: TObject);
    procedure Edit1Change(Sender: TObject);
    procedure FormShow(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;
  db_username,db_pass:string;
  fstr:string;
implementation

{$R *.DFM}
procedure TForm1.Button1Click(Sender: TObject);
var i,j: integer;
    currcode:string;
begin
    if edit1.text='' then
    begin
      application.MessageBox('pno 数据不能为空！','提示',$30);
      edit1.SetFocus ;
      exit;
    end;
    if OpenDialog1.Execute then begin
     ADOConnection1.ConnectionString:='DBQ='+OpenDialog1.FileName+';DefaultDir=c:\;Driver={Microsoft Excel Driver (*.xls)};DriverId=790;FIL=excel 8.0;FILEDSN=C:\whdsxls.dsn;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;ReadOnly=1;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;';
     ADOConnection1.open;
     with ADOQuery1 do begin
       close;
       sql.Clear ;
       sql.Add('select * from [sheet1$] order by f3,F4');
       open;
     end;
    end else exit;
    try
      whdb.StartTransaction ;
      with qu_tb do
      begin
        close;
        sql.Clear ;
        sql.Add('insert into pdmaclmx (flno,pID,mid,sl,cllyl)');
        sql.Add('values (:flno,:pID,:mid,:sl,:cllyl)');
        i:=1;
        while not ADOQuery1.Eof do begin
          if  ADOQuery1.fieldbyname('F7').asfloat<0.0001 then begin
                ADOQuery1.next;
                continue;
          end;
          if  trim(ADOQuery1.fieldbyname('F3').asstring)='' then begin
              if  trim(ADOQuery1.fieldbyname('F5').asstring)='' then
                 currcode:=trim(ADOQuery1.fieldbyname('F6').asstring)
              else currcode:=trim(ADOQuery1.fieldbyname('F5').asstring);
          end
          else currcode:=trim(ADOQuery1.fieldbyname('F3').asstring)+trim(ADOQuery1.fieldbyname('F4').asstring);

          parambyname('flno').asinteger   :=i;
          parambyname('pID').asstring   :=trim(Edit1.text);
          parambyname('MID').asstring   :=currcode;
          parambyname('SL').asFLOAT   :=ADOQuery1.fieldbyname('F7').asfloat;
          parambyname('cllyl').asinteger   :=100;
          execsql;
          i:=i+1;
          ADOQuery1.Next ;
        end;
      end;
      whdb.Commit ;
      application.MessageBox('写入数据完毕！','提示',$30);
    except
      whdb.Rollback ;
      application.MessageBox('pno 数据重复！','提示',$30);
      application.MessageBox(pchar(trim(edit1.text)),'提示',$30);
      application.MessageBox('数据提交失败，请检查！','提示',$30);
    end;
    ADOConnection1.Close;
//  copyFile(pchar('c:\whdsxlso.xls'),pchar('c:\whdsxls.xls'),false);
  with qu_tb do begin
    close;
    sql.Clear ;
// select pno,pname from product where pmx='1' and iscomb=0 order by pno
   sql.Add('select pno,pname,pmx,iscomb from product where pmx=1 and iscomb=0 order by pno');
    open;
  end;

end;

procedure TForm1.FormCreate(Sender: TObject);
begin
//  copyFile(pchar('d:\qyxlsdat\wlwk.xls'),pchar('c:\whdsxls.xls'),false);
{  with ADOQuery1 do begin
    close;
    sql.Clear ;
    sql.Add('select * from [sheet1$]');
    open;}
//  ADOQuery1.Active :=false;
//  ADOConnection1.Connected :=false;
//  copyFile(pchar('c:\whdsxlso.xls'),pchar('c:\whdsxls.xls'),false);
    whdb.Connected :=true;
    qu_tb.Active :=true;
end;

procedure TForm1.Button2Click(Sender: TObject);
begin
  with tb_tran do begin
    try
      whdb.StartTransaction;
      close;
      sql.clear;
      sql.add('update pdmaclmx set pdmaclmx.pid=product.pid from product where pdmaclmx.pid=product.pno');
      execsql;
      sql.clear;
      sql.add('update pdmaclmx set pdmaclmx.mid=material.mid from material where pdmaclmx.mid=material.mno');
      execsql;
      whdb.commit;
      application.MessageBox('代码整理完毕！','提示',$30);
    except
      whdb.Rollback;
    end;
  end;
end;

procedure TForm1.Edit1Exit(Sender: TObject);
begin
  if trim(edit1.Text)<>'' then
  begin
    fstr:=TRIM(edit1.Text)+'*';
    qu_tb.Filter :='pno= '+QuotedStr(fstr);
  //  qu_tb.Filter :='pno= '+#39+fstr+#39;
    qu_tb.Filtered :=true;
  //  qu_tb.First;
  end;
end;

procedure TForm1.Edit1Change(Sender: TObject);
begin
  qu_tb.Filter :='';
  qu_tb.Filtered :=false;
  if trim(edit1.Text)<>'' then
  begin
    fstr:=TRIM(edit1.Text)+'*';
    qu_tb.Filter :='pno= '+QuotedStr(fstr);
  //  qu_tb.Filter :='pno= '+#39+fstr+#39;
    qu_tb.Filtered :=true;
  //  qu_tb.First;
  end;
end;

procedure TForm1.FormShow(Sender: TObject);
begin
  with qu_tb do begin
    close;
    sql.Clear ;
// select pno,pname from product where pmx='1' and iscomb=0 order by pno
   sql.Add('select pno,pname,pmx,iscomb from product where pmx=1 and iscomb=0 order by pno');
    open;
  end;
end;

end.