当前位置:   article > 正文

Spring Boot 导出xlsx_spring-boot 输出 xlsx 文件流

spring-boot 输出 xlsx 文件流

 需引入的实体类

  1. <dependency>
  2. <groupId>com.alibaba</groupId>
  3. <artifactId>easyexcel</artifactId>
  4. <version>2.2.3</version>
  5. </dependency>
  6. <dependency>
  7. <groupId>org.apache.poi</groupId>
  8. <artifactId>poi</artifactId>
  9. <version>3.17</version>
  10. </dependency>

 Excel对应实体类

list需要有一个对应的实体类,可以通过ExcelProperty注解来设置列名,可以通过ExcelIgnore注解来忽略某个属性

  1. import com.alibaba.excel.annotation.ExcelIgnore;
  2. import com.alibaba.excel.annotation.ExcelProperty;
  3. import io.swagger.annotations.ApiModelProperty;
  4. import lombok.Data;
  5. @Data
  6. public class Vo {
  7. @ExcelProperty("AA")
  8. @ApiModelProperty(value = "AA")
  9. private java.lang.String aa;
  10. @ExcelProperty("BB")
  11. @ApiModelProperty(value = "BB")
  12. private java.lang.String bb;
  13. // 忽略该属性
  14. @ExcelIgnore()
  15. @ApiModelProperty(value = "CC")
  16. private java.lang.String cc;
  17. }

 后端接口代码

 前端可以通过该接口直接获取xlsx信息,通过HttpServletResponse直接返回数据流

  1. @GetMapping(value = "/exportXlsx")
  2. public Result exportXlsx(HttpServletResponse response) throws IOException{
  3. // 设置响应头
  4. response.setContentType("application/vnd.ms-excel");
  5. response.setCharacterEncoding("utf-8");
  6. // 设置防止中文名乱码
  7. String filename = URLEncoder.encode("列表信息", "utf-8");
  8. // 获取当前时间
  9. SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
  10. String date = simpleDateFormat.format(new Date());
  11. // 文件下载方式(附件下载还是在当前浏览器打开) 给xlsx加上时间戳 便于查看
  12. response.setHeader("Content-disposition", "attachment;filename=" +
  13. filename+"-"+ simpleDateFormat.format(new Date())+ "-" + System.currentTimeMillis() + ".xlsx");
  14. //需要返回的信息的列表
  15. List<Vo> excelList = new ArrayList<>();
  16. // 写入数据到excel 第二个参数必须与excelList对应 registerWriteHandler来自适应宽度
  17. EasyExcel.write(response.getOutputStream(), Vo.class)
  18. .sheet("列表信息").registerWriteHandler(new CustomCellWriteWeightConfig(0))
  19. .doWrite(excelList);
  20. return Result.OK("导出列表信息成功");
  21. }

 自适应宽度

自适应设置excel中的的每个列宽

  1. import com.alibaba.excel.enums.CellDataTypeEnum;
  2. import com.alibaba.excel.metadata.CellData;
  3. import com.alibaba.excel.metadata.Head;
  4. import com.alibaba.excel.util.CollectionUtils;
  5. import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
  6. import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
  7. import org.apache.poi.ss.usermodel.Cell;
  8. import java.nio.ByteBuffer;
  9. import java.nio.CharBuffer;
  10. import java.nio.charset.StandardCharsets;
  11. import java.util.HashMap;
  12. import java.util.List;
  13. import java.util.Map;
  14. public class CustomCellWriteWeightConfig extends AbstractColumnWidthStyleStrategy {
  15. private static final int MAX_COLUMN_WIDTH = 255;
  16. private static final int COLUMN_WIDTH_BASE = 255;
  17. private final Map<Integer, Map<Integer, Double>> cache = new HashMap<>(8);
  18. private Integer relativeRowIndex = -1;
  19. public CustomCellWriteWeightConfig() {
  20. }
  21. public CustomCellWriteWeightConfig(Integer relativeRowIndex) {
  22. //这里是指定从第几行开始自适应。0是第一行,1是第二行,以此类推
  23. this.relativeRowIndex = relativeRowIndex;
  24. }
  25. @Override
  26. protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head, Integer integer, Boolean isHead) {
  27. boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
  28. if (needSetWidth) {
  29. if(this.relativeRowIndex == -1 || relativeRowIndex >= this.relativeRowIndex){
  30. Map<Integer, Double> maxColumnWidthMap = cache.computeIfAbsent(writeSheetHolder.getSheetNo(), k -> new HashMap<>(16));
  31. double columnWidth = this.dataLength(cellDataList, cell, isHead);
  32. if (columnWidth >= 0) {
  33. if (columnWidth > MAX_COLUMN_WIDTH) {
  34. columnWidth = MAX_COLUMN_WIDTH;
  35. }
  36. Double maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
  37. if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
  38. maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
  39. writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), (int)(maxColumnWidthMap.get(cell.getColumnIndex())*COLUMN_WIDTH_BASE));
  40. }
  41. }
  42. }
  43. }
  44. }
  45. /**
  46. * 计算长度
  47. * @param cellDataList
  48. * @param cell
  49. * @param isHead
  50. * @return
  51. */
  52. private double dataLength(List<CellData> cellDataList, Cell cell, Boolean isHead) {
  53. if (isHead) {
  54. return cell.getStringCellValue().getBytes().length;
  55. } else {
  56. CellData<?> cellData = cellDataList.get(0);
  57. CellDataTypeEnum type = cellData.getType();
  58. if (type == null) {
  59. return -1;
  60. } else {
  61. switch (type) {
  62. case STRING:
  63. return getExcelWidth(cellData.getStringValue());
  64. case BOOLEAN:
  65. return getExcelWidth(cellData.getBooleanValue().toString());
  66. case NUMBER:
  67. return getExcelWidth(cellData.getNumberValue().toString());
  68. default:
  69. return -1;
  70. }
  71. }
  72. }
  73. }
  74. /**
  75. * 调整单元格字符字节宽度,easyExcel默认直接用的UTF-8的byte长度,导致一旦三字节的字符过多就会变得很宽,一字节的字符过多就会不够宽
  76. */
  77. private double getExcelWidth(String str){
  78. double length = 0.0;
  79. char[] chars = str.toCharArray();
  80. for(char c : chars){
  81. byte[] bytes = this.getUtf8Bytes(c);
  82. if(bytes.length == 1){
  83. length += 1.05;
  84. }
  85. if(bytes.length == 2){
  86. length += 1.5;
  87. }
  88. if(bytes.length == 3){
  89. length += 1.85;
  90. }
  91. if(bytes.length == 4){
  92. length += 2.2;
  93. }
  94. }
  95. return length;
  96. }
  97. private byte[] getUtf8Bytes(char c) {
  98. char[] chars = {c};
  99. CharBuffer charBuffer = CharBuffer.allocate(chars.length);
  100. charBuffer.put(chars);
  101. charBuffer.flip();
  102. ByteBuffer byteBuffer = StandardCharsets.UTF_8.encode(charBuffer);
  103. return byteBuffer.array();
  104. }
  105. }

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

闽ICP备14008679号