easyExsel导出

目录

一、首先引入依赖

二、然后封装一个VO

三、Controller层

四、Service实现类 

引用样式

自适应列宽  

自适应行高

五、测试

postman

​编辑

 浏览器

异常

---

分配到这个任务了，写个小demo记录下，具体可参考EasyExcel官方文档

我用的是web上传、下载那块代码

一、首先引入依赖 <!-- easy Excel --> <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>3.1.0</version> <exclusions> <exclusion> <artifactId>poi-ooxml-schemas</artifactId> <groupId>org.apache.poi</groupId> </exclusion> </exclusions> </dependency> 二、然后封装一个VO @Data @AllArgsConstructor @NoArgsConstructor @EqualsAndHashCode public class ExportStudentInfoVO implements Serializable { private static final long serialVersionUID = -3275970951989418695L; @ExcelIgnore // 忽略导出 private String stuId; @ExcelProperty("学生姓名") private String stuName; @ExcelProperty("学生性别") private String stuGender; @ExcelProperty("学生年龄") private Integer stuAge; @ExcelProperty("监护人联系方式") private String guardianPhone; @DateTimeFormat("yyyy-MM-dd HH:mm:ss") @ColumnWidth(21) //设置宽度 @ExcelProperty(value = "入学时间") private Date createDate; } 三、Controller层 @RestController @RequestMapping("info") public class InfoController { @Resource private InfoService infoService; @Operation(summary = "学生信息导出") @RequestMapping(value = "/excelDownload", method = RequestMethod.GET, produces = "application/json; charset=utf-8") public void excelOrderContainerDownload(HttpServletResponse response){ infoService.excelDownload(response); } } 四、Service实现类 

这里的list，模拟从DB中查到的数据

.registerWriteHandler(new CustomCellWriteWidthConfig()) /*自适应列宽*/  .registerWriteHandler(new CustomCellWriteHeightConfig()) /*自适应行高*/ .registerWriteHandler(EasyExcelUtils.getStyleStrategy()) /*引用样式*/

以上三个是excel表格进行一个处理，让其看起来更加美观，如果要使用可以往下翻对应的代码复制使用，不加也不影响导出

@Service @Slf4j public class InfoServiceImpl implements InfoService { @Override public void excelDownload(HttpServletResponse response) { List<ExportStudentInfoVO> list = new ArrayList<>(); list.add(new ExportStudentInfoVO("001","张三","男",18,"18488789989", new Date())); list.add(new ExportStudentInfoVO("002","李四","女",21,"15233337777", new Date())); list.add(new ExportStudentInfoVO("003","王五","男",19,"15623332333", new Date())); try { response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); response.setCharacterEncoding("utf-8"); SimpleDateFormat sdf = new SimpleDateFormat("yyyy.MM.dd"); String currentDate = sdf.format(new Date()); // URLEncoder.encode 可以防止中文乱码 String fileName = URLEncoder.encode("学生信息列表" + currentDate, "UTF-8").replaceAll("\\+", "%20"); response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx"); EasyExcel.write(response.getOutputStream(), ExportStudentInfoVO.class) .sheet("学生信息") .registerWriteHandler(new CustomCellWriteWidthConfig()) /*自适应列宽*/ .registerWriteHandler(new CustomCellWriteHeightConfig()) /*自适应行高*/ .registerWriteHandler(EasyExcelUtils.getStyleStrategy()) /*引用样式*/ .doWrite(list); } catch (Exception e) { log.error("导出失败~"); e.printStackTrace(); } } } 引用样式 package cn.homed.common.utils.excel; import com.alibaba.excel.write.metadata.style.WriteCellStyle; import com.alibaba.excel.write.metadata.style.WriteFont; import com.alibaba.excel.write.style.HorizontalCellStyleStrategy; import org.apache.poi.ss.usermodel.BorderStyle; import org.apache.poi.ss.usermodel.HorizontalAlignment; import org.apache.poi.ss.usermodel.IndexedColors; import org.apache.poi.ss.usermodel.VerticalAlignment; public class EasyExcelUtils { /** * 设置excel样式 */ public static HorizontalCellStyleStrategy getStyleStrategy() { // 头的策略 样式调整 WriteCellStyle headWriteCellStyle = new WriteCellStyle(); // 头背景 浅绿 headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); WriteFont headWriteFont = new WriteFont(); // 头字号 headWriteFont.setFontHeightInPoints((short) 12); // 字体样式 headWriteFont.setFontName("宋体"); headWriteCellStyle.setWriteFont(headWriteFont); // 自动换行 headWriteCellStyle.setWrapped(true); // 设置细边框 headWriteCellStyle.setBorderBottom(BorderStyle.THIN); headWriteCellStyle.setBorderLeft(BorderStyle.THIN); headWriteCellStyle.setBorderRight(BorderStyle.THIN); headWriteCellStyle.setBorderTop(BorderStyle.THIN); // 设置边框颜色 25灰度 headWriteCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex()); headWriteCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex()); headWriteCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex()); headWriteCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex()); // 水平对齐方式 headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER); // 垂直对齐方式 headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 内容的策略 宋体 WriteCellStyle contentStyle = new WriteCellStyle(); // 设置垂直居中 contentStyle.setWrapped(true); contentStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 设置 水平居中 // contentStyle.setHorizontalAlignment(HorizontalAlignment.CENTER); WriteFont contentWriteFont = new WriteFont(); // 内容字号 contentWriteFont.setFontHeightInPoints((short) 12); // 字体样式 contentWriteFont.setFontName("宋体"); contentStyle.setWriteFont(contentWriteFont); // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现 return new HorizontalCellStyleStrategy(headWriteCellStyle, contentStyle); } } 自适应列宽   package cn.homed.common.utils.excel; import com.alibaba.excel.enums.CellDataTypeEnum; import com.alibaba.excel.metadata.Head; import com.alibaba.excel.metadata.data.CellData; import com.alibaba.excel.metadata.data.WriteCellData; import com.alibaba.excel.write.metadata.holder.WriteSheetHolder; import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy; import org.apache.commons.collections.CollectionUtils; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Sheet; import java.util.HashMap; import java.util.List; import java.util.Map; public class CustomCellWriteWidthConfig extends AbstractColumnWidthStyleStrategy { private final Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<>(); @Override protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer integer, Boolean isHead) { boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList); if (needSetWidth) { Map<Integer, Integer> maxColumnWidthMap = CACHE.computeIfAbsent(writeSheetHolder.getSheetNo(), k -> new HashMap<>()); Integer columnWidth = this.dataLength(cellDataList, cell, isHead); // 单元格文本长度大于60换行 if (columnWidth >= 0) { if (columnWidth > 60) { columnWidth = 60; } Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex()); if (maxColumnWidth == null || columnWidth > maxColumnWidth) { maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth); Sheet sheet = writeSheetHolder.getSheet(); sheet.setColumnWidth(cell.getColumnIndex(), columnWidth * 256); } } } } /** * 计算长度 * @param cellDataList * @param cell * @param isHead * @return */ private Integer dataLength(List<WriteCellData<?>> cellDataList, Cell cell, Boolean isHead) { if (isHead) { return cell.getStringCellValue().getBytes().length; } else { CellData<?> cellData = cellDataList.get(0); CellDataTypeEnum type = cellData.getType(); if (type == null) { return -1; } else { switch (type) { case STRING: // 换行符（数据需要提前解析好） int index = cellData.getStringValue().indexOf("\n"); return index != -1 ? cellData.getStringValue().substring(0, index).getBytes().length + 1 : cellData.getStringValue().getBytes().length + 1; case BOOLEAN: return cellData.getBooleanValue().toString().getBytes().length; case NUMBER: return cellData.getNumberValue().toString().getBytes().length; default: return -1; } } } } } 自适应行高 package cn.homed.common.utils.excel; import com.alibaba.excel.write.style.row.AbstractRowHeightStyleStrategy; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellType; import org.apache.poi.ss.usermodel.Row; import java.util.Iterator; public class CustomCellWriteHeightConfig extends AbstractRowHeightStyleStrategy { /** * 默认高度 */ private static final Integer DEFAULT_HEIGHT = 300; @Override protected void setHeadColumnHeight(Row row, int relativeRowIndex) { } @Override protected void setContentColumnHeight(Row row, int relativeRowIndex) { Iterator<Cell> cellIterator = row.cellIterator(); if (!cellIterator.hasNext()) { return; } // 默认为 1行高度 int maxHeight = 1; while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (cell.getCellTypeEnum() == CellType.STRING) { String value = cell.getStringCellValue(); int len = value.length(); int num = 0; if (len > 50) { num = len % 50 > 0 ? len / 50 : len / 2 - 1; } if (num > 0) { for (int i = 0; i < num; i++) { value = value.substring(0, (i + 1) * 50 + i) + "\n" + value.substring((i + 1) * 50 + i, len + i); } } if (value.contains("\n")) { int length = value.split("\n").length; maxHeight = Math.max(maxHeight, length) + 1; } } } row.setHeight((short) ((maxHeight) * DEFAULT_HEIGHT)); } } 五、测试

测试的话可以用postman进行测试 ，或者把链接粘在浏览器上

postman

postman测试的时候记得点这个下拉框选择发送并下载

然后弹出这个界面点击保存

然后桌面上就可以看到已经成功的下载下来了，数据也都是没问题的

 浏览器

直接贴链接即可

可以看到数据也是没问题的 

异常

最后讲一下，刚开始我这个小demo没跑起来，编译、运行都没问题，一调接口就报错了

异常是 com.alibaba.excel.exception.ExcelGenerateException: java.lang.NoClassDefFoundError: org/apache/xmlbeans/impl/common/SystemCache

搜了一下是由于缺少了相关的依赖库或者版本不匹配所致，可能需要添加 Apache POI 或者 XMLBeans 这些依赖库，并且确保版本号是兼容的。

然后加上这两个依赖就可以了，不知道你们有没有遇到

<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>4.1.2</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>4.1.2</version> </dependency>

好了，分享就到这里，晚安