Java读写Excel原来这么简单

前言

相信现在很多搞后端的同学大部分做的都是后台管理系统，那么管理系统就肯定免不了 Excel 的导出导入功能，今天我们就来介绍一下 Java 如何实现 Excel 的导入导出功能。

Java领域解析，生成Excel比较有名的框架有Apache poi,Jxl等，但他们都存在一个严重的问题就是非常的耗内存，如果你的系统并发量不大的话可能还行，但是一旦并发上来后一定会OOM或者JVM频繁的full gc.

EasyExcel是阿里巴巴开源的一个excel处理框架，以使用简单，节省内存著称，今天我们来使用阿里巴巴开源的EasyExcel框架来实现Excel的导入导出功能。

官方文档：EasyExcel

本文主要有以下几个知识点：

• 从Excel读取数据
• 导出数据到Excel
• Excel模板填充

正文

首先第一步得先导入EasyExcel的Jar包

<dependency>
　　<groupId>com.alibaba</groupId>
　　<artifactId>easyexcel</artifactId>
　　<version>2.2.4</version>
</dependency>

<!--xls-->
<dependency>
　　<groupId>org.apache.poi</groupId>
　　<artifactId>poi</artifactId>
　　<version>3.17</version>
</dependency>
<dependency>
　　<groupId>org.apache.poi</groupId>
　　<artifactId>poi-ooxml</artifactId>
　　<version>3.17</version>
</dependency>

导出数据到Excel.

接下来看看如何导出数据到到Excel中，有两种写法，一种是不创建对象的写入，另一种是根据对象写入。

- 不创建对象的写入

@SpringBootTest
class Tests {
/*
 * 不创建对象的写
 */
 @Test
 public void test() {
 // 生成Excel路径
 String fileName = "C:\\Users\\likun\\Desktop\\测试.xlsx";
        EasyExcel.write(fileName).head(head()).sheet("模板").doWrite(dataList());
    }
    
     private List<List<String>> head() {
      List<List<String>> list = new ArrayList<>();
      List<String> head0 = new ArrayList<>();
      head0.add("姓名");
      List<String> head1 = new ArrayList<>();
      head1.add("年龄");
      List<String> head2 = new ArrayList<>();
      head2.add("生日");
      list.add(head0);
      list.add(head1);
      list.add(head2);
      return list;
  }
  
    private List<List<Object>> dataList() {
        List<List<Object>> list = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            List<Object> data = new ArrayList<>();
            data.add("张三");
            data.add(25);
            data.add(new Date());
            list.add(data);
        }
        return list;
    }
}

代码很简单，核心就一句代码：

EasyExcel.write(fileName).head(head()).sheet("模板").doWrite(dataList());

head()用来放表头数据，dataList()用来放每一行的数据。

看下效果图：

如果想设置自动列宽可以这样子：

EasyExcel.write(fileName).head(head()).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
        .sheet("模板").doWrite(dataList());

效果图：

- 根据对象写入

接下来是根据对象导入Excel，首先我们要定义一个对象：

@Data
public class User {

    @ExcelProperty("姓名")
    private String name;
    
    @ExcelProperty("性别")
    private String sex;
    
    @ExcelProperty("年龄")
    private Integer age;
    
    @ExcelProperty("身份证")
    private String cardid;
}

使用@ExcelProperty注解来指定标题名称

@SpringBootTest
class Tests {

 @Test
 public void test() {
      // 生成Excel路径
      String fileName = "C:\\Users\\likun\\Desktop\\测试.xlsx";
      EasyExcel.write(fileName, User.class).sheet("模板").doWrite(data());
    }
    
    private List<User> data() {
        List<User> userList = new ArrayList<>();
        User user;
        for (int i = 1; i <= 10; i++) {
            user = new User();
            user.setName("张三" + i);
            user.setSex("男");
            user.setAge(i);
            user.setCardid("440582xxxx");
            userList.add(user);
        }
        return userList;
    }
}

使用对象导出数据也是很简单，只要doWrite方法传入我们的对象集合就可以了。

效果图：

忽略字段

如果对象里面有些字段我们并不想导出到Excel中，只要使用@ExcelIgnore注解就可以了：

/*
 忽略这个字段
*/ 
@ExcelIgnore 
private String filed;

写入指定的列

如果我们想导出数据到指定的列中该如何设置呢？

