devbox
代码吟游诗人
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

EasyExcel导入导出样式、自适应列宽、自适应行高_easyexcel设置列宽自适应

作者：代码吟游诗人 | 2024-02-05 15:15:36

赞

踩

easyexcel设置列宽自适应

一、先加依赖

  1.         <dependency>
  2.             <groupId>com.alibaba</groupId>
  3.             <artifactId>easyexcel</artifactId>
  4.             <version>3.1.1</version>
  5.         </dependency>

二、话不多说，直接先看导出样式设置，可以根据自己实际情况调整：

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

 三、自适应列宽

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

四、自适应行高

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

五、导出

  1. 先创建导出实体类
  1. import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
  2. import com.alibaba.excel.annotation.ExcelProperty;
  3. import com.terton.aisp.ssp.entity.Feedback;
  4. import lombok.AllArgsConstructor;
  5. import lombok.Data;
  6. import lombok.NoArgsConstructor;
  7.  
  8. @Data
  9. @AllArgsConstructor
  10. @NoArgsConstructor
  11. @ExcelIgnoreUnannotated // 只导出带有ExcelProperty注解的字段
  12. public class ExcelFeedbackVO extends Feedback {
  13.  
  14.     /**
  15.      * 站点ID
  16.      */
  17.     // 设置表头名称 ，索引最好带上，不会出问题。
  18.     @ExcelProperty(value = "站点id", index = 0)
  19.     private String siteId;
  20.     /**
  21.      * 搜索内容
  22.      */
  23.     @ExcelProperty(value = "搜索内容", index = 1)
  24.     private String title;
  25.     /**
  26.      * 反馈内容
  27.      */
  28.     @ExcelProperty(value = "反馈内容", index = 2)
  29.     private String content;
  30.     /**
  31.      * IP地址
  32.      */
  33.     @ExcelProperty(value = "搜索IP", index = 3)
  34.     private String ip;
  35.  
  36. }

        2、创建一个对外暴露的接口。

  1. @PostMapping("/downloadBack")    
  2. public void downloadBack(HttpServletResponse response, String siteId, Integer content) throws TException, IOException {
  3.         List<Feedback> list = feedbackService.searchBack(siteId, content);
  4.         setResponse(response, "列表");
  5.         EasyExcel.write(response.getOutputStream(), ExcelFeedbackVO.class /* 引用需要导出的字段*/)
  6.                 .sheet("列表")
  7.                 .registerWriteHandler(new CustomCellWriteWidthConfig()) /*自适应列宽*/
  8.                 .registerWriteHandler(new CustomCellWriteHeighConfig()) /*自适应行高（根据自己情况选择使用，我这里没用到）*/
  9.                 .registerWriteHandler(EasyExcelUtils.getStyleStrategy()) /*引用样式*/
  10.                 .doWrite(list); /* 想到导出模板的话，此处给一个空的集合就好*/
  11.     }

        3、设置响应体信息（由于我这个项目导入导出比较多，把此方法变成公共方法）

  1. import javax.servlet.http.HttpServletResponse;
  2. import java.net.URLEncoder;
  3. import java.nio.charset.StandardCharsets;
  4.  
  5. public class SetResponse {
  6.     /**
  8.      * 导出设置响应信息
  9.      *
  10.      * @param response 响应
  11.      */
  12.     public static void setResponse(HttpServletResponse response,String fileName) {
  13.         // 文件名
  14.         String sheetName = URLEncoder.encode(fileName, StandardCharsets.UTF_8) + ".xlsx";
  15.         // contentType 响应内容的类型
  16.         response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
  17.         // 设置字符
  18.         response.setCharacterEncoding("utf-8");
  19.         // 设置文件名
  20.         response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + sheetName);
  21.     }
  22.  
  23. }

         4、访问接口

                模板

模板

                 正常导出

