### SQL Server 带列名导出至 Excel 的方法与实现
#### 背景介绍
在日常工作中，经常需要将 SQL Server 数据库中的数据导出到 Excel 文件中，以便进行进一步的数据分析或报告制作。特别是当需要导出的数据带有列名时，这将大大方便后续的数据处理工作。本文档介绍了一种利用 SQL Server 存储过程实现带列名导出至 Excel 的方法。
#### 技术细节与实现步骤
##### 1. 存储过程设计思路
该文档提供了一个名为 `p_exporttb` 的存储过程，用于将 SQL Server 查询结果导出到 Excel 文件中，并确保导出的文件包含列名。该过程首先检查目标 Excel 文件是否存在，若不存在，则会创建一个新的 Excel 文件；如果文件已存在，则直接使用。同时，此过程还支持自定义 Excel 文件的路径、名称以及工作表名称等参数。
##### 2. 存储过程详解
- **删除现有存储过程（如果存在）**：
```sql
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[p_exporttb]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[p_exporttb]
GO
```
此部分代码的作用是在创建新的存储过程之前，先检查是否存在同名的存储过程，如果存在则删除，避免版本冲突。
- **创建存储过程**：
```sql
CREATE PROC p_exporttb
@sqlstr sysname, -- 查询语句
@path nvarchar(1000), -- 文件存放目录
@fname nvarchar(250), -- 文件名
@sheetname varchar(250)='' -- 工作表名，默认为文件名
AS
```
- `@sqlstr`: 指定要执行的 SQL 查询语句。
- `@path`: 指定 Excel 文件的保存路径。
- `@fname`: 指定 Excel 文件的名称。
- `@sheetname`: 指定工作表的名称，默认情况下与文件名相同。
- **参数检测**：
```sql
IF ISNULL(@fname, '') = '' SET @fname = 'temp.xls'
IF ISNULL(@sheetname, '') = '' SET @sheetname = REPLACE(@fname, '.', '#')
```
这部分代码用于确保所有必要的参数都已正确设置，例如如果没有指定文件名，则默认使用 "temp.xls"。
- **检查文件是否存在**：
```sql
IF RIGHT(@path, 1) <> '\' SET @path = @path + '\'
CREATE TABLE #tb (abit bit, bbit bit, cbit bit)
SET @sql = @path + @fname
INSERT INTO #tb EXEC master..xp_fileexist @sql
```
该段代码首先确保路径字符串以反斜杠结尾，然后创建一个临时表 `#tb`，并通过执行 `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;ExtendedProperties="Excel 5.0;HDR=YES' + ';DATABASE=' + @sql + '"'
```
- 如果文件不存在，则使用 `DRIVER={Microsoft Excel Driver (*.xls)}` 创建新的 Excel 文件。
- 如果文件已存在，则使用 `Provider=Microsoft.Jet.OLEDB.4.0` 连接到现有的 Excel 文件。
- **连接数据库并创建临时表**：
```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
```
使用 `sp_oacreate` 和 `sp_oamethod` 创建与 Excel 的连接。
- **创建表的 SQL**：
```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 ('tinyint', '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
```
- 此部分代码用于根据提供的 SQL 查询创建一个临时表 `##tmp_<随机字符串>`。
- 然后，通过分析查询结果中的字段类型来构建正确的数据格式，确保导出的数据能够正确显示在 Excel 中。
##### 3. 调用示例
```sql
EXEC p_exporttb @sqlstr = 'SELECT * FROM 地区资料', @path = 'C:\', @fname = 'aa.xls', @sheetname = '地区资料'
```
以上代码演示了如何调用 `p_exporttb` 存储过程，将 `地区资料` 表中的所有记录导出到位于 `C:\` 目录下名为 `aa.xls` 的 Excel 文件中，并将数据放置在名为 `地区资料` 的工作表中。
#### 结论
通过上述存储过程，可以方便地将 SQL Server 数据库中的数据导出到 Excel 文件，并且确保每列都有对应的列名，这对于数据分析等工作非常有用。此方法不仅提供了灵活性，还可以满足不同的业务需求。
belllab2014-04-04不错，可以使用。偶尔用下比较方便。
