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

 需引入的实体类

<dependency>
    <groupId>com.alibaba</groupId>
	<artifactId>easyexcel</artifactId>
	<version>2.2.3</version>
</dependency>
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi</artifactId>
	<version>3.17</version>
</dependency>

 Excel对应实体类

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

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
 
@Data
public class Vo {
    @ExcelProperty("AA")
    @ApiModelProperty(value = "AA")
    private java.lang.String aa;
 
    @ExcelProperty("BB")
    @ApiModelProperty(value = "BB")
    private java.lang.String bb;
 
    // 忽略该属性
    @ExcelIgnore()
    @ApiModelProperty(value = "CC")
    private java.lang.String cc;
}

 后端接口代码

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

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

 自适应宽度

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

import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.util.CollectionUtils;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import org.apache.poi.ss.usermodel.Cell;
 
import java.nio.ByteBuffer;
import java.nio.CharBuffer;
import java.nio.charset.StandardCharsets;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
 
public class CustomCellWriteWeightConfig extends AbstractColumnWidthStyleStrategy {
    private static final int MAX_COLUMN_WIDTH = 255;
    private static final int COLUMN_WIDTH_BASE = 255;
    private final Map<Integer, Map<Integer, Double>> cache = new HashMap<>(8);
 
    private Integer relativeRowIndex = -1;
 
    public CustomCellWriteWeightConfig() {
    }
 
    public CustomCellWriteWeightConfig(Integer relativeRowIndex) {
        //这里是指定从第几行开始自适应。0是第一行，1是第二行，以此类推
        this.relativeRowIndex = relativeRowIndex;
    }
 
    @Override
    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head, Integer integer, Boolean isHead) {
        boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
        if (needSetWidth) {
            if(this.relativeRowIndex == -1 || relativeRowIndex >= this.relativeRowIndex){
                Map<Integer, Double> maxColumnWidthMap = cache.computeIfAbsent(writeSheetHolder.getSheetNo(), k -> new HashMap<>(16));
 
                double columnWidth = this.dataLength(cellDataList, cell, isHead);
                if (columnWidth >= 0) {
                    if (columnWidth > MAX_COLUMN_WIDTH) {
                        columnWidth = MAX_COLUMN_WIDTH;
                    }
                    Double maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
                    if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
                        maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
                        writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), (int)(maxColumnWidthMap.get(cell.getColumnIndex())*COLUMN_WIDTH_BASE));
                    }
                }
            }
        }
    }
 
    /**
     * 计算长度
     * @param cellDataList
     * @param cell
     * @param isHead
     * @return
     */
    private double dataLength(List<CellData> cellDataList, Cell cell, Boolean isHead) {
        if (isHead) {
            return cell.getStringCellValue().getBytes().length;
        } else {
            CellData<?> cellData = cellDataList.get(0);
            CellDataTypeEnum type = cellData.getType();
            if (type == null) {
                return -1;
            } else {
                switch (type) {
                    case STRING:
                        return getExcelWidth(cellData.getStringValue());
                    case BOOLEAN:
                        return getExcelWidth(cellData.getBooleanValue().toString());
                    case NUMBER:
                        return getExcelWidth(cellData.getNumberValue().toString());
                    default:
                        return -1;
                }
            }
        }
    }
 
    /**
     * 调整单元格字符字节宽度，easyExcel默认直接用的UTF-8的byte长度，导致一旦三字节的字符过多就会变得很宽，一字节的字符过多就会不够宽
     */
    private double getExcelWidth(String str){
        double length = 0.0;
        char[] chars = str.toCharArray();
        for(char c : chars){
            byte[] bytes = this.getUtf8Bytes(c);
            if(bytes.length == 1){
                length += 1.05;
            }
            if(bytes.length == 2){
                length += 1.5;
            }
            if(bytes.length == 3){
                length += 1.85;
            }
            if(bytes.length == 4){
                length += 2.2;
            }
        }
        return length;
    }
 
    private byte[] getUtf8Bytes(char c) {
        char[] chars = {c};
        CharBuffer charBuffer = CharBuffer.allocate(chars.length);
        charBuffer.put(chars);
        charBuffer.flip();
        ByteBuffer byteBuffer = StandardCharsets.UTF_8.encode(charBuffer);
        return byteBuffer.array();
    }
 
}