CN101231665A - 将数据库中数据导出成Excel文件的装置及其方法 - Google Patents

Abstract

本发明公开了一种将数据库中数据导出成Excel文件的装置及其方法，其中该方法包括：步骤一，将数据库的数据导出成文本文件；步骤二，按每次设定的数据量读取文本文件的数据，对每次读取的文本文件的数据进行格式转换，并将生成的Excel文件写入磁盘；步骤三，对Excel文件进行修复，生成供Excel程序读取和打开的文件。本发明在将数据库中的数据转换成Excel文件时极大地减少了内存占用量，使得转换的效率也比现有技术有较大的提高。

Description

将数据库中数据导出成Excel文件的装置及其方法

技术领域

本发明涉及数据处理技术，特别是涉及一种将数据库中数据导出成Excel文件的装置及其方法。

背景技术

如今的社会属于信息***的时代，每天都在产生大量的信息，这些信息大部分都存放在数据库中，然而存放在数据库中的信息属于原始信息，不能很方便地从这些杂乱的原始信息中获取有用的信息，那么此时就需要工具将这些原始信息组织起来，以比较直观的方式将它们显示出来，而比较常用的工具就是Excel等日常办公软件。

目前，网络上有好几种开源的软件(比如Poi，Jxl)能直接操纵(读、写)Excel文件，但这些软件限于Excel文件的组成格式，都有它们的缺点，那就是当数据量大时，极耗内存。下面通过了解Excel文件的组成格式，描述极耗内存的原因。

Excel文件是基于BIFF(Binary Interchange File Format，二进制文件格式)格式的。在BIFF格式文件数据的二进制存储中，低位8字节存放在地址的低位，高位8字节存放在地址的高位，因此在用十六进制编辑器来查看Excel文件时，对于一个完整的双字节，它的显示如：09 08，那么它的十六进制值应该为0x0809。整个Excel文件的分块如表1所示。

表1

Excel Header区	占1block(512 bytes)，固定大小
		WorkBook区	最小占8个block(4096 bytes)
Summary Information区	占8个block(4096 bytes)，固定大小
		DocumentSummary Information区	占8个block(4096 bytes)，固定大小
RootEntry区	占1个block(512 bytes)，固定大小

在表1所示的Excel文件分块表中：

1)Excel文件包含一个WorkBook，一个WorkBook可以包含一个或多个WorkSheet。

2)在Excel文件的数据结构中，每512bytes称为一个block(块)，而Excel文件是由整数个block来组成的，因此Excel文件的大小也均为512的倍数，并且对每个数据block，必须有一个指针(blockPointer)指示其在文件中的位置，这些blockPointer是集中存放在Excel文件的开头或者尾部。存放blockPointer的block叫指针block(pointerBlock)，由于指针block块的数目可能很多，所以Excel文件采用了两级指针的方式，即还需要一段数据区存储指向指针block的指针(pointerBlockPointer，第二级指针)，存放第二级指针的block称为扩展指针block(extendPointerBlock)。

3)Excel文件中每条完整的数据是由一个标识、长度和数据内容来体现，称为一个记录体(Record)，如某条数据显示09 08 02 00 00 00，那么09 08代表这条完整数据的标识，02 00代表后面内容的长度，而00 00为实际的内容数据。

4)Excel文件必需有一个Excel Header区，这个区占1个block大小，放在Excel文件的开头位置，这个区主要内容有：整个Excel文件的block数，及1 09个pointerBlockPointer位置等，具体组成可参见表2。

5)Excel文件对整个工作薄WorkBook必须有一个描述区域，该描述区域放在WorkSheet表数据之前，在Excel Header区之后，其中包括Excel文件中每个WorkSheet表的表名及其在整个文件中的偏移量、单元格的格式、默认字体、国家等内容，其中，最重要的就是每个WorkSheet表在整个文件中的偏移量，一个WorkBook最少要占8个Block大小的区域。

6)Excel文件对每个WorkSheet表开始之前，也必须有一个描述区域，这个描述区域放在单元格数据开始之前，其中存放有这个WorkSheet表中行数、列数、以及每32行数据开始的位置到每个WorkSheet表开始的位置的偏移量等信息，每张WorkSheet表最多可以存放达65536行的数据。

