我刚开始用DEPHI,老板让用dephi实现excel导入SQLserver,请高人看一下我写的程序出错之处,给我一个样板最好.
unit Unitx;interfaceuses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, DB, ADODB, Buttons, DBTables;type
  TForm1 = class(TForm)
    ADOQuery1: TADOQuery;
    Edit1: TEdit;
    Button1: TButton;
    Edit2: TEdit;
    SpeedButton1: TSpeedButton;
    OpenDialog1: TOpenDialog;
    ADOQuery2: TADOQuery;
    Label1: TLabel;
    Button2: TButton;
    Query1: TQuery;
   
    procedure Button1Click(Sender: TObject);
    procedure SpeedButton1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;var
  Form1: TForm1;implementation{$R *.dfm}procedure TForm1.Button1Click(Sender: TObject);
VAR
  i,j:integer;
   ExcelFileName:STRING;
   s_sql:string;begin
  ExcelFileName:=edit2.Text;
  ADOQUERY1.ConnectionString:= 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source='
        + ExcelFileName + ';Extended Properties=Excel 8.0;Persist Security Info=False';with adoquery1 do
  begin
   adoquery1.Close;
   adoquery1.sql.Clear;
   adoquery1.sql.Add('select * from [Sheet1$]');
   prepared:=true;
   adoquery1.open;
   end;
    edit1.Text:=inttostr(adoquery1.RecordCount);
   adoquery1.Close;
   s_sql:='select * from student';
   adoquery2.close;
   adoquery2.sql.add(s_sql);
   adoquery2.Open;   for j:=1 to adoquery1.RecordCount do
    begin
      adoquery2.Append;
       for i:=0 to adoquery1.FieldCount do
        begin
          adoquery2.Fields[0].AsVariant:=adoquery1.Fields[0].AsVariant;
        end;
   adoquery2.Post;
      adoquery1.Next;
      application.messagebox('导入成功!','',0);   end;end;procedure TForm1.SpeedButton1Click(Sender: TObject);
begin
 if opendialog1.Execute then
   edit2.Text:=opendialog1.FileName
end;procedure TForm1.Button2Click(Sender: TObject);
begin
close;
end;end.

