xlsold.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;
    Edit1: TEdit;
    Label1: TLabel;
    Label2: TLabel;
    Edit2: TEdit;
    OpenDialog1: TOpenDialog;
    Label4: TLabel;
    Label6: TLabel;
    Edit5: TEdit;
    procedure Button1Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.DFM}
procedure TForm1.Button1Click(Sender: TObject);
var i,m: integer;
    prevcode,currcode:string;
begin
    if edit1.text='' then
    begin
      application.MessageBox('上级id 数据不能为空！','提示',$30);
      edit1.SetFocus ;
      exit;
    end;
    if edit2.text='' then
    begin
      application.MessageBox('起始序号不能为空！','提示',$30);
      edit2.SetFocus ;
      exit;
    end;
    if edit5.text='' then
    begin
      application.MessageBox('级别不能为空！','提示',$30);
      edit5.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 f2');
       open;
     end;
    end else exit;
    try
      whdb.StartTransaction ;
      with qu_tb do
      begin
        close;
        sql.Clear ;
        sql.Add('insert into material (MID,MNO,SSIGN,MNAME,PRICE,MJB,MMX,DW,MTYPE,BZ,YBPRICE)');
        sql.Add('values (:MID,:MNO,:SSIGN,:MNAME,:PRICE,:MJB,:MMX,:DW,:MTYPE,:BZ,:YBPRICE)');
        i:=strtoint(edit2.text);
        m:=strtoint(edit5.text);
        prevcode:='';
        while not ADOQuery1.Eof do begin
          if (trim(ADOQuery1.fieldbyname('F3').asstring)='') and (trim(ADOQuery1.fieldbyname('F4').asstring)='') then begin
                ADOQuery1.next;
                continue;
          end;
          if  trim(ADOQuery1.fieldbyname('F2').asstring)='' then begin
                currcode:=trim(ADOQuery1.fieldbyname('F3').asstring);
          end else currcode:=trim(ADOQuery1.fieldbyname('F2').asstring);
          if  prevcode=currcode then begin
                ADOQuery1.next;
                continue;
          end;
          prevcode:=currcode;
          parambyname('MID').asstring   :=Edit1.text+formatfloat('000',i);
          parambyname('MNO').asstring   :=currcode;
          if ADOQuery1.fieldbyname('F3').asstring<>'' then
            parambyname('ssign').asstring   :=ADOQuery1.fieldbyname('F3').asstring
          else
            parambyname('ssign').asstring   :=ADOQuery1.fieldbyname('F4').asstring ;
          if ADOQuery1.fieldbyname('F4').asstring<>'' then
            parambyname('mname').asstring   :=ADOQuery1.fieldbyname('F4').asstring
          else
            parambyname('mname').asstring   :=ADOQuery1.fieldbyname('F3').asstring ;
//          parambyname('price').asfloat   :=ADOQuery1.fieldbyname('F5').asfloat*0.067773;
          parambyname('price').asfloat   :=0;
          parambyname('ybprice').asfloat   :=ADOQuery1.fieldbyname('F5').asfloat;
          parambyname('bz').asstring   :=ADOQuery1.fieldbyname('F6').asstring;
          parambyname('mjb').asinteger   :=m;
          parambyname('mmx').asstring   :='1';
          parambyname('mtype').asinteger   :=0;
          parambyname('dw').asstring   :='只';
          execsql;
          i:=i+1;
          ADOQuery1.Next ;
        end;
      end;
      whdb.Commit ;
      application.MessageBox('写入数据完毕！','提示',$30);
    except
      on E:EDBEngineError do begin
        whdb.Rollback ;
        application.MessageBox(pchar(E.Message + #13 + '数据提交失败，请再试一次！'),'提示',$30);
      end;
    end;
    ADOQuery1.EnableControls ;
  with qu_tb do begin
    close;
    sql.Clear ;
    sql.Add('select * from material  order by mid');
    open;
  end;

end;

procedure TForm1.FormCreate(Sender: TObject);
begin
    whdb.Connected :=true;
    qu_tb.Active :=true;
end;

end.