7)Excel文件每次对每32行数据(称为RowBlcok)打包进行存储，在对32行中每行中有数据的单元格存储之后，必须记录这32行中每行开始到这32行开始的偏移量。

8)Excel文件结尾至少有1个block的RootEntry总结区。

由上述的Excel文件描述，可见要生成一个Excel文件，必须事先知道Excel文件的大小，目前所有的开源***在生成Excel文件时，都是将所有的数据放入内存中，等到程序关闭时，再计算内存中Excel文件的行数、列数，并生成Excel文件的block指针和其它所有信息，然后一次写入到磁盘，形成Excel文件，所以当数据量一大，所有的数据都保存在内存中，就会占用非常大的内存空间。这种缺点在以Java语言开发的***中会被放大，这是因为Java语言对内存处理技术与别的语言不同，Java对内存的管理是由jvm(Java虚拟机)实现，并不是由程序员控制的，一旦某个线程接受一个从数据库中数据导出成Excel文件的命令，而数据库中数据量又非常大，则可能会导致这个线程因占用资源过多而直接被中断掉，从而导致导出命令不成功，这在每天产生大量数据的***(如网管***的数据统计分析)中比较常见。

发明内容

本发明所要解决的技术问题在于提供一种将数据库中数据导出成Excel文件的装置及其方法，用于现有技术中在生成Excel文件时极耗内存和对大量数据处理时的速度较慢的问题。

为了实现上述目的，本发明提供了一种将数据库中数据导出成Excel文件的装置，其特征在于，包括：

文本文件导出模块，用于将数据库的数据导出成文本文件；

文本文件读取模块，连接所述文本文件导出模块，用于按每次设定的数据量读取所述文本文件的数据；

数据格式转换模块，连接所述文本文件读取模块，用于对每次读取的所述文本文件的数据进行格式转换，并将生成的Excel文件写入磁盘；

Excel文件修复模块，连接所述数据格式转换模块，用于以随机读写文件的方式对所述Excel文件进行修复，生成供Excel程序读取和打开的文件。

所述的将数据库中数据导出成Excel文件的装置，其中，所述文本文件导出模块通过数据库程序将所述数据导出成所述文本文件。

所述的将数据库中数据导出成Excel文件的装置，其中，所述文本文件读取模块按每次1024字节的数据量读取所述文本文件的数据，并通过统计读取所述文本文件的数据时在内存中的换行符的个数获取所述文本文件的行数，及分析行数的第一行获取所述文本文件的列数。

所述的将数据库中数据导出成Excel文件的装置，其中，所述数据格式转换模块根据所述文本文件的行数和列数构造所述Excel文件的Excel Header区内容和WorkBook的描述区内容，再将每次读取的32行所述文本文件的数据转换成所述Excel文件的WorkSheet表数据，并当转换完成后对所述Excel文件的Summary Information区和Document Summary Information区写入内容，得到所述Excel文件。

所述的将数据库中数据导出成Excel文件的装置，其中，所述数据格式转换模块根据每列所述文本文件的数据的格式，将所述文本文件的数据转换成相应类型的单元格：

当格式为整数类型时，将所述文本文件的数据转换成Excel整数类型的单元格；或

当格式为浮点类型时，将所述文本文件的数据转换成Excel浮点类型的单元格；或

当格式为其他类型时，将所述文本文件的数据转换成字符串类型的单元格。

为了实现上述目的，本发明还提供了一种将数据库中数据导出成Excel文件的方法，其特征在于，包括：

步骤一，将数据库的数据导出成文本文件；

步骤二，按每次设定的数据量读取所述文本文件的数据，对每次读取的所述文本文件的数据进行格式转换，并将生成的Excel文件写入磁盘；

步骤三，对所述Excel文件进行修复，生成供Excel程序读取和打开的文件。

所述的将数据库中数据导出成Excel文件的方法，其中，所述步骤一中，进一步包括：

通过数据库程序将所述数据导出成所述文本文件的步骤。

所述的将数据库中数据导出成Excel文件的方法，其中，所述步骤二中，进一步包括：

按每次1024字节的数据量读取所述文本文件的数据，并通过统计读取所述文本文件的数据时在内存中的换行符的个数获取所述文本文件的行数，及分析行数的第一行获取所述文本文件的列数的步骤。

