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

Springboot + EasyExcel + Vue 实现excel下载功能_customcellwritewidthconfig

作者：odstu | 2024-02-05 14:58:51

赞

踩

customcellwritewidthconfig

一、添加EasyExcel依赖

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

二、后端代码示例

controller：

  1.     @GetMapping("/download")
  2.     public void download(HttpServletResponse response) throws IOException {
  3.         String dataFormat = new SimpleDateFormat("yyyyMMdd").format(new Date());
  4.         //xlsx格式:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet  xls格式:application/vnd.ms-excelExport
  5.         response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
  6.         response.setCharacterEncoding("utf-8");
  7.         String fileName = URLEncoder.encode("满意度调查信息导出表" + dataFormat, "UTF-8").replaceAll("\\+", "%20");
  8.         response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
  9.         EasyExcel
  10.                 .write(response.getOutputStream(), ScorePsnExcelDTO.class)
  11.                 .registerWriteHandler(EasyExcelUtils.getStyle())//引用样式
  12.                 .registerWriteHandler(new CustomCellWriteWidthConfig())//自适应列宽
  13.                 .registerWriteHandler(new CustomCellWriteHeightConfig())//自适应行高
  14.                 .sheet("调查表")
  15.                 .doWrite(data());//业务数据
  16.     }

DTO（模板数据）：

  1. package cn.hsa.pss.pw.web.thirdinterface.excelExport.dto;
  2.  
  3. import com.alibaba.excel.annotation.ExcelProperty;
  4. import lombok.Data;
  5.  
  6. /**
  7.  * 医疗机构评价 -评价人DTO
  8.  *
  9.  * @Author：
  10.  * @Date：2024-01-23 16:46
  11.  * @Description：
  12.  */
  13. @Data
  14. public class ScorePsnExcelDTO {
  15.  
  16.     @ExcelProperty(value = "序号", index = 0)
  17.     private Integer no;
  18.  
  19.     //定点统筹区（参保人统筹区
  20.     @ExcelProperty(value = "定点统筹区", index = 1)
  21.     private String areaCode;
  22.  
  23.     //医药机构编码
  24.     @ExcelProperty(value = "医药机构编码", index = 2)
  25.     private String medInsCode;
  26.  
  27.     //医药机构名称
  28.     @ExcelProperty(value = "医药机构名称", index = 3)
  29.     private String medInsName;
  30.  
  31.     //医药机构类型
  32.     @ExcelProperty(value = "医药机构类型", index = 4)
  33.     private String medInsType;
  34.  
  35.     //医疗类别
  36.     @ExcelProperty(value = "医疗类别", index = 5)
  37.     private String medType;
  38.  
  39.     //就医人次
  40.     @ExcelProperty(value = "就医人次", index = 6)
  41.     private Integer medNum;
  42.  
  43.     //参与调查人次
  44.     @ExcelProperty(value = "参与调查人次", index = 7)
  45.     private Integer scoreNum;
  46.  
  47.     //很不满意
  48.     @ExcelProperty(value = {"评价分布", "很不满意"}, index = 8)
  49.     private Integer scoreOne;
  50.  
  51.     //不满意
  52.     @ExcelProperty(value = {"评价分布", "不满意"}, index = 9)
  53.     private Integer scoreTwo;
  54.  
  55.     //一般
  56.     @ExcelProperty(value = {"评价分布", "一般"}, index = 10)
  57.     private Integer scoreThree;
  58.  
  59.     //比较满意
  60.     @ExcelProperty(value = {"评价分布", "比较满意"}, index = 11)
  61.     private Integer scoreFour;
  62.  
  63.     //很满意
  64.     @ExcelProperty(value = {"评价分布", "很满意"}, index = 12)
  65.     private Integer scoreFive;
  66.  
  67.     //参与调查率
  68.     @ExcelProperty(value = "参与调查率", index = 13)
  69.     private Double scoreRate;
  70.  
  71.     //满意度
  72.     @ExcelProperty(value = "满意度", index = 14)
  73.     private String goodRate;
  74. }

关键点1：响应头设置

  1. //如果前端接收xlsx格式，则
  2.         response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
  3.  
  4. //如果前端接收xls格式，则
  5.         response.setContentType("application/vnd.ms-excelExport");

关键点2：ScorePsnExcelDTO 

1、 @ExcelProperty(value = "序号", index = 0) 

value对应的导出excel的列名，index代表顺序

2、如果涉及到单元格合并，可以这么写：

