SQL导出为Excel表
程序员文章站
2022-05-22 13:57:09
...
SQL导出为Excel表 Excel Version: SQL Server 7.0/2000Created by: Alexander Chigrikhttp://www.MSSQLCity.com/ - all about MS SQL(SQL Server Articles, FAQ, Scripts, Tips and Test Exams). This stored procedure can be used to insert the result set
SQL导出为Excel表 ExcelVersion: SQL Server 7.0/2000 Created by: Alexander Chigrik http://www.MSSQLCity.com/ - 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' /* Version: SQL Server 7.0/2000 Created by: Alexander Chigrik http://www.MSSQLCity.com/ - 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 GO 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) 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_USER SET NOCOUNT ON EXEC @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 END 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 EXEC @hr = sp_OAMethod @QueryResults, 'CurrentResultSet', @CurrentResultSet OUT IF @hr 0 BEGIN PRINT 'error get CurrentResultSet' RETURN END EXEC @hr = sp_OAMethod @QueryResults, 'Columns', @Columns OUT IF @hr 0 BEGIN PRINT 'error get Columns' RETURN END EXEC @hr = sp_OAMethod @QueryResults, 'Rows', @Rows OUT IF @hr 0 BEGIN PRINT 'error get Rows' RETURN END EXEC @hr = sp_OACreate 'Excel.Application', @object OUT IF @hr 0 BEGIN PRINT 'error create Excel.Application' RETURN END EXEC @hr = sp_OAGetProperty @object, 'WorkBooks', @WorkBooks OUT IF @hr 0 BEGIN PRINT 'error create WorkBooks' RETURN END EXEC @hr = sp_OAGetProperty @WorkBooks, 'Add', @WorkBook OUT IF @hr 0 BEGIN PRINT 'error with method Add' RETURN END EXEC @hr = sp_OAGetProperty @object, 'Range("A1")', @Range OUT IF @hr 0 BEGIN PRINT 'error create Range' RETURN END SELECT @indRow = 1 SELECT @off_Row = 0 SELECT @off_Column = 1 WHILE (@indRow 0 BEGIN PRINT 'error get GetColumnString' RETURN END EXEC @hr = sp_OASetProperty @Range, 'value', @result_str IF @hr 0 BEGIN PRINT 'error set value' RETURN END EXEC @hr = sp_OAGetProperty @Range, 'Offset', @Range OUT, @off_Row, @off_Column IF @hr 0 BEGIN PRINT 'error get Offset' RETURN END SELECT @indColumn = @indColumn + 1 END 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 END 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 EXEC @hr = sp_OAMethod @WorkBook, 'Close' IF @hr 0 BEGIN PRINT 'error with method Close' RETURN END EXEC @hr = sp_OADestroy @object IF @hr 0 BEGIN PRINT 'error destroy Excel.Application' RETURN END EXEC @hr = sp_OADestroy @SQLServer IF @hr 0 BEGIN PRINT 'error destroy SQLDMO.SQLServer' RETURN END GO
推荐阅读
-
使用Python导出Excel图表以及导出为图片的方法
-
精妙的SQL和SQL SERVER 与ACCESS、EXCEL的数据导入导出转换
-
精妙的SQL和SQL SERVER 与ACCESS、EXCEL的数据导入导出转换
-
SQL Server使用Merge语句当源表数据集为空时,无法进行查询的问题
-
sql2005数据导出方法(使用存储过程导出数据为脚本)
-
用ASP将数据库中的数据直接导出到EXCEL表中
-
Excel表格数据导入数据库users表中,利用excel公式自动生成sql语句方法
-
在Excel 员工信息表中利用条件格式标注进行生日提醒以提前15天为例
-
SQL Server2008导出数据之Excel详细解析
-
使用PHPExcel导出Excel表