解决方案 »

  1.   

    //在sqlserver中写一个存储过程,直接调用即可
    Create proc procname
     @path varchar(800)
    as
     Select * into 新表名 
      from OPENROWSET('MICROSOFT.JET.OLEDB.4.0' ,'Excel 8.0;HDR=YES;   
       DATABASE='+@path+', Sheet1$)) as t
    go
      

  2.   

    var
      s1:string;
    begin
      screen.Cursor:=crSQLWait;
      s1:='insert into table_bus '+
          ' (BS_NO,BS_TYPE,bs_whos,bs_mode,bs_seat,bs_soalseat,bs_drseat,bs_oiltype,bs_buycard,bs_unit,BS_BDATE,bs_edate) '+
          ' SELECT 车号,dbo.uf_GetFullNameCode(车型名称),dbo.uf_GetFullNameCode(所属单位),dbo.uf_GetFullNameCode(厂牌型号),核定座位,售票座位,驾驶室前排座位,dbo.uf_GetFullNameCode(燃料名称),购置证号,本外单位,启用日期,dbo.uf_ConvertNullDate(终止时间) '+
          ' FROM OpenDataSource(''Microsoft.Jet.OLEDB.4.0'',''Data Source="'+trim(LabeledEdit1.text)+'";Extended Properties=Excel 8.0;Persist Security Info=False'')...[Sheet1$] '+
          ' where 车号 not in(select bs_no from table_bus) ';
      try
        DataModule1.ADOConnection1.Execute(s1);
      finally
        screen.Cursor:=crArrow;
      end;
    end;adoconnection连接sql数据库,OpenDataSource取excel数据,就是上面各位的方法procedure TForm1.Button3Click(Sender: TObject);
    var
    oe:variant;
    n1,str:string;
    j:integer;
    begin
      if opendialog1.Execute then
      opendialog1.DefaultExt:='xls';
      str:=opendialog1.FileName;
      oe:=CreateOleObject('Excel.Application');
      oe.visible:=true;
      oe.workbooks.open(str);
      j:=2;  //假定标题在第一行
       try
    with adoQuery2 do
       begin
        n1:='select * from ##tbl_cardgrant1 ';
        close;
        sql.Clear;
        sql.Add(n1);
        while oe.worksheets['查询结果1'].cells[j,1].text<>'' do
        begin
            for i:=1 to adoquery1.fieldcount do
                Parameters.Items[i-1].Value:=oe.worksheets['查询结果1'].cells[j,1].text;
                memo2.Text:=n1;
                execsql;
                j:=j+1;
        end;
    oe.visible:=false;
        showmessage('导入完毕');
       end;
       except
         oe.visible:=false;
         showmessage('数据库错误或有重复编号,请检查');
       end;
    end;  
    ExcelApp: Variant;
    excelapp:= createoleobject('Excel.Application')
    ExcelApp.Cells[i,1].Value
     jbno    := trim(ExcelApp.Cells[i,3].Value);
              empname := trim(excelapp.cells[i,2].value);
    sql.Add('insert into qj_runtmp(empno,empname,jbno,startdate,enddate,starttime,endtime,starttime2,endtime2,qjreson,createdate,createuser,flag)');
              sql.Add('values(:empno,:empname,:jbno,:startdate,:enddate,:starttime,:endtime,:starttime2,:endtime2,:qjreson,:createdate,:createuser,:flag)');
              ADO_qj.Parameters.ParamByName('empno').Value     := trim(empno);
              ado_qj.Parameters.ParamByName('empname').Value   := trim(empname);
              ADO_qj.Parameters.ParamByName('jbno').Value      := trim(jbno);procedure TForm1.Button1Click(Sender: TObject);
    begin
      ADOConnection1.Connected:=false;
      ADOConnection1.ConnectionString:='Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\db1.mdb;Persist Security Info=False';
      ADOConnection1.Execute('select * into a FROM [excel 8.0;database=C:\1.xls].[sheet1$]');
      ADOConnection1.Connected:=true;
    end;
      

  3.   

    这样的导入对EXCEL的数据要求非常严格,有点问题就会出错,或者EXCEL里明明有数据导入到SQL里都是NULL值或者空字符实践证明,这样的程序应用并不大,我都是建议客户手工导入,或者由服务人员导入
      

  4.   

    谢谢两位高人指点,在你们的提示下,我已完成任务:
    完整代码如下:
    unit Unit1;interfaceuses
      Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
      Dialogs, DB, ADODB, StdCtrls, Buttons;type
      TForm1 = class(TForm)
        Button1: TButton;
        ADOConnection1: TADOConnection;
        Edit1: TEdit;
        Label1: TLabel;
        SpeedButton1: TSpeedButton;
        OpenDialog1: TOpenDialog;
        Button2: TButton;
        Edit2: TEdit;
        Label2: TLabel;
        procedure Button1Click(Sender: TObject);
        procedure SpeedButton1Click(Sender: TObject);
        procedure Button2Click(Sender: TObject);
      private
        { Private declarations }
      public
        { Public declarations }
      end;var
      Form1: TForm1;implementation{$R *.dfm}procedure TForm1.Button1Click(Sender: TObject);
      var
      path:string;
      N_TNAME:string;
      begin
         path:=trim(edit1.Text);
         N_TNAME:=TRIM(edit2.text);     adoconnection1.Execute('SELECT * into '+N_TNAME+' FROM OPENdatasource(''Microsoft.Jet.OLEDB.4.0'',''Data Source="'+path+'";User ID=Admin;Password=;Extended properties=Excel 5.0'')...sheet1$');
         SHOWMESSAGE('导入成功');end;procedure TForm1.SpeedButton1Click(Sender: TObject);
    begin
         if opendialog1.Execute then
          edit1.Text:=opendialog1.FileName;
    end;procedure TForm1.Button2Click(Sender: TObject);
    begin
    CLOSE;
    end;end.
      

  5.   

    OpenDataSource是可以的,先在isqlw里试一下,看看行不行,有没有语法问题。
    另外,以后一定要把Delphi打对,别写成Dephi