导出数据到Excel概述 选择自 Terrylee_1218 的 Blog 关键字 导出数据到Excel概述 出处
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:EXEC ExportToExcel @server = '.', @uname = 'sa', @QueryText = 'SELECT au_fname FROM pubs..authors', @filename = 'c:\ImportToExcel.xls'/* Version: SQL Server 7.0/2000 Created by: Alexander Chigrik - all about MS SQL (SQL Server Articles, FAQ, Scripts, Tips and Test Exams).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:EXEC ExportToExcel @server = '.', @uname = 'sa', @QueryText = 'SELECT au_fname FROM pubs..authors', @filename = 'c:\ImportToExcel.xls' */IF OBJECT_ID('ExportToExcel') IS NOT NULL DROP PROC ExportToExcel GOCREATE 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)IF @QueryText IS NULL BEGIN PRINT 'Set the query string' RETURN END-- Sets the server to the local server IF @server IS NULL SELECT @server = @@servername-- Sets the username to the current user name IF @uname IS NULL SELECT @uname = SYSTEM_USERSET NOCOUNT ONEXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @SQLServer OUT IF @hr <> 0 BEGIN PRINT 'error create SQLDMO.SQLServer' RETURN END-- 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 ENDSELECT @result_str = 'ExecuteWithResults("' + @QueryText + '")' EXEC @hr = sp_OAMethod @SQLServer, @result_str, @QueryResults OUT IF @hr <> 0 BEGIN PRINT 'error with method ExecuteWithResults' RETURN ENDEXEC @hr = sp_OAMethod @QueryResults, 'CurrentResultSet', @CurrentResultSet OUT IF @hr <> 0 BEGIN PRINT 'error get CurrentResultSet' RETURN ENDEXEC @hr = sp_OAMethod @QueryResults, 'Columns', @Columns OUT IF @hr <> 0 BEGIN PRINT 'error get Columns' RETURN ENDEXEC @hr = sp_OAMethod @QueryResults, 'Rows', @Rows OUT IF @hr <> 0 BEGIN PRINT 'error get Rows' RETURN ENDEXEC @hr = sp_OACreate 'Excel.Application', @object OUT IF @hr <> 0 BEGIN PRINT 'error create Excel.Application' RETURN ENDEXEC @hr = sp_OAGetProperty @object, 'WorkBooks', @WorkBooks OUT IF @hr <> 0 BEGIN PRINT 'error create WorkBooks' RETURN ENDEXEC @hr = sp_OAGetProperty @WorkBooks, 'Add', @WorkBook OUT IF @hr <> 0 BEGIN PRINT 'error with method Add' RETURN ENDEXEC @hr = sp_OAGetProperty @object, 'Range("A1")', @Range OUT IF @hr <> 0 BEGIN PRINT 'error create Range' RETURN ENDSELECT @indRow = 1 SELECT @off_Row = 0 SELECT @off_Column = 1WHILE (@indRow <= @Rows) BEGIN SELECT @indColumn = 1WHILE (@indColumn <= @Columns) BEGINEXEC @hr = sp_OAMethod @QueryResults, 'GetColumnString', @result_str OUT, @indRow, @indColumn IF @hr <> 0 BEGIN PRINT 'error get GetColumnString' RETURN ENDEXEC @hr = sp_OASetProperty @Range, 'value', @result_str IF @hr <> 0 BEGIN PRINT 'error set value' RETURN ENDEXEC @hr = sp_OAGetProperty @Range, 'Offset', @Range OUT, @off_Row, @off_Column IF @hr <> 0 BEGIN PRINT 'error get Offset' RETURN ENDSELECT @indColumn = @indColumn + 1ENDSELECT @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 ENDENDSELECT @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 ENDEXEC @hr = sp_OAMethod @WorkBook, 'Close' IF @hr <> 0 BEGIN PRINT 'error with method Close' RETURN ENDEXEC @hr = sp_OADestroy @object IF @hr <> 0 BEGIN PRINT 'error destroy Excel.Application' RETURN ENDEXEC @hr = sp_OADestroy @SQLServer IF @hr <> 0 BEGIN PRINT 'error destroy SQLDMO.SQLServer' RETURN END GO作者Blog:http://blog.csdn.net/Terrylee_1218/
MIS 是指 Management Information System,管理信息系统用什么做都行,看你熟悉什么。数据量大小只关系到数据库的选择和维护。
有什么方便的方法吗
里面的字段要求是不是要一样
但是如果EXCEL中的表的格式不规则怎么办呢
请教一下大虾
关键字 导出数据到Excel概述
出处
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:EXEC ExportToExcel @server = '.',
@uname = 'sa',
@QueryText = 'SELECT au_fname FROM pubs..authors',
@filename = 'c:\ImportToExcel.xls'/*
Version: SQL Server 7.0/2000
Created by: Alexander Chigrik
- all about MS SQL
(SQL Server Articles, FAQ, Scripts, Tips and Test Exams).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:EXEC ExportToExcel @server = '.',
@uname = 'sa',
@QueryText = 'SELECT au_fname FROM pubs..authors',
@filename = 'c:\ImportToExcel.xls'
*/IF OBJECT_ID('ExportToExcel') IS NOT NULL DROP PROC ExportToExcel
GOCREATE 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)IF @QueryText IS NULL
BEGIN
PRINT 'Set the query string'
RETURN
END-- Sets the server to the local server
IF @server IS NULL SELECT @server = @@servername-- Sets the username to the current user name
IF @uname IS NULL SELECT @uname = SYSTEM_USERSET NOCOUNT ONEXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @SQLServer OUT
IF @hr <> 0
BEGIN
PRINT 'error create SQLDMO.SQLServer'
RETURN
END-- 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
ENDSELECT @result_str = 'ExecuteWithResults("' + @QueryText + '")'
EXEC @hr = sp_OAMethod @SQLServer, @result_str, @QueryResults OUT
IF @hr <> 0
BEGIN
PRINT 'error with method ExecuteWithResults'
RETURN
ENDEXEC @hr = sp_OAMethod @QueryResults, 'CurrentResultSet', @CurrentResultSet OUT
IF @hr <> 0
BEGIN
PRINT 'error get CurrentResultSet'
RETURN
ENDEXEC @hr = sp_OAMethod @QueryResults, 'Columns', @Columns OUT
IF @hr <> 0
BEGIN
PRINT 'error get Columns'
RETURN
ENDEXEC @hr = sp_OAMethod @QueryResults, 'Rows', @Rows OUT
IF @hr <> 0
BEGIN
PRINT 'error get Rows'
RETURN
ENDEXEC @hr = sp_OACreate 'Excel.Application', @object OUT
IF @hr <> 0
BEGIN
PRINT 'error create Excel.Application'
RETURN
ENDEXEC @hr = sp_OAGetProperty @object, 'WorkBooks', @WorkBooks OUT
IF @hr <> 0
BEGIN
PRINT 'error create WorkBooks'
RETURN
ENDEXEC @hr = sp_OAGetProperty @WorkBooks, 'Add', @WorkBook OUT
IF @hr <> 0
BEGIN
PRINT 'error with method Add'
RETURN
ENDEXEC @hr = sp_OAGetProperty @object, 'Range("A1")', @Range OUT
IF @hr <> 0
BEGIN
PRINT 'error create Range'
RETURN
ENDSELECT @indRow = 1
SELECT @off_Row = 0
SELECT @off_Column = 1WHILE (@indRow <= @Rows)
BEGIN
SELECT @indColumn = 1WHILE (@indColumn <= @Columns)
BEGINEXEC @hr = sp_OAMethod @QueryResults, 'GetColumnString', @result_str OUT, @indRow, @indColumn
IF @hr <> 0
BEGIN
PRINT 'error get GetColumnString'
RETURN
ENDEXEC @hr = sp_OASetProperty @Range, 'value', @result_str
IF @hr <> 0
BEGIN
PRINT 'error set value'
RETURN
ENDEXEC @hr = sp_OAGetProperty @Range, 'Offset', @Range OUT, @off_Row, @off_Column
IF @hr <> 0
BEGIN
PRINT 'error get Offset'
RETURN
ENDSELECT @indColumn = @indColumn + 1ENDSELECT @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
ENDENDSELECT @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
ENDEXEC @hr = sp_OAMethod @WorkBook, 'Close'
IF @hr <> 0
BEGIN
PRINT 'error with method Close'
RETURN
ENDEXEC @hr = sp_OADestroy @object
IF @hr <> 0
BEGIN
PRINT 'error destroy Excel.Application'
RETURN
ENDEXEC @hr = sp_OADestroy @SQLServer
IF @hr <> 0
BEGIN
PRINT 'error destroy SQLDMO.SQLServer'
RETURN
END
GO作者Blog:http://blog.csdn.net/Terrylee_1218/