Foxtable(狐表)用户栏目专家坐堂 → [原创]使用OpenXml导入导出Excel2007格式的文件,支持100W行上百M的大文件


  共有126850人关注过本帖树形打印复制链接

主题:[原创]使用OpenXml导入导出Excel2007格式的文件,支持100W行上百M的大文件

帅哥哟,离线,有人找我吗?
有点蓝
  1楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:超级版主 帖子:111381 积分:566969 威望:0 精华:9 注册:2015/6/24 9:21:00
[原创]使用OpenXml导入导出Excel2007格式的文件,支持100W行上百M的大文件  发帖心情 Post By:2017/5/9 15:11:00 [只看该作者]

上次共享了一个例子,是关于如何读写大数据的文本文件的。这次我们来解决如何读写大数据的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编辑过]

 回到顶部
帅哥哟,离线,有人找我吗?
狐狸爸爸
  2楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:管理员 帖子:47497 积分:251403 威望:0 精华:91 注册:2008/6/17 17:14:00
  发帖心情 Post By:2017/5/9 15:20:00 [只看该作者]

收藏,学习。

图片点击可在新窗口打开查看

 回到顶部
帅哥哟,离线,有人找我吗?
rjh4078
  3楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:狐精 帖子:3373 积分:24889 威望:0 精华:0 注册:2012/3/26 21:47:00
  发帖心情 Post By:2017/5/9 15:37:00 [只看该作者]

开源的 内置到ft啊

 回到顶部
帅哥哟,离线,有人找我吗?
Hopenight
  4楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:四尾狐 帖子:862 积分:10841 威望:0 精华:0 注册:2016/5/6 14:34:00
  发帖心情 Post By:2017/5/9 22:57:00 [只看该作者]

收藏,太有用了....


 回到顶部
帅哥哟,离线,有人找我吗?
大雪山
  5楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:童狐 帖子:297 积分:2996 威望:0 精华:0 注册:2008/9/29 10:13:00
  发帖心情 Post By:2017/5/9 23:17:00 [只看该作者]

学习


 回到顶部
帅哥哟,离线,有人找我吗?
李小胖
  6楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:童狐 帖子:292 积分:2516 威望:0 精华:0 注册:2017/1/8 15:53:00
  发帖心情 Post By:2017/5/10 10:22:00 [只看该作者]

留下脚印,打数据导入

 回到顶部
帅哥哟,离线,有人找我吗?
wei0769
  7楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:八尾狐 帖子:1824 积分:19564 威望:0 精华:0 注册:2013/4/10 14:38:00
  发帖心情 Post By:2017/5/10 10:44:00 [只看该作者]

加入到foxtabel中,最大好处可以随时看帮助


 回到顶部
帅哥哟,离线,有人找我吗?
zhy400137
  8楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:五尾狐 帖子:1133 积分:8227 威望:0 精华:0 注册:2013/11/3 19:00:00
  发帖心情 Post By:2017/5/22 19:50:00 [只看该作者]

留下记好

 回到顶部
帅哥哟,离线,有人找我吗?
gua12gua
  9楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:一尾狐 帖子:450 积分:4521 威望:0 精华:0 注册:2013/12/17 12:55:00
  发帖心情 Post By:2017/5/22 20:23:00 [只看该作者]

ddddddddd

 回到顶部
帅哥哟,离线,有人找我吗?
blackzhu
  10楼 | QQ | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信 一级勋章
等级:狐仙 帖子:9879 积分:57640 威望:0 精华:15 注册:2008/9/1 9:45:00
  发帖心情 Post By:2017/5/23 8:51:00 [只看该作者]

学习

 回到顶部
总数 145 1 2 3 4 5 6 7 8 9 10 下一页 ..15