@ExcelProperty(value = {"评价分布", "很不满意"}, index = 8)

 @ExcelProperty(value = {"评价分布", "一般"}, index = 10)

效果如下：

关键点3：

要使用get方法

自适应行高：

  1. package cn.hsa.pss.pw.web.thirdinterface.excelExport.config;
  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. /**
  11.  * 自适应行高
  12.  *
  13.  * @Author：
  14.  * @Date：2024-02-01 14:00
  15.  * @Description：
  16.  */
  17. public class CustomCellWriteHeightConfig extends AbstractRowHeightStyleStrategy {
  18.  
  19.     /**
  20.      * 默认高度
  21.      */
  22.     private static final Integer DEFAULT_HEIGHT = 300;
  23.  
  24.     @Override
  25.     protected void setHeadColumnHeight(Row row, int relativeRowIndex) {
  26.  
  27.     }
  28.  
  29.     @Override
  30.     protected void setContentColumnHeight(Row row, int relativeRowIndex) {
  31.         Iterator<Cell> cellIterator = row.cellIterator();
  32.         if (!cellIterator.hasNext()) {
  33.             return;
  34.         }
  35.         // 默认为 1行高度
  36.         int maxHeight = 1;
  37.         while (cellIterator.hasNext()) {
  38.             Cell cell = cellIterator.next();
  39.             if (cell.getCellTypeEnum() == CellType.STRING) {
  40.                 String value = cell.getStringCellValue();
  41.                 int len = value.length();
  42.                 int num = 0;
  43.                 if (len > 50) {
  44.                     num = len % 50 > 0 ? len / 50 : len / 2 - 1;
  45.                 }
  46.                 if (num > 0) {
  47.                     for (int i = 0; i < num; i++) {
  48.                         value = value.substring(0, (i + 1) * 50 + i) + "\n" + value.substring((i + 1) * 50 + i, len + i);
  49.                     }
  50.                 }
  51.                 if (value.contains("\n")) {
  52.                     int length = value.split("\n").length;
  53.                     maxHeight = Math.max(maxHeight, length) + 1;
  54.                 }
  55.             }
  56.         }
  57.         row.setHeight((short) ((maxHeight) * DEFAULT_HEIGHT));
  58.     }
  59.  
  60.  
  61. }

自适应列宽：

  1. package cn.hsa.pss.pw.web.thirdinterface.excelExport.config;
  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.  * 自适应列宽
  19.  *
  20.  * @Author：
  21.  * @Date：2024-02-01 13:38
  22.  * @Description：
  23.  */
  24. public class CustomCellWriteWidthConfig extends AbstractColumnWidthStyleStrategy {
  25.  
  26.     private final Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<>();
  27.  
  28.     protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer integer, Boolean isHead) {
  29.         boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
  30.         if (needSetWidth) {
  31.             Map<Integer, Integer> maxColumnWidthMap = CACHE.computeIfAbsent(writeSheetHolder.getSheetNo(), k -> new HashMap<>());
  32.  
  33.             Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
  34.             // 单元格文本长度大于60换行
  35.             if (columnWidth >= 0) {
  36.                 if (columnWidth > 60) {
  37.                     columnWidth = 60;
  38.                 }
  39.                 Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
  40.                 if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
  41.                     maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
  42.                     Sheet sheet = writeSheetHolder.getSheet();
  43.                     sheet.setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
  44.                 }
  45.             }
  46.         }
  47.     }
  48.  
  49.     /**
  50.      * 计算长度
  51.      *
  52.      * @param cellDataList
  53.      * @param cell
  54.      * @param isHead
  55.      * @return
  56.      */
  57.     private Integer dataLength(List<WriteCellData<?>> cellDataList, Cell cell, Boolean isHead) {
  58.         if (isHead) {
  59.             return cell.getStringCellValue().getBytes().length;
  60.         } else {
  61.             CellData<?> cellData = cellDataList.get(0);
  62.             CellDataTypeEnum type = cellData.getType();
  63.             if (type == null) {
  64.                 return -1;
  65.             } else {
  66.                 switch (type) {
  67.                     case STRING:
  68.                         // 换行符（数据需要提前解析好）
  69.                         int index = cellData.getStringValue().indexOf("\n");
  70.                         return index != -1 ?
  71.                                 cellData.getStringValue().substring(0, index).getBytes().length + 1 : cellData.getStringValue().getBytes().length + 1;
  72.                     case BOOLEAN:
  73.                         return cellData.getBooleanValue().toString().getBytes().length;
  74.                     case NUMBER:
  75.                         return cellData.getNumberValue().toString().getBytes().length;
  76.                     default:
  77.                         return -1;
  78.                 }
  79.             }
  80.         }
  81.     }
  82.  
  83.  
  84. }

