本文的方法均已亲自验证
sql2005 导出成 Excel
前提：使用sql Server2005 外围应用配置器――“功能的外围应用配置器”――开启xp_cmdshell功能
@H_403_5@EXEC master..xp_cmdshell
@H_403_5@'bcp "SELECT Id,Campaign_Code,Unique_Id,CreateDate From SZF02D253.DRTV.dbo.Campaign_Source ORDER BY Id" queryout "c:\test.xls" -c -S "SZF02D253" -U "sa" -P "wayne"'
说明.
@H_403_5@以上语句最好写在同一行。xp_cmdshell 处可以换行，但是之后的后面的字符串（ ‘….’） 必须写成一行，不能进行换行，否则会出错。
@H_403_5@参数 c:\test.xls ：为导出的Excel文件名
@H_403_5@参数 SZF02D253.DRTV.dbo.Campaign_Source ：服务器名+数据库+所有者+表名
@H_403_5@参数 -S "SZF02D253" ： 服务器名，也可以用IP代替，即写成 -S "10.131.44.159"
@H_403_5@参数 -U "sa" ：sql账户名
@H_403_5@参数 -P "wayne" ：sql账户密码
@H_403_5@注意：可以自动生成Excel文件。若Excel文件不存在，则自动产生文件；若文件存在，则覆盖原来的文件。
前提 1：使用sql Server2005 外围应用配置器――“功能的外围应用配置器”――开启“xp_cmdshell” 功能
@H_403_5@EXEC master..xp_cmdshell
@H_403_5@'bcp DRTV.dbo.Campaign_Source out "c:\test3.xls" -c -S "SZF02D253" -U "sa" -P "wayne"'
说明
@H_403_5@参数 DRTV.dbo.Campaign_Source ：数据库+所有者+表名 ，在这里不能用 “服务器名+数据库+所有者+表名”。
@H_403_5@参数 c:\test3.xls ：为导出的Excel文件名。
@H_403_5@注意：可以自动生成Excel文件。若Excel文件不存在，则自动产生文件；若文件存在，则覆盖原来的文件。
@H_403_5@方法三 导出数据表到Excel（Excel已存在）
@H_403_5@前提 1：使用sql Server2005 外围应用配置器――“功能的外围应用配置器”――开启“即席远程查询”功能（即 OPENROWSET 和 OPENDATASOURCE）
@H_403_5@前提 2：Excel文件已经存在，工作表（固定名称）已经存在，要导出的列在工作表中已经设置好，工作表中列的顺序必须与sql语句中字段顺序一致。
@H_403_5@INSERT INTO OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE=c:\test4.xls',ImportData$)
@H_403_5@ SELECT Id,CreateDate
@H_403_5@ FROM SZF02D253.DRTV.dbo.Campaign_Source
@H_403_5@说明
@H_403_5@参数 c:\test4.xls ：为导出的Excel文件名
@H_403_5@参数 ImportData$ ：其中ImportData为Excel文件中工作表名，$符号必须带上，作用不祥。
@H_403_5@参数 SZF02D253.DRTV.dbo.Campaign_Source ：服务器名+数据库+所有者+表名
@H_403_5@参数 Id,CreateDate ：是要导出的列，这些列必须在 ImportData 工作表中设置好，并且字段顺序一致
@H_403_5@方法四 导出数据表到Excel（综合以上各种方法，强烈推荐）
@H_403_5@前提 1：使用sql Server2005 外围应用配置器――“功能的外围应用配置器”――开启“即席远程查询”功能（即 OPENROWSET 和 OPENDATASOURCE）
@H_403_5@前提 2：使用sql Server2005 外围应用配置器――“功能的外围应用配置器”――开启“OLE 自动化”功能
@H_403_5@前提 3：使用sql Server2005 外围应用配置器――“功能的外围应用配置器”――开启“xp_cmdshell” 功能
@H_403_5@前提 4：创建下面的存储过程 ExportExcel
@H_403_5@前提 5：调用 ExportExcel ，并提供相关参数
/*
以下情形可以正常运行
@H_403_5@1、Excel文件不存在,将自动创建文件及WorkSheet.
@H_403_5@2、Excel文件存在，WorkSheet不存在，将自动在Excel文件中新建WorkSheet.
@H_403_5@3、Excel文件存在，WorkSheet存在，且WorkSheet中定义的字段包含了所有查询结果的字段(字段先后顺序可不一致)
@H_403_5@
@H_403_5@以下情形将报错
@H_403_5@1、Excel文件存在，WorkSheet存在，但WorkSheet中未定义字段，则报错.
@H_403_5@2、Excel文件存在，WorkSheet存在，但WorkSheet中定义的字段少于查询结果字段，则报错.
@H_403_5@
@H_403_5@*/
@H_403_5@IF EXISTS ( SELECT *
@H_403_5@ FROM dbo.sysobjects
@H_403_5@ WHERE id = OBJECT_ID(N'[dbo].[ExportExcel]')
@H_403_5@ AND OBJECTPROPERTY(id,N'IsProcedure') = 1 )
@H_403_5@ DROP PROCEDURE [dbo].[ExportExcel]
@H_403_5@GO
CREATE PROC ExportExcel
@H_403_5@ @sqlstr SYSNAME,--查询语句
@H_403_5@ @path NVARCHAR(1000),--Excel文件存放目录
@H_403_5@ @fname NVARCHAR(250),--Excel文件名
@H_403_5@ @sheetname VARCHAR(250) = '' --WorkSheet工作表名,默认为文件名
@H_403_5@AS
@H_403_5@ DECLARE @err INT,@H_403_5@ @src NVARCHAR(255),@H_403_5@ @desc NVARCHAR(255),@H_403_5@ @out INT
@H_403_5@ DECLARE @obj INT,@H_403_5@ @constr NVARCHAR(1000),@H_403_5@ @sql VARCHAR(8000),@H_403_5@ @fdlist VARCHAR(8000)
@H_403_5@
@H_403_5@--输入参数检测
@H_403_5@ IF ISNULL(@fname,'') = ''
@H_403_5@ SET @fname = 'temp.xls'
@H_403_5@ IF ISNULL(@sheetname,'') = ''
@H_403_5@ SET @sheetname = REPLACE(@fname,'.','#')
@H_403_5@
@H_403_5@--检查文件是否已经存在
@H_403_5@ IF RIGHT(@path,1) <> '\'
@H_403_5@ SET @path = @path + '\'
@H_403_5@ CREATE TABLE #tb ( a BIT,b BIT,c BIT )
@H_403_5@ SET @sql = @path + @fname
@H_403_5@ INSERT INTO #tb
@H_403_5@ EXEC master..xp_fileexist @sql
@H_403_5@
@H_403_5@--数据库创建语句
@H_403_5@ SET @sql = @path + @fname
@H_403_5@ IF EXISTS ( SELECT 1
@H_403_5@ FROM #tb
@H_403_5@ WHERE a = 1 )
@H_403_5@ SET @constr = 'DRIVER={Microsoft Excel Driver (*.xls)};DSN='''';READONLY=FALSE'
@H_403_5@ + ';CREATE_DB="' + @sql + '";DBQ=' + @sql
@H_403_5@ ELSE
@H_403_5@ SET @constr = 'Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties="Excel 5.0;HDR=YES'
@H_403_5@ + ';DATABASE=' + @sql + '"'
@H_403_5@
@H_403_5@--连接数据库
@H_403_5@ EXEC @err= sp_oacreate 'adodb.connection',@obj OUT
@H_403_5@ IF @err <> 0
@H_403_5@ GOTO lberr
@H_403_5@ EXEC @err= sp_oamethod @obj,'open',NULL,@constr
@H_403_5@ IF @err <> 0
@H_403_5@ GOTO lberr
@H_403_5@
@H_403_5@-- 创建表的sql
@H_403_5@ DECLARE @tbname SYSNAME
@H_403_5@ SET @tbname = '##tmp_' + CONVERT(VARCHAR(38),NEWID())
@H_403_5@ SET @sql = 'select * into [' + @tbname + '] from(' + @sqlstr + ') a'
@H_403_5@ EXEC(@sql)
@H_403_5@ SELECT @sql = '',@H_403_5@ @fdlist = ''
@H_403_5@ SELECT @fdlist = @fdlist + ',' + a.name,@H_403_5@ @sql = @sql + ',[' + a.name + '] '
@H_403_5@ + CASE WHEN b.name IN ( 'char','nchar','varchar','nvarchar' )
@H_403_5@ THEN 'text(' + CAST(CASE WHEN a.length > 255 THEN 255
@H_403_5@ ELSE a.length
@H_403_5@ END AS VARCHAR) + ')'
@H_403_5@ WHEN b.name IN ( 'tynyint','int','bigint','tinyint' )
@H_403_5@ THEN 'int'
@H_403_5@ WHEN b.name IN ( 'smalldatetime','datetime' )
@H_403_5@ THEN 'datetime'
@H_403_5@ WHEN b.name IN ( 'money','smallmoney' ) THEN 'money'
@H_403_5@ ELSE b.name
@H_403_5@ END
@H_403_5@ FROM tempdb..syscolumns a
@H_403_5@ LEFT JOIN tempdb..systypes b ON a.xtype = b.xusertype
@H_403_5@ WHERE b.name NOT IN ( 'image','text','uniqueidentifier','sql_variant',@H_403_5@ 'ntext','varbinary','binary','timestamp' )
@H_403_5@ AND a.id = ( SELECT id
@H_403_5@ FROM tempdb..sysobjects
@H_403_5@ WHERE name = @tbname
@H_403_5@ )
@H_403_5@ SELECT @sql = 'create table [' + @sheetname + '](' + SUBSTRING(@sql,2,@H_403_5@ 8000) + ')',@H_403_5@ @fdlist = SUBSTRING(@fdlist,8000)
@H_403_5@
@H_403_5@ EXEC @err= sp_oamethod @obj,'execute',@out OUT,@sql --执行
@H_403_5@
@H_403_5@ EXEC @err= sp_oamethod @obj,'close' --关闭，若不关闭sql进程将一直占用此Excel文件。
@H_403_5@
@H_403_5@ IF @err <> 0
@H_403_5@ GOTO lberr
@H_403_5@ EXEC @err= sp_oadestroy @obj
@H_403_5@
@H_403_5@--导入数据
@H_403_5@ SET @sql = 'openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel 5.0;HDR=YES;DATABASE='
@H_403_5@ + @path + @fname + ''',[' + @sheetname + '$])'
@H_403_5@ EXEC('insert into
'+@sql+'('+@fdlist+') select
'+@fdlist+' from ['+@tbname+']')
@H_403_5@ SET @sql = 'drop table [' + @tbname + ']'
@H_403_5@ EXEC(@sql)
@H_403_5@ RETURN
@H_403_5@ lberr:
@H_403_5@ EXEC sp_oageterrorinfo 0,@src OUT,@desc OUT
@H_403_5@ lbexit:
@H_403_5@ SELECT CAST(@err AS VARBINARY(4)) AS 错误号,@H_403_5@ @src AS 错误源,@H_403_5@ @desc AS 错误描述
@H_403_5@ SELECT @sql,@H_403_5@ @constr,@H_403_5@ @fdlist
@H_403_5@GO
EXEC ExportExcel
@H_403_5@@sqlstr='select * from order',@H_403_5@@path='c:\',@H_403_5@@fname='aa.xls',@H_403_5@@sheetname='OrderSheet'
*/
Excel 导入 sql2005