easyExcel简单使用 包含列数据自定义转换

枚举类 

package cn.com.do1.ce.modules.enums;

import cn.com.do1.ce.common.base.exception.CeException;

import java.util.Arrays;
import java.util.Objects;

/**
 * @author daizhigang
 *   订单状态枚举
 */
public enum OrderStatusEnum {

	NO_PAY(1, "未支付"),


	CANCEL(2, "超时取消"),

	PAY(3, "已支付"),

	REFUNDED(4, "已退款"),

	REFUNDING(5, "退款中"),

	REFUND_ABNORMAL(6, "退款失败");


	private Integer val;
	private String text;

	OrderStatusEnum(Integer val, String text) {
		this.val = val;
		this.text = text;
	}

	public int getValue() {
		return val;
	}

	public String getText() {
		return text;
	}

	public static String getTextByVal(Integer val) {
		return Arrays.stream(OrderStatusEnum.values())
				.filter(orderStatusEnum -> Objects.equals(orderStatusEnum.getValue(), val))
				.findFirst()
				.orElseThrow(() -> new CeException("无法匹配"))
				.getText();
	}

}

自定义的转换器，将orderStatus的int类型转为自定义的string 

package cn.com.do1.ce.modules.tickets.excel;

import cn.com.do1.ce.modules.enums.OrderStatusEnum;
import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.property.ExcelContentProperty;

/**
 * Integer to string converter
 *
 * @author daizhichao
 */
public class OrderStatusConverter implements Converter<Integer> {
    @Override
    public Class supportJavaTypeKey() {
        return Integer.class;
    }

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

    /**
     * 这里读的时候会调用
     *
     * @param cellData
     *            NotNull
     * @param contentProperty
     *            Nullable
     * @param globalConfiguration
     *            NotNull
     * @return
     */
    @Override
    public Integer convertToJavaData(CellData cellData, ExcelContentProperty contentProperty,
        GlobalConfiguration globalConfiguration) {
        String value = cellData.getStringValue();
        return 1;
    }

    /**
     * 这里是写的时候会调用 不用管
     *
     * @param value
     *            NotNull
     * @param contentProperty
     *            Nullable
     * @param globalConfiguration
     *            NotNull
     * @return
     */
    @Override
    public CellData convertToExcelData(Integer value, ExcelContentProperty contentProperty,
        GlobalConfiguration globalConfiguration) {
        return new CellData(OrderStatusEnum.getTextByVal(value));
    }

}

需要输出的列表vo 

package cn.com.do1.ce.modules.tickets.vo;

import cn.com.do1.ce.modules.tickets.excel.OrderStatusConverter;
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.math.BigDecimal;
import java.util.Date;

/**
 * @author Akizora
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
public class MgrTicketListNotPageVO {
    /**
     * 订单号
     */
    @ExcelProperty(value = "订单号",index = 0)
    @ColumnWidth(value = 35)
    private String orderNum;

    /**
     * 门票名称
     */
    @ExcelProperty(value = "门票名称",index = 1)
    @ColumnWidth(value = 35)
    private String ticketName;

    /**
     * 票码
     */
    @ExcelProperty(value = "票码",index = 2)
    @ColumnWidth(value = 35)
    private String ticketCode;

    /**
     * 多语言
     */
    @ExcelIgnore
    private Integer languageType;

    /**
     * 订单状态
     */
    @ExcelProperty(value = "订单状态", converter = OrderStatusConverter.class, index = 3)
    @ColumnWidth(value = 15)
    private Integer orderStatus;

    /**
     * 角色类型
     * 参考枚举 RoleTypeEnum
     */
    @ExcelIgnore
    private Integer roleType;

    /**
     * 实付总额
     */
    @ExcelProperty(value = "实付总额",index = 4)
    @ColumnWidth(value = 15)
    private BigDecimal actualAmount;

    /**
     * 应付总额
     */
    @ExcelProperty(value = "应付总额",index = 5)
    @ColumnWidth(value = 15)
    private BigDecimal shouldAmount;

    /**
     * 下单时间
     */
    @ExcelProperty(value = "下单时间",index = 6)
    @ColumnWidth(value = 25)
    private Date createTime;

    /**
     * 购买人
     */
    @ExcelProperty(value = "购买人",index = 7)
    @ColumnWidth(value = 30)
    private String orderPeople;

    /**
     * 手机号
     */
    @ExcelProperty(value = "手机号",index = 8)
    @ColumnWidth(value = 20)
    private String mobile;

    /**
     * 单价
     */
    @ExcelProperty(value = "单价",index = 9)
    @ColumnWidth(value = 8)
    private BigDecimal ticketPrice;

    /**
     * 数量
     */
    @ExcelProperty(value = "数量",index = 10)
    @ColumnWidth(value = 8)
    private Integer quantity;

    /**
     * pc端还是mgr端 使用
     * 1-mgr
     * 2-pc
     */
    @ExcelIgnore
    private Integer useWhere;

    @ExcelIgnore
    private String userId;
}

从controller层到xml的代码 

@ApiOperation("导出门票订单列表")
    @PostMapping("/export")
    public void ticketExport(@RequestBody(required = false) JSONObject request, HttpServletResponse response) throws IOException {
        JSONArray idList = Optional.ofNullable(request).map(jsonObject -> jsonObject.getJSONArray("ids")).orElse(null);

        List<String> ids = idList.toJavaList(String.class);
        if(BaseUtil.isEmpty(ids)){
            ticketService.ticketExport(null, response);
            return;
        }
        ticketService.ticketExport(ids,response);
    }

接口定义 

void ticketExport(List<String> ids, HttpServletResponse response) throws IOException;

实现类

@Override
    public void ticketExport(List<String> ids, HttpServletResponse response) throws IOException {
        // 构建通过 订单号ids 返回的vo
        List<MgrTicketListNotPageVO> ticketList = baseMapper.getTicketExportList(ids);

        List<MgrTicketListNotPageVO> needToExportData = this.getNotPageTicketList(ticketList);
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
        String fileName = URLEncoder.encode("门票订单.xlsx", "UTF-8");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
        EasyExcel.write(response.getOutputStream(), MgrTicketListNotPageVO.class)
                .sheet("门票订单列表")
                .doWrite(needToExportData);
    }

mapper

List<MgrTicketListNotPageVO> getTicketExportList(@Param("ids") List<String> ids);

底层mp的xml sql

<select id="getTicketExportList" resultType="cn.com.do1.ce.modules.tickets.vo.MgrTicketListNotPageVO">
        SELECT
        tt.ticket_id,
        tt.ticket_name,
        o.order_num,
        o.should_amount,
        o.actual_amount,
        o.order_amount,
        o.order_status,
        o.create_time,
        o.mobile,
        o.order_people,
        o.quantity,
        tt.ticket_price
        FROM
        tb_ticket AS tt
        LEFT JOIN tb_order_item AS toi ON toi.product_id = tt.ticket_id
        LEFT JOIN tb_order AS o ON toi.order_num = o.order_num
        WHERE 1=1 AND o.order_num is not null
        <if test="ids !=null and ids != '' ">
            and o.order_num in
            <foreach collection="ids" item="item" separator="," open="(" close=")">
                #{item}
            </foreach>
        </if>
        Order By o.create_time DESC
    </select>

发送请求：

最终效果：