关于VB6.0 SQL2005 Excel

本文的方法均已亲自验证

EXCEL 与 SQL数据库

SQL2005 导出成 Excel

方法一 导出SQL查询到Excel （Excel不存在）

前提：使用SQL Server2005 外围应用配置器――“功能的外围应用配置器”――开启xp_cmdshell功能
EXEC master..xp_cmdshell
'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"'

说明.
以上语句最好写在同一行。xp_cmdshell 处可以换行，但是之后的后面的字符串（ ‘….’） 必须写成一行，不能进行换行，否则会出错。
参数 c:test.xls ：为导出的Excel文件名
参数 SZF02D253.DRTV.dbo.Campaign_Source ：服务器名+数据库+所有者+表名
参数 -S "SZF02D253" ： 服务器名，也可以用IP代替，即写成 -S "10.131.44.159"
参数 -U "sa" ：SQL账户名
参数 -P "wayne" ：SQL账户密码
注意：可以自动生成Excel文件。若Excel文件不存在，则自动产生文件；若文件存在，则覆盖原来的文件。

方法二 导出数据表到Excel（Excel不存在）

前提 1：使用SQL Server2005 外围应用配置器――“功能的外围应用配置器”――开启“xp_cmdshell” 功能
EXEC master..xp_cmdshell
'bcp DRTV.dbo.Campaign_Source out "c:test3.xls" -c -S "SZF02D253" -U "sa" -P "wayne"'

说明
参数 DRTV.dbo.Campaign_Source ：数据库+所有者+表名 ，在这里不能用 “服务器名+数据库+所有者+表名”。
参数 c:test3.xls ：为导出的Excel文件名。
注意：可以自动生成Excel文件。若Excel文件不存在，则自动产生文件；若文件存在，则覆盖原来的文件。

方法三 导出数据表到Excel（Excel已存在）
前提 1：使用SQL Server2005 外围应用配置器――“功能的外围应用配置器”――开启“即席远程查询”功能（即 OPENROWSET 和 OPENDATASOURCE）
前提 2：Excel文件已经存在，工作表（固定名称）已经存在，要导出的列在工作表中已经设置好，工作表中列的顺序必须与SQL语句中字段顺序一致。
INSERT INTO OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE=c:test4.xls',ImportData$)
SELECT Id,CreateDate
FROM SZF02D253.DRTV.dbo.Campaign_Source
说明
参数 c:test4.xls ：为导出的Excel文件名
参数 ImportData$ ：其中ImportData为Excel文件中工作表名，$符号必须带上，作用不祥。
参数 SZF02D253.DRTV.dbo.Campaign_Source ：服务器名+数据库+所有者+表名
参数 Id,CreateDate ：是要导出的列，这些列必须在 ImportData 工作表中设置好，并且字段顺序一致

方法四 导出数据表到Excel（综合以上各种方法，强烈推荐）
前提 1：使用SQL Server2005 外围应用配置器――“功能的外围应用配置器”――开启“即席远程查询”功能（即 OPENROWSET 和 OPENDATASOURCE）
前提 2：使用SQL Server2005 外围应用配置器――“功能的外围应用配置器”――开启“OLE 自动化”功能
前提 3：使用SQL Server2005 外围应用配置器――“功能的外围应用配置器”――开启“xp_cmdshell” 功能
前提 4：创建下面的存储过程 ExportExcel
前提 5：调用 ExportExcel ，并提供相关参数

/*

存储过程：将查询结果导出到Excel (仅支持导出标准数据类型)

以下情形可以正常运行
1、Excel文件不存在,将自动创建文件及WorkSheet.
2、Excel文件存在，WorkSheet不存在，将自动在Excel文件中新建WorkSheet.
3、Excel文件存在，WorkSheet存在，且WorkSheet中定义的字段包含了所有查询结果的字段(字段先后顺序可不一致)

以下情形将报错
1、Excel文件存在，WorkSheet存在，但WorkSheet中未定义字段，则报错.
2、Excel文件存在，WorkSheet存在，但WorkSheet中定义的字段少于查询结果字段，则报错.

*/

IF EXISTS ( SELECT *
FROM dbo.sysobjects
WHERE id = OBJECT_ID(N'[dbo].[ExportExcel]')
AND OBJECTPROPERTY(id,N'IsProcedure') = 1 )
DROP PROCEDURE [dbo].[ExportExcel]
GO

CREATE PROC ExportExcel
@sqlstr SYSNAME,--查询语句
@path NVARCHAR(1000),--Excel文件存放目录
@fname NVARCHAR(250),--Excel文件名
@sheetname VARCHAR(250) = '' --WorkSheet工作表名,默认为文件名
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="' + @sql + '";DBQ=' + @sql
ELSE
SET @constr = 'Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties="Excel 5.0;HDR=YES'
+ ';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(' + @sqlstr + ') 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 name = @tbname
)
SELECT @sql = 'create table [' + @sheetname + '](' + SUBSTRING(@sql,2,
8000) + ')',
@fdlist = SUBSTRING(@fdlist,8000)

EXEC @err= sp_oamethod @obj,'execute',@out OUT,@sql --执行

EXEC @err= sp_oamethod @obj,'close' --关闭，若不关闭SQL进程将一直占用此Excel文件。

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 + ''',[' + @sheetname + '$])'
EXEC('insert into '+@sql+'('+@fdlist+') select '+@fdlist+' from ['+@tbname+']')
SET @sql = 'drop table [' + @tbname + ']'
EXEC(@sql)
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

/*
--调用示例

EXEC ExportExcel
@sqlstr='select * from order',
@path='c:',
@fname='aa.xls',
@sheetname='OrderSheet'

--如果查询语句中使用了order by,请加上top 100 percent

*/

Excel 导入 SQL2005