如何将 SQL Server 2005 数据传输或导出到 Excel

How do you transfer or export SQL Server 2005 data to Excel

提问by YourMomzThaBomb

I have a simple SQL 'Select' query, and I'd like to dump the results into an Excel file. I'm only able to save as .csv and converting to .xls creates some super ugly output. In any case, as far as I can tell (using Google) this doesn't seem to be so straight forward. Any help would be greatly appreciated.

我有一个简单的 SQL 'Select' 查询，我想将结果转储到 Excel 文件中。我只能保存为 .csv 并转换为 .xls 会创建一些超级丑陋的输出。无论如何，据我所知（使用谷歌）这似乎不是那么简单。任何帮助将不胜感激。

采纳答案by Sunny Milenov

Use "External data" from Excel. It can use ODBC connection to fetch data from external source: Data/Get External Data/New Database Query

使用 Excel 中的“外部数据”。它可以使用 ODBC 连接从外部来源获取数据：Data/Get External Data/New Database Query

That way, even if the data in the database changes, you can easily refresh.

这样，即使数据库中的数据发生变化，您也可以轻松刷新。

回答by Ryan Farley

SSIS is a no-brainer for doing stuff like this and is very straight forward (and this is just the kind of thing it is for).

SSIS 很容易做这样的事情，而且非常直接（这就是它的用途）。

1. Right-click the database in SQL Management Studio
2. Go to Tasks and then Export data, you'll then see an easy to use wizard.
3. Your database will be the source, you can enter your SQL query
4. Choose Excel as the target
5. Run it at end of wizard

1. 在 SQL Management Studio 中右键单击数据库
2. 转到任务，然后导出数据，您将看到一个易于使用的向导。
3. 您的数据库将是源，您可以输入您的 SQL 查询
4. 选择 Excel 作为目标
5. 在向导结束时运行它

If you wanted, you could save the SSIS package as well (there's an option at the end of the wizard) so that you can do it on a schedule or something (and even open and modify to add more functionality if needed).

如果需要，您也可以保存 SSIS 包（向导末尾有一个选项），以便您可以按计划或其他方式进行操作（甚至可以打开和修改以添加更多功能，如果需要）。

回答by Mikhail Glukhov

I've found an easy way to export query results from SQL Server Management Studio 2005 to Excel.

我找到了一种将查询结果从 SQL Server Management Studio 2005 导出到 Excel 的简单方法。

1) Select menu item Query -> Query Options.

1) 选择菜单项查询-> 查询选项。

2) Set check box in Results -> Grid -> Include column headers when copying or saving the results.

2) 在结果 -> 网格 ->复制或保存结果时包括列标题中设置复选框。

After that, when you Select All and Copy the query results, you can paste them to Excel, and the column headers will be present.

之后，当您全选并复制查询结果时，您可以将它们粘贴到 Excel 中，列标题将出现。

回答by JonH

See this

看到这个

---

---

This is by far the best post for exporting to excel from SQL:

这是迄今为止从 SQL 导出到 excel 的最佳帖子：

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

To quote from user madhivanan,

引用用户madhivanan，

Apart from using DTS and Export wizard, we can also use this query to export data from SQL Server2000 to Excel

除了使用 DTS 和导出向导，我们还可以使用此查询将数据从 SQL Server2000 导出到 Excel

Create an Excel file named testing having the headers same as that of table columns and use these queries

创建一个名为 testing 的 Excel 文件，其标题与表列的标题相同，并使用这些查询

1 Export data to existing EXCEL file from SQL Server table

1 将数据从 SQL Server 表导出到现有的 EXCEL 文件

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=D:\testing.xls;', 
    'SELECT * FROM [SheetName$]') select * from SQLServerTable

2 Export data from Excel to new SQL Server table

2 将数据从 Excel 导出到新的 SQL Server 表

select * 
into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=D:\testing.xls;HDR=YES', 
    'SELECT * FROM [Sheet1$]')

