专家坐堂-[原创]使用OpenXml导入导出Excel2007格式的文件，支持100W行上百M的大文件Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌握的快速软件开发工具!

-- [原创]使用OpenXml导入导出Excel2007格式的文件，支持100W行上百M的大文件
上次共享了一个例子，是关于如何读写大数据的文本文件的。这次我们来解决如何读写大数据的Excel文档。

本例特点如下：

1、仅支持Excel2007以上格式的Excel文档，即后缀为“.xlsx”的文档。xlsx文档支持最多1048576行16384列的数据。

2、使用微软开源的Open Xml SDK 2.0操作Excel文档。系统无需安装ms Office、wps Office等软件都可以导入导出Excel文档。

3、系统需要.net 4.0支持，win10及以上系统自带。XP、win7需要下载并安装.net 4.0，win7建议安装.net 4.52。

4、支持以流的方式读写Excel文档，内存占用小。

5、读写速度比使用COM的方式慢，小文件还是建议使用Foxtable默认的导入导出功能。

6、支持混合的单元格类型数据导入，比如Excel某列的单元格类型既有字符型，又有数值型，可以统一按照字符型进行导入。

7、支持多个表的数据导入同一个Excel的同一个Sheet中，即使是不同的列和类型，不过标题暂时只能显示第一个表的标题。

8、支持多个表的数据导入同一个Excel的不同Sheet中。

9、导入导出数据均支持选择指定名称的列。

10、从Excel导入支持选择开始导入的行和导入多少行数据，比如可以从Excel文档的第100行开始导入500行内容，同时还可以选择导出的列。

11、提供2种操作Excel文档的方式，DOM方式和SAX方式：

DOM方式：需要加载全部的Excel数据，组件内存占用比较大，实测40~50M的Excel文档加载就会出现内存溢出的错误；不过速度比SAX方式稍快

SAX方式：以流的方式操作Excel数据，组件本身内存占用非常小，大概在50M左右。

12、Excel文档的第一行必须为标题行，否则可能出错。

13、目前不支持格式、合并等处理。

14、目前仅支持标准的字符型、数值型、逻辑型和日期型数据。

如果有其它类型数据测试不能操作的，可以回复上传Excel文档做分析。

使用方法：

1、把2个组件：DocumentFormat.OpenXml.dll和ExOpenXml.dll拷贝到Foxtable的安装目录下，并在项目中添加引用ExOpenXml.dll

下载信息 [文件大小：下载次数： ]
点击浏览该文件:openxml组件.zip

ExOpenXml.dll为对DocumentFormat.OpenXml.dll的二次封装，绕过了许多官方示例的陷阱，提供了几个导入导出函数方便使用。以后根据使用反馈在决定是否增加其它功能。

调用分为3个步骤：

一、定义一个文档操作对象ExOXExecl，并打开一个Excel文档，如果文档不存在，则创建

Dim _execl As New ExOpenXml.ExOXExecl()

_execl.Open("d:\\simple.xlsx")

Open语法：Open(FilePath,IsReadOnly)

FilePath	文档完整路径，如果不存在，则会新建。如果文档被其它程序打开，则会提示无法打开。
IsReadOnly	可选参数，逻辑型。是否以只读方式打开文档。不填默认为false，打开的文档在关闭前会先保存。

除了ToExecl以外，其它函数使用建议都把IsReadOnly设置为true，不然关闭文档的时候就会再保存一次，产生不必要的开销。WriteToExecl因为是以流的方式操作的，关闭流后也就保存了文档，所以也不需要再保存一次。

二、调用操作函数，传入适当的参数，如导出DataTable数据到Excel

_execl.ToExecl(CurrentTable.DataTable.Basetable)

三、操作完毕，关闭文档。一定要关闭文档，不然可能无法保存，并且一直保持对文档的占用，直到程序关闭

_execl.Dispose()

2、操作函数说明：

1、DataTable导出到Excel，DOM方式

语法：
ToExecl(DataTable, SheetName, isAppend, Columns)

DataTable	需要导出数据的表格。不是Foxtable的DataTable，而是System.Data.DataTable类型。Foxtable中即是DataTable.BaseTable
SheetName	可选参数，字符型，导出后的表的名称。不填取DataTable的名称，如果DataTable没有名称，则按顺序默认取Sheet1、Sheet2、......
isAppend	可选参数，逻辑型，追加模式。不填默认为true，即把数据追加到同名的Sheet中。如果设置为false，则会清除同名的Sheet内容，再导入新的数据。
Columns	可选参数，列名称字符型数组。可以指定DataTable中需要导出数据的列名称。

2、DataTable导出到Excel，SAX方式

语法：
WriteToExecl(DataTable, SheetName, isAppend, Columns)

参数用法同上

3、从Excel获取数据返回，DOM方式

语法：
ToDataTable(SheetName, Columns, StartIndex, Length) as DataTable

SheetName	可选参数，字符型，导入的Sheet名称。不填默认取第一个Sheet
Columns	可选参数，列名称字符型数组。可以指定Sheet中需要导入数据的标题名称。默认第一行为标题行
StartIndex	可选参数，从此行索引开始取数据，行索引从0开始，对应DataTable的第一行，文档的第二行，文档第三行为1，以此类推。不填默认为0
Length	可选参数，从指定行索引开始提取此参数指定的行数的数据，填0或者不填默认从指定行索引开始取所有行

如果没有数据，则返回nothing，否则返回一个新的System.Data.DataTable对象

4、从Excel提取数据追加到Datatable中，DOM方式

语法：
AppendToDataTable(DataTable，SheetName, Columns, StartIndex, Length)

DataTable	接收数据的DataTable，System.Data.DataTable类型。数据追加时忽略列的名称和Columns参数以及Sheet的标题不一致的列数据
SheetName	可选参数，字符型，导入的Sheet名称。不填默认取第一个Sheet
Columns	可选参数，列名称字符型数组。可以指定Sheet中需要导入数据的标题名称。默认第一行为标题行
StartIndex	可选参数，从此行索引开始取数据，行索引从0开始，对应DataTable的第一行，文档的第二行，文档第三行为1，以此类推。不填默认为0
Length	可选参数，从指定行索引开始提取此参数指定的行数的数据，填0或者不填默认从指定行索引开始取所有行

5、从Excel获取数据返回，SAX方式

语法：
ReadToDataTable(SheetName, Columns, StartIndex, Length) as DataTable

参数和返回值同ToDataTable

6、从Excel提取数据追加到Datatable中，SAX方式

语法：
ReadToDataTable(DataTable，SheetName, Columns, StartIndex, Length)

参数和返回值同AppendToDataTable

注意5、6的函数方法名称虽然一样，但是用法不一样。5的用法会返回一个新的DataTable对象，而6的用法则在已有的DataTable中追加数据。

测试例子：

下载信息 [文件大小：下载次数： ]
点击浏览该文件:使用openxml导入导出excel2007格式.zip

以下是一些测试数据，只是随便测试，未必准确，仅作参考，一般用户无需理会

以下内容只有回复后才可以浏览

2021-11-17

1、改正了表格数据有一些xml不支持的控制字符时导出出错的问题

需要注意的是，组件已经更新为.net 4.0的版本

2019-09-17

1、修正导入日期格式的一个bug

2017-10-25

1、修正文件名为中文名的错误

2、修正日期列的判断异常

重新下载组件复制到狐表安装目录即可

[此贴子已经被作者于2021/11/17 16:29:40编辑过]