所述的将数据库中数据导出成Excel文件的方法，其中，所述步骤二中，进一步包括：

根据所述文本文件的行数和列数构造所述Excel文件的Excel Header区内容和WorkBook的描述区内容，再将每次读取的32行所述文本文件的数据转换成所述Excel文件的WorkSheet表数据，并当转换完成后对所述Excel文件的Summary Information区和Document Summary Information区写入内容，得到所述Excel文件。

所述的将数据库中数据导出成Excel文件的方法，其中，所述步骤二中，进一步包括：

当将每次读取的32行所述文本文件的数据转换成所述Excel文件的WorkSheet表数据后，修改所述WorkBook的描述区中BoundSheet单元格中的WorkSheet偏移量的步骤。

本发明的有益技术效果：

与现有技术相比，本发明在将数据库中的数据转换成Excel文件时，每次只转换了少量的数据，除了需要每次存放输入1024Byte的字节数组和几个相同长度的辅助字节数组外，不再占用内存，不需要在内存保存所有需要转换的数据，极大地减少了内存占用量，同时在转换过程中通过文本文件导出模块和文本文件读取模块将对数据库中的数据操作转化成对文件数据的操作，在对文件操作时，采用了IO读取优化技术，使得转换的效率也比现有技术有较大的提高。

以下结合附图和具体实施例对本发明进行详细描述，但不作为对本发明的限定。

附图说明

图1是本发明将数据库中数据导出成Excel文件的装置结构图；

图2是本发明将数据库中数据导出成Excel文件的实施流程图；

图3是本发明快速读取文本文件的实施流程图；

图4是本发明数据格式转换的实施流程图；

图5是本发明Excel文件修复的实施流程图。

具体实施方式

下面结合附图和具体实施方式对本发明的技术方案作进一步更详细的描述。

如图1所示，是本发明数据库中数据导出成Excel文件的装置结构图。该装置100包括：文本文件导出模块10、文本文件读取模块20、数据格式转换模块30、Excel文件修复模块40。

文本文件导出模块10，用于将数据库的数据导出成文本文件；这种导出是由数据库程序完成的，所以速度比在程序中得到结果快上很多。

文本文件读取模块20，连接文本文件导出模块10，用于读取文本文件的数据。

文本文件读取模块20每次都读取1024个字节的数据，并存放在缓存中，数据格式转换模块30在读取数据时，实际上是从缓存中取得文本文件的数据，当缓存的中数据被读取完之后，文本文件读取模块20再次读取1024个字节存入缓存；此外，文本文件读取模块20提供文本文件中行数和列数。

数据格式转换模块30，连接文本文件读取模块20，用于通过由文本文件数据格式到Excel文件数据格式的转换，将文本文件的数据转换成Excel文件，Excel文件为二进制文件。

Excel文件修复模块40，连接数据格式转换模块30，用于在数据格式转换完成之后，对转换完成之后的Excel文件进行修复，生成供Excel软件(或程序)正确的读取和打开的文件。

Excel文件修复模块40采用文件随机读写技术，将Excel文件修复成可以被Excel软件读取和打开的文件。

通过装置100在转换Excel文件使得耗费内存极少。

如图2所示，是本发明将数据库中的数据导出成Excel文件的实施流程图。该流程是将数据库中的数据转换成Excel文件的过程，是每次从输入流中读取一定量的数据，将这些数据转换成Excel文件格式，然后写入存储介质(磁盘)，这样可以保证转换时只占用极少的内存，转换完成之后，再利用随机读写文件的方式对存储介质(磁盘)上Excel文件进行修改，使Excel软件能正确将内容读取出来。流程的步骤具体是：

步骤S201，将数据库中的数据导出成文本文件；

步骤S202，快速获取文本文件的数据；

步骤S203，对文本文件的数据进行数据格式转换，生成Excel文件；

步骤S204，判断转换是否已完成，若是，则进入步骤S205；否则，转至步骤S203继续格式转换；

步骤S205，对Excel文件进行文件修复。

要实现文本文件的数据到Excel文件的转换，就需要了解Excel文件的二进制内容组成，Excel文件的二进制内容主要组成是一些Record，包括WorkBook的Record及WorkSheet表的Record。下面将分别列出这些Record。

