枚举类
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>
发送请求:
最终效果: