sql导入和导出数据到Excel的方法

接口类型

有两种接口可供选择:Microsoft.Jet.OLEDB.4.0(以下简称 Jet 引擎)和Microsoft.ACE.OLEDB.12.0(以下简称 ACE 引擎)。

Jet 引擎可以访问 Office 97-2003,但不能访问 Office 2007。

ACE 引擎是随 Office 2007 一起发布的数据库连接组件,既可以访问 Office 2007,也可以访问 Office 97-2003。

另外:Microsoft.ACE.OLEDB.12.0 可以访问正在打开的 Excel 文件,而 Microsoft.Jet.OLEDB.4.0 是不可以的。

Microsoft.ACE.OLEDB.12.0 安装文件:

http://www.microsoft.com/downloads/details.aspx?displaylang=zh-cn&FamilyID=7554f536-8c28-4598-9b72-ef94e038c891

向导操作

(以下介绍方法,适合导入导出.xlsx文件,65535行以上数据时选用。)

第一步:

在目标数据库上右键选择导出数据(或是点击开始并选择运行并输入CMD然后在命令提示符里输入DTSWIZARD。)就会出现SQL Server 导入和导出向导的欢迎界面将显示出来。

第二步:

点击下一步按钮,它将进入选择数据源向导界面。

导入:则选择数据源为“Office 12.0 Access Database” (excel导入,导出类似),如图

@B]EWD4}Z(}EH{1BE~6)X%2

 

点“属性”,选择“所有”,Data Source这列填Excel2007文件的绝对路径,如:D:\2007.xlsx,Extended properties这列填“Excel 12.0”(关键),之后点“确定”,点“下一步”,配置复制到数据库的相关信息,这个可以参照导出数据第一步。

其余操作略。

sql语句导入导出

    --> Jet 引擎访问 Excel 97-2003  
    SELECT  *
    FROM    OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                       'Excel 8.0;HDR=Yes;IMEX=1;Database=D:/97-2003.xls',
                       'select * from [Sheet1$]')  
    SELECT  *
    FROM    OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                       'Excel 8.0;HDR=Yes;IMEX=1;Database=D:/97-2003.xls',
                       [Sheet1$])  
    SELECT  *
    FROM    OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
                           'Excel 8.0;HDR=Yes;IMEX=1;Database=D:/97-2003.xls')...[Sheet1$]  
    SELECT  *
    FROM    OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
                           'Data Source=D:/97-2003.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1"')...[Sheet1$]  
      
    --> ACE 引擎访问 Excel 97-2003  
    SELECT  *
    FROM    OPENROWSET('Microsoft.ACE.OLEDB.12.0',
                       'Excel 12.0;HDR=Yes;IMEX=1;Database=D:/97-2003.xls',
                       'select * from [Sheet1$]')  
    SELECT  *
    FROM    OPENROWSET('Microsoft.ACE.OLEDB.12.0',
                       'Excel 12.0;HDR=Yes;IMEX=1;Database=D:/97-2003.xls',
                       [Sheet1$])  
    SELECT  *
    FROM    OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
                           'Excel 12.0;HDR=Yes;IMEX=1;Database=D:/97-2003.xls')...[Sheet1$]  
    SELECT  *
    FROM    OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
                           'Data Source=D:/97-2003.xls;Extended Properties="Excel 12.0;HDR=Yes;IMEX=1"')...[Sheet1$]  
      
    --> ACE 引擎访问 Excel 2007  
    SELECT  *
    FROM    OPENROWSET('Microsoft.ACE.OLEDB.12.0',
                       'Excel 12.0;HDR=Yes;IMEX=1;Database=D:/2007.xlsx',
                       'select * from [Sheet1$]')  
    SELECT  *
    FROM    OPENROWSET('Microsoft.ACE.OLEDB.12.0',
                       'Excel 12.0;HDR=Yes;IMEX=1;Database=D:/2007.xlsx',
                       [Sheet1$])  
    SELECT  *
    FROM    OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
                           'Excel 12.0;HDR=Yes;IMEX=1;Database=D:/2007.xlsx')...[Sheet1$]  
    SELECT  *
    FROM    OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
                           'Data Source=D:/2007.xlsx;Extended Properties="Excel 12.0;HDR=Yes;IMEX=1"')...[Sheet1$] 

2007 工作簿文件的扩展名是:xlsx

HDR=Yes/No

可选参数,指定 Excel 表的第一行是否列名,缺省为 Yes,可以在注册表中修改缺省的行为。

IMEX=1

可选参数,将 Excel 表中混合 Intermixed 数据类型的列强制解析为文本。

大数据导出excel

存储过程(来源互联网)

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
go


/*--数据导出EXCEL

导出查询中的数据到Excel,包含字段名,文件为真正的Excel文件
如果文件不存在,将自动创建文件
如果表不存在,将自动创建表
基于通用性考虑,仅支持导出标准数据类型
--Modify
1、修改邹建的错误:关于provider的书写
在openrowset时为database
在ADODb.connection.open时为data source,一个很不容易找到的错误,否则0x80004005 Microsoft JET Database Engine 不能执行选定查询
2、执行过程过长时,中途无法取消
3、一些限制
--Jet SQL无法修改列名,应使用ADOX.Catagory
--SQLServer2005索引最大为1284 bytes,order by 最大为8060 bytes
--grant exec分配不了权限
4、要对@varQuerySQL中结果集排序,请在@varOrderBy指定结果集中的列,存储过程会按此排序,不要再在@varQuerySQL中再排序

--邹建 2003.10(引用请保留此信息)--*/


/*--调用示例--

--表的联合
p_QueryToExcel @varQuerySQL= 'select top 65000 A.* from a inner join B on A.a1=B.a1'
,@varExcelFullPath= 'E:\aa21.xls',@varSheetName= 'sheet'

select * from liupeng --256003
--102400条记录
--时间:
[email protected]没有order by TopName,Identitier
1:50,1:21, 1:55, 1:27(加索引), 1:34(加索引)
[email protected]有order by
大于9:00
--Select语句列必须带函数的列必须有别名
p_QueryToExcel @varQuerySQL= 'select MetaID as 标识,Title as 书名,Creator as 作者,Publisher as 出版者,Price as 单价,CopyNum as 复本数,Identitier as 分类号,TopName as 类目,issuedate as 出版时间,ISBN,publishdate as 公布时间,(case
when IsRef= 0 then ''否''
when IsRef = 1 then ''是''
else '''' end ) as 是否教参
from [liupeng]
where CategoryTypeID=1 ',@varExcelFullPath= 'E:\aa3.xls',@varSheetName= 'sheet'

select count(*) from ast_document --334138
--102400条记录
--时间:
[email protected]没有order by TopName,Identitier
2:00, 1:10, 1:27, 1:21, 1:20

p_QueryToExcel @varQuerySQL=' select top 2000 MetaID as 标识,TopIdentitier,DocID,Title,Creator,Publisher,Price,CopyNum,Identitier,TopName,substring(convert(varchar, issuedate, 20), 1, 7) as 出版日期,ISBN,substring(convert(varchar,publishdate, 20), 1, 10) as 发布日期,(case
when IsRef= 0 then''否''
when IsRef = 1 then ''是''
else ''''end ) as IsRef
,Abstract
from [liupeng]'
,@varExcelFullPath= 'E:\aa004.xls',@varSheetName= 'sheet',@varOrderBy='TopIdentitier,DocID'

p_QueryToExcel @varQuerySQL= 'select top 102400
DocID,
MetaTypeID,
MetaID,
Title,
Creator,
[Year],
Publisher,
PublishDate,
Price,
ISBN,
IssueDate,
IsScan,
Status,
InsertedTime,substring(Abstract,1,255) as 摘要 from ast_document'
,@varExcelFullPath= 'E:\aa2.xls',@varSheetName= 'sheet',@varOrderBy='MetaID,Title'

--Excel 12:[Sheet1$]则,会报错“书签无效”,应使用'select * from [Sheet1$]'
insert into OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=D:\test.xlsx;',
'select * from [Sheet1$]') select top 10 DocID from Test.dbo.ast_document

--在Excel 12中一个sheet中加入10万条记录比在多个Sheet中加入10万条记录要满很多
--因此不用Excel 12存
SELECT *
FROM OPENDATASOURCE( 'Microsoft.Jet.OLEDB.4.0',
'Data Source=F:\aa.xls; Extended properties="Excel 8.0;HDR=YES"')...sheet$

*/

ALTER PROC [dbo].[p_QueryToExcel]
    @varQuerySQL VARCHAR(8000) , [email protected]:查询语句,各列必须有名称,没有的需要别名,由于
