每N条记录一个Worksheet,导出同一个Workbook中

 

/*

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('.','nvarchar(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 + CASE WHEN @IndexCount = 1

                                                                     THEN ''

                                                                  ELSE RTRIM(@i) END;

                         --设置SheetName属性,形式为@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;

                                     EXEC @hr = sp_OADestroy @objSheet;

                                 END

                               EXEC @hr = sp_OADestroy @objSheets;

                               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

    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主存储过程

CREATE PROC dbo.usp_ExportTableToExcel

    @ExcelVersion int = 2007,          --Excel版本

    @ExcelPath nvarchar(1024),         --Excel文件路径

    @DatabaseName sysname,             --导出Excel数据所在的数据库

    @TableName sysname,                --导出Excel数据的表

    @Columns nvarchar(4000) = N'*',    --导出哪些列,默认为所有列

    @whereAnd nvarchar(4000) = '',     --过滤条件,形式为: and col = 'xx' and col2 > 1

    @PageRecord INT = 65535,           --每个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,

    DECLARE @RecordCount INT;

    SET @cmd = N'SELECT @RecordCount=COUNT(*) FROM ' + @DatabaseName + '.dbo.' + @TableName;

    SET @cmd = @cmd + ' WHERE 1 = 1 ' + @whereAnd;

    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

          SET @Columns = STUFF(@Columns,1,1,'');

      END

   

    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('''+

                       CASE WHEN @ExcelVersion = 2007 THEN 'Microsoft.Ace.OleDb.12.0'

                            ELSE 'Microsoft.Jet.OleDb.4.0' END

                      + ''',''' + CASE WHEN @ExcelVersion = 2007 THEN 'Excel 12.0'

                                       ELSE 'Excel 8.0' END

                      + ';HDR=YES;Database=' + @ExcelPath

                      + ''',''SELECT * FROM [' + @TableName + CASE WHEN @page = 1 THEN ''

                                                   ELSE RTRIM(@i) END + '$]'')';

          SET @cmd =  @cmd + ' SELECT ' + @Columns + '

                              FROM (

                                  SELECT '+@Columns + ',rowid=ROW_NUMBER() OVER(ORDER BY (SELECT 1))

                                  FROM ' + @DatabaseName + '.dbo.' + @TableName

                               + ' WHERE 1 = 1 ' + @whereAnd + '

                              ) AS T

                              WHERE rowid BETWEEN ' + RTRIM((@i-1) * @pageRecord + 1)

                                     + ' AND ' + RTRIM(@i * @pageRecord);

          EXEC(@cmd);

         

          SET @i = @i + 1;

      END

    RETURN 0

GO

 

-----------------------------------------------------------------------

-----------------------------------------------------------------------

 

--测试导出Excel

 

--生成万条测试数据

 

CREATE TABLE tb(id INT IDENTITY,date DATETIME,dzbz NUMERIC(2,1),fssd NUMERIC(2,1),dzxh VARCHAR(6),ylxh INT,com VARCHAR(4))

 

INSERT tb(date,dzbz,fssd,dzxh,ylxh,com)

    SELECT TOP(100) --这里设置多少行

        DATEADD(hour,ROW_NUMBER() OVER(ORDER BY o.object_id)/24,'2009-05-01') AS Date,

        (ABS(CHECKSUM(NEWID())) % 5 + 4) / 10. AS dzbz,

        (ABS(CHECKSUM(NEWID())) % 3 + 12) / 10. AS fssd,

        'DZ_00' + RTRIM(CASE WHEN ROW_NUMBER() OVER(ORDER BY o.object_id) % 4 = 0

                          THEN 4 ELSE ROW_NUMBER() OVER(ORDER BY o.object_id) % 4 END) AS dzxh,

        CASE WHEN ROW_NUMBER() OVER(ORDER BY o.object_id) % 2 = 0 THEN 1 ELSE 0 END AS ylxh,

        CASE WHEN ((ROW_NUMBER() OVER(ORDER BY o.object_id)-1) / 4 % 4) %2 = 0

                THEN 'com1' ELSE 'com2' END AS com

    FROM sys.objects AS o

 

 

--执行主存储过程,导出Excel

EXEC dbo.usp_ExportTableToExcel

         2007,                    --Excel版本

         N'G:/Book1.xlsx',         --导出的Excel文件路径

         'tempdb',                --表所在的数据库

         'tb',                    --要导出哪个表

         '*',                     --导出哪些列

         '',                      --过滤条件

         10,                    --每个Sheet多少条记录

         1;                       --创建文件

 

GO

--删除测试

DROP PROC dbo.usp_ExportTableToExcel,dbo.usp_CreateExcelFile,

         dbo.usp_AddExcelSheet;

DROP TABLE tb;

 

评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值