数据

 六、导入

        1、监听器（关键）

         注意事项：监听器里面不能使用注入的形式将service注入进来，所以可以使用构造器

  1. import com.alibaba.excel.context.AnalysisContext;
  2. import com.alibaba.excel.event.AnalysisEventListener;
  3. import com.terton.aisp.common.enums.ExcelNameModelEnum;
  4. import com.terton.aisp.common.util.PingYinUtil;
  5. import com.terton.aisp.word.entity.MassWord;
  6. import com.terton.aisp.word.enums.UploadBatchCountEnum;
  7. import com.terton.aisp.word.service.IMassWordService;
  8. import com.terton.aisp.word.service.impl.MassWordServiceImpl;
  9. import com.terton.aisp.word.vo.ExcelMassWordModelVO;
  10. import com.terton.framework.util.StringUtil;
  11. import lombok.AllArgsConstructor;
  12. import lombok.NoArgsConstructor;
  13. import lombok.extern.slf4j.Slf4j;
  14. import org.springframework.util.CollectionUtils;
  15.  
  16. import java.util.ArrayList;
  17. import java.util.Date;
  18. import java.util.List;
  19. import java.util.Map;
  20. import java.util.stream.Collectors;
  21.  
  22. @Slf4j
  23. @AllArgsConstructor
  24. @NoArgsConstructor
  25. public class UploadMassWordListener extends AnalysisEventListener<ExcelMassWordModelVO> {
  26.     private IMassWordService massWordService;
  27.     private String siteId;
  28.     private Integer wordLevel;
  29.     private String userName;
  30.     private List<ExcelMassWordModelVO> list = new ArrayList<>();
  31.     private List<String> massWordList = new ArrayList<>();
  32.     private List<String> massWords = new ArrayList<>();
  33.     private Integer successNum = 0;
  34.     private Integer errorNum = 0;
  35.  
  36.     /**
  37.      * 表头校验 此方法里面可进行对导入的excel的格式进行判断，先执行
  38.      */
  39.     @Override
  40.     public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
  41.              // headMap 表头数据，拿出进行判断就好
  42.     }
  43.  
  44.     /**
  45.      * 每读一行触发一次 
  46.      *
  47.      * @param modelVO         ”
  48.      * @param analysisContext “
  49.      */
  50.     @Override
  51.     public void invoke(ExcelMassWordModelVO modelVO, AnalysisContext analysisContext) {
  52.        log.info("读取到一条数据：{}",modelVO)
  53.        // 可以对数据进行判断是否有效。 根据自己的实际业务逻辑判断，
  54.        // 判断完成的数据可以用一个List来接收
  55.        // 为了防止数据过多，数据一直存在内存中，这里可以设置一个阈值，当list中的数据超过这个值，就先把这批数据添加到数据库。
  56.        if (StringUtil.notBlankAndNull(modelVO.getMassWord()) && StringUtil.notBlankAndNull(modelVO.getTargetWord())) {
  57.             if (!CollectionUtils.isEmpty(massWords) && massWords.contains(modelVO.getMassWord())) {
  58.                 throw new RuntimeException(ExcelNameModelEnum.FailImport.RESULT_FIELD.getText() + modelVO.getMassWord());
  59.             }
  60.             massWords.add(modelVO.getMassWord());
  61.             massWordList.add(modelVO.getMassWord());
  62.             list.add(modelVO);
  63.             try {
  64.                 if (list.size() >= UploadBatchCountEnum.MASS_WORD.getCount() /*我这里阈值设置的是500*/) {
  65.                     save();
  66.                 }
  67.             } catch (Exception e) {
  68.                 throw new RuntimeException(e.getMessage());
  69.             }
  70.         }
  71.         
  72.     }
  73.  
  74.     /**
  75.      * 完毕触发
  76.      *
  77.      * @param analysisContext ”
  78.      */
  79.     @Override
  80.     public void doAfterAllAnalysed(AnalysisContext analysisContext) {
  81.         // 写数据库
  82.         if (!CollectionUtils.isEmpty(list)) {
  83.             save();
  84.         }
  85.         massWords.clear();
  86.         log.info("成功写入数量:{}", successNum);
  87.         log.info("失败写入数量:{}", errorNum);
  88.         successNum = 0;
  89.         errorNum = 0;
  90.     }
  91.  
  92.     private void save() {
  93.         try {
  94.             List<MassWord> recordList = massWordService.getByMasswordsAndSiteId(massWordList, siteId);
  95.             if (!CollectionUtils.isEmpty(recordList)) {
  96.                 List<String> collect = recordList.stream().map(MassWord::getMassWord).collect(Collectors.toList());
  97.                 if (!CollectionUtils.isEmpty(collect)) {
  98.                     throw new RuntimeException(ExcelNameModelEnum.FailImport.RESULT_FIELD.getText() + collect);
  99.                 }
  100.             }
  101.             for (ExcelMassWordModelVO wordModelVO : list) {
  102.                 MassWord newMassWord = getMassWord(wordModelVO);
  103.                 massWordService.save(newMassWord);
  104.                 successNum += 1;
  105.             }
  106.         } catch (Exception e) {
  107.             errorNum += 1;
  108.             throw new RuntimeException(e.getMessage());
  109.         }
  110.         massWordList.clear();
  111.         list.clear();
  112.     }
  113.  
  114.     private MassWord getMassWord(ExcelMassWordModelVO wordModelVO) {
  115.         MassWord massWord = new MassWord();
  116.         massWord.setMassWord(wordModelVO.getMassWord());
  117.         massWord.setTargetWord(wordModelVO.getTargetWord());
  118.         massWord.setWordLevel(wordLevel);
  119.         massWord.setSiteId(siteId);
  120.         massWord.setAcronym(PingYinUtil.getFirstSpell(wordModelVO.getMassWord()));
  121.         massWord.setFullPinYin(PingYinUtil.getFullSpell(wordModelVO.getMassWord()));
  122.         massWord.setCrTime(new Date());
  123.         massWord.setCrUser(userName);
  124.         return massWord;
  125.     }
  126.  
  127.     public UploadMassWordListener(MassWordServiceImpl massWordService, String siteId,Integer wordLevel, String userName) {
  128.         this.massWordService = massWordService;
  129.         this.siteId = siteId;
  130.         this.wordLevel = wordLevel;
  131.         this.userName = userName;
  132.     }
  133.  
  134. }

        2、导入与监听器的使用。

  1.     public void upload(MultipartFile file, String siteId, Integer wordLevel,String userName) throws IOException {
  2.         UploadMassWordListener listener = new UploadMassWordListener(this, siteId, wordLevel, userName);
  3.         EasyExcel.read(file.getInputStream(), ExcelMassWordModelVO.class, listener)
  4.                 .sheet(0)
  5.                 .headRowNumber(1)
  6.                 .doRead();
  7.     }

到这里大概就结束了，希望对你有用。

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

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

  

闽ICP备14008679号