--如果查询语句中使用了order by,请加上top 100 percent
--对是否包含where分类限制不做要求
    @varExcelFullPath NVARCHAR(4000) , [email protected]:生成Excel的完整路径
    @varSheetName VARCHAR(512) = 'sheet' ,
    @varOrderBy VARCHAR(512) = '类目' , --必须是@varQuerySQl中的列名
    @varArrayHiddenCols VARCHAR(1024) = 'TopIdentitier,DocID'
AS 
    BEGIN
--声明和初始化变量

        DECLARE @varSheetName_pre VARCHAR(1024)
        DECLARE @intOneSheetMaxNumber INT
        DECLARE @intRecCount INT
        DECLARE @sinSheetNumber SMALLINT
        DECLARE @sinCircle INT
        DECLARE @varTempSQL VARCHAR(8000)

        SET @intOneSheetMaxNumber = 60000
        SET @sinSheetNumber = 1
        SET @varExcelFullPath = CONVERT(VARCHAR(8000), LTRIM(RTRIM(@varExcelFullPath)))
        SET @varQuerySQL = LOWER(LTRIM(RTRIM(@varQuerySQL)))
        SET @sinCircle = 0
        SET @varSheetName_pre = @varSheetName
        SET @varOrderBy = LTRIM(RTRIM(@varOrderBy))
        SET @varArrayHiddenCols = LOWER(LTRIM(RTRIM(@varArrayHiddenCols)))
--select top 1 1 from ast_document where metaid not in ('CategoryTypeID','DocID')
        SET @varArrayHiddenCols = REPLACE(@varArrayHiddenCols, ',', ',') --CategoryTypeID,DocID
        PRINT @varArrayHiddenCols
-- set @varOrderByWithNoAliasTable = ltrim(rtrim(@varOrderByWithNoAliasTable))
-- [email protected]的别名这里取AAA
-- if @varOrderByWithNoAliasTable is null or @varOrderByWithNoAliasTable = ''
-- set @varOrderBy = 'Order by AAA.CategoryTypeid, AAA.HiberarchyCode'
-- else
-- set @varOrderBy = 'AAA.' + replace(@varOrderByWithNoAliasTable,',' ,',AAA.' )

/*=================================检测参数有效性====================*/
----判断@varExcelFullPath
        IF ( @varExcelFullPath IS NULL )
            OR ( @varExcelFullPath = '' ) 
            BEGIN
                RAISERROR ('Excel文件路径不能为空。',1,1)
                RETURN 50001
            END

----判断@varQuerySQL
        IF ( @varQuerySQL IS NULL )
            OR ( @varQuerySQL = '' ) 
            BEGIN
                RAISERROR ('查询语句不能为空。',1,2)
                RETURN 50001
            END

----判断@varQuerySQL 'SQL语句'
----假设用户没有恶意调用
        SET @varTempSQL = @varQuerySQL
        IF LEFT(@varTempSQL, 1) = '(' 
            BEGIN
                RAISERROR ('不能将整个SQL语句用括号包起来。',2,16)
                RETURN 50002
            END

        IF CHARINDEX('select ', @varTempSQL, 1) = 0 
            BEGIN
                RAISERROR ('Error 缺少select语句。',2,16)
                RETURN 50002
            END

        IF CHARINDEX(' aaa', @varTempSQL, 1) > 0
            OR CHARINDEX(' bbb', @varTempSQL, 1) > 0
            OR CHARINDEX(' myrownumber', @varTempSQL, 1) > 0 
            BEGIN
                RAISERROR ('请使用AAA、BBB、MyRowNumber以外的别名。',2,16)
                RETURN 50002
            END

        IF LEFT(@varTempSQL, 1) = '(' 
            BEGIN
                RAISERROR ('不能将整个SQL语句用括号包起来。',2,16)
                RETURN 50002
            END
--针对'
--set @varQuerySQL = replace(@varQuerySQL,char(39),char(39)+char(39))
/*
if charindex(char(39),@varTempSQL,1) > 0
begin
--RAISERROR ('Error 传输的SQL语句不能包含英文撇,请使用''代替。',3,17)
--return 50002
end
*/

        IF @varOrderBy IS NULL
            OR @varOrderBy = '' 
            BEGIN
                RAISERROR ('排序不能为空。',1,5)
                RETURN 50001
            END
        ELSE 
            BEGIN
                SET @varOrderBy = 'BBB.' + REPLACE(@varOrderBy, ',', ',BBB.')
            END
        PRINT @varOrderBy

