[向海爷提问]使用sp_OACreate建Excel对象后进程不释放
/*Author : 梁嘉辉Date : 2009-06-15Function : 分Sheet导出同一Excel工作薄Comment : 使用或转载请保留此信息*/---------------------------------创建存储过程-------------------------------USE tempdbGO--创建Excel文件CREATE PROC dbo.usp_CreateExcelFile @ExcelPath nvarchar(1024), --Excel文件路径 @strErrorMessage VARCHAR(1000) OUTPUT --输出错误信息AS SET NOCOUNT ON; DECLARE @hr INT; DECLARE @objExcel INT; DECLARE @objWorkBooks INT; DECLARE @objWorkBook INT; DECLARE @cmd NVARCHAR(4000); SET @strErrorMessage = ''; --创建Excel.Application对象 EXEC @hr = sp_OACreate 'Excel.Application',@objExcel OUTPUT; IF @hr = 0 BEGIN --创建WorkBooks对象 EXEC @hr = sp_OAGetProperty @objExcel,'Workbooks',@objWorkbooks OUTPUT; IF @hr = 0 BEGIN --使用Workbooks对象的Add添加一个Workbook EXEC @hr = sp_OAGetProperty @objWorkbooks,'Add',@objWorkBook OUTPUT; IF @hr = 0 BEGIN --使用SaveAs方法保存 SET @cmd = 'SaveAs("' + @ExcelPath + '")' EXEC @hr = sp_OAMethod @objWorkBook,@cmd; --关闭掉Workbook IF @hr = 0 EXEC @hr = sp_OAMethod @objWorkBook,'Close'; ELSE SET @strErrorMessage = '保存Excel文件失败!'; END ELSE SET @strErrorMessage = '添加工作薄失败!'; END ELSE SET @strErrorMessage = '创建工作薄失败!'; END ELSE SET @strErrorMessage = '创建Excel对象失败!' IF @hr = 0 BEGIN EXEC @hr = sp_OAMethod @objExcel,'Quit'; END --消除Excel对象 IF @hr = 0 EXEC @hr = sp_OADestroy @objWorkbooks; IF @hr = 0 EXEC @hr = sp_OADestroy @objExcel;GO--在Excel里添加SheetCREATE PROC dbo.usp_AddExcelSheet @ExcelPath nvarchar(1024), --Excel文件路径 @SheetName sysname, --Sheet名字 @IndexCount INT, --一共要生成多少个Sheet @columns VARCHAR(1000), --Sheet的列名,用逗号分隔 @strErrorMessage VARCHAR(1000) OUTPUTAS SET NOCOUNT ON DECLARE @hr INT; DECLARE @objExcel INT; DECLARE @objWorkBooks INT; DECLARE @objWorkBook INT; DECLARE @objSheets INT; DECLARE @objSheet INT; DECLARE @cmd NVARCHAR(4000); DECLARE @i INT; DECLARE @id INT; DECLARE @col VARCHAR(256); SET @strErrorMessage = ''; --创建Excel.Application对象 EXEC @hr = sp_OACreate 'Excel.Application',@objExcel OUTPUT; IF @hr = 0 BEGIN --创建Workbooks对象 EXEC @hr = sp_OAGetProperty @objExcel,'Workbooks',@objWorkbooks OUTPUT; IF @hr = 0 BEGIN --打开Excel文件 SET @cmd = 'Open("' + @ExcelPath + '")'; EXEC @hr = sp_OAMethod @objWorkbooks,@cmd,@objWorkBook OUTPUT; IF @hr = 0 BEGIN --得到Sheets对象 EXEC @hr = sp_OAGetProperty @objWorkbook,'Sheets',@objSheets OUTPUT; SET @i = 1; --分解字段名 DECLARE @tb TABLE(id int identity,col varchar(256)); INSERT @tb(col) SELECT B.x.value('.','varchar(256)') AS col FROM ( SELECT CONVERT(XML,'<v>'+REPLACE(@columns,',','</v><v>')+'</v>') AS col ) AS A CROSS APPLY A.col.nodes('//v') AS B(x); --循环多少个Sheet WHILE @i <= @IndexCount BEGIN --添加Sheet EXEC @hr = sp_OAGetProperty @objSheets,'Add',@objSheet OUTPUT; SET @cmd = @SheetName + RTRIM(@i); --设置Sheet的Name属性,形式为@SheetName + 序号 EXEC @hr = sp_OASetProperty @objSheet,'Name',@cmd; SET @id = (SELECT TOP(1) id FROM @tb ORDER BY id); --循环所有的列,将Sheet的第一行设置为列号 WHILE @id IS NOT NULL BEGIN SET @col = (SELECT col FROM @tb WHERE id=@id); SET @cmd = 'Range("' + CHAR(65-1+@id) + '1").value'; EXEC @hr = sp_OASetProperty @objSheet,@cmd,@col; SET @id = (SELECT TOP(1) id FROM @tb WHERE id > @id ORDER BY id); END SET @i = @i +1; END IF @hr = 0 EXEC @hr = sp_OAMethod @objWorkBook,'Save'; IF @hr = 0 EXEC @hr = sp_OAMethod @objWorkBook,'Close'; ELSE SET @strErrorMessage = '保存Excel文件失败!'; END ELSE SET @strErrorMessage = '打开工作薄失败!'; END ELSE SET @strErrorMessage = '创建工作薄对象失败!'; END ELSE SET @strErrorMessage = '创建Excel对象失败!' IF @hr = 0 EXEC @hr = sp_OAMethod @objExcel,'Quit'; --消除Excel对象 IF @hr = 0 EXEC @hr = sp_OADestroy @objWorkbooks; IF @hr = 0 EXEC @hr = sp_OADestroy @objExcel;GO--导出Excel主存储过程CREATE PROC dbo.usp_ExportTableToExcel @ExcelPath nvarchar(1024), --Excel文件路径 @DatabaseName sysname, --导出Excel数据所在的数据库 @TableName sysname, --导出Excel数据的表 @Columns nvarchar(4000) = N'*', --导出哪些列,默认为所有列 @PageRecord INT, --每个Sheet的记录数 @IsCreate BIT = 1 --1表示创建新的Excel文件,0表示在源Excel文件中追加SheetAS SET NOCOUNT ON; DECLARE @IsExcelExist INT; DECLARE @strErrorMessage VARCHAR(1000); DECLARE @cmd NVARCHAR(4000); --如果没有对@ExcelPath参数设置值,则退出 IF @ExcelPath IS NULL OR @ExcelPath = '' BEGIN RAISERROR('必须设置Excel文件路径!',16,1); RETURN -1; END --判断数据库是否存在 IF DB_ID(@DatabaseName) IS NULL BEGIN SET @cmd = '数据库' + @DatabaseName + '不存在!'; RAISERROR(@cmd,16,1); RETURN -1; END --判断数据表是否存在 SET @cmd = @DatabaseName + '.dbo.' + @TableName; IF OBJECT_ID(@cmd,'U') IS NULL BEGIN SET @cmd = '表' + @TableName + '不存在'; RAISERROR(@cmd,16,1); RETURN -1; END --启用ole自动化和xp_cmdshell EXEC sp_configure 'show advanced options',1; RECONFIGURE WITH OVERRIDE; EXEC sp_configure 'Ole Automation Procedures',1; RECONFIGURE WITH OVERRIDE; EXEC sp_configure 'xp_cmdshell',1; RECONFIGURE WITH OVERRIDE; EXEC sp_configure 'Ad Hoc Distributed Queries',1; RECONFIGURE WITH OVERRIDE; --判断Excel文件是否存在 EXEC xp_fileexist @ExcelPath,@IsExcelExist OUTPUT; --如果设置了创建新的Excel文件 IF @IsCreate = 1 BEGIN --文件已存在,则先删除 IF @IsExcelExist = 1 BEGIN SET @cmd = N'del ' + @ExcelPath ; EXEC master.dbo.xp_cmdshell @cmd,NO_OUTPUT; END --创建Excel文件 SET @strErrorMessage = ''; EXEC dbo.usp_CreateExcelFile @ExcelPath,@strErrorMessage OUTPUT; END ELSE --不是创建新的Excel文件,则要判断文件是否存在 BEGIN IF @IsExcelExist = 0 BEGIN RAISERROR('文件不存在!',16,1); RETURN -1; END END IF @strErrorMessage <> '' --创建Excel.Workbook里出错 BEGIN RAISERROR(@strErrorMessage,16,1); RETURN -1; END --分页,计算出一共有几页 --页数=总记录数/每页记录数 + 如果总记录数%每页记录数<>0,则1 DECLARE @RecordCount INT; SET @cmd = N'SELECT @RecordCount=COUNT(*) FROM ' + @DatabaseName + '.dbo.' + @TableName; EXEC sp_executesql @cmd,N'@RecordCount INT OUTPUT',@RecordCount OUTPUT; DECLARE @page INT; DECLARE @i INT; SET @page = @RecordCount / @PageRecord; SET @page = @page + CASE WHEN @RecordCount % @PageRecord = 0 THEN 0 ELSE 1 END; --如果@Columns参数为*,表示查所以的列,则先通过系统视图得到所有列名 IF @Columns = '*' BEGIN SET @Columns = ''; SET @cmd = N'SELECT @columns = @columns + '','' + c.name FROM ' + @DatabaseName + '.sys.tables AS t JOIN ' + @DatabaseName + '.sys.columns AS c ON t.object_id=c.object_id WHERE t.name=''' + @TableName + ''''; EXEC sp_executesql @cmd,N'@columns VARCHAR(1000) OUTPUT',@columns OUTPUT END SET @Columns = STUFF(@Columns,1,1,''); SET @strErrorMessage = ''; --添加Sheet EXEC dbo.usp_AddExcelSheet @ExcelPath,@TableName,@page,@Columns,@strErrorMessage OUTPUT; IF @strErrorMessage <> '' --添加Excel.Sheet时出错 BEGIN RAISERROR(@strErrorMessage,16,1); RETURN -1; END SET @i = 1; --循环每一页,将记录插入到该页的Sheet里 WHILE @i <= @page BEGIN SET @cmd = 'INSERT INTO OPENROWSET(''Microsoft.Jet.OleDb.4.0'',''Excel 8.0;Database=' + @ExcelPath + ''',[' + @TableName + RTRIM(@i) + '$])'; SET @cmd = @cmd + ' SELECT ' + @Columns + ' FROM ( SELECT '+@Columns + ',rowid=ROW_NUMBER() OVER(ORDER BY GETDATE()) FROM ' + @DatabaseName + '.dbo.' + @TableName + ' ) AS T WHERE rowid BETWEEN ' + RTRIM((@i-1) * @pageRecord + 1) + ' AND ' + RTRIM(@i * @pageRecord); EXEC(@cmd); SET @i = @i + 1; END RETURN 0GO