共計 5468 個字符，預計需要花費 14 分鐘才能閱讀完成。
這篇文章主要講解了“從 SQL Server 中導入和導出 Excel 的基本方法”，文中的講解內容簡單清晰，易于學習與理解，下面請大家跟著丸趣 TV 小編的思路慢慢深入，一起來研究和學習“從 SQL Server 中導入和導出 Excel 的基本方法”吧！
從 SQL Server 中導入 / 導出 Excel 的基本方法
/*=================== 導入 / 導出 Excel 的基本方法 ===================*/
從 Excel 文件中, 導入數據到 SQL 中, 很簡單, 直接用下面的語句:
/*===================================================================*/
– 如果接受數據導入的表已經存在
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$)
/*===================================================================*/
– 如果從 SQL 數據庫中, 導出數據到 Excel, 如果 Excel 文件已經存在, 而且已經按照要接收的數據創建好表頭, 就可以簡單的用:
insert into OPENROWSET(MICROSOFT.JET.OLEDB.4.0
,Excel 5.0;HDR=YES;DATABASE=c:test.xls,sheet1$)
select * from 表
– 如果 Excel 文件不存在, 也可以用 BCP 來導成類 Excel 的文件, 注意大小寫:
– 導出表的情況
EXEC master..xp_cmdshell bcp 數據庫名.dbo. 表名 out c:test.xls /c -/S 服務器名 /U 用戶名 -P 密碼
– 導出查詢的情況
EXEC master..xp_cmdshell bcp SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname queryout c:test.xls /c -/S 服務器名 /U 用戶名 -P 密碼
/*– 說明:
c:test.xls 為導入 / 導出的 Excel 文件名.
sheet1$ 為 Excel 文件的工作表名, 一般要加上 $ 才能正常使用.
–*/
– 上面已經說過, 用 BCP 導出的是類 Excel 文件, 其實質為文本文件,
– 要導出真正的 Excel 文件. 就用下面的方法
/*– 數據導出 EXCEL
導出表中的數據到 Excel, 包含字段名, 文件為真正的 Excel 文件
, 如果文件不存在, 將自動創建文件
, 如果表不存在, 將自動創建表
基于通用性考慮, 僅支持導出標準數據類型
– 鄒建 2003.10–*/
/*– 調用示例
p_exporttb @tbname= 地區資料,@path=c:,@fname=aa.xls
–*/
if exists (select * from dbo.sysobjects where id = object_id(N[dbo].[p_exporttb]) and OBJECTPROPERTY(id, NIsProcedure) = 1)
drop procedure [dbo].[p_exporttb]
GO
create proc p_exporttb
@tbname sysname, – 要導出的表名
@path nvarchar(1000), – 文件存放目錄
@fname nvarchar(250)= – 文件名, 默認為表名
as
declare @err int,@src nvarchar(255),@desc nvarchar(255),@out int
declare @obj int,@constr nvarchar(1000),@sql varchar(8000),@fdlist varchar(8000)
– 參數檢測
if isnull(@fname,)= set @fname=@tbname+.xls
– 檢查文件是否已經存在
if right(@path,1) set @path=@path+
create table #tb(a bit,b bit,c bit)
set @sql=@path+@fname
insert into #tb exec master..xp_fileexist @sql
– 數據庫創建語句
set @sql=@path+@fname
if exists(select 1 from #tb where a=1)
set @constr=DRIVER={Microsoft Excel Driver (*.xls)};DSN=;READONLY=FALSE
+;CREATE_DB= +;DATABASE=+@sql+
– 連接數據庫
exec @err=sp_oacreate adodb.connection,@obj out
if @err 0 goto lberr
exec @err=sp_oamethod @obj,open,null,@constr
if @err 0 goto lberr
/*– 如果覆蓋已經存在的表, 就加上下面的語句
– 創建之前先刪除表 / 如果存在的話
select @sql=drop table [+@tbname+]
exec @err=sp_oamethod @obj,execute,@out out,@sql
–*/
– 創建表的 SQL
select @sql=,@fdlist=
select @fdlist=@fdlist+,[+a.name+]
,@sql=@sql+,[+a.name+]
+case when b.name in(char,nchar,varchar,nvarchar) then
text(+cast(case when a.length 255 then 255 else a.length end as varchar)+)
when b.name in(tynyint,int,bigint,tinyint) then int
when b.name in(smalldatetime,datetime) then datetime
when b.name in(money,smallmoney) then money
else b.name end
FROM syscolumns a left join systypes b on a.xtype=b.xusertype
where b.name not in(image,text,uniqueidentifier,sql_variant,ntext,varbinary,binary,timestamp)
and object_id(@tbname)=id
select @sql=create table [+@tbname
+](+substring(@sql,2,8000)+)
,@fdlist=substring(@fdlist,2,8000)
exec @err=sp_oamethod @obj,execute,@out out,@sql
if @err 0 goto lberr
exec @err=sp_oadestroy @obj
– 導入數據
set @sql=openrowset(MICROSOFT.JET.OLEDB.4.0,Excel 5.0;HDR=YES
;DATABASE=+@path+@fname+,[+@tbname+$])
exec(insert into) select from )
return
lberr:
exec sp_oageterrorinfo 0,@src out,@desc out
lbexit:
select cast(@err as varbinary(4)) as 錯誤號
,@src as 錯誤源,@desc as 錯誤描述
select @sql,@constr,@fdlist
go
– 上面是導表的, 下面是導查詢語句的.
/*– 數據導出 EXCEL
導出查詢中的數據到 Excel, 包含字段名, 文件為真正的 Excel 文件
, 如果文件不存在, 將自動創建文件
, 如果表不存在, 將自動創建表
基于通用性考慮, 僅支持導出標準數據類型
– 鄒建 2003.10–*/
/*– 調用示例
p_exporttb @sqlstr=select * from 地區資料
,@path=c:,@fname=aa.xls,@sheetname= 地區資料
–*/
if exists (select * from dbo.sysobjects where id = object_id(N[dbo].[p_exporttb]) and OBJECTPROPERTY(id, NIsProcedure) = 1)
drop procedure [dbo].[p_exporttb]
GO
create proc p_exporttb
@sqlstr varchar(8000), – 查詢語句, 如果查詢語句中使用了 order by , 請加上 top 100 percent
@path nvarchar(1000), – 文件存放目錄
@fname nvarchar(250), – 文件名
@sheetname varchar(250)= – 要創建的工作表名, 默認為文件名
as
declare @err int,@src nvarchar(255),@desc nvarchar(255),@out int
declare @obj int,@constr nvarchar(1000),@sql varchar(8000),@fdlist varchar(8000)
– 參數檢測
if isnull(@fname,)= set @fname=temp.xls
if isnull(@sheetname,)= set @sheetname=replace(@fname,.,#)
– 檢查文件是否已經存在
if right(@path,1) set @path=@path+
create table #tb(a bit,b bit,c bit)
set @sql=@path+@fname
insert into #tb exec master..xp_fileexist @sql
– 數據庫創建語句
set @sql=@path+@fname
if exists(select 1 from #tb where a=1)
set @constr=DRIVER={Microsoft Excel Driver (*.xls)};DSN=;READONLY=FALSE
+;CREATE_DB= +;DATABASE=+@sql+
– 連接數據庫
exec @err=sp_oacreate adodb.connection,@obj out
if @err 0 goto lberr
exec @err=sp_oamethod @obj,open,null,@constr
if @err 0 goto lberr
– 創建表的 SQL
declare @tbname sysname
set @tbname=##tmp_+convert(varchar(38),newid())
set @sql=select * into [+@tbname+] from() a
exec(@sql)
select @sql=,@fdlist=
select @fdlist=@fdlist+,[+a.name+]
,@sql=@sql+,[+a.name+]
+case when b.name in(char,nchar,varchar,nvarchar) then
text(+cast(case when a.length 255 then 255 else a.length end as varchar)+)
when b.name in(tynyint,int,bigint,tinyint) then int
when b.name in(smalldatetime,datetime) then datetime
when b.name in(money,smallmoney) then money
else b.name end
FROM tempdb..syscolumns a left join tempdb..systypes b on a.xtype=b.xusertype
where b.name not in(image,text,uniqueidentifier,sql_variant,ntext,varbinary,binary,timestamp)
and a.id=(select id from tempdb..sysobjects where)
select @sql=create table [+@sheetname
+](+substring(@sql,2,8000)+)
,@fdlist=substring(@fdlist,2,8000)
exec @err=sp_oamethod @obj,execute,@out out,@sql
if @err 0 goto lberr
exec @err=sp_oadestroy @obj
– 導入數據
set @sql=openrowset(MICROSOFT.JET.
感謝各位的閱讀，以上就是“從 SQL Server 中導入和導出 Excel 的基本方法”的內容了，經過本文的學習后，相信大家對從 SQL Server 中導入和導出 Excel 的基本方法這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是丸趣 TV，丸趣 TV 小編將為大家推送更多相關知識點的文章，歡迎關注！