--exec ('select 1 from (select * from ast_document where docid < 0) as A')
--select 1以便得到count记录数,保证@varQuerySQL不包含AAA别名
        SET @varTempSQL = 'select 1 from (' + @varQuerySQL + ') as AAA'
        EXEC (@varTempSQL)

[email protected]@rowcount在下一个begin...end之后就成为0
        SET @intRecCount = @@rowcount
        IF @intRecCount = 0 
            BEGIN
                RAISERROR ('查询记录集为空。',1,5)
                RETURN 50001
            END
        PRINT @intRecCount


        DECLARE @varTopCategoryCode VARCHAR(256)
        DECLARE @varTopCategoryName VARCHAR(512)
        DECLARE @err INT ,
            @src VARCHAR(255) ,
            @desc VARCHAR(255) ,
            @out INT --Error跟踪
        DECLARE @obj INT ,
            @constr VARCHAR(1000) ,
            @fdlist VARCHAR(8000) ,
            @fdlist_AAA VARCHAR(8000)
        DECLARE @tbname SYSNAME

--检查Excel文件是否已经存在
        CREATE TABLE #tb ( a BIT, b BIT, c BIT )
        SET @varExcelFullPath = LTRIM(RTRIM(@varExcelFullPath))

        INSERT  INTO #tb
                EXEC master..xp_fileexist @varExcelFullPath
        PRINT 'xp_fileexist' + @varExcelFullPath
--select * from #tb
/*
xp_fileexist 返回的三个列, 分别代表

文件已存在 文件是目录 父目录已存在
----- ----- ------
0 0 1
*/

/*
declare @saveas varchar(2048),@sheet int

set @saveas = 'ActiveWorkbook.SaveAs("'[email protected]+'")'

exec @err = sp_oacreate 'excel.application' ,@obj output
if @err <> 0 goto lberr

exec @err = sp_oamethod @obj ,'workbooks.add' ,@sheet output
if @err <> 0 goto lberr

exec @err = sp_oamethod @obj ,@saveas
if @err <> 0 goto lberr

--exec @err = sp_oamethod @obj ,'ActiveWorkbook.Save'
if @err <> 0 goto lberr

exec @err = sp_oamethod @obj ,'Workbooks.Close'
if @err <> 0 goto lberr

exec @err = sp_oamethod @obj ,'quit'
exec @err = sp_oadestroy @obj

return
*/

--数据库创建语句
        SET @varTempSQL = @varExcelFullPath
        IF EXISTS ( SELECT  1
                    FROM    #tb
                    WHERE   a = 1 ) 
            BEGIN
