Java批量操作Excel文件实践(java 大批量数据excel导出)

createh52个月前 (02-01)技术教程17

前言 | 问题背景

在操作Excel的场景中,通常会有一些针对Excel的批量操作,批量的意思一般有两种:

对批量的Excel文件进行操作。如导入多个Excel文件,并处理数据,或导出多个Excel文件。这类场景,往往操作很相似,但是要反复读写Excel文件。对单个或复数个进行批量操作。如对Excel文件,进行批量替换文本,批量添加公式或者批量增加样式。这类场景,一般需要操作的Excel文件不多,但是需要反复执行特定操作,这种时候需要有易用的API来帮忙。

现有的Excel组件中,POI是非常常用的组件,但是针对上述不同的场景,其分别会对组件提出两类要求。

第一类场景会反复读取或者写入文件,需要组件对于内存有足够好的优化,否则很容易出现内存溢出(out of memory)的问题。

第二类场景则需要组件提供易用的API,例如替换字符串,如果没有查找(find)或者替换(replace)的接口API。则需要自己遍历单元格(cell)来查找值。

虽然POI在上面两种要求上可能会有欠缺,但还有其他的组件可以选择,比如EasyExcel,GcExcel等。

下面是以GcExcel为例,对上述两类场景,分别列举的例子。

场景1 批量导入Excel文件,并读取特定区域的数据

例如有多个Excel文件,名字都是GUID。这些Excel文件来自于填报的数据,需要对其中的内容进行汇总。

如Excel的表单内容如下图:

需要对B3到C6的格子进行取值,可以用下面的代码提取数据。

@Test
public void testImportFormFile() {
String folderPath = "path/testFolder"; //使用你的路径
File folder = new File(folderPath);
File[] files = folder.listFiles();
if (files != null) {
for (File file: files){
if(file.isFile() && file.getName().endsWith(".xlsx")){
Workbook wb = new Workbook();
wb.open(file.getAbsolutePath());
Object[][] value = (Object[][]) wb.getActiveSheet().getRange("B3:C6").getValue();
System.out.println(value[0][1]); //小葡萄
System.out.println(value[1][1]); //20.0
System.out.println(value[2][1]); //开发部
System.out.println(value[3][1]); //610123456789012345
//添加处理数据的逻辑
}
}
}
}

通过listFiles()方法,获取所有的Excel文件。循环读取每一个文件,通过GcExcel打开Excel文件。使用IRange上的getValue()方法可以把Excel中的格子以二维数组的方式读取出来。

之后就可以通过访问二维数组来处理业务逻辑。

场景2 批量导出Excel文件,导出前把数据写在特定位置

继续以第一个Excel文件为例子,当在数据库中已经存有一些数据,希望把数据写入并导出到复数个Excel文件里或者导出为PDF文件。

真实的场景有,如企业发放工资,每个月需要给每一位员工发放一份电子版的工资单,因为每个员工的工资单信息不相同,这个场景下,则需要把数据批量导出为复数个PDF。

@Test
public void testExportFormFile() {
String outPutPath = "E:/testFolder";
//给valueList初始化数据,替换为从数据库,CSV或者JSON等中获取数据。
ArrayList valueList = new ArrayList();
for (Object[][] value : valueList) {
Workbook wb = new Workbook();
wb.getActiveSheet().getRange("B3:C6").setValue(value);
wb.save(outPutPath + UUID.randomUUID().toString() + ".xlsx");
}
}

GcExcel可以直接把二维数组设置给一个range,从数据库中把数据加载出来以后,可以整理成二维数组。

之后通过GcExcel的SetValue()把二维数组直接设置到sheet上,最后通过工作簿(workbook)上的save方法保存导出。

场景3 打开Excel文件,批量替换关键字

在这个场景中,需要把Excel文件作为模板,把其中的一些自定义关键字,替换成数据。

