如何将excel的数据导入到SQL Server？

用ADOConnection操作，这样最快：
procedure TfrmEhlib.btnFromExcelClick(Sender: TObject);
var
  tName: TStrings;
begin
  if not FileExists(ExtractFilePath(Application.ExeName) + 'store.xls') then Exit;
  tName := TStringList.Create;
  acStore.GetTableNames(tName, false);
  if tName.IndexOf('sExcel') >= 0 then
    acStore.Execute('drop table sExcel');
  acStore.Execute('select * into sExcel from [Excel 8.0; Database=' + ExtractFilePath(Application.ExeName)+ 'store.xls' + '].[sheet1$]');
  MessageBox(self.Handle, 'Excel已成功导入表sExcel', '提示', mb_IconInformation + mb_Ok);
  tName.Free;
end;

SQLServer从Excel取，这个用ADOQuery就可以了，试一下：
select * into sExcel
from OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="d:\test.xls";Extended Properties="Excel 5.0;HDR=Yes;";Persist Security Info=False')...sheet1$

你自几编写一段代码
给个例了
将excel表的数据传到表中
procedure TFormDataDisk.BitBtn2Click(Sender: TObject);
var FileName,SQLText,DepartNameText,DepartID:string;
    xlsfile:TFileStream;
    month,m:integer;
    huicaiNum,juejinNum,LiancaiNum,shengchanNum,QitaNum,danhaoNum:integer;
    row,column,pozit:integer;
    value:variant;
begin
   DriveItem.Enabled :=False;
   FolderItem.Enabled :=False;
   FileItem.Enabled :=False;
   BitBtn2.Enabled :=False;
   FileName:=PathEdit.Text+'\'+trim(XlsName.Text);
   ExcelApp.Connect;
   try
       ExcelApp.Workbooks.Open(FileName,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,0);
   except
      showmessage('警告：文件路径不正确，文件不存在！');
      ExcelApp.Quit;
      ExcelApp.Destroy;
      exit;
   end;
