Excel生成-ExcelWriter

由来

Excel有读取也便有写出,Hutool针对将数据写出到Excel做了封装。

原理

Hutool将Excel写出封装为ExcelWriter,原理为包装了Workbook对象,每次调用merge(合并单元格)或者write(写出数据)方法后只是将数据写入到Workbook,并不写出文件,只有调用flush或者close方法后才会真正写出文件。

由于机制原因,在写出结束后需要关闭ExcelWriter对象,调用close方法即可关闭,此时才会释放Workbook对象资源,否则带有数据的Workbook一直会常驻内存。

使用例子

1. 将行列对象写出到Excel

我们先定义一个嵌套的List,List的元素也是一个List,内层的一个List代表一行数据,每行都有4个单元格,最终list对象代表多行数据。

  1. List<String> row1 = CollUtil.newArrayList("aa", "bb", "cc", "dd");
  2. List<String> row2 = CollUtil.newArrayList("aa1", "bb1", "cc1", "dd1");
  3. List<String> row3 = CollUtil.newArrayList("aa2", "bb2", "cc2", "dd2");
  4. List<String> row4 = CollUtil.newArrayList("aa3", "bb3", "cc3", "dd3");
  5. List<String> row5 = CollUtil.newArrayList("aa4", "bb4", "cc4", "dd4");
  6. List<List<String>> rows = CollUtil.newArrayList(row1, row2, row3, row4, row5);

然后我们创建ExcelWriter对象后写出数据:

  1. //通过工具类创建writer
  2. ExcelWriter writer = ExcelUtil.getWriter("d:/writeTest.xlsx");
  3. //通过构造方法创建writer
  4. //ExcelWriter writer = new ExcelWriter("d:/writeTest.xls");
  5. //跳过当前行,既第一行,非必须,在此演示用
  6. writer.passCurrentRow();
  7. //合并单元格后的标题行,使用默认标题样式
  8. writer.merge(row1.size() - 1, "测试标题");
  9. //一次性写出内容,强制输出标题
  10. writer.write(rows, true);
  11. //关闭writer,释放内存
  12. writer.close();

效果: 写出效果图

2. 写出Map数据

构造数据:

  1. Map<String, Object> row1 = new LinkedHashMap<>();
  2. row1.put("姓名", "张三");
  3. row1.put("年龄", 23);
  4. row1.put("成绩", 88.32);
  5. row1.put("是否合格", true);
  6. row1.put("考试日期", DateUtil.date());
  7. Map<String, Object> row2 = new LinkedHashMap<>();
  8. row2.put("姓名", "李四");
  9. row2.put("年龄", 33);
  10. row2.put("成绩", 59.50);
  11. row2.put("是否合格", false);
  12. row2.put("考试日期", DateUtil.date());
  13. ArrayList<Map<String, Object>> rows = CollUtil.newArrayList(row1, row2);

写出数据:

  1. // 通过工具类创建writer
  2. ExcelWriter writer = ExcelUtil.getWriter("d:/writeMapTest.xlsx");
  3. // 合并单元格后的标题行,使用默认标题样式
  4. writer.merge(rows.size() - 1, "一班成绩单");
  5. // 一次性写出内容,使用默认样式,强制输出标题
  6. writer.write(rows, true);
  7. // 关闭writer,释放内存
  8. writer.close();

效果: 写出效果

3. 写出Bean数据

定义Bean:

  1. public class TestBean {
  2. private String name;
  3. private int age;
  4. private double score;
  5. private boolean isPass;
  6. private Date examDate;
  7. public String getName() {
  8. return name;
  9. }
  10. public void setName(String name) {
  11. this.name = name;
  12. }
  13. public int getAge() {
  14. return age;
  15. }
  16. public void setAge(int age) {
  17. this.age = age;
  18. }
  19. public double getScore() {
  20. return score;
  21. }
  22. public void setScore(double score) {
  23. this.score = score;
  24. }
  25. public boolean isPass() {
  26. return isPass;
  27. }
  28. public void setPass(boolean isPass) {
  29. this.isPass = isPass;
  30. }
  31. public Date getExamDate() {
  32. return examDate;
  33. }
  34. public void setExamDate(Date examDate) {
  35. this.examDate = examDate;
  36. }
  37. }

构造数据:

  1. TestBean bean1 = new TestBean();
  2. bean1.setName("张三");
  3. bean1.setAge(22);
  4. bean1.setPass(true);
  5. bean1.setScore(66.30);
  6. bean1.setExamDate(DateUtil.date());
  7. TestBean bean2 = new TestBean();
  8. bean2.setName("李四");
  9. bean2.setAge(28);
  10. bean2.setPass(false);
  11. bean2.setScore(38.50);
  12. bean2.setExamDate(DateUtil.date());
  13. List<TestBean> rows = CollUtil.newArrayList(bean1, bean2);

写出数据:

  1. // 通过工具类创建writer
  2. ExcelWriter writer = ExcelUtil.getWriter("d:/writeBeanTest.xlsx");
  3. // 合并单元格后的标题行,使用默认标题样式
  4. writer.merge(4, "一班成绩单");
  5. // 一次性写出内容,使用默认样式,强制输出标题
  6. writer.write(rows, true);
  7. // 关闭writer,释放内存
  8. writer.close();

效果: 写出Bean数据

4. 自定义Bean的key别名(排序标题)

在写出Bean的时候,我们可以调用ExcelWriter对象的addHeaderAlias方法自定义Bean中key的别名,这样就可以写出自定义标题了(例如中文)。

