Java批量操作Excel文件实践

原创
2023/07/05 15:33
阅读数 221
AI总结

摘要:本文由葡萄城技术团队于OSCHINA原创并首发。转载请注明出处:葡萄城官网,葡萄城为开发者提供专业的开发工具、解决方案和服务,赋能开发者。

前言 | 问题背景

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

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

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

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

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

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

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

什么是GcExcel?

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

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

如Excel的表单内容如下图:

A screenshot of a computer Description automatically generated

需要对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<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 Java的代码为例,同样的思路也可以使用其他的组件来实现。

扩展链接:

GrapeCity Documents for Excel(服务端Excel组件)V3.0 正式发布

用它来开发“在线Excel”系统,竟如此简单!

如何使用JavaScript实现前端导入和导出excel文件

展开阅读全文
加载中
点击引领话题📣 发布并加入讨论🔥
0 评论
1 收藏
0
分享
AI总结
返回顶部
顶部