@Data
public class User {

    @ExcelProperty(value = "姓名", index = 0)
    private String name;
    
    @ExcelProperty(value = "性别", index = 1)
    private String sex;
    
    @ExcelProperty(value = "年龄", index = 2)
    private Integer age;
    
    @ExcelProperty(value = "身份证", index = 4)
    private String cardid;
}

@ExcelProperty的index可以指定导出的列索引，来看下效果图：

复杂头写入

很多时候Excel里会有很多复杂的表头，那么如何实现呢？

@Data
public class User {
    @ExcelProperty("姓名")
    private String name;
    
    @ExcelProperty("性别")
    private String sex;
    
    @ExcelProperty("年龄")
    private Integer age;
    
    @ExcelProperty("身份证")
    private String cardid;
    
    @ExcelProperty({"普通高等学校全日制教育", "学历"})
    private String kultur;
    
    @ExcelProperty({"普通高等学校全日制教育", "学位"})
    private String degree;
    
    @ExcelProperty({"普通高等学校全日制教育", "专业"})
    private String major;
    
    @ExcelProperty({"普通高等学校全日制教育", "获得学历时间"})
    private String graduatetime;
    
    @ExcelProperty({"普通高等学校全日制教育", "毕业院校"})
    private String school;
}

很简单不再细说，直接来看效果图：

写入到模板

我们上面都是生成新的数据写到Excel，如果说现在有一个模板文件，就像下面这种：

模板文件里面已经有一条数据了，那我们怎么在后面添加数据呢？

其实很简单：

String templateName = "C:\\Users\\likun\\Desktop\\模板.xlsx";
String fileName = "C:\\Users\\likun\\Desktop\\测试.xlsx";
EasyExcel.write(fileName).withTemplate(templateName).sheet("模板").doWrite(data());

使用withTemplate(templateName)方法传入模板路径就可以了，有个地方需要注意的是：这里的write方法只传文件路径，不传对象，如果传了对象又会生成新的表头，效果图如下：

注意：EasyExcel导出数据都是生成新的 Excel 文件，而不是在原来的文件上修改。

行高、列宽

这里参考官方文档的例子：

@Data
@ContentRowHeight(10)
@HeadRowHeight(20)
@ColumnWidth(25)
public class WidthAndHeightData {
    @ExcelProperty("字符串标题")
    private String string;
    @ExcelProperty("日期标题")
    private Date date;
    /**
     * 宽度为50
     */
    @ColumnWidth(50)
    @ExcelProperty("数字标题")
    private Double doubleData;
}

都是加个注解的事儿，这里不再细说。

合并单元格

@ContentLoopMerge(eachRow = 2)
@ExcelProperty("姓名")
private String name;

@ContentLoopMerge(eachRow = 2)表示姓名这一列每隔两行就进行合并

效果图：

@ContentLoopMerge还有一个columnExtend属性，可以对列进行合并

@ContentLoopMerge(eachRow = 2,columnExtend = 4)
@ExcelProperty("姓名")
private String name;

效果图：

当然这些只是简单的合并，如果需要复杂的合并可以自己定义一个策略，具体实现可以参考官方文档。

自定义拦截器

有时候我们会有一些特殊的需求，比如说我们想给某个单元格设置下拉框，那么我们可以通过自定义拦截器来实现，据图代码如下：

public class CustomSheetWriteHandler implements SheetWriteHandler {

 @Override
 public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
    }
    
 @Override
 public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(2, 2, 0, 0);
        DataValidationHelper helper = writeSheetHolder.getSheet().getDataValidationHelper();
        DataValidationConstraint constraint = helper.createExplicitListConstraint(new String[] {"测试1", "测试2"});
        DataValidation dataValidation = helper.createValidation(constraint, cellRangeAddressList);
        writeSheetHolder.getSheet().addValidationData(dataValidation);
    }
}

我们需要定义一个拦截器实现SheetWriteHandler方法，然后重写拦截方法，在afterSheetCreate方法里面对第二行第一列的单元格设置下拉框，然后只要注册上去就可以了：

.registerWriteHandler(new CustomSheetWriteHandler())

效果图：

Excel模板填充

还有一个常见的业务需求就是模板填充，网上大部分都是简单的填充，今天来看一下复杂模板的填充，下面是模板：

