devbox
数据灵魂
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

easy Exsel导出

作者：数据灵魂 | 2024-02-05 15:16:30

赞

踩

easy Exsel导出

目录

一、首先引入依赖

二、然后封装一个VO

三、Controller层

四、Service实现类 

引用样式

自适应列宽  

自适应行高

五、测试

postman

​编辑

 浏览器

异常

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

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

一、首先引入依赖

  1.         <!--    easy Excel    -->
  2.         <dependency>
  3.             <groupId>com.alibaba</groupId>
  4.             <artifactId>easyexcel</artifactId>
  5.             <version>3.1.0</version>
  6.             <exclusions>
  7.                 <exclusion>
  8.                     <artifactId>poi-ooxml-schemas</artifactId>
  9.                     <groupId>org.apache.poi</groupId>
  10.                 </exclusion>
  11.             </exclusions>
  12.         </dependency>

二、然后封装一个VO

  1. @Data
  2. @AllArgsConstructor
  3. @NoArgsConstructor
  4. @EqualsAndHashCode
  5. public class ExportStudentInfoVO implements Serializable {
  6.  
  7.     private static final long serialVersionUID = -3275970951989418695L;
  8.  
  9.  
  10.     @ExcelIgnore // 忽略导出
  11.     private String stuId;
  12.  
  13.     @ExcelProperty("学生姓名")
  14.     private String stuName;
  15.  
  16.     @ExcelProperty("学生性别")
  17.     private String stuGender;
  18.  
  19.     @ExcelProperty("学生年龄")
  20.     private Integer stuAge;
  21.  
  22.     @ExcelProperty("监护人联系方式")
  23.     private String guardianPhone;
  24.  
  25.     @DateTimeFormat("yyyy-MM-dd HH:mm:ss")
  26.     @ColumnWidth(21) //设置宽度
  27.     @ExcelProperty(value = "入学时间")
  28.     private Date createDate;
  29. }

三、Controller层

  1. @RestController
  2. @RequestMapping("info")
  3. public class InfoController {
  4.  
  5.     @Resource
  6.     private InfoService infoService;
  7.  
  8.     @Operation(summary = "学生信息导出")
  9.     @RequestMapping(value = "/excelDownload", method = RequestMethod.GET, produces = "application/json; charset=utf-8")
  10.     public void excelOrderContainerDownload(HttpServletResponse response){
  11.         infoService.excelDownload(response);
  12.     }
  13.  
  14. }

四、Service实现类 

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

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

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

  1. @Service
  2. @Slf4j
  3. public class InfoServiceImpl  implements InfoService {
  4.  
  5. @Override
  6.     public void excelDownload(HttpServletResponse response) {
  7.         List<ExportStudentInfoVO> list = new ArrayList<>();
  8.         list.add(new ExportStudentInfoVO("001","张三","男",18,"18488789989", new Date()));
  9.         list.add(new ExportStudentInfoVO("002","李四","女",21,"15233337777", new Date()));
  10.         list.add(new ExportStudentInfoVO("003","王五","男",19,"15623332333", new Date()));
  11.  
  12.         try {
  13.             response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
  14.             response.setCharacterEncoding("utf-8");
  15.             SimpleDateFormat sdf = new SimpleDateFormat("yyyy.MM.dd");
  16.             String currentDate = sdf.format(new Date());
  17.             // URLEncoder.encode 可以防止中文乱码
  18.             String fileName = URLEncoder.encode("学生信息列表" + currentDate, "UTF-8").replaceAll("\\+", "%20");
  19.             response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
  20.             EasyExcel.write(response.getOutputStream(), ExportStudentInfoVO.class)
  21.                     .sheet("学生信息")
  22.                     .registerWriteHandler(new CustomCellWriteWidthConfig()) /*自适应列宽*/
  23.                     .registerWriteHandler(new CustomCellWriteHeightConfig()) /*自适应行高*/
  24.                     .registerWriteHandler(EasyExcelUtils.getStyleStrategy()) /*引用样式*/
  25.                     .doWrite(list);
  26.         } catch (Exception e) {
  27.             log.error("导出失败~");
  28.             e.printStackTrace();
  29.         }
  30.     }
  31.  
  32. }