其中，WorkBook描述区的主要组成Record：

a1)BOF(标识：0809H)，WorkBook的开始Record。

a2)Window1(标识：003DH)，文档窗口高度、宽度等的一些设置。

a3)Font(标识：0031H)，字体设置，每个WorkBook至少需要4个FontRecord。

a4)XF(标识：00E0H)，单元格、行、列等的设置，每个WorkBook至少需要21个XF Record。

a5)Style(标识：0293H)，单元格的显示样式设置。

a6)BoundSheet(标识：0085H)，每个WorkSheet表在WorkBook中的表示记录体，包括WorkSheet表的名字、在文件中的偏移量等信息。

a7)EOF(标识：000AH)，WorkBook结束记录。

其中，WorkSheet表的主要组成Record：

b1)BOF(标识：0809H)，WorkSheet表的开始标记；

b2)Dimensions(标识：0200H)，表中被使用的第一行和最后一行及被使用的第一列和最后列指示记录体。

b3)Row(标识：0208H)，行数据记录体(包括列数据)。

b4)DBCELL(标识：00D7)，记录每行的相关信息，包括某行在RowBlock中的偏移量等信息。

b5)Window2(标识：023EH)，指示哪些行、列可见。

b6)EOF(标识000AH)，WorkSheet表的结束标记。

为了能让Excel软件正确读取用流的方式转换的二进制文件，对ExcelHeader区和RootEntry区的实现不能错一个字节，否则就会读取失败，这两个区是整个Excel文件中最重要的部分，首先描述Excel Header区的构成。

Excel Header区是由512个字节组成，表2给出这些字节的组成。该表2中Excel Header区组成表如下：

表2

位置	作用
		0-43	Excel软件相关信息，固定不变
44-47	表示blockPointer所占block的个数
		48-51	RootEntry区的起始位置，以block为单位。
52-67	固定不变信息
		68-71	指示extendPointerBlock起始位置
72-75	指示extendPointerBlock的数目
		76-511	存放109个PointerblockPointer

Excel Header区中除固定不变的信息外，其它信息都需要在转换完成之后，用随机读写方式进行修改。

RootEntry区也是由512个字节组成，详细的字节组成如表3所示。表3中RootEntry区的组成表如下：

表3

位置	作用
		0-127	Root Entry的名字等信息，一般不变。
128-255	WorkBook名字，及WorkBook起始位置，以block为单位。
		256-377	Summary Information区的名字，及这个区的起始位置，以block为单位。
378-511	Document Summary Information区的名字，及这个区的起始位置，以block为单位。

由于在将文本文件的数据转换成Excel文件的过程中，需要根据数据block的数目来生成block指针的信息，所以下面根据实际数据所占用的block数目(dataBlock)来推导最后需要的totalBlock。

每个数据block需要一个4Byte的指针来指示，所以需要指针block数为pointerBlock，该pointerBlock采用公式一计算：

pointerBlock＝(dataBlock+127)/(512/4)    (公式一)

为了容纳这些数据block指针，这些指针block也需要一个指针来指示(第二级指针)，由于在Excel Header区中预留有109个位置来存放第二级指针，所以还需要扩展block数为extendPointerBlock，该extendPointerBlock采用公式二计算：

extendPointerBlock＝(pointerBlock-109+127)/(512/4)    (公式二)

故实际数据为dataBlock，总共需要totalBlock个存储空间，该totalBlock采用公式三计算：

totalBlock＝dataBlock+pointerBlock+extendPointerBlock  (公式三)

如图3所示，是本发明快速读取文本文件的实现流程图，在该流程中，由文本文件读取模块20快速读取文本文件，参见如下步骤：

步骤S301，获取文本文件的行数和列数。

该步骤中，通过每次读取1024Byte的数据，在内存中统计换行符的个数来得到行数，由于文本文件的数据是从数据库中获得，每一行的列数都是相同的，所以列数只要分析第一行就可以得到。

步骤S302，为数据格式提供每次32行数据的输入。

该步骤中，文本文件读取模块20需要为数据格式转换模块30提供输入，即从文件头部开始，每次提供32行数据，在形成行数据时，也需要采用每次读取1024 Byte的方法，在内存中进行列数据划分。

该步骤中，如果返回的数据不足32行，则文件读取完毕。

