EasyExcel文件导出

EasyExcel文件导出

EasyExcel导出工具类

public class EasyExcelExportUtil {

    private static final short FONT_SIZE_TEN = 10;
    private static final short FONT_SIZE_ELEVEN = 11;

    /**
     * easyExcel导出（适合上万的数据导出）
     *
     * @param response  http响应
     * @param dataList  数据内容
     * @param fileName  文件名
     * @param sheetName sheet名
     * @param clazz     导出对象类
     * @throws Exception 可能抛出的异常
     */
    public static void export(HttpServletResponse response, List<? extends Object> dataList, String fileName, String sheetName, Class clazz) throws Exception {
        // 样式简单设置（取个样式模板）
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = getStyle();
        // 开始导出
        response.setHeader("Content-Type", "application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ExcelTypeEnum.XLSX.getValue());
        response.setCharacterEncoding("UTF-8");
        EasyExcel.write(response.getOutputStream(), clazz).excelType(ExcelTypeEnum.XLSX).sheet(sheetName).registerWriteHandler(horizontalCellStyleStrategy).doWrite(dataList);
        // easyExcel 导出完成自动关闭流，也是可以再手动关一次的（官网这么写的）
    }

    /**
     * easyExcel样式模板
     * 格表头 11号、加粗、宋体
     * 内容 10号
     *
     * @return 样式模板
     */
    private static HorizontalCellStyleStrategy getStyle() {
        // 表头样式居中对齐、字体宋体11号不加粗（默认加粗）、自动换行（已默认）
        WriteFont writeFont = new WriteFont();
        writeFont.setBold(false);
        writeFont.setFontName("宋体");
        writeFont.setFontHeightInPoints(FONT_SIZE_TEN);

        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        headWriteCellStyle.setWriteFont(writeFont);
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 内容样式垂直、水平居中、字体宋体10号不加粗（默认不加粗）、自动换行
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        contentWriteCellStyle.setWrapped(true);
        contentWriteCellStyle.setWriteFont(writeFont);
        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }
}

导出对象定义

@ExcelIgnoreUnannotated
@ContentRowHeight(25)
@HeadRowHeight(25)
public class ExcelDTO {

    /**
     * 序号
     */
    @ColumnWidth(20)
    @ExcelProperty(value = "序号")
    private Integer order;

    /**
     * 状态
     */
    @ColumnWidth(20)
    @ExcelProperty(value = "状态")
    private String state;
    
    /**
     * 日期
     */
    @ColumnWidth(20)
    @ExcelProperty(value = "日期", converter = LocalDateConverter.class)
    private LocalDate date;
    
    // 省略get set
}

特殊数据转换(这里以日期转换为例)

public class LocalDateConverter implements Converter<LocalDate> {

    @Override
    public Class<LocalDate> supportJavaTypeKey() {
        return LocalDate.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    @Override
    public LocalDate convertToJavaData(CellData cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        return LocalDate.parse(cellData.getStringValue(), DateTimeFormatter.ofPattern("yyyy-MM-dd"));
    }

    @Override
    public CellData convertToExcelData(LocalDate value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        String localDate = DateUtils.formatLocalDate(value, "yyyy-MM-dd");
        if (localDate == null) {
            return new CellData("");
        } else {
            return new CellData(localDate);
        }
    }
}

Controller调用

@PostMapping("/excel/list")
public void getExcelList(@RequestBody PageQueryDTO queryDTO, HttpServletResponse response) {
   
    List<ExcelDTO> dataList = this.excelService.queryExcelExportRecord(queryDTO);

    String fileName = "导出数据";
    String sheetName = "导出数据";
    try {
        EasyExcelExportUtil.export(response, dataList, fileName, sheetName, RemitPayBookExcelDTO.class);
    } catch (Exception e) {
        throw new BusinessException(CommonEnums.BUSINESS_ERROR, e);
    }
}