要想使用EasyExcel填充模板，我们需要在添加占位符{字段名}，表格的需要用{自定义名称.字段名}，来简单看下代码：

首先我们需要为表格定义一个简历对象：

@Data
public class WorkHistory {
    private String ubegintime;
    private String uendtime;
    private String uworkcomp;
    private String uworkdesc;
}

接下来开始填充数据:

    @Test
    public void test() {
        // 生成Excel路径
        String filePath = "C:\\Users\\likun\\Desktop\\测试.xlsx";
        String templatePath = "C:\\Users\\likun\\Desktop\\模板.xlsx";
        ExcelWriter excelWriter = EasyExcel.write(filePath).withTemplate(templatePath).build();
        WriteSheet writeSheet = EasyExcel.writerSheet().build();
        FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
        // 填充数据
        Map<String, Object> map = new HashMap<>(64);
        map.put("uname", "张三");
        map.put("usex", "男");
        map.put("ubirthday", "2020.10.01");
        map.put("ucardid", "440582xxxxxxxx");
        map.put("umarriage", "未婚");
        map.put("unation", "汉族");
        map.put("unative", "广东xxxx");
        map.put("ubirthplace", "广东xxxx");
        map.put("upolity", "团员");
        map.put("uworktime", "2020.05.15");
        map.put("uhealth", "良好");
        excelWriter.fill(map, writeSheet);
        excelWriter.fill(new FillWrapper("data1", data1()), fillConfig, writeSheet);
        // 别忘记关闭流
        excelWriter.finish();
    }

    private List<WorkHistory> data1() {
        List<WorkHistory> list = new ArrayList<>();
        WorkHistory workHistory;
        for (int i = 1; i <= 3; i++) {
            workHistory = new WorkHistory();
            workHistory.setUbegintime("2020.05.01");
            workHistory.setUendtime("2020.05.01");
            workHistory.setUworkcomp("xxx公司");
            workHistory.setUworkdesc("后勤");
            list.add(workHistory);
        }
        return list;
    }

填充数据主要是下面两行代码：

excelWriter.fill(map, writeSheet);
excelWriter.fill(new FillWrapper("data1", data1()), fillConfig, writeSheet)

上面是填充字段，下面是填充我们的表格，注意这里data1的名字要和模板里面的名字一样。

forceNewRow(Boolean.TRUE)代表表格每次都会重新生成新的一行，而不是使用下面的空行。

看下填充的效果图：

合并单元格

可以看到数据已经填充进去了，但是表格单元格格式不符合我们的预期效果，虽然 EasyExcel 也提供了自定义策略来合并单元格，但是因为是通过回调方法触发，不好控制，因此我们这里使用原生的 Apache POI 来实现:

......
FileInputStream inputStream = new FileInputStream(new File(filePath));
XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
XSSFSheet sheet = workbook.getSheetAt(0);
// 合并列
sheet.addMergedRegion(new CellRangeAddress(8, 8, 1, 2));
sheet.addMergedRegion(new CellRangeAddress(8, 8, 3, 4));
sheet.addMergedRegion(new CellRangeAddress(8, 8, 5, 9));
sheet.addMergedRegion(new CellRangeAddress(8, 8, 10, 11));
sheet.addMergedRegion(new CellRangeAddress(9, 9, 1, 2));
sheet.addMergedRegion(new CellRangeAddress(9, 9, 3, 4));
sheet.addMergedRegion(new CellRangeAddress(9, 9, 5, 9));
sheet.addMergedRegion(new CellRangeAddress(9, 9, 10, 11));
// 合并行
sheet.addMergedRegion(new CellRangeAddress(6, 9, 0, 0));

String mergeExcelPath="C:\\Users\\likun\\Desktop\\合并单元格.xlsx";
FileOutputStream outputStream = new FileOutputStream(mergeExcelPath);
workbook.write(outputStream);
outputStream.flush();

核心代码是就是

sheet.addMergedRegion(new CellRangeAddress(row1, row2, col1, col2));

来看下效果图吧：

设置边框

可以看到单元格已经合并了，现在就是合并后没有边框，当然也有提供API供我们使用，

RegionUtil.setBorderBottom(BorderStyle.THIN, new CellRangeAddress(8, 8, 1, 2), sheet);

可以看到单元格已经设置了边框，至于其它的请大伙自行设置，这边只做个简单演示。

