SpringBoot 项目使用 EasyExcel 插件构建 Excel 表格格式（行高、列宽和字体等）工具类

本文主要讲了如何使用 EasyExcel 插件，在导出 Excel 时，设置行高，列宽，表头格式，内容字体大小等工具类。

1、代码使用的依赖

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

<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.20</version>
</dependency>

2、行高列宽设置工具类

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.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import org.apache.poi.ss.usermodel.*;
import org.springframework.util.CollectionUtils;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 表头宽度根据数据内容自适应
 */
public class CustomWidthStyleStrategy extends AbstractColumnWidthStyleStrategy {

    private Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<>();

    /**
     * 设置列宽
     */
    @Override
    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.computeIfAbsent(writeSheetHolder.getSheetNo(), k -> new HashMap<>());

            int 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;
        } else {
            // 不是头的话 看是什么类型 用数字加就可以了
            WriteCellData cellData = cellDataList.get(0);
            CellDataTypeEnum type = cellData.getType();
            if (type == null) {
                return -1;
            } else {
                switch (type) {
                    case STRING:
                        return cellData.getStringValue().getBytes().length + 3;
                    case BOOLEAN:
                        return cellData.getBooleanValue().toString().getBytes().length + 1;
                    case NUMBER:
                        return cellData.getNumberValue().toString().getBytes().length + 3;
                    default:
                        return -1;
                }
            }
        }
    }

    /**
     * 单元格样式策略
     */
    public static HorizontalCellStyleStrategy getHorizontalCellStyleStrategy() {
        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 设置边框
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderTop(BorderStyle.NONE);
        // 配置字体
        WriteFont contentWriteFont = new WriteFont();
        // 字体
contentWriteFont.setFontName("宋体");
        // 字体大小
contentWriteFont.setFontHeightInPoints(fontHeightInPoints);
        // 设置加粗
        contentWriteFont.setBold(false);
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        // 【水平居中需要使用以下两行】
        // 设置文字左右居中
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.JUSTIFY);
        // 设置文字上下居中
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置 自动换行
        contentWriteCellStyle.setWrapped(true);
// contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
// contentWriteCellStyle.setFillForegroundColor(IndexedColors.PINK.getIndex());
        // 样式策略
        return new HorizontalCellStyleStrategy(null, contentWriteCellStyle);
    }
}

3、代码如何使用工具类

public void exportInfo(Request request, HttpServletResponse response) {

try {
response.setContentType("multipart/form-data");
response.setCharacterEncoding("utf-8");
List<UserDto> items = new ArrayList<>();
for(int i = 0; i < 10; i++) {
UserDto user = new UserDto();
user.setName("长江" + i + "号");
user.setType(i);
items.add(user);
}
        String fileName = "用户信息";
response.setHeader(
"Content-Disposition", 
"attachment;filename*=utf-8'zh_cn'" + 
URLEncoder.encode(fileName, "UTF-8") + 
ExportFileType.EXCEL_XLSX.getDefaultExtName());

EasyExcel.write(response.getOutputStream(), OnlineInfoResultExcelDto.class)
.autoCloseStream(true)
.charset(/*fileType.equals(ExportFileType.EXCEL_XLSX.ordinal()) ? StandardCharsets.UTF_8 : */Charset.forName("GBK"))
.sheet("Sheet0")
.registerWriteHandler(getHorizontalCellStyleStrategy())
.registerWriteHandler(new CustomWidthStyleStrategy())
.doWrite(data);
} catch (IOException e) {
throw new OnlineMonitorException(500, "export error");
}
}

4、导出类

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

@Data
public class User {

@ExeclProperty("姓名")
private String name;

@ExeclProperty("类型")
private Integet type;

}

原文地址：https://blog.csdn.net/Reggie97/article/details/144447579

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！