3 Export data from Excel to existing SQL Server table

3 将数据从 Excel 导出到现有的 SQL Server 表

Insert into SQLServerTable Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=D:\testing.xls;HDR=YES', 
    'SELECT * FROM [SheetName$]')

4 If you dont want to create an EXCEL file in advance and want to export data to it, use

4 如果您不想提前创建EXCEL 文件并想将数据导出到其中，请使用

EXEC sp_makewebtask 
    @outputfile = 'd:\testing.xls', 
    @query = 'Select * from Database_name..SQLServerTable', 
    @colheaders =1, 
    @FixedFont=0,@lastupdated=0,@resultstitle='Testing details'

(Now you can find the file with data in tabular format)

（现在您可以找到包含表格格式数据的文件）

5 To export data to new EXCEL file with heading(column names), create the following procedure

5 要将数据导出到带有标题（列名称）的新 EXCEL 文件，请创建以下过程

create procedure proc_generate_excel_with_columns
(
    @db_name    varchar(100),
    @table_name varchar(100),   
    @file_name  varchar(100)
)
as

--Generate column names as a recordset
declare @columns varchar(8000), @sql varchar(8000), @data_file varchar(100)
select 
    @columns=coalesce(@columns+',','')+column_name+' as '+column_name 
from 
    information_schema.columns
where 
    table_name=@table_name
select @columns=''''''+replace(replace(@columns,' as ',''''' as '),',',',''''')

--Create a dummy file to have actual data
select @data_file=substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))+'\data_file.xls'

--Generate column names in the passed EXCEL file
set @sql='exec master..xp_cmdshell ''bcp " select * from (select '+@columns+') as t" queryout "'+@file_name+'" -c'''
exec(@sql)

--Generate data in the dummy file
set @sql='exec master..xp_cmdshell ''bcp "select * from '+@db_name+'..'+@table_name+'" queryout "'+@data_file+'" -c'''
exec(@sql)

--Copy dummy file to passed EXCEL file
set @sql= 'exec master..xp_cmdshell ''type '+@data_file+' >> "'+@file_name+'"'''
exec(@sql)

