Java批量操作Excel文件实践

发布时间：2023/06/28 16:06 发布者：Leo

返回博客中心

前言 | 问题背景

在处理Excel的场景中，通常需要进行一些批量操作。批量操作可以分为两种情况：

针对多个Excel文件进行操作，例如导入多个Excel文件并处理数据，或者导出多个Excel文件。这种情况下，操作步骤通常相似，但需要反复读写Excel文件。
针对单个或多个Excel文件进行批量操作，例如批量替换文本、批量添加公式或样式。在这种情况下，所需操作的Excel文件数量不多，但需要重复执行特定操作，因此需要提供易于使用的API。

目前，POI是一种非常常用的Excel组件，但针对上述两种场景，对组件有不同的要求：

对于第一种场景，由于需要反复读写文件，组件需要对内存进行优化，以防止出现内存溢出的问题。
对于第二种场景，需要组件提供易于使用的API。例如，如果没有提供查找（find）或替换（replace）的接口API，就需要自己遍历单元格（cell）来查找值。

POI在上述两个要求上存在着一些不足，好在还有其他选择的组件，如EasyExcel和GcExcel for Java。

以下以GcExcel为例，分别列举了上述两种场景的示例。

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

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

Excel的表单内容如下图：

A screenshot of a computer

我们需要对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打开它们。然后，可以使用`getValue()`方法从IRange对象中以二维数组的形式读取Excel中的单元格数据。接下来，可以通过访问这个二维数组来处理业务逻辑。

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

在以第一个Excel文件为例的情况下，假设已经在数据库中有一些数据，并且希望将这些数据写入并导出到多个Excel文件或导出为PDF文件。

在实际场景中，例如企业发放工资，每个月需要为每位员工生成一份电子版工资单。由于每位员工的工资单信息不同，因此需要将数据批量导出为多个PDF文件。

@Test
public void testExportFormFile() {
    String outPutPath = "E:/testFolder";
    //给valueList初始化数据，替换为从数据库，CSV或者JSON等中获取数据。
    ArrayList<Object[][]> valueList = new ArrayList<Object[][]>();
    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<Object[]> valueList = new ArrayList<Object[]>();
    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<Object[]> valueList = new ArrayList<Object[]>();
    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 for Java的代码为例，同样的思路也可以使用其他的组件来实现。

GcExcel | 下载试用
GrapeCity Documents for Excel （简称：GcExcel）是一款基于 Java 平台的服务端高性能表格组件，可与纯前端表格控件 SpreadJS 前后端兼容，无需依赖 Office、POI 或第三方应用软件，在前端展示电子表格数据，在服务端批量创建、加载、编辑、打印、导入/导出 Excel 文档，为您开发的应用程序提供在线文档的前后端数据同步、在线填报与服务端批量导出与打印，以及类 Excel 报表模板设计与服务端高性能处理等一整套类 Excel 全栈解决方案。