写出数据:

  1. // 通过工具类创建writer
  2. ExcelWriter writer = ExcelUtil.getWriter("d:/writeBeanTest.xlsx");
  3. //自定义标题别名
  4. writer.addHeaderAlias("name", "姓名");
  5. writer.addHeaderAlias("age", "年龄");
  6. writer.addHeaderAlias("score", "分数");
  7. writer.addHeaderAlias("isPass", "是否通过");
  8. writer.addHeaderAlias("examDate", "考试时间");
  9. // 合并单元格后的标题行,使用默认标题样式
  10. writer.merge(4, "一班成绩单");
  11. // 一次性写出内容,使用默认样式,强制输出标题
  12. writer.write(rows, true);
  13. // 关闭writer,释放内存
  14. writer.close();

效果: Excel生成-ExcelWriter - 图4

提示(since 4.1.5) 默认情况下Excel中写出Bean字段不能保证顺序,此时可以使用addHeaderAlias方法设置标题别名,Bean的写出顺序就会按照标题别名的加入顺序排序。 如果不需要设置标题但是想要排序字段,请调用writer.addHeaderAlias("age", "age")设置一个相同的别名就可以不更换标题。 未设置标题别名的字段不参与排序,会默认排在前面。

5. 写出到流

  1. // 通过工具类创建writer,默认创建xls格式
  2. ExcelWriter writer = ExcelUtil.getWriter();
  3. //创建xlsx格式的
  4. //ExcelWriter writer = ExcelUtil.getWriter(true);
  5. // 一次性写出内容,使用默认样式,强制输出标题
  6. writer.write(rows, true);
  7. //out为OutputStream,需要写出到的目标流
  8. writer.flush(out);
  9. // 关闭writer,释放内存
  10. writer.close();

6. 写出到客户端下载(写出到Servlet)

  1. 写出xls
  1. // 通过工具类创建writer,默认创建xls格式
  2. ExcelWriter writer = ExcelUtil.getWriter();
  3. // 一次性写出内容,使用默认样式,强制输出标题
  4. writer.write(rows, true);
  5. //out为OutputStream,需要写出到的目标流
  6. //response为HttpServletResponse对象
  7. response.setContentType("application/vnd.ms-excel;charset=utf-8");
  8. //test.xls是弹出下载对话框的文件名,不能为中文,中文请自行编码
  9. response.setHeader("Content-Disposition","attachment;filename=test.xls");
  10. ServletOutputStream out=response.getOutputStream();
  11. writer.flush(out, true);
  12. // 关闭writer,释放内存
  13. writer.close();
  14. //此处记得关闭输出Servlet流
  15. IoUtil.close(out);
  1. 写出xlsx
  1. ExcelWriter writer = ExcelUtil.getWriter(true);
  2. writer.write(rows, true);
  3. response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
  4. response.setHeader("Content-Disposition","attachment;filename=test.xlsx");
  5. writer.flush(out, true);
  6. writer.close();
  7. IoUtil.close(out);

注意 ExcelUtil.getWriter()默认创建xls格式的Excel,因此写出到客户端也需要自定义文件名为XXX.xls,否则会出现文件损坏的提示。 若想生成xlsx格式,请使用ExcelUtil.getWriter(true)创建。

  1. 下载提示文件损坏问题解决

有用户反馈按照代码生成的Excel下载后提示文件损坏,无法打开,经过排查,可能是几个问题:

  • (1)writer和out流没有正确关闭,请在代码末尾的finally块增加关闭。
  • (2)扩展名不匹配。getWriter默认生成xls,Content-Disposition中也应该是xls,只有getWriter(true)时才可以使用xlsx
  • (3)Maven项目中Excel保存于ClassPath中(src/main/resources下)宏替换导致被破坏,解决办法是添加filtering(参考:https://blog.csdn.net/qq_42270377/article/details/92771349)
  • (4)Excel打开提示文件损坏,WPS可以打开。这是Excel的安全性控制导致的,解决办法见:https://blog.csdn.net/zm9898/article/details/99677626

自定义Excel

1. 设置单元格背景色

  1. ExcelWriter writer = ...;
  2. // 定义单元格背景色
  3. StyleSet style = writer.getStyleSet();
  4. // 第二个参数表示是否也设置头部单元格背景
  5. style.setBackgroundColor(IndexedColors.RED, false);

2. 自定义字体

  1. ExcelWriter writer = ...;
  2. //设置内容字体
  3. Font font = writer.createFont();
  4. font.setBold(true);
  5. font.setColor(Font.COLOR_RED);
  6. font.setItalic(true);
  7. //第二个参数表示是否忽略头部样式
  8. writer.getStyleSet().setFont(font, true);

3. 写出多个sheet

  1. //初始化时定义表名
  2. ExcelWriter writer = new ExcelWriter("d:/aaa.xls", "表1");
  3. //切换sheet,此时从第0行开始写
  4. writer.setSheet("表2");
  5. ...
  6. writer.setSheet("表3");
  7. ...

4. 更详细的定义样式

在Excel中,由于样式对象个数有限制,因此Hutool根据样式种类分为4个样式对象,使相同类型的单元格可以共享样式对象。样式按照类别存在于StyleSet中,其中包括:

  • 头部样式 headCellStyle
  • 普通单元格样式 cellStyle
  • 数字单元格样式 cellStyleForNumber
  • 日期单元格样式 cellStyleForDate

其中cellStyleForNumber cellStyleForDate用于控制数字和日期的显示方式。

因此我们可以使用以下方式获取CellStyle对象自定义指定种类的样式:

  1. StyleSet style = writer.getStyleSet();
  2. CellStyle cellStyle = style.getHeadCellStyle();
  3. ...