使用EasyExcel自定义导出表格

一、介绍

作者：fyupeng
技术专栏：☞ https://github.com/fyupeng
分布式博客项目地址：☞ https://github.com/fyupeng/distributed-blog-system-api

留给读者

工作中难免会遇到数据导出的问题，最简单的解决方案就是引入阿里的EasyExcel来解决。

二、代码

• 实体类

@Data
public class Person {@ExcelProperty(value = "序号", index = 0)private String seqNumber;@ExcelProperty(value = "名称", index = 1)private String name;@ExcelProperty(value = "爱好", index = 2)private String hobby;}

• 实现代码

private final static DateTimeFormatter DATE_TIME_FORMAT = DateTimeFormatter.ofPattern("yyyy-MM-dd");public static void export(HttpServletRequest request, HttpServletResponse response) throws IOException {Person person = new Person();person.setName("小明");person.setHobby("苹果");List<Person> list = new ArrayList<>();list.add(person);String fileName = "人.xlsx";SXSSFWorkbook sheets = exportExcel(list);response.setContentType("application/octet-stream;charset=UTF8");response.setHeader("Content-Type", "application/vnd.ms-excel");fileName = URLEncoder.encode(fileName, "UTF-8") + DATE_TIME_FORMAT.format(LocalDate.now());response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");response.addHeader("Cache-Control", "no-cache");OutputStream out = response.getOutputStream();sheets.write(out);out.flush();out.close();}private static SXSSFWorkbook exportExcel(List<Person> moduleList) {// 标题String[] title = {"序号", "名称", "爱好"};// 创建一个工作薄SXSSFWorkbook workbook = new SXSSFWorkbook();// 创建一个工作表 sheetSXSSFSheet sheet1 = workbook.createSheet("Sheet1");// 创建第二行Row row = sheet1.createRow(1);// 创建单元格Cell cell = null;// 创建表头for (int i = 0; i < title.length; i++) {cell = row.createCell(i);// 设置样式CellStyle cellStype = workbook.createCellStyle();cellStype.setAlignment(HorizontalAlignment.CENTER); // 设置字体居中// 设置字体Font font = workbook.createFont();font.setFontHeightInPoints((short) 13);cellStype.setFont(font);cell.setCellStyle(cellStype);cell.setCellValue(title[i]);}// 从第三行开始追加数据for (int i = 2; i < (moduleList.size() + 2); i++) {// 创建第i行Row nextRow = sheet1.createRow(i);for (int j = 0; j < 3; j++) {Person person = moduleList.get(i - 2);Cell cell2 = nextRow.createCell(j);if (j == 0) {cell2.setCellValue(String.valueOf(i - 1));} else if (j == 1) {cell2.setCellValue(person.getName());} else if (j == 2) {cell2.setCellValue(person.getHobby());}}}return workbook;}

• 展示效果
  

三、总结

生活总能给你带来惊喜，工作中总结未尝不是？