EasyExcel导出案例

1. 自定义转换器
2. 多次写减少内存缓存的数据
3. @ExcelProperty order：排序
4. @ColumnWidth 列宽

PrescriptionOrdersDto

/**
 * @author qb
 * @version 1.0
 * @since 2022/12/20 8:36
 */
@Data
public class PrescriptionOrdersDto {

    /**
     * 订单短id（京东物流取货使用）
     */
    @ExcelProperty(value = "物流取货码", order = 17)
    @ColumnWidth(13)
    private String shortOrderId;

    /**
     * 订单编号
     */
    @ExcelProperty(value = "处方编号", order = 0)
    @ColumnWidth(22)
    private String orderId;

    @ExcelProperty(value = "登记号", order = 0)
    @ColumnWidth(10)
    private String hisPatId;


    /**
     * 取药方式
     */
    @ExcelProperty(value = "取药方式", order = 11, converter = PickupTypeConverter.class)
    @ColumnWidth(11)
    private String pickupType;

    /**
     * 支付类型
     */
    @ExcelProperty(value = "支付方式", order = 10, converter = PaymentSettingConverter.class)
    @ColumnWidth(11)
    private String type;


    /**
     * 审核状态
     */
    @ExcelProperty(value = "处方状态", order = 8, converter = AuditStatusConverter.class)
    @ColumnWidth(11)
    private String auditStatus;


    /**
     * 订单状态
     */
    @ExcelProperty(value = "支付状态", order = 9, converter = OrderStatusConverter.class)
    @ColumnWidth(11)
    private String status;

    /**
     * 开方日期
     */
    @ExcelProperty(value = "开方时间", order = 4)
    @ColumnWidth(16)
    private LocalDateTime prescriptionTime;

    /**
     * 就诊人身份证号
     */
    @ExcelProperty(value = "患者身份证号", order = 6)
    @ColumnWidth(18)
    private String idCard;

    /**
     * 姓名
     */
    @ExcelProperty(value = "患者姓名", order = 5)
    @ColumnWidth(11)
    private String name;

    /**
     * 手机号
     */
    @ExcelProperty(value = "患者手机号", order = 7)
    @ColumnWidth(14)
    private String phone;


    /**
     * 科室名称
     */
    @ExcelProperty(value = "开方科室", order = 2)
    @ColumnWidth(20)
    private String deptName;

    /**
     * 医生姓名
     */
    @ExcelProperty(value = "开方医生", order = 3)
    @ColumnWidth(15)
    private String doctorName;


    /**
     * 收货姓名
     */
    @ExcelProperty(value = "收件人姓名", order = 12)
    @ColumnWidth(14)
    private String receiverName;

    /**
     * 收货手机
     */
    @ExcelProperty(value = "收件人手机号", order = 13)
    @ColumnWidth(17)
    private String receiverPhone;

    /**
     * 快递状态
     */
    @ExcelProperty(value = "物流状态", order = 15, converter = CourierStatusConverter.class)
    @ColumnWidth(12)
    private String courierStatus;

    /**
     * 快递单号
     */
    @ExcelProperty(value = "物流单号", order = 14)
    @ColumnWidth(15)
    private String courierId;



}

PickupTypeConverter

converter 转换器只举一个例子，其他的都差不多一样的写法

public class PickupTypeConverter implements Converter<String> {

    @Override
    public Class<?> supportJavaTypeKey() {
        //对象属性类型（java中数据类型）
        return String.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        //CellData属性类型（Excel中数据类型）
        return CellDataTypeEnum.STRING;
    }

    @Override
    public String convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty,
                                    GlobalConfiguration globalConfiguration) throws Exception {
        return Converter.super.convertToJavaData(cellData, contentProperty, globalConfiguration);
    }

    @Override
    public WriteCellData<?> convertToExcelData(String value, ExcelContentProperty contentProperty,
                                               GlobalConfiguration globalConfiguration) throws Exception {

        //return new WriteCellData<>(PickupType.getDescByCode(value));
        return new WriteCellData<>("根据业务最后转换成的值");
    }


}

FileUtils 工具类

public class FileUtils {

    public static void setAttachmentResponseHeader(HttpServletResponse response, String realFileName) throws UnsupportedEncodingException {
        String percentEncodedFileName = percentEncode(realFileName);

        StringBuilder contentDispositionValue = new StringBuilder();
        contentDispositionValue.append("attachment; filename=")
                .append(percentEncodedFileName)
                .append(";")
                .append("filename*=")
                .append("utf-8''")
                .append(percentEncodedFileName);

        response.addHeader("Access-Control-Expose-Headers", "Content-Disposition,download-filename");
        response.setHeader("Content-disposition", contentDispositionValue.toString());
        response.setHeader("download-filename", percentEncodedFileName);
    }

    public static String percentEncode(String s) throws UnsupportedEncodingException {
        String encode = URLEncoder.encode(s, StandardCharsets.UTF_8.toString());
        return encode.replaceAll("\\+", "%20");
    }

}

具体导出业务实现

	// ConsolePageOrderParam 查询参数替换成自己的查询参数
	@SneakyThrows
    public void export(ConsolePageOrderParam param,HttpServletResponse response) {
        @Cleanup
        ServletOutputStream os  = response.getOutputStream();
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8");
        response.setCharacterEncoding("utf-8");
        LocalDateTime now = LocalDateTime.now();
        String fileName = now.format(DateTimeFormatter.ofPattern("yyyyMMddHHmmss"))+ ".xlsx";
        FileUtils.setAttachmentResponseHeader(response,  URLEncoder.encode(fileName,"UTF-8"));
        ExcelWriter excelWriter = EasyExcel.write(os, PrescriptionOrdersDto.class).build();
        // 这里注意 如果同一个sheet只要创建一次
        WriteSheet writeSheet = EasyExcel.writerSheet("处方记录1").build();
        // 页码设置为1   每页大小默认每600条写一次
        AtomicInteger defaultPage = new AtomicInteger(1);
        while (true){
            param.setPageSize(600);
            param.setCurrent(defaultPage.get());
            // 分页查询---退换成自己的查询业务
            Page<PrescriptionOrdersDto> page = baseMapper.selectPageList(new Page<>(param.getCurrent(), param.getPageSize())
                    ,getWrapper(param));
            List<PrescriptionOrdersDto> data = page.getRecords();
            excelWriter.write(data, writeSheet);
            if((long) defaultPage.get() * param.getPageSize() >= page.getTotal()){
                page.getRecords().clear();
                data.clear();
                log.info("break 之前 清除list内容");
                break;
            }
            page.getRecords().clear();
            data.clear();
            log.info("清除list内容");
            defaultPage.incrementAndGet();
        }
        excelWriter.finish();
    }

效果图