/*
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_OA