官方文档:easypoi
1.jar坐标
<!-- https://mvnrepository.com/artifact/cn.afterturn/easypoi-spring-boot-starter -->
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-spring-boot-starter</artifactId>
<version>4.4.0</version>
</dependency>
2.注释:
2.1.@Excel
属性 | 类型 | 默认值 | 功能 |
---|---|---|---|
name | String | null | 列名,支持name_id |
needMerge | boolean | fasle | 是否需要纵向合并单元格(用于含有list中,单个的单元格,合并list创建的多个row) |
orderNum | String | “0” | 列的排序,支持name_id |
replace | String[] | {} | 值得替换 导出是{a_id,b_id} 导入反过来 |
savePath | String | “upload” | 导入文件保存路径,如果是图片可以填写,默认是upload/className/ IconEntity这个类对应的就是upload/Icon/ |
type | int | 1 | 导出类型 1 是文本 2 是图片,3 是函数,10 是数字 默认是文本 |
width | double | 10 | 列宽 |
height | double | 10 | 列高,后期打算统一使用@ExcelTarget的height,这个会被废弃,注意 |
isStatistics | boolean | fasle | 自动统计数据,在追加一行统计,把所有数据都和输出[这个处理会吞没异常,请注意这一点] |
isHyperlink | boolean | false | 超链接,如果是需要实现接口返回对象 |
isImportField | boolean | true | 校验字段,看看这个字段是不是导入的Excel中有,如果没有说明是错误的Excel,读取失败,支持name_id |
exportFormat | String | “” | 导出的时间格式,以这个是否为空来判断是否需要格式化日期 |
importFormat | String | “” | 导入的时间格式,以这个是否为空来判断是否需要格式化日期 |
format | String | “” | 时间格式,相当于同时设置了exportFormat 和 importFormat |
databaseFormat | String | “yyyyMMddHHmmss” | 导出时间设置,如果字段是Date类型则不需要设置 数据库如果是string 类型,这个需要设置这个数据库格式,用以转换时间格式输出 |
numFormat | String | “” | 数字格式化,参数是Pattern,使用的对象是DecimalFormat |
imageType | int | 1 | 导出类型 1 从file读取 2 是从数据库中读取 默认是文件 同样导入也是一样的 |
suffix | String | “” | 文字后缀,如% 90 变成90% |
isWrap | boolean | true | 是否换行 即支持\n |
mergeRely | int[] | {} | 合并单元格依赖关系,比如第二列合并是基于第一列 则{0}就可以了 |
mergeVertical | boolean | fasle | 纵向合并内容相同的单元格 |
fixedIndex | int | -1 | 对应excel的列,忽略名字 |
isColumnHidden | boolean | false | 导出隐藏列 |
2.@ExcelCollection
属性 | 类型 | 默认值 | 功能 |
---|---|---|---|
id | String | null | 定义ID |
name | String | null | 定义集合列名,支持nanm_id |
orderNum | int | 0 | 排序,支持name_id |
type | Class<?> | ArrayList.class | 导入时创建对象使用 |
3.@ExcelEntity
属性 | 类型 | 默认值 | 功能 |
---|---|---|---|
id | String | null | 定义ID |
4.@ExcelIgnore
忽略这个属性,多使用需循环引用中,无需多解释吧^^
5.@ExcelTarget:限定一个到处实体的注解,以及一些通用设置,作用于最外面的实体
属性 | 类型 | 默认值 | 功能 |
---|---|---|---|
value | String | null | 定义ID |
height | double | 10 | 设置行高 |
fontSize | short | 11 | 设置文字大小 |
6.使用举例
6.1:控制层
package com.example.demo.controller;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import cn.afterturn.easypoi.handler.inter.IWriter;
import com.example.demo.dto.Class;
import com.example.demo.dto.Class1;
import com.example.demo.dto.Student;
import com.google.common.collect.Lists;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
/**
* @author leishen
*/
@RestController
@RequestMapping("easypoi/")
@Api(tags = "测试easypoi")
public class EasyPoiExportController {
/**
* 使用@Excel注解
* entity – 表格标题属性
* pojoClass – Excel对象Class
* dataSet – Excel对象数据List
* exportExcel(ExportParams entity, Class<?> pojoClass,Collection<?> dataSet)
* @param response
* @throws IOException
*/
@ApiOperation("一对一导出方法1")
@GetMapping("test1")
public void test1(HttpServletResponse response) throws IOException {
List<Class1> class1List= Lists.newLinkedList();
Class1 class1=new Class1(1,"1",new Date());
class1List.add(class1);
class1=new Class1(2,"2",new Date());
class1List.add(class1);
//设置表头
ExportParams exportParams = new ExportParams();
exportParams.setTitle("班级信息表");
Workbook workbook = ExcelExportUtil.exportExcel(exportParams, Class1.class, class1List);
String fileName="测试.xlsx";
exportExcel(response, workbook, fileName);
}
/**
*
* entity – 表格标题属性
* 导出的表格列名 属性名称 列宽
* entityList – Map对象列表 ExcelExportEntity(String name, Object key, int width)
* dataSet – Excel对象数据List
* exportExcel(ExportParams entity, List<ExcelExportEntity> entityList,Collection<?> dataSet)
* @param response
* @throws IOException
*/
@ApiOperation("一对一导出方法2")
@GetMapping("test12")
public void test12(HttpServletResponse response) throws IOException {
List<Class1> class1List= Lists.newLinkedList();
Class1 class1=new Class1(1,"一年级",new Date());
class1List.add(class1);
class1=new Class1(2,"二年级",new Date());
class1List.add(class1);
List<ExcelExportEntity> entityList=new ArrayList<>();
entityList.add(new ExcelExportEntity("班级序号1","classId",20));
entityList.add(new ExcelExportEntity("班级名称2","name",20));
//设置表头
ExportParams exportParams = new ExportParams();
exportParams.setTitle("班级信息表");
Workbook workbook = ExcelExportUtil.exportExcel(exportParams, entityList, class1List);
String fileName="测试.xlsx";
exportExcel(response, workbook, fileName);
}
/**
* 使用@Excel、@ExcelCollection注解
* entity – 表格标题属性
* pojoClass – Excel对象Class
* dataSet – Excel对象数据List
* exportExcel(ExportParams entity, Class<?> pojoClass,Collection<?> dataSet)
* @param response
* @throws IOException
*/
@ApiOperation("一对多导出方法")
@GetMapping("test21")
public void test21(HttpServletResponse response) throws IOException {
List<Class> classList= Lists.newLinkedList();
List<Student> studentList=Lists.newArrayList();
Student student=new Student("张三",20,"0");
studentList.add(student);
student=new Student("李四",21,"1");
studentList.add(student);
classList.add(new Class(1, "一年级", studentList));
classList.add(new Class(2, "二年级", studentList));
Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("班级详情","111"), Class.class, classList);
String fileName="测试.xlsx";
exportExcel(response, workbook, fileName);
}
/**
* 导出多个不同列头的sheet
* @param response
* @throws IOException
*/
@ApiOperation("多个sheet")
@GetMapping("many")
public void many(HttpServletResponse response) throws IOException {
List<Class> classList= Lists.newLinkedList();
List<Student> studentList=Lists.newArrayList();
Student student=new Student("张三",20,"0");
studentList.add(student);
student=new Student("李四",21,"1");
studentList.add(student);
classList.add(new Class(1, "一年级", studentList));
classList.add(new Class(2, "二年级", studentList));
List<Map<String, Object>> list=Lists.newArrayList();
Map<String, List<Class>> classMap = classList.stream().collect(Collectors.groupingBy(Class::getName));
classMap.forEach((k,v)->{
Map<String, Object> map= Maps.newHashMap();
map.put("data", v);
ExportParams params = new ExportParams(null, k, ExcelType.XSSF);
if ("一年级".equals(k)) {
//排除哪些列不导出
params.setExclusions(new String[]{"班级序号"});
}
map.put("title",params);
map.put("entity",Class.class);
list.add(map);
});
Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.XSSF);
String fileName="测试.xlsx";
exportExcel(response, workbook, fileName);
}
@ApiOperation("大数据量导出")
@GetMapping("test3")
public void test3(HttpServletResponse response) throws IOException {
List<Class> classList= Lists.newLinkedList();
List<Student> studentList=Lists.newArrayList();
Student student=new Student("张三",20,"男");
studentList.add(student);
student=new Student("李四",21,"女");
studentList.add(student);
classList.add(new Class(1, "一年级", studentList));
classList.add(new Class(2, "二年级", studentList));
IWriter<Workbook> workbookIWriter = ExcelExportUtil.exportBigExcel(new ExportParams(), Class.class);
workbookIWriter.write(classList);
String fileName="测试.xlsx";
exportExcel(response, workbookIWriter.get(), fileName);
}
/**
* 下载文件
* @param response
* @param workbook
* @param fileName 文件名
* @throws IOException
*/
private static void exportExcel(HttpServletResponse response, Workbook workbook, String fileName) throws IOException {
response.setCharacterEncoding("UTF-8");
// 设置响应输出的头类型
response.setHeader("content-Type", "application/vnd.ms-excel");
// 下载文件的默认名称
response.setHeader("Content-Disposition", "attachment;filename="+ URLEncoder.encode(fileName, "UTF-8"));
OutputStream out = response.getOutputStream();
workbook.write(out);
out.flush();
out.close();
}
}
6.2.实体类
package com.example.demo.dto;
import cn.afterturn.easypoi.excel.annotation.Excel;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.util.Date;
@Data
@ApiModel(value = "班级")
@AllArgsConstructor
@NoArgsConstructor
public class Class1 {
/**
* 班级id
*/
@ApiModelProperty(value = "班级id")
@Excel(name = "班级序号",width = 20)
private Integer classId;
/**
* replace = {"一年级_1","2_二年级"}--->导出是用一年级替换1,用二年级替换2
* 班级名称
*/
@ApiModelProperty(value = "班级名称")
@Excel(name = "班级名称",width = 20,replace = {"一年级_1","二年级_2"})
private String name;
/**
* 创建时间
*/
@ApiModelProperty(value = "创建时间")
@Excel(name = "时间",format = "yyyy-MM-dd HH:mm:ss",width = 20)
private Date createDate;
}
package com.example.demo.dto;
import cn.afterturn.easypoi.excel.annotation.Excel;
import io.swagger.annotations.ApiModel;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@ApiModel("学生")
@AllArgsConstructor
@NoArgsConstructor
public class Student {
@Excel(name = "姓名",width = 15)
private String name;
@Excel(name = "年龄",width = 15)
private Integer age;
/**
* replace ={"男_0","女_1"}-->
*/
@Excel(name = "性别",width = 15,replace ={"男_0","女_1"})
private String sex;
}
package com.example.demo.dto;
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelCollection;
import cn.afterturn.easypoi.excel.annotation.ExcelIgnore;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.util.Date;
import java.util.List;
@Data
@ApiModel(value = "班级")
@AllArgsConstructor
@NoArgsConstructor
public class Class {
/**
* 班级id
*/
@ApiModelProperty(value = "班级id")
@Excel(name = "班级序号",width = 20,needMerge = true)
//不导出== new ExportParams().setExclusions(new String[]{"班级序号"});
//@ExcelIgnore
private Integer classId;
/**
* 班级名称
*/
@ApiModelProperty(value = "班级名称")
@Excel(name = "班级名称",width = 20,needMerge = true)
private String name;
@ExcelCollection(name = "学生列表")
private List<Student> studentList;
}