try
   Excelbook.ConnectTo(ExcelApp.Workbooks[1]);
   Excelsheet.ConnectTo(ExcelApp.worksheets[1] as _worksheet);
   FormHit:=TFormHit.Create(Application);
   FormHit.Label1.Caption :='数据正从磁盘导入数据库中，请稍后........';
   FormHit.Label2.Caption :='数据正从磁盘导入数据库中，请稍后........';
   FormHit.Show;
   FormHit.Update;
   FormHit.Refresh;
   huicaiNum:=ExcelSheet.Cells.Item[1,1];
   juejinNum:=ExcelSheet.Cells.Item[1,2];
   liancaiNum:=ExcelSheet.Cells.Item[1,3];
   QitaNum:=ExcelSheet.Cells.Item[1,4];
   shengchanNum:=ExcelSheet.Cells.Item[1,5];
   DanhaoNum:=ExcelSheet.Cells.Item[1,6];
   month:=ExcelSheet.Cells.Item[1,7];
   DepartNameText:=ExcelSheet.Cells.Item[1,8];
   DepartID:=Copy(ExcelSheet.Cells.Item[1,9],3,6);   // 导数据 //回采面数据
   pozit:=3+huicaiNum;
   row:=3;
   SQLText:='insert into bhuicai(month,QuDuiMingCheng,qh,mh,'+
           't31,t21,t121,t91,ta1,ta2,ta3,'+
            't811,t812,ta4,ta5,ta6,ta7,ta8,ta9,ta10,ta12,'+
            'ta13,ta14,ta15,ta17,ta20,ta21,ta23,ta24,ta25,'+
            'ta26,ta27,ta28,ta29,ta30,ta31,danweiname,danweiId,kuangjingname) '+
            'values(:month,:QuDuiMingCheng,:qh,:mh,:t31,:t21,'+
            ':t121,:t91,:ta1,:ta2,:ta3,:t811,:t812,:ta4,:ta5,'+
            ':ta6,:ta7,:ta8,:ta9,:ta10,:ta12,:ta13,:ta14,:ta15,'+
            ':ta17,:ta20,:ta21,:ta23,:ta24,:ta25,:ta26,:ta27,'+
            ':ta28,:ta29,:ta30,:ta31,:Danweiname,:danweiId,:kuangjingname)';
        while row<pozit do
      begin
         with DisktoDBQry do
         begin
           close;
           SQL.Clear;
           SQL.Add(SQLText);
           Parameters[0].Value:=month;
           Parameters[1].Value:=ExcelSheet.Cells.Item[row,2].value;
           Parameters[2].Value:=ExcelSheet.Cells.Item[row,3].value;
           Parameters[3].Value:=ExcelSheet.Cells.Item[row,4].value;
           Parameters[4].Value:=ExcelSheet.Cells.Item[row,5].value;
           Parameters[5].Value:=ExcelSheet.Cells.Item[row,6].value;
           Parameters[6].Value:=ExcelSheet.Cells.Item[row,7].value;
           Parameters[7].Value:=ExcelSheet.Cells.Item[row,8].value;
           Parameters[8].Value:=ExcelSheet.Cells.Item[row,9].value;
           Parameters[9].Value:=ExcelSheet.Cells.Item[row,10].value;
           Parameters[10].Value:=ExcelSheet.Cells.Item[row,11].value;
           Parameters[11].Value:=ExcelSheet.Cells.Item[row,12].value;
           Parameters[12].Value:=ExcelSheet.Cells.Item[row,13].value;
           Parameters[13].Value:=ExcelSheet.Cells.Item[row,14].value;
           Parameters[14].Value:=ExcelSheet.Cells.Item[row,15].value;
           Parameters[15].Value:=ExcelSheet.Cells.Item[row,16].value;
           Parameters[16].Value:=ExcelSheet.Cells.Item[row,17].value;
           Parameters[17].Value:=ExcelSheet.Cells.Item[row,18].value;
           Parameters[18].Value:=ExcelSheet.Cells.Item[row,19].value;
           Parameters[19].Value:=ExcelSheet.Cells.Item[row,20].value;
           parameters[20].Value:=ExcelSheet.Cells.Item[row,21].value;
           parameters[21].Value:=ExcelSheet.Cells.Item[row,22].value;
           parameters[22].Value:=ExcelSheet.Cells.Item[row,23].value;
           parameters[23].Value:=ExcelSheet.Cells.Item[row,24].value;
           parameters[24].Value:=ExcelSheet.Cells.Item[row,25].value;
           parameters[25].Value:=ExcelSheet.Cells.Item[row,26].value;
           parameters[26].value:=ExcelSheet.Cells.Item[row,27].value;
           parameters[27].Value:=ExcelSheet.Cells.Item[row,28].value;
           parameters[28].Value:=ExcelSheet.Cells.Item[row,29].value;
           parameters[29].Value:=ExcelSheet.Cells.Item[row,30].value;
           parameters[30].Value:=ExcelSheet.Cells.Item[row,31].value;
           parameters[31].Value:=ExcelSheet.Cells.Item[row,32].value;
           parameters[32].Value:=ExcelSheet.Cells.Item[row,33].value;
           parameters[33].Value:=ExcelSheet.Cells.Item[row,34].value;
           parameters[34].Value:=ExcelSheet.Cells.Item[row,35].value;
           parameters[35].Value:=ExcelSheet.Cells.Item[row,36].value;
           parameters[36].Value:=DepartNameText;
           parameters[37].Value:=DepartId;
           Parameters[38].Value:=Departnametext;
           try
              DisktoDBQry.ExecSQL;
           except
              DisktoDBQry.Cancel;
           end;
         end;
         row:=row+1;
      end;
    FormHit.Free;
    //Excelsheet.SaveAs(FileName);
    //Excelapp.Save(FileName,1);
    ExcelApp.Quit;
    ExcelApp.Destroy;
    showmessage('数据导入成功！');
except
    showmessage('数据导入失败！');
end;

// 个通过， 'doc' 的数据库中为image 类型
// 不管是WORD,EXCLE,还中JPG的都能用,exe 娄然也成。。
// 注意打开文件时一定要先定位（即要先写sql 查询确定有个再打开），要不然会出错，还有就是临时文件的删除。。
// delphi 7+sql2000通过。。unit Unit1;interfaceuses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, DB, ADODB, StdCtrls, shellapi,Buttons, OleCtnrs;
type
  TForm1 = class(TForm)
    ADOConnection1: TADOConnection;
    Button1: TButton;
    Button2: TButton;
    OpenDialog1: TOpenDialog;
    ADOQuery1: TADOQuery;
    OleContainer1: TOleContainer;
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;var
  Form1: TForm1;
   s:string;
implementation{$R *.dfm}procedure TForm1.Button1Click(Sender: TObject);
begin
if OpenDialog1.Execute then
  begin
    if not ADOQuery1.Active then ADOQuery1.Open;
    ADOQuery1.Append;
    s :=ExtractFileName(OpenDialog1.FileName);
    (ADOQuery1.FieldByName('doc') as TBlobField).LoadFromFile(OpenDialog1.filename);
     ADOQuery1.Post;end;
end;
procedure TForm1.Button2Click(Sender: TObject);
begin
(ADOQuery1.FieldByName('doc') as TBlobField).savetoFile(ExtractFileDir(Application.ExeName)+'\'+s); //  olecontainer1.CreateObjectFromFile(ExtractFileDir(Application.ExeName)+'\'+s,false);
//  olecontainer1.DoVerb(1);  shellexecute(handle,nil,pchar(s),nil,nil,sw_shownormal);
   end;end.

调试易

如何将excel的数据导入到SQL Server？

解决方案 »