在该流程中，不能通过每次读取一行方法来获取行数，这样做启动I/O操作次数太多，速度太慢。

如图4所示，是本发明数据格式转换的实施流程图。该流程具体包括：

步骤S401，首先，构造Excel文件的Excel Header区的内容。

该步骤中，根据文本文件读取模块20中获得的数据的行数和列数来构造Excel Header区的内容。其中，Excel Header区的内容可以参照上述表2中的说明进行构造。

步骤S402，构造Excel文件的WorkBook的描述区内容。

该步骤中，根据文本文件读取模块20获得的数据的行数和列数来构造WorkBook的描述区内容。其中，WorkBook的描述区包括主要组成Record。

该步骤中，在构造WorkBook的描述区内容时，根据行数和列数构造BoundSheet Record，其它Record可以固定不变。

该步骤中，WorkBook的描述区内容必须包括WorkSheet表的数目，一个WorkSheet表只能存储65536行数据，可以根据文本文件的行数计算出需要的WorkSheet表数，从而正确构造出WorkBook的描述区内容。

在构造WorkBook的描述区内容的同时，需要记录WorkBook的描述区的BoundSheet Record的位置。

步骤S403，构造WorkSheet表的开始数据，记录当前WorkSheet表距离文件开始的偏移量。

该步骤中，在每写完一个WorkSheet表数据之后，都需要修改BoundSheetRecord中的WorkSheet偏移量。

步骤S404，从文本文件中读取32行数据，将32行中每列数据转换成Excel文件的一个单元格Record。

该步骤中，每次读取32行文本文件的数据转换成Excel二进制文件。

该步骤中，如果WorkSheet表是一张新的WorkSheet表，首先构造WorkSheet表的描述区内容。

根据WorkSheet表的描述区的主要组成Record来构造WorkSheet表的描述区内容，其中主要是WorkSheet表的行数、列数及一些格式方面的信息，并记录WorkSheet表到WorkBook的起始位置的偏移量；然后，每次读取32行数据的转换成WorkSheet表数据。

该步骤中，转换的时候需要分析每列数据格式，如果此列数据是整数类型，则转换成Excel整数类型单元格Record，其Record名字是RK；如果此列数据是浮点类型的数据，则转换成Excel浮点类型的单元格Record，其Record名字是Number；其它数据类型统一转换成字符串类型的Record，其名字是Label。

步骤S405，判断是否已转换65535行，若是，即已经从文本文件中读取了65536行数据，则写WorkSheet结束Record(EOF)，然后重新开始构造新的WorkSheet数据，进入步骤S406。否则，返回至步骤S404。

步骤S406，判断所有数据是否已经转换完毕，若是，进入步骤S407；否则返回至步骤S403。

步骤S407，在转换完文本文件的数据之后，需要写Summary Information区和Document Summary Information区的内容。

该步骤中，这两个区占16个block的空间，且对内容没有限制，故可将这16个block用0填充即可。

如图5所示，是本发明Excel文件修复的实施流程图。在将文本数据格式转换成的Excel文件数据格式后，此时的Excel文件还不能被Excel软件正确读取，需要对其进行修复。该过程具体包括：

步骤S501，首先，计算dataBlock；

该步骤中，根据文件指针的当前偏移量来计算dataBlock，见公式四；

dataBlock＝文件指针偏移量/512    (公式四)

如果有余数，则dataBlock需要加1。

步骤S502，然后，根据公式一、二、三计算需要扩展的pointerBlock、extendPointerBlock，和需要的总totalBlock；根据totalBlock构造RootEntry区的内容。

步骤S503，写blockPointer，写PointerblockPointer信息。

该步骤中，根据pointerBlock数目，在pointerBlock中写block指针信息；如果pointerBlock大于109个，则需要再增加extendPointerBlock个block，并在Excel Header区和extendPointerBlock个block中写PointerblockPointer(第二级指针)信息；

步骤S504，写RootEntry区的内容。

步骤S505，最后，将文件指针移到文件头部Excel Header区，修改ExcelHeader区的各个需要修改的字节信息。

本发明提供了一种与平台无关的、高效的、耗内存极少的将数据库中的数据导出成Excel文件的数据处理装置及其方法，以在实际应用中将数据库中查询的结果数据以流的方式导出成Excel文件，不需要Excel软件的支持，其克服了目前所有其它生成Excel文件的软件极耗内存的缺点和对大量数据处理时的速度较慢的问题。