字体样式工具类：

  1. package cn.hsa.pss.pw.web.thirdinterface.excelExport.utils;
  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.  
  10. import java.util.ArrayList;
  11.  
  12. /**
  13.  * 设置excel工具类
  14.  *
  15.  * @Author：wangguangxing
  16.  * @Date：2024-02-01 14:18
  17.  * @Description：
  18.  */
  19. public class EasyExcelUtils {
  20.  
  21.     public static HorizontalCellStyleStrategy getStyle() {
  22. //自定义表头样式  浅橙色 居中
  23.         WriteCellStyle headCellStyle = new WriteCellStyle();
  24.         headCellStyle.setFillForegroundColor(IndexedColors.TAN.getIndex());  //表头颜色
  25.         headCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);    //文本居中
  26.         //字体
  27.         WriteFont writeFont = new WriteFont();
  28.         writeFont.setFontName("微软雅黑");                                   //字体
  29.         writeFont.setFontHeightInPoints((short) 10);                         //字体大小
  30.         headCellStyle.setWriteFont(writeFont);
  31.         // 自动换行
  32.         headCellStyle.setWrapped(true);
  33.  
  34.         //内容样式
  35.         WriteCellStyle contentCellStyle = new WriteCellStyle();
  36.         contentCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER); //文本居中
  37.         contentCellStyle.setWriteFont(writeFont);
  38.         //设置边框
  39.         contentCellStyle.setBorderLeft(BorderStyle.THIN);                    //左边框线
  40.         contentCellStyle.setBorderTop(BorderStyle.THIN);                     //顶部框线
  41.         contentCellStyle.setBorderRight(BorderStyle.THIN);                   //右边框线
  42.         contentCellStyle.setBorderBottom(BorderStyle.THIN);                  //底部框线
  43.         ArrayList<WriteCellStyle> contentCells = new ArrayList<>();
  44.         contentCells.add(contentCellStyle);
  45.         //样式策略
  46.         HorizontalCellStyleStrategy handler = new HorizontalCellStyleStrategy();
  47.         handler.setHeadWriteCellStyle(headCellStyle);                        //表头样式
  48.         handler.setContentWriteCellStyleList(contentCells);                  //内容样式
  49.         return new HorizontalCellStyleStrategy(headCellStyle, contentCells);
  50.     }
  51. }

三、前端代码示例

  1. 1、
  2.      exportFile() {
  3.           
  4.                 this.downLoading = true
  5.                 exportScoreList().then((res) => {
  6.                     this.downLoading = false
  7.                     const str = res.headers["content-disposition"]
  8.                     const fileName = decodeURI(str.substr(str.indexOf("%")))
  9.                     this.downloadFile(res.data, fileName)
  10.                 }).catch((err) => {
  11.                     this.downLoading = false
  12.                 })
  13.             }
  14.  
  15.  
  16. 2、
  17.  downloadFile(res, fileName) {
  18.                
  19.                 let blob = new Blob([res], {type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'})
  20.                 if (!fileName) {
  21.                     fileName = res.headers['content-disposition'].split('filename=').pop();
  22.                 }
  23.                 if ('msSaveOrOpenBlob' in navigator) {
  24.                     window.navigator.msSaveOrOpenBlob(blob, fileName);
  25.                 } else {
  26.                     const elink = document.createElement('a');
  27.                     elink.download = fileName;
  28.                     elink.style.display = 'none';
  29.                     elink.href = window.URL.createObjectURL(blob);
  30.                     document.body.appendChild(elink);
  31.                     elink.setAttribute('href', elink.href)
  32.                     elink.click();
  33.                     document.body.removeChild(elink);
  34.                     window.URL.revokeObjectURL(elink.href);
  35.  
  36.                 }
  37.             }
  38.  
  39. 3、
  40. export function exportScoreList() {
  41.     return axios({
  42.         url: `${path}/excel/download`,
  43.         method: "get",
  44.         responseType: "blob"
  45.     });
  46. }
  47.

关键点1：

responseType: "blob"    method: "get",

关键点2：

 let blob = new Blob([res], {type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'}) 

要与后台响应头类型对应上。

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

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

  

闽ICP备14008679号