【相关文档】:EasyPoi教程
1. 依赖
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-spring-boot-starter</artifactId>
<version>4.4.0</version>
</dependency>
2. 常用注解
@ExcelTarget("xxx")
public class TestPo {
@Excel(name = "excel中的列名", needMerge = true, dict = "dictName", orderNum = "-3",width = 20)
private String name;
@ExcelCollection(name = "子列表")
private List<ChildPo> list;
}
- ExcelTarget: 用于标记实体对象
- name: 该字段在excel中的列名
- orderNum: 列的顺序根据该属性的值进行确定,值越小,该列越靠前
- 列的宽度
- dict: 字典项,若字段为字典类型,且需要在字典名称和字典值做转换, 需要实现 IExcelDictHandler 接口,并在导入导出时设置该参数
- ExcelCollection: 标识该字段为集合属性
- needMerge: 若包含集合属性的字段时, 该列是否需要进行纵向合并
3.示例
1. 实体类及控制层
@RequestMapping("excel")
@RestController
public class ExcelController {
@Resource
private MyExcelUtil excelUtil;
@GetMapping("export")
public void export(HttpServletResponse response) {
MyClass myClass1 = new MyClass("一版","jingying");
MyClass myClass2 = new MyClass("六班","putong");
List<MyClass> childList = new ArrayList<>();
childList.add(myClass1);
childList.add(myClass2);
List<MySchool> list = new ArrayList<>();
list.add(new MySchool("凤翔中学","emphasis",childList));
list.add(new MySchool("柳林中学","general",childList));
excelUtil.exportExcel(response,"测试标题","花名册", MySchool.class,list);
}
}
@Data
@AllArgsConstructor
public class MySchool {
@Excel(name = "学校名称", needMerge = true, orderNum = "1", width = 10 )
private String name;
@Excel(name = "学校类型", needMerge = true, dict = "school_type", orderNum = "2", width = 10 )
private String type;
@ExcelCollection(name = "班级列表", orderNum = "3")
private List<MyClass> list;
}
@Data
@AllArgsConstructor
public class MyClass {
@Excel(name = "班级名称", orderNum = "1", width = 10)
private String name;
@Excel(name = "班级类型", dict = "class_type",orderNum = "2", width = 10)
private String type;
}
2. DictHandler
@Component
public class DictHandler implements IExcelDictHandler {
public Map<String, Map<String, String>> dictMap = new ConcurrentHashMap<>();
{
Map<String, String> dictLength = new ConcurrentHashMap<>();
dictLength.put("重点学校", "emphasis");
dictLength.put("普通学校", "general");
dictMap.put("school_type", dictLength);
Map<String, String> dictSize = new ConcurrentHashMap<>();
dictSize.put("精英班", "jingying");
dictSize.put("普通班", "putong");
dictMap.put("class_type", dictSize);
}
/**
* 从值翻译到名称
*
* @param dict 字典Key
* @param obj 对象
* @param name 属性名称
* @param value 属性值
* @return
*/
@Override
public String toName(String dict, Object obj, String name, Object value) {
Map<String, String> map = dictMap.get(dict);
for (String key : map.keySet()) {
if (map.get(key).equals(value)) {
return key;
}
}
return null;
}
/**
* 从名称翻译到值
*
* @param dict 字典Key
* @param obj 对象
* @param name 属性名称
* @param value 属性值
* @return
*/
@Override
public String toValue(String dict, Object obj, String name, Object value) {
Map<String, String> map = dictMap.get(dict);
for (String key : map.keySet()) {
if (key.equals(name)) {
return map.get(name);
}
}
return null;
}
}
3. 导出Excel
@Component
public class MyExcelUtil {
@Resource
private DictHandler dictHandler;
public void exportExcel(HttpServletResponse response, String title, String sheetName, Class clazz, List<? extends Object> list) {
try {
ExportParams exportParams = new ExportParams(title, sheetName, ExcelType.XSSF);
exportParams.setCreateHeadRows(true);
exportParams.setDictHandler(dictHandler);
Workbook workbook = ExcelExportUtil.exportExcel(exportParams, clazz, list);
OutputStream out = response.getOutputStream();
response.setCharacterEncoding(StandardCharsets.UTF_8.name());
response.setHeader("content-type", "application/vnd.ms-excel");
response.setHeader("content-disposition", "attachment;filename=" + URLEncoder.encode(sheetName + "." + "xlsx", StandardCharsets.UTF_8.name()));
workbook.write(out);
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
4. 导出效果
