代码语言
.
CSharp
.
JS
Java
Asp.Net
C
MSSQL
PHP
Css
PLSQL
Python
Shell
EBS
ASP
Perl
ObjC
VB.Net
VBS
MYSQL
GO
Delphi
AS
DB2
Domino
Rails
ActionScript
Scala
代码分类
文件
系统
字符串
数据库
网络相关
图形/GUI
多媒体
算法
游戏
Jquery
Extjs
Android
HTML5
菜单
网页交互
WinForm
控件
企业应用
安全与加密
脚本/批处理
开放平台
其它
【
MSSQL
】
SQL导出为Excel表
作者:
疯狂的流浪
/ 发布于
2011/1/10
/
846
<div>Version: SQL Server 7.0/2000 Created by: Alexander Chigrik <a href="http://www.MSSQLCity.com/">http://www.MSSQLCity.com/</a> - all about MS SQL (SQL Server Articles, FAQ, Scripts, Tips and Test Exams).</div> <div>This stored procedure can be used to insert the result set of the particular select statement into Excel file (c:\ImportToExcel.xls, by default). You can pass the server name, user name, user password, the select statement to execute, and the file name to store the results set, as in the example below:</div> <div>EXEC ExportToExcel @server = '.', @uname = 'sa', @QueryText = 'SELECT au_fname FROM pubs..authors', @filename = 'c:\ImportToExcel.xls'</div> <div>/* Version: SQL Server 7.0/2000 Created by: Alexander Chigrik <a href="http://www.MSSQLCity.com/">http://www.MSSQLCity.com/</a> - all about MS SQL (SQL Server Articles, FAQ, Scripts, Tips and Test Exams).</div> <div>This stored procedure can be used to insert the result set of the particular select statement into Excel file (c:\ImportToExcel.xls, by default). You can pass the server name, user name, user password, the select statement to execute, and the file name to store the results set, as in the example below:</div> <div>EXEC ExportToExcel @server = '.', @uname = 'sa', @QueryText = 'SELECT au_fname FROM pubs..authors', @filename = 'c:\ImportToExcel.xls' */</div> <div>IF OBJECT_ID('ExportToExcel') IS NOT NULL DROP PROC ExportToExcel GO</div> <div>CREATE PROCEDURE ExportToExcel ( @server sysname = null, @uname sysname = null, @pwd sysname = null, @QueryText varchar(200) = null, @filename varchar(200) = 'c:\ImportToExcel.xls' ) AS DECLARE @SQLServer int, @QueryResults int, @CurrentResultSet int, @object int, @WorkBooks int, @WorkBook int, @Range int, @hr int, @Columns int, @Rows int, @indColumn int, @indRow int, @off_Column int, @off_Row int, @code_str varchar(100), @result_str varchar(255)</div> <div>IF @QueryText IS NULL BEGIN PRINT 'Set the query string' RETURN END</div> <div>-- Sets the server to the local server IF @server IS NULL SELECT @server = @@servername</div> <div>-- Sets the username to the current user name IF @uname IS NULL SELECT @uname = SYSTEM_USER</div> <div>SET NOCOUNT ON</div> <div>EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @SQLServer OUT IF @hr <> 0 BEGIN PRINT 'error create SQLDMO.SQLServer' RETURN END</div> <div>-- Connect to the SQL Server IF @pwd IS NULL BEGIN EXEC @hr = sp_OAMethod @SQLServer, 'Connect', null, @server, @uname IF @hr <> 0 BEGIN PRINT 'error Connect' RETURN END END ELSE BEGIN EXEC @hr = sp_OAMethod @SQLServer, 'Connect', null, @server, @uname, @pwd IF @hr <> 0 BEGIN PRINT 'error Connect' RETURN END END</div> SELECT @result_str = 'ExecuteWithResults("' + @QueryText + '")' EXEC @hr = sp_OAMethod @SQLServer, @result_str, @QueryResults OUT IF @hr <> 0 BEGIN PRINT 'error with method ExecuteWithResults' RETURN END</div> <div>EXEC @hr = sp_OAMethod @QueryResults, 'CurrentResultSet', @CurrentResultSet OUT IF @hr <> 0 BEGIN PRINT 'error get CurrentResultSet' RETURN END</div> <div>EXEC @hr = sp_OAMethod @QueryResults, 'Columns', @Columns OUT IF @hr <> 0 BEGIN PRINT 'error get Columns' RETURN END</div> <div>EXEC @hr = sp_OAMethod @QueryResults, 'Rows', @Rows OUT IF @hr <> 0 BEGIN PRINT 'error get Rows' RETURN END</div> <div>EXEC @hr = sp_OACreate 'Excel.Application', @object OUT IF @hr <> 0 BEGIN PRINT 'error create Excel.Application' RETURN END</div> <div>EXEC @hr = sp_OAGetProperty @object, 'WorkBooks', @WorkBooks OUT IF @hr <> 0 BEGIN PRINT 'error create WorkBooks' RETURN END</div> <div>EXEC @hr = sp_OAGetProperty @WorkBooks, 'Add', @WorkBook OUT IF @hr <> 0 BEGIN PRINT 'error with method Add' RETURN END</div> <div>EXEC @hr = sp_OAGetProperty @object, 'Range("A1")', @Range OUT IF @hr <> 0 BEGIN PRINT 'error create Range' RETURN END</div> SELECT @indRow = 1 SELECT @off_Row = 0 SELECT @off_Column = 1</div> <div>WHILE (@indRow <= @Rows) BEGIN SELECT @indColumn = 1</div> <div>WHILE (@indColumn <= @Columns) BEGIN</div> <div>EXEC @hr = sp_OAMethod @QueryResults, 'GetColumnString', @result_str OUT, @indRow, @indColumn IF @hr <> 0 BEGIN PRINT 'error get GetColumnString' RETURN END</div> <div>EXEC @hr = sp_OASetProperty @Range, 'value', @result_str IF @hr <> 0 BEGIN PRINT 'error set value' RETURN END</div> <div>EXEC @hr = sp_OAGetProperty @Range, 'Offset', @Range OUT, @off_Row, @off_Column IF @hr <> 0 BEGIN PRINT 'error get Offset' RETURN END</div> SELECT @indColumn = @indColumn + 1</div> <div>END</div> SELECT @indRow = @indRow + 1 SELECT @code_str = 'Range("A' + LTRIM(str(@indRow)) + '")' EXEC @hr = sp_OAGetProperty @object, @code_str, @Range OUT IF @hr <> 0 BEGIN PRINT 'error create Range' RETURN END</div> <div>END</div> SELECT @result_str = 'exec master..xp_cmdshell ''del ' + @filename + ''', no_output' EXEC(@result_str) SELECT @result_str = 'SaveAs("' + @filename + '")' EXEC @hr = sp_OAMethod @WorkBook, @result_str IF @hr <> 0 BEGIN PRINT 'error with method SaveAs' RETURN END</div> <div>EXEC @hr = sp_OAMethod @WorkBook, 'Close' IF @hr <> 0 BEGIN PRINT 'error with method Close' RETURN END</div> <div>EXEC @hr = sp_OADestroy @object IF @hr <> 0 BEGIN PRINT 'error destroy Excel.Application' RETURN END</div> <div>EXEC @hr = sp_OADestroy @SQLServer IF @hr <> 0 BEGIN PRINT 'error destroy SQLDMO.SQLServer' RETURN END GO</div>
试试其它关键字
导为Excel
同语言下
.
SQL查询 多列合并成一行用逗号隔开
.
查看存储过程修改时间,最近执行时间
.
设置手动批量删除数据库相关进程
.
获取某个表中特定字段的所有字符串形式
.
SQL 如何去除重复的字符串
.
怎么去掉一个字段中的重复数据
.
String 去除空格 回车 换行 水平制表符
.
SQL查询和替换含有回车,空格,TAB
.
SQL SERVER 查询每日新增用户数量、次留数量
.
判断两个字符串是否存在相同的内容
可能有用的
.
C#实现的html内容截取
.
List 切割成几份 工具类
.
SQL查询 多列合并成一行用逗号隔开
.
一行一行读取txt的内容
.
C#动态修改文件夹名称(FSO实现,不移动文件)
.
c# 移动文件或文件夹
.
c#图片添加水印
.
Java PDF转换成图片并输出给前台展示
.
网站后台修改图片尺寸代码
.
处理大图片在缩略图时的展示
疯狂的流浪
贡献的其它代码
(
8
)
.
直接在页面上显示Extjs日期控件
.
中国象棋博弈树搜索算法
.
时间操作工具类
.
SQL导出为Word
.
SQL导出为Excel表
.
SQL实现交叉表的方法
.
解密存储过程,视图,触发器
.
去除外键的存储过程
Copyright © 2004 - 2024 dezai.cn. All Rights Reserved
站长博客
粤ICP备13059550号-3