【相关文档】:EasyPoi教程
<dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-spring-boot-starter</artifactId> <version>4.4.0</version> </dependency>
@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; }
@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; }
@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; } }
@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(); } } }