--set @constr= 'DRIVER={Microsoft Excel Driver (*.xls)};DSN= '''';READONLY=FALSE '
-- + ';CREATE_DB= "'[email protected]+ '";DBQ='[email protected]
                RAISERROR ('暂不支持对已经存在的Excel,做导出操作。',1,5)
                RETURN 50001
            END
        ELSE 
            SET @constr = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source='
                + @varExcelFullPath
                + ';Extended Properties="Excel 8.0;HDR=YES"'
--set @constr= 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source='[email protected]+ ';Extended Properties="Excel 12.0;HDR=YES"'


--连接数据库
        EXEC @err= sp_OACreate 'ADODB.Connection', @obj OUT
        PRINT '连接数据库1' + CONVERT(VARCHAR(50), @obj)
        IF @err <> 0 
            GOTO lberr

        EXEC @err= sp_OASetProperty @obj, 'ConnectionString', @constr
        IF @err <> 0 
            GOTO lberr

        EXEC @err= sp_oamethod @obj, 'Open'
        PRINT '连接数据库2' + @constr
        IF @err <> 0 
            GOTO lberr

        PRINT '创建临时表1'

--创建相同表结构的临时表
        SET @tbname = '##tmp_' + CONVERT(VARCHAR(38), NEWID())
        SET @varTempSQL = 'select top 1 AAA.* into [' + @tbname + '] from ('
            + @varQuerySQL + ') AAA'
        PRINT '创建临时表2' + @varTempSQL
        EXEC(@varTempSQL)

        SET @varTempSQL = ''
        SET @fdlist = ''
        SET @fdlist_AAA = ''
        SELECT  @fdlist = @fdlist + ',[' + a.name + ']' ,
                @varTempSQL = @varTempSQL + ',[' + a.name + '] '
                + CASE WHEN b.name LIKE '%char'
                       THEN CASE WHEN a.length > 255 THEN 'text(255)'
                                 ELSE 'text(' + CAST(a.length AS VARCHAR)
                                      + ')'
                            END
                       WHEN b.name LIKE '%int'
                            OR b.name = 'bit' THEN 'int'
                       WHEN b.name LIKE '%datetime' THEN 'datetime'
                       WHEN b.name LIKE '%money' THEN 'money'
                       WHEN b.name LIKE '%text' THEN 'memo'
                       ELSE b.name
                  END
        FROM    tempdb..syscolumns a
                LEFT JOIN tempdb..systypes b ON a.xtype = b.xusertype
        WHERE   b.name NOT IN ( 'image', 'uniqueidentifier', 'sql_variant',
                                'varbinary', 'binary', 'timestamp' )
                AND a.id = ( SELECT id
                             FROM   tempdb..sysobjects
                             WHERE  name = @tbname
                           )
                AND CHARINDEX(a.name, @varArrayHiddenCols) = 0

        SELECT  @fdlist_AAA = @fdlist_AAA + ',AAA.[' + a.name + ']'
        FROM    tempdb..syscolumns a
                LEFT JOIN tempdb..systypes b ON a.xtype = b.xusertype
        WHERE   b.name NOT IN ( 'image', 'uniqueidentifier', 'sql_variant',
                                'varbinary', 'binary', 'timestamp' )
                AND a.id = ( SELECT id
                             FROM   tempdb..sysobjects
                             WHERE  name = @tbname
                           )
                AND CHARINDEX(a.name, @varArrayHiddenCols) = 0

        PRINT @fdlist + ' ' + @fdlist_AAA
        PRINT '转换数据类型'

/*=====================分页插入Excel的Sheet=======================*/
--如果不按分类存放

--获得记录总数@intRecCount,上面判断时已经获得
----设置Sheet名称
----判断@intRecCount与@intOneSheetMaxNumber大小,定义Sheet名称后
----分页插入各个Sheet

--如果<[email protected]
        IF @intRecCount <= @intOneSheetMaxNumber 
            BEGIN
                SET @varSheetName = @varSheetName_pre + '1'
--直接创建Excel和导入数据
                SET @varTempSQL = 'create table [' + @varSheetName + '] ('
                    + SUBSTRING(@varTempSQL, 2, 8000) + ')'
                SET @fdlist = SUBSTRING(@fdlist, 2, 8000)
                SET @fdlist_AAA = SUBSTRING(@fdlist_AAA, 2, 8000)

                PRINT '准备创建Table ' + @varTempSQL
                EXEC @err= sp_oamethod @obj, 'Execute', @out OUT, @varTempSQL
                IF @err <> 0 
                    GOTO lberr

--为导入数据
                SET @varTempSQL = 'openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel 8.0;HDR=Yes;database='
                    + @varExcelFullPath + ''',[' + @varSheetName + '$])'