引用样式

  1. package cn.homed.common.utils.excel;
  2.  
  3. import com.alibaba.excel.write.metadata.style.WriteCellStyle;
  4. import com.alibaba.excel.write.metadata.style.WriteFont;
  5. import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
  6. import org.apache.poi.ss.usermodel.BorderStyle;
  7. import org.apache.poi.ss.usermodel.HorizontalAlignment;
  8. import org.apache.poi.ss.usermodel.IndexedColors;
  9. import org.apache.poi.ss.usermodel.VerticalAlignment;
  10.  
  11.  
  12. public class EasyExcelUtils {
  13.     /**
  14.      * 设置excel样式
  15.      */
  16.     public static HorizontalCellStyleStrategy getStyleStrategy() {
  17.         // 头的策略  样式调整
  18.         WriteCellStyle headWriteCellStyle = new WriteCellStyle();
  19.         // 头背景 浅绿
  20.         headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
  21.         WriteFont headWriteFont = new WriteFont();
  22.         // 头字号
  23.         headWriteFont.setFontHeightInPoints((short) 12);
  24.         // 字体样式
  25.         headWriteFont.setFontName("宋体");
  26.         headWriteCellStyle.setWriteFont(headWriteFont);
  27.         // 自动换行
  28.         headWriteCellStyle.setWrapped(true);
  29.         // 设置细边框
  30.         headWriteCellStyle.setBorderBottom(BorderStyle.THIN);
  31.         headWriteCellStyle.setBorderLeft(BorderStyle.THIN);
  32.         headWriteCellStyle.setBorderRight(BorderStyle.THIN);
  33.         headWriteCellStyle.setBorderTop(BorderStyle.THIN);
  34.         // 设置边框颜色 25灰度
  35.         headWriteCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
  36.         headWriteCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
  37.         headWriteCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
  38.         headWriteCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
  39.         // 水平对齐方式
  40.         headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
  41.         // 垂直对齐方式
  42.         headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
  43.         // 内容的策略 宋体
  44.         WriteCellStyle contentStyle = new WriteCellStyle();
  45.         // 设置垂直居中
  46.         contentStyle.setWrapped(true);
  47.         contentStyle.setVerticalAlignment(VerticalAlignment.CENTER);
  48.         // 设置 水平居中
  49. //        contentStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
  50.         WriteFont contentWriteFont = new WriteFont();
  51.         // 内容字号
  52.         contentWriteFont.setFontHeightInPoints((short) 12);
  53.         // 字体样式
  54.         contentWriteFont.setFontName("宋体");
  55.         contentStyle.setWriteFont(contentWriteFont);
  56.         // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
  57.         return new HorizontalCellStyleStrategy(headWriteCellStyle, contentStyle);
  58.     }
  59. }

