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

最新推荐文章于 2024-06-24 10:57:10 发布
最新推荐文章于 2024-06-24 10:57:10 发布
阅读量2.3k 收藏 7
点赞数
分类专栏： 数据库开发管理 文章标签： excel output cmd insert 数据库 date
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/liangCK/article/details/4274390
版权

 

/*

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;

                         --设置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;

                                     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;

 

liangCK
关注
  • 0
    点赞
  • 踩
  • 7
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
Oracle 导出 多个sheet,怎么将超过65000条的记录从ACCESS自动导出到多个EXCEL2003sheet中？（只导出EXCEL2003格式）...
weixin_42517623的博客
04-08 1645
EXCEL中使用powerpivot 处理ORACLE等等的数据，通常几百万行,并且对其进行数据分析的速度就像处理分析几百行数据一样快.要不把整个数据库的数据弄进出，然后切片机处理。-----------------------------------------------------------------------------------------------------------...
C#导出到EXCEL(worksheet)
在等一个可能
04-28 4460
    string saveFileName = @"e:/xx.xls";   Listlist=daochuInfo.selExcel();//数据源   if (list.Count==0)       return;    bool fileSaved=false;    Excel.Application xlApp=new Excel.Application();    if(xlA
table表格导出excel时，同一个cell中设置不同的字体颜色
最新发布
weixin_45907435的博客
06-24 409
worksheet.getColumn(Number(index) + 2).width = width * 2 // 考虑到字符宽度与字符数的比例，可能需要乘以某个系数。const worksheet = workbook.addWorksheet('Sheet1') // 创建一个工作表 // 添加表头。{ text: '这是颜色', font: { color: { argb: 'FF0000' } } }, // 红色文字。{ text: '设置的文字' } // 默认颜色文字。
利用Exceljs/file-saver实现纯前端的同一个sheet页实现导出多个表格，拼接
LY__lyxka的博客
04-15 1946
vue版纯前端实现导出（同一个sheet页导出多个表格）- 组合1.安装依赖2.引入依赖3.导出测试4.效果 1.安装依赖 npm install exceljs --save npm install file-saver --save 2.引入依赖 import FileSaver from "file-saver"; const ExcelJS = require("exceljs"); 3.导出测试 // 导出测试 onExcels() { let baseData = [ ["
Excel中使用VBA将每个表分别导出为单独的工作簿
但老师
11-03 3694
需求描述 一份工作簿里面有5个表 将工作表1导出来成为一个单独的工作簿,命名1.xlsx 将工作表2导出来成为一个单独的工作簿,命名2.xlsx 将工作表3导出来成为一个单独的工作簿,命名3.xlsx 将工作表4导出来成为一个单独的工作簿,命名4.xlsx 将工作表5导出来成为一个单独的工作簿,命名5.xlsx VBA Sub outputSheetAsWorkbook() Dim Wkb As Workbook Dim Sht As Worksheet Dim strPath
React实现一个excel文件导出
caizhuangzhuang的博客
07-06 499
------------------------------------
JAVA POI导出Execl表格生成多个sheet的实现
qq_40083897的博客
04-30 4010
接上一篇文章链接：SpringBoot集成POI导出Execl表格之统一工具类。补中了一个Execl表格根据数量生成多个sheet（比如我们有10万条数据导出Execl表格，且不说会不会内存溢出问题，一个表格存这个多数据要下拉框也要拉很长时间） 解决：我们可以在一个Execl表格中创建多个sheet分别来存放这些数据，这样就解决了一些问题，也方便查看。 效果如下： 具体的jar包依赖等，...
python 导出数据到excel 中，一个好用的导出数据到excel模块，XlsxWriter
weixin_30877181的博客
12-27 744
最近公司有项目需要导出数据到excel，首先想到了，tablib，xlwt，xlrd,xlwings，win32com[还可以操作word],openpyxl,等模块但是 实际操作中tablib 写入数据速度慢，操作简单，支持xlsx，[文件生成较大]，貌似一次最多写入65536条行xlwt　　写入速度比tablib 约快一倍，一次最大写入行数65536条，不支持xlsx 后缀文件，...
Excel中3个workbook区别
qq_29860591的博客
03-23 998
第一种：HSSFWorkbook poi导出excel最常用的方式；但是此种方式的局限就是导出的行数至多为65535行，超出65536条后系统就会报错。此方式因为行数不足七万行所以一般不会发生内存不足的情况（OOM）。 第二种：XSSFWorkbook 这种形式的出现是为了突破HSSFWorkbook的65535行局限。其对应的是excel2007(1048576行，16384列)扩展名...
多个润乾报表，导出到一个excel中的不同sheet页中
03-20
2. **Excel工作簿与工作表**：在Excel中，一个工作簿（Workbook）是包含多个工作表（Worksheet）的文件，每个工作表都是独立的数据表，可以通过不同的sheet标签进行区分。 3. **数据导出**：润乾报表支持将报表数据...
c# 在同一个worksheet中生成表格和数据
10-31
本篇文章将详细讲解如何利用C#语言和Apose.Cells控件在同一个worksheet中生成表格和数据，以及如何通过Visual Studio 2005进行开发。 首先，Apose.Cells是一款强大的.NET组件，它允许开发者无需Microsoft Office...
C#导出一个数据集到Excel
10-29
在提供的"ExportToExcel"文件中，可能包含了上述代码的实现，或者是一个可以直接使用的类库，帮助开发者快速实现数据集到Excel的导出功能。如果你有具体的代码或问题，可以进一步分析和解决。总之，理解和掌握这个...
EPPlus 的一个导入导出例子
04-04
在这个"EPPlus的一个导入导出例子"中，我们可以学习如何在Visual Studio 2015环境下使用EPPlus库进行Excel文件的导入和导出操作。 首先，你需要在项目中引用EPPlus库。这可以通过NuGet包管理器完成，搜索“EPPlus”...
C# 导出Excel 多个Sheet 和 单个Sheet
热门推荐
智障侠的博客
07-17 1万+
在工作中，会遇到这样的需求，选取某一年，导出选择年份的月度、季度或者年度报表，月度报表不可能产生12个单独的Excel，这样的话客户可能会和你拼命，将12个月份分为12个Sheet放在一个Excel里面，可能客户会请你吃饭。 目前的做法是根据选择的年份，将该年份所有的数据全部获取到，根据表的类型（几个Sheet）分为多个DataTable放到DataSet里面，接着根据DataSet.Table...
SQL 2005修改系统表
10-07 7139
  SQL 2005 修改系统表方法。 在 SQL Server 2000 中修改系统表的方法大部分人都知道，介绍如何在 SQL 2005 中修改系统表的资料目前还比较少，虽然微软不赞成修改系统表，而且也把修改系统表的代码隐藏起来了。但微软一贯喜欢给自己留个小后门。我是这么想的：最先知道如何修改 SQL 2005 系统表的人只有微软 SQL Server 2005 的设计、开发者。于是就到
SQL Express自动备份
11-16 5550
--第一步--在master库中建立一个备份数据库的存储过程.USE master GOCREATE PROC p      @db_name sysname,         --数据库名      @bk_path NVARCHAR(1024)     --备份文件的路径 AS      IF ISNULL(@db_name,)=          
SSSI导EXCEL数据常见问题一
09-15 4789
在使用SQL SERVER将EXCEL导入到数据库中的时候.经常会发生一些问题.会让我们有些感觉到莫名其妙.今天在论坛上.一个朋友在使用SSIS将EXCEL导入到SQL SERVER的数据库内的时候.发生了这个错误:Excel 源 [1]] 错误: 输出“Excel 源输出”(9) 上的输出列“Description 3”(546) 出错。返回的列状态是:“文本被截断，或者一个或多个字符在
SQL 2005加密数据方法
10-10 4116
--Author: liangCK 小梁 --示例一,使用证书加密数据.  --建立测试数据表  CREATE TABLE tb(ID int IDENTITY(1,1),data varbinary(8000)); GO --建立证书一,该证书使用数据库主密钥来加密 CREATE CERTIFICATE Cert_Demo1  WITH    SUBJ
devexpress 导出多个grid 到同一个excel
09-24
要将多个DevExpress Grid控件的内容导出到同一个Excel文件中，你可以按照以下步骤进行操作： 1. 首先，根据需要，将多个DevExpress Grid控件绑定到不同的数据源，确保每个Grid控件显示的数据不同。 2. 创建一个空...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值