[向海爷提问]使用sp_OACreate建Excel对象后进程不释放

[liangCK]

/*
Author      : 梁嘉辉
Date        : 2009-06-15
Function    : 分Sheet导出同一Excel工作薄

Comment     : 使用或转载请保留此信息
*/

-------------------------------------------------------------------
--创建存储过程
-------------------------------------------------------------------

USE tempdb
GO

--创建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里添加Sheet
CREATE PROC dbo.usp_AddExcelSheet
    @ExcelPath nvarchar(1024),             --Excel文件路径
    @SheetName sysname,                    --Sheet名字
    @IndexCount INT,                       --一共要生成多少个Sheet
    @columns VARCHAR(1000),                --Sheet的列名,用逗号分隔
    @strErrorMessage VARCHAR(1000) OUTPUT
AS
    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文件中追加Sheet
AS
    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 0
GO

[nettman]

收藏！

[jiangnantian]

http://www.kacin.cn/index2.asp?id=15329

[jiangnantian]

http://www.kacin.cn/index2.asp?id=15329
有奖知识问答,赢取QQ币等丰富奖品!

[qjq300]

收藏！

[wdp_bj]

收藏！

[showmerr]

UP

[cis111]

微软的东西的互操作性 真不错。。。。

[dksame3236956]

有点看不懂了~~~呵呵

[fuxiaoyang13]

关注！！！

[mingyicz]

mark

[tanxiaolei2009]

看不懂！

[lovezx1028]

来学习的啦。。。

[bzhyan]

学习 ！！

[fuxiaoyang13]

学习学习了！

[changjiangzhibin]

路过学习

[nettman]

学习！

[scy251147]

[cookies10wen]

很好很强大

[zzxap]

梁家辉

[一开始的回忆]

Excel十万条数据怎么装入的？不是最多3W多么？