--set @varTempSQL= 'openrowset(''Microsoft.ACE.OLEDB.12.0'',''Excel 12.0;HDR=Yes;database='[email protected][email protected]+ ''',''select * from ['[email protected]+ '$]'')'
                PRINT '导入数据:' + 'insert into ' + @varTempSQL + '(' + @fdlist
                    + ') select ' + @fdlist_AAA + ' from (' + @varQuerySQL
                    + ') as AAA'
                EXEC('insert into '[email protected]+ '('[email protected]+ ') select '[email protected]_AAA+ ' from ('[email protected]+ ') as AAA')
            END
--如果>@intOneSheetMaxNumber
        ELSE 
            BEGIN
                SET @sinSheetNumber = @intRecCount / @intOneSheetMaxNumber
                    + ( CASE ( @intRecCount % @intOneSheetMaxNumber )
                          WHEN 0 THEN 0
                          ELSE 1
                        END )

                SET @fdlist = SUBSTRING(@fdlist, 2, 8000)
                SET @fdlist_AAA = SUBSTRING(@fdlist_AAA, 2, 8000)

                WHILE ( @sinCircle < @sinSheetNumber ) 
                    BEGIN
                        SET @varSheetName = @varSheetName_pre
                            + CONVERT(VARCHAR(50), @sinCircle + 1)
                        PRINT '' + CONVERT(VARCHAR(50), @sinCircle + 1)
                            + ' 个Sheet / 总共 '
                            + CONVERT(VARCHAR(50), @sinSheetNumber)
                            + ' 个Sheet'
--直接创建Excel和导入数据
                        DECLARE @varTempSQL2 VARCHAR(8000)
                        SET @varTempSQL2 = ''
                        SELECT  @varTempSQL2 = 'create table ['
                                + @varSheetName + '] ('
                                + SUBSTRING(@varTempSQL, 2, 8000) + ')'


                        PRINT '准备创建Table ' + @varTempSQL2
                        EXEC @err= sp_oamethod @obj, 'Execute', @out OUT,
                            @varTempSQL2
                        PRINT 'create table :' + @varTempSQL
                        IF @err <> 0 
                            GOTO lberr

--为导入数据
/*
WITH OrderedTable AS
(
SELECT BBB.*,ROW_NUMBER() OVER (ORDER BY Docid) AS 'MyRowNumber'
FROM (@varQuerySQL) as BBB
)
insert into openrowset() (@fdlist)
SELECT @fdlist
FROM OrderedTable
WHERE MyRowNumber BETWEEN (@intOneSheetMaxNumber * @sinCircle) AND (@intOneSheetMaxNumber * (@sinCircle+1))
*/
--set @varTempSQL= 'openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel 8.0;HDR=Yes;database='[email protected]+ ''',['[email protected]+ '$])'
--set @varTempSQL= 'openrowset(''Microsoft.ACE.OLEDB.12.0'',''Excel 12.0;HDR=Yes;database='[email protected][email protected]+ ''',''select * from ['[email protected]+ '$]'')'

                        SET @varTempSQL2 = 'WITH OrderedTable AS
(
SELECT BBB.*,ROW_NUMBER() OVER (ORDER BY ' + @varOrderBy
                            + ') AS ''MyRowNumber''
FROM (' + @varQuerySQL
                            + ') as BBB
)
insert into openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel 8.0;HDR=Yes;database='
                            + @varExcelFullPath + ''',[' + @varSheetName
                            + '$])' + ' select ' + @fdlist
                            + ' FROM OrderedTable WHERE MyRowNumber BETWEEN '
                            + CONVERT(VARCHAR(50), @intOneSheetMaxNumber
                            * @sinCircle + 1) + ' AND '
                            + CONVERT(VARCHAR(50), @intOneSheetMaxNumber
                            * ( @sinCircle + 1 ))

                        PRINT '导入数据:' + @varTempSQL2
                        EXEC(@varTempSQL2)

                        SET @sinCircle = @sinCircle + 1
                    END
            END


--关闭和释放OA对象
        EXEC @err= sp_OAMethod @obj, 'Close'
        EXEC @err= sp_OAMethod @obj, 'Dispose'
        PRINT 'sp_oadestroy' + CONVERT(VARCHAR(50), @obj)
        EXEC @err= sp_OADestroy @obj

        SET @varTempSQL = 'drop table [' + @tbname + ']'
        PRINT 'drop table ' + @varTempSQL
        EXEC(@varTempSQL)

        RETURN
        lberr:
        EXEC sp_oageterrorinfo 0, @src OUT, @desc OUT
        lbexit:
        SELECT  CAST(@err AS VARBINARY(4)) AS 错误号 ,
                @src AS 错误源 ,
                @desc AS 错误描述
        SELECT  @varTempSQL ,
                @constr ,
                @fdlist

    END

调用:

DECLARE @path NVARCHAR(1000)
  --文件存放目录
DECLARE @fileName NVARCHAR(256)
 --新文件名
DECLARE @sqlstr VARCHAR(8000)
   --查询语句
SET @path = 'c:/'
SET @fileName = 'tb' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120),
                                               '-', ''), ' ', ''), ':', '')
    + '.xls'
SET @path[email protected]path[email protected]
SET @sqlstr = 'SELECT id,mobile,channel FROM dbo.tbl_test '
EXEC dbo.p_QueryToExcel @sqlstr, @path, 'sheet','id'

参考文章

http://blog.csdn.net/Limpire/article/details/2599760

  • 单一数据类型列的类型解析
  • 混合数据类型列的自然解析
  • 混合数据类型列的强制解析——IMEX=1
  • 如何解决 NULL 值问题
  • SQL Server 2000 中的列顺序问题
  • 如何访问隐藏的 Sheet
  • 如何访问非常规命名的 Sheet
  • 此条目发表在学习笔记分类目录,贴了, 标签。将固定链接加入收藏夹。

    发表评论

    此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据