比如在有一个制式的报表,需要把数据填写进去。例如表头,姓名,报表相关的条目,数据等信息。可能会把报表制作成一个模板,之后把表头,姓名等位置留空,或者用关键字作为占位符。例如“%Name%”可以作为名字的占位符,在填写数据的时候,可以对%Name%进行替换。

@Test
public void testReplaceTemplateFile() {
String templateFilePath = "test.xlsx";
Workbook wb = new Workbook();
wb.open(templateFilePath);
IRange usedRange = wb.getActiveSheet().getUsedRange();
//load data
ArrayList valueList = new ArrayList();
for (Object[] value : valueList) {
usedRange.replace(value[0],value[1]);
}
wb.save("result.xlsx", SaveFileFormat.Xlsx);
}

通过工作簿(workbook)打开模板(template)文件,准备好数据以后,直接通过IRange的replace方法替换自定义的关键字。

替换完之后,保存为新的Excel即可。

对于更高级复杂的数据填充,GcExcel也有模板功能,设置好模板后,可以直接绑定数据源,GcExcel会自动填充数据导模板里。

场景4 打开Excel模板文件,批量获取计算结果

例如有一个Excel文件,用于计算保险或者行业数据。需要在固定的位置填入值,使用Excel中的公式计算结果。

@Test
public void testCalcFormulaByTemplateFile() {
String templateFilePath = "E:/testFolder/testFormula.xlsx";
Workbook wb = new Workbook();
wb.open(templateFilePath);
ArrayList valueList = new ArrayList();
for (Object[] value : valueList) {
//获取特定的值,比如以下
Object A1Value = value[0];
Object A2Value = value[1];
Object result = null;
wb.getActiveSheet().getRange("A1").setValue(A1Value);
wb.getActiveSheet().getRange("A2").setValue(A2Value);
result = wb.getActiveSheet().getRange("A3").getValue();
System.out.println(result);
}
}

GcExcel的公式计算是在取值的时候计算的,因此不需要显示调用calculate之类的方法,只需要把输入的参数准备好,放在Excel特定的cell中,就可以直接获取公式的计算结果了。

以上就是一些常见的批量处理Excel的方法,仅是用GcExcel Java的代码为例,同样的思路也可以使用其他的组件来实现。

相关文章

【干货】如何使用Java实现百万数据的Excel导出功能?

Java作为一种常用的编程语言,在实现大量数据导出功能时具有很高的效率和可扩展性。本文将介绍如何使用Java实现百万数据的Excel导出功能。一、需求分析在很多实际应用场景中,我们需要将大量数据导出到...

盘点三种Excel转SHP文件的方法(附练习数据下载)

概述数据的获取渠道是多种多样的,获取的数据格式也是多种多样,作为一名GISer,需要熟练掌握各种格式的数据之间的转换,例如本文要介绍的Excel格式的数据,经常会遇到,如果需要转换为SHP格式应该怎么...

【Java技巧】高效数据传输:Java通过绑定快速将数据导出至Excel

前言把数据导出至 Excel 是很常见的需求,而数据的持久化,往往又放在数据库中。因此把数据库中的数据导出到 Excel中,成了非常普遍的一个需求。以关系型数据库为例,数据表是一个二维矩阵,但是为了易...

如何在SpringBoot中实现Excel数据导出功能?

在一些企业级的应用中,对于数据导出成Excel表格的需求是一个非常常见的需求,在SpringBoot中,我们可以借助于Apache POI库来实现这个需求,下面我们就来详细介绍一下如何在Spring...

原来用hutool导入导出Excel这么丝滑!

1. hutool 工具介绍unset这个我就不用过多介绍了,它是一个非常好用的 Java 工具类库。我们在日常工作中用到的工具类,他都有。官网:https://plus.hutool.cn/ uns...

用的处理excel导入导出的Java类库和工具包有哪些

常用的处理excel导入导出的Java类库和工具包有以下几种:Apache POI:一款流行的用于读写Microsoft Office格式文件的Java库。它能够读写Excel、Word和PowerP...