插入头像

EasyExcel也支持头像导出，但是只能插入到一个单元格里面，因此我们还是用原生API来插入头像：

// 转换成流
ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
BufferedImage bufferImg = ImageIO.read(new File("C:\\Users\\likun\\Pictures\\头像\\1.jpg"));
ImageIO.write(bufferImg, "jpg", byteArrayOut);

XSSFDrawing patriarch = sheet.createDrawingPatriarch();
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) 11, 2, (short) 12, 6);
anchor.setAnchorType(ClientAnchor.AnchorType.DONT_MOVE_AND_RESIZE);
patriarch.createPicture(anchor, workbook.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));

只要用XSSFClientAnchor配置好参数，就能在指定的位置插入图片。前四个参数是偏移量，默认为0就可以了，后四个就是图片边缘的单元格位置，具体细节这里不再细说。

new XSSFClientAnchor(0, 0, 0, 0, (short) 11, 2, (short) 12, 6);

效果图：

从Excel读取数据

先来看下如何从Excel读取数据，首先定义一个监听器继承 AnalysisEventListener 类：

@EqualsAndHashCode(callSuper = true)
@Data
public class ExcelListener extends AnalysisEventListener<Object> {
    private static final Logger LOGGER = LoggerFactory.getLogger(ExcelListener.class);
/**
 * 自定义用于暂时存储data
 */ 
 private List<JSONObject> dataList = new ArrayList<>();
 
/**
 * 导入表头
 */
 private Map<String, Integer> importHeads = new HashMap<>(16);
 
/**
 * 这个每一条数据解析都会来调用
 */
 @Override
 public void invoke(Object data, AnalysisContext context) {
        String headStr = JSON.toJSONString(data);
        dataList.add(JSONObject.parseObject(headStr));
    }
    
/**
 * 这里会一行行的返回头
 */
 @Override
 public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        for (Integer key : headMap.keySet()) {
            if (importHeads.containsKey(headMap.get(key))) {
                continue;
            }
            importHeads.put(headMap.get(key), key);
        }
    }
    
/**
 * 所有数据解析完成了 都会来调用
 */
 @Override
 public void doAfterAllAnalysed(AnalysisContext context) {
     LOGGER.info("Excel解析完毕");
    }
}

当解析每一条数据时都会调用invoke方法，invokeHeadMap方法会返回我们的表格头，当所有数据都解析完毕时最后会调用doAfterAllAnalysed方法。

上面代码是我项目里面用的，你们也可以根据自己需求编写，上面用JSONObject集合来存放Excel中每一条数据，用一个Map存放我们的表格头。

那么有了监听器之后该如何使用呢？

这里有个很重要的点就是 监听器不能被spring管理，要每次读取excel都要new.

看下如何读取前端发送过来的Excel文件：

    @PostMapping("upload")
    @ResponseBody
    public String upload(MultipartFile file) throws IOException {
       ExcelListener excelListener = new ExcelListener();
       EasyExcel.read(file.getInputStream(), excelListener).sheet().doRead();
       ......
    }

只要调用read方法就可以读取数据，那么接下来只要去拿到数据就可以了。

比如读取表格头数据：

Map<String, Integer> importHeads = excelListener.getImportHeads();

或者读取数据集合

List<JSONObject> dataList = excelListener.getDataList();

当然我们也可以根据文件路径去读取

    @Test
    public void test() {
        // 生成Excel路径
        String fileName = "C:\\Users\\likun\\Desktop\\测试.xlsx";
        ExcelListener excelListener = new ExcelListener();
        EasyExcel.read(fileName, excelListener).sheet().doRead();
        // 表格头数据
        Map<String, Integer> importHeads = excelListener.getImportHeads();
        System.out.println(importHeads);
        // 每一行数据
        List<JSONObject> dataList = excelListener.getDat![image]aList();
        for (JSONObject object : dataList) {
            System.out.println(object);
        }
    }

这是我们要读取的Excel数据

来看下读取到的数据：

上面的读取是不使用对象的读取方式，也有使用对象去读取的方式，因为和上面导出的差不多这里就不再展开描述没如果有需要的同学可以参考官方文档

总结

代码已上传Github：https://github.com/chenwuguii/wugui

今天有关Java操作Excel的知识点就暂时到这里，如果有什么不对的地方请多多指教！