导出是项目中最常见的功能，例如考勤记录导出，账单明细导出，订单记录导出等等。导出的工具类有许多种，目前常见的有poi,easypoi,poi...,今天我要说的是基于hutool-poi的导出，hutool-poi是将poi做了封装，简化了大量的代码编写。
使用方式：
maven
在项目的pom.xml中引入
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.7.3</version>
</dependency>
gradle
在项目的build.gradle中引入
compile 'cn.hutool:hutool-all:5.7.3'
我要实现的是将班级学生信息导出到excel文件中并且还要按照班级名称动态合并单元格，我先创建学生对象：
public class Student{
private String className;
private String stuName;
private String sex;
private Integer age;
private String interesting;
public String getClassName() {
return className;
}
public void setClassName(String className) {
this.className = className;
}
public String getStuName() {
return stuName;
}
public void setStuName(String stuName) {
this.stuName = stuName;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public String getInteresting() {
return interesting;
}
public void setInteresting(String interesting) {
this.interesting = interesting;
}
public Student(String className, String stuName, String sex, Integer age, String interesting) {
this.className = className;
this.stuName = stuName;
this.sex = sex;
this.age = age;
this.interesting = interesting;
}
}
下面是控制器：
public class Controller{
@GetMapping("/export")
public void export(HttpServletResponse response){
List<Student> students = new ArrayList<>();
students.add(new Student("一班", "小明", "男", 12, "打球"));
students.add(new Student("一班", "小蓝", "女", 14, "打乒乓"));
students.add(new Student("一班", "小刚", "男", 16, "打台球"));
students.add(new Student("二班", "小猪", "女", 12, "打羽毛球"));
students.add(new Student("二班", "小猴", "女", 11, "踢鞋"));
students.add(new Student("二班", "小马", "男", 12, "打架"));
students.add(new Student("二班", "小鸭", "女", 13, "跑步"));
students.add(new Student("三班", "tom", "男", 12, "找事"));
students.add(new Student("三班", "jack", "男", 14, "喝酒"));
students.add(new Student("三班", "marry", "女", 11, "抽烟"));
students.add(new Student("三班", "larry", "男", 13, "打球"));
students.add(new Student("四班", "孙悟空", "男", 12, "幽默"));
students.add(new Student("五班", "喜喜", "女", 11, "笑话"));
students.add(new Student("五班", "笑笑", "女", 12, "邋遢"));
students.add(new Student("五班", "嘿嘿", "男", 12, "打球"));
students.add(new Student("六班", "小数", "男", 12, "打球"));
ExcelWriter writer = ExcelUtil.getBigWriter();
ServletOutputStream out = null;
try {
CellStyle headCellStyle = writer.getHeadCellStyle();
Font font = writer.createFont();
font.setBold(true);
headCellStyle.setFont(font);
headCellStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
headCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
writer.merge(3, "学生信息管理表", true);
writer.addHeaderAlias("className", "班级").addHeaderAlias("stuName", "学生姓名")
.addHeaderAlias("sex", "性别").addHeaderAlias("age", "年龄");
writer.setOnlyAlias(true);
LinkedHashMap<String, List<Student>> map = students.stream().collect(Collectors.groupingBy(item -> item.getClassName(),
LinkedHashMap::new, Collectors.toList()));
int firstRow = 2, lastRow = 2;
for (List<Student> studentList : map.values()) {
int size = studentList.size();
lastRow = firstRow + size -1;
if(size > 1){
writer.merge(firstRow, lastRow, 0, 0, null, true);
}
firstRow = firstRow + size;
}
writer.write(students, true);
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename=PaymentDetailReport"+ DateUtil.format(new Date(),
"yyyyMMddHHmm") +".xls");
out = response.getOutputStream();
writer.flush(out, true);
} catch (IOException e) {
e.printStackTrace();
} finally {
IoUtil.close(out);
writer.close();
}
}
}
通过ExcelUtil.getBigWriter()创建Writer对象，BigExcelWriter用于大数据量的导出，不会引起溢出；
writer.addHeaderAlias("字段", "字段别名")，给所属的字段起别名，写在表头；
writer.setOnlyAlias(true)：是指只导出定义了别名的字段；
writer.write(students, true)：第二个参数是true指导出的excel是xlsx格式的，false指导出的excel是xls格式的；
writer.flush(out, true)：是写出到excel中；
writer.close()：关闭writer，释放内存；
LinkedHashMap<String, List<Student>> map = students.stream().collect(Collectors.groupingBy(item -> item.getClassName(),
LinkedHashMap::new, Collectors.toList()));使用java的stream对同一个班级的学生信息进行分组，LinkedHashMap的key是班级名称,value是班级名称下对的学生信息，
调用writer.merge(firstRow, lastRow, 0, 0, null, true)：合并行，第一个参数是合并行的开始行号（行号从0开始），第二个参数是合并行的结束行号，第三个参数是合并的列号开始(列号从0开始)，第四个参数是合并的列号结束，第五个参数是合并后的内容，null不设置，第六个参数指是否支持设置样式，true指的是。
特别注意，如果只有一行不能调用merge方法，否则会报错。
