Vue downloads excel and adaptive table width (front and back end)

This article uses SpringBoot + vue + easyExcel to implement the function of exporting Excel, and solve the problem of Chinese garbled characters in the file and the problem of adaptive width of Excel. You need to import the pom package first.

 <dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
</dependency>

easyExcel open source address: https://github.com/alibaba/easyexcel

1 excel file download

Download the excel flow file in vue and set the download file name: https://segmentfault.com/a/1190000037500013

Use vue-json-excel control: https://www.npmjs.com/package/vue-json-excel

method one:

Artificially construct a label and click it automatically.

PS: Note that the file name cannot be obtained through this.filename in the above link.

 axios
    .get(`/api/audit/export`, {
      responseType: "blob" //服务器响应的数据类型，可以是 'arraybuffer', 'blob', 'document', 'json', 'text', 'stream'，默认是'json'
    })
    .then((res) => {
      if (!res) return;
      const blob = new Blob([res.data], { type: "application/vnd.ms-excel" }); // 构造一个blob对象来处理数据，并设置文件类型

      if (window.navigator.msSaveOrOpenBlob) {
        //兼容IE10
        navigator.msSaveBlob(blob, this.filename);
      } else {
        const href = URL.createObjectURL(blob); //创建新的URL表示指定的blob对象
        const a = document.createElement("a"); //创建a标签
        a.style.display = "none";
        a.href = href; // 指定下载链接
        a.download = "test.xlsx"; //指定下载文件名
        a.click(); //触发下载
        URL.revokeObjectURL(a.href); //释放URL对象
      }
      // 这里也可以不创建a链接，直接window.open(href)也能下载
    })
    .catch((err) => {
      console.log(err);
    });

Method two:

Use the js-file-download package

1. get request

 axios
.get(`后端接口链接`, {
  responseType: "blob" //返回的数据类型
})
.then((res) => {
  fileDownload(res.data, "test111.xlsx");
});

2. post request

 formData.value.pageNum = 1;
formData.value.pageSize = 200;

axios
.post(`后端接口链接`, formData.value, {
  responseType: "blob" //返回的数据类型
})
.then((res) => {
  fileDownload(res.data, "test111.xlsx");
});

2 File name acquisition, and garbled solution

https://www.jianshu.com/p/22d591ed0c34

The backend sets the encoding method to utf-8

 fileName = URLEncoder.encode("中文excel文件名称", "UTF-8").replaceAll("\\+", "%20");

Vue front end: use decodeURIComponent to decompile Chinese file titles

 let dis = res.headers["content-disposition"];
let filename = decodeURIComponent(dis.split("attachment;filename*=")[1]);

3 Adaptive excel width

https://chowdera.com/2022/02/202202160504097268.html

https://www.codetd.com/en/article/13815011

3.1 Inherit AbstractColumnWidthStyleStrategy to implement a width strategy

This width strategy will set the width of the title based on the width of the title or the width of the content.

PS: Appropriately increasing the width can avoid the problem that the number is displayed as *; avoid the problem that the time is displayed as #

 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 com.qunar.base.meerkat.util.DateUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.ss.usermodel.Cell;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * easyExcel 自适应列宽
 *
 */
@Slf4j
public class ExcelWidthStyleStrategy extends AbstractColumnWidthStyleStrategy {

    private Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<>();
    
    // 适当增加宽度，能避免 数字显示为 * 的问题
    public static final int DEFAULT = 2;

    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
        if (needSetWidth) {
            Map<Integer, Integer> maxColumnWidthMap = CACHE.get(writeSheetHolder.getSheetNo());
            if (maxColumnWidthMap == null) {
                maxColumnWidthMap = new HashMap<>();
                CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
            }

            Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
            if (columnWidth >= 0) {
                if (columnWidth > 255) {
                    columnWidth = 255;
                }

                Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
                if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
                    maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
                    writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
                }

            }
        }
    }

    private Integer dataLength(List<WriteCellData<?>> cellDataList, Cell cell, Boolean isHead) {
        if (isHead) {
            return cell.getStringCellValue().getBytes().length + DEFAULT;
        } else {
            CellData cellData = cellDataList.get(0);
            CellDataTypeEnum type = cellData.getType();
            if (type == null) {
                return -1;
            } else {
                switch (type) {
                    case STRING:
                        return cellData.getStringValue().getBytes().length + DEFAULT;
                    case BOOLEAN:
                        return cellData.getBooleanValue().toString().getBytes().length + DEFAULT;
                    case NUMBER:
                        return cellData.getNumberValue().toString().getBytes().length + DEFAULT;
                    case DATE:
                        return DateUtil.PATTERN_YYYY_MM_DD_HH_MM_SS.getBytes().length + DEFAULT;
                    default:
                        return -1;
                }
            }
        }
    }
}

3.2 easyExcel write file registration handler

 EasyExcel.write(response.getOutputStream(), DownloadData.class).registerWriteHandler(new ExcelWidthStyleStrategy()).sheet("模板").doWrite(downloadDataList);