EXCEL怎样导入导出数据库

-- 导入Excel到SQL数据库的方法
SELECT * INTO XLImport6
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
                    'Data Source=C:/abc.xls;
                    Extended Properties=Excel 8.0')       //[Sheet1$]

SELECT * INTO XLImport7
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                'Excel 8.0;Database=C:/abc.xls', [Sheet1$])

SELECT * INTO XLImport8
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                'Excel 8.0;Database=C:/abc.xls',
                'SELECT * FROM [Sheet1$]'


SQL导出到Excel

1）导出整个表：
use bcp, write out a csv file, :-), something like

xp_cmdshell "bcp <dbname>..<tablename> out c:/file.csv -Usa -P<password> -c"

2）导出表的部分内容：
no need to use a temporary table, you can use straight sql statement like this

master..xp_cmdshell 'bcp "SELECT * FROM pubs.dbo.t1" queryout c:/t1.csv -c -t, -Usa -P[password] -S[server name]'



[+RED]EXCEL怎样导入导出数据库[+black]

//导出数据到excel
procedure CopyDbDataToExcel(Target: TDbgrid);
var
    iCount, jCount: Integer;
    XLApp: Variant;
    Sheet: Variant;
begin
   Screen.Cursor := crHourGlass;
   if not VarIsEmpty(XLApp) then
   begin
      XLApp.DisplayAlerts := False;
      XLApp.Quit;
      VarClear(XLApp);
   end;
//通过ole创建Excel对象
try
  XLApp := CreateOleObject('Excel.Application');
except
  Screen.Cursor := crDefault;
  Exit;
end;
  XLApp.WorkBooks.Add[XLWBatWorksheet];
  XLApp.WorkBooks[1].WorkSheets[1].Name := '对帐工作薄';
  Sheet := XLApp.Workbooks[1].WorkSheets['对帐工作薄'];
  if not Target.DataSource.DataSet.Active then
  begin
    Screen.Cursor := crDefault;
    Exit;
  end;
    Target.DataSource.DataSet.first;
  for iCount := 0 to Target.Columns.Count - 1 do
  begin
     Sheet.cells[1, iCount + 1] := Target.Columns.Items[iCount].Title.Caption;
  end;
  jCount := 1;
  while not Target.DataSource.DataSet.Eof do
  begin
     for iCount := 0 to Target.Columns.Count - 1 do
     begin
        Sheet.cells[jCount + 1, iCount + 1] := Target.Columns.Items[iCount].Field.AsString;
     end;
     Inc(jCount);
     Target.DataSource.DataSet.Next;
  end;
     XlApp.Visible := True;
     Screen.Cursor := crDefault;
end;

procedure TfrmMain.Button1Click(Sender: TObject);
var i,j:integer;
    opendialog1:Topendialog;
    s0,s1,s2:string;
begin
   try
        opendialog1:=Topendialog.Create(self);
        opendialog1.InitialDir:=ExtractFileDir(paramstr(0));//文件的打存放初始路径
     if opendialog1.Execute then
     begin
        Try
            ExcelApplication1.Connect;//EXCEL应用程序
        Except
            Messagebox(0,'Excel 可能没有安装！!','提示！',mb_Ok);
            exit;
        End;
            ExcelApplication1.Visible[0]:=false;
            ExcelApplication1.Caption:='Excel Application';
        try
            excelapplication1.Workbooks.Open(opendialog1.FileName,
             null,null,null,null,null,null,null,null,null,null,null,null,0);//打开指定的EXCEL 文件
        except
        begin
            ExcelApplication1.Disconnect;//出现异常情况时关闭
            ExcelApplication1.Quit;showmessage('请选择EXCEL电子表格!');
            exit;
        end;
        end;

        ExcelWorkbook1.ConnectTo(ExcelApplication1.Workbooks[1]);
        //ExcelWorkbook1与Eexcelapplication1建立连接
        ExcelWorksheet1.ConnectTo(ExcelWorkbook1.Worksheets[1] as _Worksheet);
       //Excelworksheet1与Excelworkbook1建立连接
        //开始从EXCEL中取数，取完数后关闭EXCEL
        for i:=1 to 10000 do//最大取值10000
        begin
        if trim(excelworksheet1.cells.item[i+1,1])<>'' then
        begin
           s0:= excelworksheet1.cells.item[i+1,1];
           s1:= excelworksheet1.cells.item[i+1,2];
           s2:= excelworksheet1.cells.item[i+1,3];
            with qry do
            begin
               close;
               sql.Clear;
               sql.add('insert into xsz(PZNO,PNO,TCount,inday)values('+
                ''''+s0+''''+','+''''+s1+''''+','+''''+s2+''''+','+''''+datetostr(date)+''')');
               ExecSQL;
            end;
        end
        end;
         ExcelApplication1.Disconnect;
         ExcelApplication1.Quit;
   end;

   with qryxsz do
   begin
      sql.Clear;
      sql.Add('select * from xsz order by PZNO,PNO,TCount,inday');
      open;
   end;
   for j:=0 to   grdxsz.Columns.Count do
   grdxsz.Columns[j].Width:=64;//缩小宽度
   messagebox(0,'文件已经导入数据库！','完成！',mb_ok);
except
   ExcelApplication1.Disconnect;
   ExcelApplication1.Quit;
   exit;
  // messagebox(0,'文件已经导入数据库失败！','完成！',mb_iconError+mb_ok);
end;
end;

try
       if qryxsz.RecordCount<1 then exit;
       CopyDbDataToExcel(Grdxsz);
    except
     //  messagebox(0,'请安装EXCEL软件，再使用本功能。','提示',mb_ok);
       exit;
    end;

这是我在一个对帐系统中用到的将两张excel表中的数据到如数据库进行相应处理后再导出excel.