Easyexcel工具类封装及表格自动列宽设置

Easyexcel工具类封装及表格自动列宽设置

1、导入依赖

<!-- Easyexcel依赖，版本根据个人需求自行选择 -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.3.1</version>
</dependency>

2、封装工具类EasyexcelUtils

@Slf4j
public class EasyExcelUtil {
    /**
     * 导出 Excel 直接可以在Spring web Controller 中使用
     *
     * @param response  HttpServletResponse
     * @param fileName  文件名
     * @param list      数据 list，
     * @param clazz     导出结构体
     * @param sheetName 导入文件的 sheet 名
     */
    public static <T> void exportExcel(HttpServletResponse response, String fileName, List<T> list, Class<T> clazz, String sheetName) {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        //处理文件名
        String downloadFileName = fileName + MyDateUtils.format(new Date(), DateTimeFormat.forPattern("yyyyMMddHHmmss")) + ".xlsx";
        // 这里URLEncoder.encode可以防止中文乱码
        String fileNameEnCode = null;
        try {
            fileNameEnCode = URLEncoder.encode(downloadFileName, "UTF-8").replaceAll("\\+", "+");
        } catch (UnsupportedEncodingException e) {
            log.error("UnsupportedEncodingException: ", e);
        }
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileNameEnCode);
        try {
            writeExcel(response.getOutputStream(), list, clazz, sheetName);
        } catch (IOException e) {
            log.error("Excel数据导出异常：", e);
        }
    }
}

3、设置下载表格自动列宽

public class ExcelColumnHandler extends AbstractColumnWidthStyleStrategy {

    private static final int MAX_COLUMN_WIDTH = 255;
    private static final int COLUMN_WIDTH = 20;
    private  Map<Integer, Map<Integer, Integer>> CACHE = new HashMap(8);

    public ExcelColumnHandler() {
    }

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

            Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
            if (columnWidth >= 0) {
                if (columnWidth > MAX_COLUMN_WIDTH) {
                    columnWidth = MAX_COLUMN_WIDTH;
                }else {
                    if(columnWidth<COLUMN_WIDTH){
                        columnWidth =columnWidth*2;
                    }
                }

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

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

3、设置下载实体对象

@Data
public class ClassVO {

    /**
     * 主键ID
     */
    @ExcelIgnore
    private String idNo;

    /**
     * 线索编号
     */
    @ExcelProperty(value = "姓名", index = 0)
    private String name;

    /**
     * 所属区域
     */
    @ExcelProperty(value = "年龄", index = 1)
    private Integer age;

    /**
     * 来源渠道
     */
    @ExcelProperty(value = "年级", index = 2)
    private String classNo;
}

@ExcelProperty中：value对应的值为下载的列名，index为第几列

5、具体实现

public void download(HttpServletResponse response, ClassParam classParam) {
    String fileName = classParam.getFileName();
    // 查询数据库获取数据
    List<ClueVO> = getList(classParam).getData();
    EasyExcelUtil.exportExcel(response, fileName, list, ClassVO.class, fileName);
}