自适应列宽  

  1. package cn.homed.common.utils.excel;
  2.  
  3. import com.alibaba.excel.enums.CellDataTypeEnum;
  4. import com.alibaba.excel.metadata.Head;
  5. import com.alibaba.excel.metadata.data.CellData;
  6. import com.alibaba.excel.metadata.data.WriteCellData;
  7. import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
  8. import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
  9. import org.apache.commons.collections.CollectionUtils;
  10. import org.apache.poi.ss.usermodel.Cell;
  11. import org.apache.poi.ss.usermodel.Sheet;
  12.  
  13. import java.util.HashMap;
  14. import java.util.List;
  15. import java.util.Map;
  16.  
  17.  
  18. public class CustomCellWriteWidthConfig extends AbstractColumnWidthStyleStrategy {
  19.  
  20.     private final Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<>();
  21.     @Override
  22.     protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer integer, Boolean isHead) {
  23.         boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
  24.         if (needSetWidth) {
  25.             Map<Integer, Integer> maxColumnWidthMap = CACHE.computeIfAbsent(writeSheetHolder.getSheetNo(), k -> new HashMap<>());
  26.  
  27.             Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
  28.             // 单元格文本长度大于60换行
  29.             if (columnWidth >= 0) {
  30.                 if (columnWidth > 60) {
  31.                     columnWidth = 60;
  32.                 }
  33.                 Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
  34.                 if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
  35.                     maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
  36.                     Sheet sheet = writeSheetHolder.getSheet();
  37.                     sheet.setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
  38.                 }
  39.             }
  40.         }
  41.     }
  42.     /**
  43.      * 计算长度
  44.      * @param cellDataList
  45.      * @param cell
  46.      * @param isHead
  47.      * @return
  48.      */
  49.     private Integer dataLength(List<WriteCellData<?>> cellDataList, Cell cell, Boolean isHead) {
  50.         if (isHead) {
  51.             return cell.getStringCellValue().getBytes().length;
  52.         } else {
  53.             CellData<?> cellData = cellDataList.get(0);
  54.             CellDataTypeEnum type = cellData.getType();
  55.             if (type == null) {
  56.                 return -1;
  57.             } else {
  58.                 switch (type) {
  59.                     case STRING:
  60.                         // 换行符（数据需要提前解析好）
  61.                         int index = cellData.getStringValue().indexOf("\n");
  62.                         return index != -1 ?
  63.                                 cellData.getStringValue().substring(0, index).getBytes().length + 1 : cellData.getStringValue().getBytes().length + 1;
  64.                     case BOOLEAN:
  65.                         return cellData.getBooleanValue().toString().getBytes().length;
  66.                     case NUMBER:
  67.                         return cellData.getNumberValue().toString().getBytes().length;
  68.                     default:
  69.                         return -1;
  70.                 }
  71.             }
  72.         }
  73.     }
  74. }

自适应行高

  1. package cn.homed.common.utils.excel;
  2.  
  3. import com.alibaba.excel.write.style.row.AbstractRowHeightStyleStrategy;
  4. import org.apache.poi.ss.usermodel.Cell;
  5. import org.apache.poi.ss.usermodel.CellType;
  6. import org.apache.poi.ss.usermodel.Row;
  7.  
  8. import java.util.Iterator;
  9.  
  10. public class CustomCellWriteHeightConfig extends AbstractRowHeightStyleStrategy {
  11.     /**
  12.      * 默认高度
  13.      */
  14.     private static final Integer DEFAULT_HEIGHT = 300;
  15.  
  16.     @Override
  17.     protected void setHeadColumnHeight(Row row, int relativeRowIndex) {
  18.     }
  19.  
  20.     @Override
  21.     protected void setContentColumnHeight(Row row, int relativeRowIndex) {
  22.         Iterator<Cell> cellIterator = row.cellIterator();
  23.         if (!cellIterator.hasNext()) {
  24.             return;
  25.         }
  26.         // 默认为 1行高度
  27.         int maxHeight = 1;
  28.         while (cellIterator.hasNext()) {
  29.             Cell cell = cellIterator.next();
  30.             if (cell.getCellTypeEnum() == CellType.STRING) {
  31.                 String value = cell.getStringCellValue();
  32.                 int len = value.length();
  33.                 int num = 0;
  34.                 if (len > 50) {
  35.                     num = len % 50 > 0 ? len / 50 : len / 2 - 1;
  36.                 }
  37.                 if (num > 0) {
  38.                     for (int i = 0; i < num; i++) {
  39.                         value = value.substring(0, (i + 1) * 50 + i) + "\n" + value.substring((i + 1) * 50 + i, len + i);
  40.                     }
  41.                 }
  42.                 if (value.contains("\n")) {
  43.                     int length = value.split("\n").length;
  44.                     maxHeight = Math.max(maxHeight, length) + 1;
  45.                 }
  46.             }
  47.         }
  48.         row.setHeight((short) ((maxHeight) * DEFAULT_HEIGHT));
  49.     }
  50. }

五、测试

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

postman

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

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

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

 浏览器

直接贴链接即可

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

异常

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

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

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

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

  1.         <dependency>
  2.             <groupId>org.apache.poi</groupId>
  3.             <artifactId>poi</artifactId>
  4.             <version>4.1.2</version>
  5.         </dependency>
  6.         <dependency>
  7.             <groupId>org.apache.poi</groupId>
  8.             <artifactId>poi-ooxml</artifactId>
  9.             <version>4.1.2</version>
  10.         </dependency>

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

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/数据灵魂/article/detail/61021
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号