请求给一个好用的excel导入SQL Server的例子，急，在线等！

Question

请求给一个好用的excel导入SQL Server的例子，希望各位慷慨解囊

Answer 1

unit Unit1;interfaceuses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls,Comobj, DB, DBTables, ADODB, ComCtrls;type
  TForm1 = class(TForm)
    Button1: TButton;
    ADOConnection1: TADOConnection;
    ADOQuery1: TADOQuery;
    OpenDialog1: TOpenDialog;
    ADOQuery2: TADOQuery;
    Label2: TLabel;
    Edit1: TEdit;
    Label1: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    Label5: TLabel;
    procedure Button1Click(Sender: TObject);
     Procedure ExportToDb;
  private
    { Private declarations }
  public
    { Public declarations }
  end;var
  Form1: TForm1;implementation{$R *.dfm}  Procedure TForm1.ExportToDb;//从excel导入到资料库
  var
     ProgressBar3,ProgressBar4:tProgressBar;
    excelapp1,sheet1:variant;
    i:integer;
    sqlstr,col1,col2:string;
    col3:real;
    j,z:integer;
  begin
     try
      excelapp1:=CreateOleObject('excel.application');
      if OpenDialog1.Execute then
      begin
        excelapp1.workbooks.open(OpenDialog1.filename);      end;
      ProgressBar3:=tProgressBar.Create(self);
      ProgressBar4:=tProgressBar.Create(self);
      ProgressBar3.Parent:=self;
      ProgressBar4.Parent:=self;
      ProgressBar3.Top:=125;
      ProgressBar3.Width:=150;
      ProgressBar3.Left:=122;
      ProgressBar4.Top:=170;
      ProgressBar4.Left:=122;
      ProgressBar4.Width:=150;
      sheet1:=excelapp1.workbooks[1].worksheets[1];
      j:=0;
      z:=2;
      //获得文件本生的数据；
       for z:=2 to 65500 do
        begin
         if trim(sheet1.cells[z,1])<>'' then
             begin
             j:=j+1;
             label4.Font.Color:=clred;
             label4.Caption:='获得数据：            获得中....      ';
             Application.ProcessMessages;
             end else
             begin
             break;
             end;
         end;
       label4.Font.Color:=clWindowText;
       label4.Caption:='获得数据：           成功！      ';
       ProgressBar3.Min:=0;
       ProgressBar3.max:=100;//strtoint(edit1.Text);
       ProgressBar3.Stepby(100 div j);
       //ProgressBar3.Stepby(100 div strtoint(edit1.Text));
       //ProgressBar3.Smooth:=true;
      //判断每一条记录是否都有该学生
      for i:=2 to 65500 do
      begin
         if trim(sheet1.cells[i,1])<>'' then
             begin
                    ADOQuery2.Active:=false;
                    ADOQuery2.Parameters.ParamByName('xh').Value:=trim(sheet1.cells[i,1]);
                    ADOQuery2.Active:=true;
                    if  ADOQuery2.RecordCount <>1 then
                        begin
                         showmessage('数据有错，有部分学生没有在库中！');
                         excelapp1.workbooks.close;
                         excelapp1.quit;
                         excelapp1:=unassigned;
                         ProgressBar4.Free;
                         ProgressBar3.free;                         exit;
                        end;
                  label1.Font.Color:=clred;
                  //ProgressBar3.Stepby(100 div j);
                  ProgressBar3.StepIt;                  label5.Caption:=floattostr(i/j);
                  Application.ProcessMessages;//此句使得label和其他控件可以显示出来
             end else
             begin
             break;
             end;
        
      end;
       ProgressBar3.Position:=0;
      label1.Font.Color:=clWindowText;
       ProgressBar4.Min:=0;
       ProgressBar4.max:=i;
       //ProgressBar4.Stepby(100 div i);
      for j:=2 to i do
      begin
         if trim(sheet1.cells[j,1])<>'' then
             begin
                    //插入数据
                    ADOQuery1.Active:=false;
                    ADOQuery1.Parameters.ParamByName('xh').Value:=trim(sheet1.cells[j,1]);
                    ADOQuery1.Parameters.ParamByName('zl').Value:='01';
                    ADOQuery1.Parameters.ParamByName('je').Value:=sheet1.cells[j,3];
                    ADOQuery1.ExecSQL;
                    //ProgressBar4.Stepby(100 div i);//进度条
                    ProgressBar4.StepIt;
                    label3.Font.Color:=clred;
                    Application.ProcessMessages;//此句使得label和其他控件可以显示出来
             end else 
             begin
             break;             end;
       //ProgressBar4.Position:=0;
      end;
      ProgressBar4.Position:=0;
      label1.Font.Color:=clWindowText;
      label3.Font.Color:=clWindowText ;
      showmessage('数据倒入完毕,共有'''+inttostr(j-2)+'''人!');
      excelapp1.workbooks.close;
      excelapp1.quit;
      excelapp1:=unassigned;
      ProgressBar4.Free;
      ProgressBar3.free;    except
      excelapp1.workbooks.close;
      excelapp1.quit;
      excelapp1:=unassigned;
      ProgressBar4.Free;
      ProgressBar3.free;
      
    end;
 end;procedure TForm1.Button1Click(Sender: TObject);
begin
ExportToDb;
end;end.

Answer 2

这个市我这几天刚刚编好的。数据在导入之前有先判断的。希望各位高手指点一下，还有我的那个processbar一直不能按百分比来显示。不知谁能帮助一下。谢谢啦

Answer 3

用OpenDataSource，sql Server中的函数。

Answer 4

How to import data from Excel to SQL Server:http://support.microsoft.com/kb/321686

Answer 5

--如果接受数据导入的表已经存在
insert into 表 select * from 
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;DATABASE=c:\test.xls',sheet1$)--如果导入数据并生成表
select * into 表 from 
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;DATABASE=c:\test.xls',sheet1$)