--Delete dummy file 
set @sql= 'exec master..xp_cmdshell ''del '+@data_file+''''
exec(@sql)

After creating the procedure, execute it by supplying database name, table name and file path:

创建过程后，通过提供数据库名称、表名称和文件路径来执行它：

EXEC proc_generate_excel_with_columns 'your dbname', 'your table name','your file path'

Its a whomping 29 pages but that is because others show various other ways as well as people asking questions just like this one on how to do it.

它有惊人的 29 页，但那是因为其他人展示了各种其他方式，以及人们就如何做到这一点提出问题。

Follow that thread entirely and look at the various questions people have asked and how they are solved. I picked up quite a bit of knowledge just skimming it and have used portions of it to get expected results.

完全遵循该线程并查看人们提出的各种问题以及如何解决这些问题。我只是略读一下就学到了很多知识，并使用了其中的一部分来获得预期的结果。

To update single cells

更新单个单元格

A member also there Peter Larson posts the following: I think one thing is missing here. It is great to be able to Export and Import to Excel files, but how about updating single cells? Or a range of cells?

那里的成员 Peter Larson 发表了以下内容：我认为这里缺少一件事。能够导出和导入 Excel 文件真是太好了，但是如何更新单个单元格呢？还是一系列单元格？

This is the principle of how you do manage that

这是你如何管理它的原则

update OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;Database=c:\test.xls;hdr=no', 
'SELECT * FROM [Sheet1$b7:b7]') set f1 = -99

You can also add formulas to Excel using this:

您还可以使用以下方法将公式添加到 Excel：

update OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;Database=c:\test.xls;hdr=no', 
'SELECT * FROM [Sheet1$b7:b7]') set f1 = '=a7+c7'

Exporting with column names using T-SQL

使用 T-SQL 导出列名

Member Mladen Prajdic also has a blog entry on how to do this here

成员 Mladen Prajdic 也有一篇关于如何在此处执行此操作的博客条目

References: www.sqlteam.com(btw this is an excellent blog / forum for anyone looking to get more out of SQL Server).

参考资料：www.sqlteam.com（顺便说一句，这是一个优秀的博客/论坛，适合希望从 SQL Server 中获得更多收益的任何人）。

回答by Mitchel Sellers

If you are looking for ad-hoc items rather than something that you would put into SSIS. From within SSMS simply highlight the results grid, copy, then paste into excel, it isn't elegant, but works. Then you can save as native .xls rather than .csv

如果您正在寻找临时项目而不是您将放入 SSIS 的东西。从 SSMS 中简单地突出显示结果网格，复制，然后粘贴到 excel 中，它并不优雅，但有效。然后你可以保存为原生 .xls 而不是 .csv

回答by Michael K. Campbell

Here's a video that will show you, step-by-step, how to export data to Excel. It's a great solution for 'one-off' problems where you need to export to Excel:
Ad-Hoc Reporting

这是一个视频，将逐步向您展示如何将数据导出到 Excel。对于需要导出到 Excel 的“一次性”问题，这是一个很好的解决方案：
Ad-Hoc Reporting

回答by Bill Anderson

It's a LOT easier just to do it from within Excel.!! Open Excel Data>Import/Export Data>Import Data Next to file name click "New Source" Button On Welcome to the Data Connection Wizard, choose Microsoft SQL Server. Click Next. Enter Server Name and Credentials. From the drop down, choose whichever database holds the table you need. Select your table then Next..... Enter a Description if you'd like and click Finish. When your done and back in Excel, just click "OK" Easy.

只需在 Excel 中进行操作就容易多了。！！打开 Excel 数据>导入/导出数据>导入数据 在文件名旁边单击“新建源”按钮 在“欢迎使用数据连接向导”上，选择 Microsoft SQL Server。点击下一步。输入服务器名称和凭据。从下拉列表中，选择包含您需要的表的任何数据库。选择您的表格，然后选择 Next..... 如果您愿意，请输入描述并单击 Finish。完成并返回 Excel 后，只需单击“确定”即可。

回答by Prathap

Create the excel data source and insert the values,

创建excel数据源并插入值，

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;Database=D:\testing.xls;', 
'SELECT * FROM [SheetName$]') select * from SQLServerTable

More informations are available here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

此处提供更多信息 http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

回答by Alex

A handy tool Convert SQL to Excelconverts SQL table or SQL query result to Excel file without programming.

一个方便的工具将 SQL转换为 Excel，无需编程即可将 SQL 表或 SQL 查询结果转换为 Excel 文件。

Main Features - Convert/export a SQL Table to Excel file - Convert/export multiple tables (multiple query results) to multiple Excel worksheets. - Allow flexible TSQL query which can have multiple SELECT statements or other complex query statements.

主要功能 - 将 SQL 表转换/导出到 Excel 文件 - 将多个表（多个查询结果）转换/导出到多个 Excel 工作表。- 允许灵活的 TSQL 查询，可以有多个 SELECT 语句或其他复杂的查询语句。

B. Regards, Alex

B. 问候，亚历克斯

回答by Michael Geller

There exists several tools to export/import from SQL Server to Excel.

有多种工具可以从 SQL Server 导出/导入到 Excel。

Google is your friend :-)

谷歌是你的朋友 :-)

We use DbTransfer (which is one of those which can export a complete Database to an Excel file also) here: http://www.dbtransfer.de/Products/DbTransfer.

我们在此处使用 DbTransfer（它也是可以将完整数据库导出到 Excel 文件的一种）：http://www.dbtransfer.de/Products/DbTransfer 。

We have used the openrowset feature of sql server before, but i was never happy with it, becuase it's not very easy to use and lacks of features and speed...

我们之前使用过sql server的openrowset功能，但是我从来不喜欢它，因为它不是很容易使用并且缺乏功能和速度......