当然，本发明还可有其他多种实施例，在不背离本发明精神及其实质的情况下，熟悉本领域的技术人员当可根据本发明作出各种相应的改变和变形，但这些相应的改变和变形都应属于本发明所附的权利要求的保护范围。

Claims (10)

1.一种将数据库中数据导出成Excel文件的装置，其特征在于，包括：

文本文件导出模块，用于将数据库的数据导出成文本文件；

文本文件读取模块，连接所述文本文件导出模块，用于按每次设定的数据量读取所述文本文件的数据；

数据格式转换模块，连接所述文本文件读取模块，用于对每次读取的所述文本文件的数据进行格式转换，并将生成的Excel文件写入存储介质；

Excel文件修复模块，连接所述数据格式转换模块，用于以随机读写文件的方式对所述Excel文件进行修复，生成供Excel程序读取和打开的文件。

2.根据权利要求1所述的将数据库中数据导出成Excel文件的装置，其特征在于，所述文本文件导出模块通过数据库程序将所述数据导出成所述文本文件。

3.根据权利要求1或2所述的将数据库中数据导出成Excel文件的装置，其特征在于，所述文本文件读取模块按每次1024字节的数据量读取所述文本文件的数据，并通过统计读取所述文本文件的数据时在内存中的换行符的个数获取所述文本文件的行数，及分析行数的第一行获取所述文本文件的列数。

4.根据权利要求3所述的将数据库中数据导出成Excel文件的装置，其特征在于，所述数据格式转换模块根据所述文本文件的行数和列数构造所述Excel文件的Excel Header区内容和WorkBook的描述区内容，再将每次读取的32行所述文本文件的数据转换成所述Excel文件的WorkSheet表数据，并当转换完成后对所述Excel文件的Summary Information区和DocumentSummary Information区写入内容，得到所述Excel文件。

5.根据权利要求4所述的将数据库中数据导出成Excel文件的装置，其特征在于，所述数据格式转换模块根据每列所述文本文件的数据的格式，将所述文本文件的数据转换成相应类型的单元格：

当格式为整数类型时，将所述文本文件的数据转换成Excel整数类型的单元格；或

当格式为浮点类型时，将所述文本文件的数据转换成Excel浮点类型的单元格；或

当格式为其他类型时，将所述文本文件的数据转换成字符串类型的单元格。

6.一种将数据库中数据导出成Excel文件的方法，其特征在于，包括：

步骤一，将数据库的数据导出成文本文件；

步骤二，按每次设定的数据量读取所述文本文件的数据，对每次读取的所述文本文件的数据进行格式转换，并将生成的Excel文件写入存储介质；

步骤三，对所述Excel文件进行修复，生成供Excel程序读取和打开的文件。

7.根据权利要求6所述的将数据库中数据导出成Excel文件的方法，其特征在于，所述步骤一中，进一步包括：

通过数据库程序将所述数据导出成所述文本文件的步骤。

8.根据权利要求6或7所述的将数据库中数据导出成Excel文件的方法，其特征在于，所述步骤二中，进一步包括：

按每次1024字节的数据量读取所述文本文件的数据，并通过统计读取所述文本文件的数据时在内存中的换行符的个数获取所述文本文件的行数，及分析行数的第一行获取所述文本文件的列数的步骤。

9.根据权利要求8所述的将数据库中数据导出成Excel文件的方法，其特征在于，所述步骤二中，进一步包括：

根据所述文本文件的行数和列数构造所述Excel文件的Excel Header区内容和WorkBook的描述区内容，再将每次读取的32行所述文本文件的数据转换成所述Excel文件的WorkSheet表数据，并当转换完成后对所述Excel文件的Summary Information区和Document Summary Information区写入内容，得到所述Excel文件。

10.根据权利要求9所述的将数据库中数据导出成Excel文件的方法，其特征在于，所述步骤二中，进一步包括：

当将每次读取的32行所述文本文件的数据转换成所述Excel文件的WorkSheet表数据后，修改所述WorkBook的描述区中BoundSheet单元格中的WorkSheet偏移量的步骤。

Images