excel个表问题,在线等

Question

怎样把excel表导入到sql server数据库中,请各位大虾帮帮我啊

Answer 1

QL语句导入导出大全*******  导出到excel
EXEC master..xp_cmdshell 'bcp SettleDB.dbo.shanghu out c:\temp1.xls -c -q -S"GNETDATA/GNETDATA" -U"sa" -P""'/***********  导入Excel
SELECT * 
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions
SELECT cast(cast(科目编号 as numeric(10,2)) as nvarchar(255))+'　' 转换后的别名
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions/** 导入文本文件
EXEC master..xp_cmdshell 'bcp "dbname..tablename" in c:\DT.txt -c -Sservername -Usa -Ppassword'/** 导出文本文件
EXEC master..xp_cmdshell 'bcp "dbname..tablename" out c:\DT.txt -c -Sservername -Usa -Ppassword'
或
EXEC master..xp_cmdshell 'bcp "Select * from dbname..tablename" queryout c:\DT.txt -c -Sservername -Usa -Ppassword'导出到TXT文本，用逗号分开
exec master..xp_cmdshell 'bcp "库名..表名" out "d:\tt.txt" -c -t ,-U sa -P password'
BULK INSERT 库名..表名
FROM 'c:\test.txt'
WITH (
    FIELDTERMINATOR = ';',
    ROWTERMINATOR = '\n'
)

Answer 2

ADO指向SQLSERVER：
ADOQUERY：
select * into TABLENAME from [SHEET1$] in "d:\TEMP\123.xls" "excel 8.0;"

Answer 3

procedure TF_Main.ToolButtonExcelClick(Sender: TObject);
var
  OpenDialog:TOpenDialog;
  iCount:integer;
begin
  if ShowIYN('是否真的要导入Excel数据,要生的数据将被删除?')<>IDCANCEL then
  try
    OpenDialog:=TOpenDialog.Create(Self);
    OpenDialog.DefaultExt := 'xls';
    OpenDialog.Filter := 'Microsoft Excel 文件  (*.xls)|*.xls';
    OpenDialog.Execute;
    if OpenDialog.FileName<>'' then
    try
      Screen.Cursor:=crSqlWait;
      ADOConExcel.Close;
      ADOConExcel.ConnectionString:='Provider=Microsoft.Jet.OLEDB.4.0;Data
Source='
                                        +OpenDialog.FileName+';Extended
Properties=Excel 8.0;Persist Security Info=False';
      ADOConExcel.Open;
      with ADOQryExcel do
      begin
        Close;
        Sql.Clear;
        Sql.Add('SELECT 材料编号,百平米用量,预算价,最低价 FROM
[Sheet1$]');
        Prepared:=true;
        Open;
      end;
      iCount:=ADOQryExcel.RecordCount;
      ADOConExcel.BeginTrans;
      with F_DataModule.ADOCom do
      begin
        CommandText:='DELETE FROM MatPriCal WHERE
cYearMonth='+''''+FormatDateTime('yyyymm',DateTimePicker.DateTime)+'''';
        Execute;
      end;
      ADOQryExcel.First;
      while not ADOQryExcel.Eof do
      begin
        with F_DataModule.ADOCom do
        begin
          CommandText:='INSERT INTO MatPriCal
VALUES('+''''+FormatDateTime('yyyymm',DateTimePicker.DateTime)+''''+','+''
''+Trim(ADOQryExcel.FieldByName('材料编号
').AsString)+''''+','+Trim(ADOQryExcel.FieldByName('百平米用量
').AsString)+','+Trim(ADOQryExcel.FieldByName('预算价
').AsString)+','+Trim(ADOQryExcel.FieldByName('最低价').AsString)+')';
          Execute;
        end;
        ADOQryExcel.Next;
        F_Main.StatusBar.Refresh;
        F_Main.StatusBar.Panels[2].text:='当前数
:'+IntToStr(ADOQryExcel.RecNo)+'(共'+IntToStr(iCount)+'条)';
      end;
      ADOConExcel.CommitTrans;
      ShowWIE(2,'导入成功!');
    except
      ADOConExcel.RollbackTrans;
      ShowWIE(2,'导入失败!');
    end;
  finally
    ADOQryExcel.Close;
    ADOConExcel.Close;
    OpenDialog.Free;
    Screen.Cursor:=crDefault;
    F_Main.StatusBar.Panels[2].Text:='';
  end;
end;或用sql 自带的openrowset,参考程序
if ShowIYN('是否真的要导入Excel数据?')<>IDCANCEL then
  try
    ADOConInfoExcel.Open;
    OpenDialog:=TOpenDialog.Create(Self);
    OpenDialog.DefaultExt := 'xls';
    OpenDialog.Filter := 'Microsoft Excel 文件  (*.xls)|*.xls';
    OpenDialog.Execute;
    if OpenDialog.FileName<>'' then
    try
      with ADOQryInfoExcel do
      begin
        Close;
        Sql.Clear;
        Sql.Add(' SELECT TOP 1 * FROM OPENROWSET');
        Sql.Add('(');
        Sql.Add('''MSDASQL.1''');
        Sql.Add(',');
        Sql.Add('''DRIVER=Microsoft Excel Driver (*.xls);');
        Sql.Add('DBQ='+OpenDialog.FileName+'''');
        Sql.Add(',');
        Sql.Add('''SELECT * FROM [Sheet1$]''');
        Sql.Add(')');
        Open;
      end;
      if Copy(ADOQryInfoExcel.Fields[2].AsString,1,4)<>mUser.UserArea
then
      begin
        ShowWIE(1,'不能导入其它地区数据!');
        ADOQryInfoExcel.Close;
        ADOConInfoExcel.Close;
        exit;
      end;
      //ADOConInfoExcel.BeginTrans;
      with ADOComInfoExcel do
      begin
        CommandText:=' UPDATE C';
        CommandText:=CommandText+' SET C.MNo=A.类别编码,C.MName=A.材料名称
,C.spec=A.材料规格,C.unit=A.材料单位,C.Factory=A.生产厂家,C.TradeMark=A.厂
牌,C.Price=A.材料单价,C.pingyin=A.材料拼音,C.hs=A.换算系数 FROM
InfoPrice'+mUser.UserArea;
        CommandText:=CommandText+' C,  OPENROWSET';
        CommandText:=CommandText+'(';
        CommandText:=CommandText+'''MSDASQL.1''';
        CommandText:=CommandText+',';
        CommandText:=CommandText+'''DRIVER=Microsoft Excel Driver
(*.xls);';
        CommandText:=CommandText+'DBQ='+OpenDialog.FileName+'''';
        CommandText:=CommandText+',';
        CommandText:=CommandText+'''SELECT * FROM [Sheet1$]''';
        CommandText:=CommandText+') A WHERE C.RDate=A.发布日期 AND
C.RNO=A.发布编号 AND C.Area=A.地区编码';
        Execute;
        CommandText:=' INSERT INTO InfoPrice'+mUser.UserArea;CommandText:=CommandText+'(RDate,RNO,Area,MNo,MName,spec,unit,Factory,Trad
eMark,Price,pingyin,hs) ';
        CommandText:=CommandText+' SELECT 发布日期,发布编号,地区编码,类别
编码,材料名称,材料规格,材料单位,生产厂家,厂牌,材料单价,材料拼音,换算系数
FROM  OPENROWSET';
        CommandText:=CommandText+'(';
        CommandText:=CommandText+'''MSDASQL.1''';
        CommandText:=CommandText+',';
        CommandText:=CommandText+'''DRIVER=Microsoft Excel Driver
(*.xls);';
        CommandText:=CommandText+'DBQ='+OpenDialog.FileName+'''';
        CommandText:=CommandText+',';
        CommandText:=CommandText+'''SELECT * FROM [Sheet1$]''';
        CommandText:=CommandText+') A';
        CommandText:=CommandText+' WHERE NOT EXISTS(SELECT 1 FROM
InfoPrice'+mUser.UserArea+' C';
        CommandText:=CommandText+' WHERE C.RDate=A.发布日期 AND C.RNO=A.发
布编号 AND C.Area=A.地区编码)';
        Execute;
      end;
      //ADOConInfoExcel.CommitTrans;
      RefreshGridData;
      ShowWIE(2,'导入成功!');
    except
      //ADOConInfoExcel.RollbackTrans;
      ShowWIE(2,'导入失败!');
    end;
  finally
    ADOQryInfoExcel.Close;
    ADOConInfoExcel.Close;
    OpenDialog.Free;
  end;