废话不多说,直接上代码
导入依赖
<dependencies>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel-core</artifactId>
<version>3.1.0</version>
<scope>compile</scope>
</dependency>
</dependencies>
构建导出大文件工具EasyExcel
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import org.springframework.beans.BeanUtils;
import org.springframework.lang.Nullable;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;
import javax.servlet.http.HttpServletResponse;
import javax.validation.constraints.NotNull;
import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
/**
* 导出工具类
* @author
*/
public class EasyExcelUtils {
/**
* 导出公共方法
*
* @param datas 要导出的数据集
* @param clazz 导出的实体
* @param fileName 导出文件名称
* @throws IOException
*/
public static void export(List datas, @Nullable Class<?> clazz, String fileName, String sheetName) throws Exception {
ArrayList arrayList = getArrayList(datas, clazz);
ServletRequestAttributes sra = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
HttpServletResponse response = sra.getResponse();
response.setContentType("application/vnd.ms-excel;charset=utf-8");
// 设置文件名
String exportFileName = URLEncoder.encode(fileName, "UTF-8");
//String exportSheetName = URLEncoder.encode(sheetName, "UTF-8");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + exportFileName + ExcelTypeEnum.XLSX.getValue());
//
EasyExcel.write(response.getOutputStream(), clazz)
.excelType(ExcelTypeEnum.XLSX)
.autoCloseStream(true)
.sheet(sheetName)
.doWrite(arrayList);
}
@NotNull
private static ArrayList getArrayList(List datas, Class<?> clazz) throws InstantiationException, IllegalAccessException, NoSuchMethodException, InvocationTargetException {
ArrayList arrayList = new ArrayList<>(datas.size());
for (Object source : datas) {
Object target = clazz.getDeclaredConstructor().newInstance();
BeanUtils.copyProperties(source, target);
arrayList.add(target);
}
return arrayList;
}
}
Excel字段实体类
Excel字段实体类
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.annotation.format.NumberFormat;
import com.alibaba.excel.annotation.ExcelIgnore;
import java.math.BigDecimal;
import java.util.Date;
@Data
public class TradeExcel {
@ExcelProperty(value = "订单编号")
@NumberFormat("0")
private Long tid;
@ExcelProperty(value = "子订单编号")
@NumberFormat("0")
private Long oid;
@ExcelProperty(value = "所属商家")
private String shopName;
@ExcelProperty(value = "公司名称")
private String companyName;
@ExcelProperty(value = "超市经理姓名")
private String accountName;
@ExcelProperty(value = "超市经理电话")
private String accountPhone;
@ExcelIgnore
@ExcelProperty(value = "店铺类型")
private String openType;
@ExcelIgnore
@ExcelProperty(value = "用户类型")
private String userType;
@ExcelProperty(value = "交易类型")
private String tradeTypeTitle;
@ExcelProperty(value = "运送方式", converter = ShippingTypeConverter.class)
private String shippingType;
@ExcelProperty(value = "商品重量")
private BigDecimal totalWeight;
@ExcelProperty(value = "支付状态", converter = PayStatusConverter.class)
private String payStatus;
@ExcelProperty(value = "物流发货状态", converter = ShippingStatusConverter.class)
private String shippingStatus;
@ExcelProperty(value = "售后状态", converter = AfterSalesStatusConverter.class)
private String afterSalesStatus;
@ExcelProperty(value = "买家是否已评价", converter = BuyerRateConverter.class)
private String buyerRate;
@ExcelProperty(value = "订单状态", converter = StatusConverter.class)
private String status;
@ExcelProperty(value = "取消订单状态", converter = CancelStatusConverter.class)
private String cancelStatus;
@ExcelProperty(value = "取消订单原因")
private String cancelReason;
@ExcelProperty(value = "订单投诉状态", converter = ComplaintsStatusConverter.class)
private String complaintsStatus;
@ExcelProperty(value = "实际应付金额")
private BigDecimal payment;
@ExcelProperty(value = "实际已支付金额")
private BigDecimal payedFee;
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
@ExcelProperty(value = "创建时间")
private Date createdTime;
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
@ExcelProperty(value = "卖家确认时间")
private Date confirmTime;
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
@ExcelProperty(value = "付款时间")
private Date payTime;
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
@ExcelProperty(value = "发货时间")
private Date consignTime;
}
导出字段类型转换器
import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.data.CellData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
public class AfterSalesStatusConverter implements Converter<String> {
@Override
public Class supportJavaTypeKey() {
return String.class;
}
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return CellDataTypeEnum.STRING;
}
@Override
public WriteCellData<String> convertToExcelData(String value, ExcelContentProperty contentProperty,
GlobalConfiguration globalConfiguration) throws Exception {
WriteCellData<String> cellData = new WriteCellData();
if ("SUCCESS".equals(value)) {
return new WriteCellData<>("退款成功");
} else if ("CLOSED".equals(value)) {
return new WriteCellData<>("退款关闭");
} else if ("REFUNDING".equals(value)) {
return new WriteCellData<>("退款中");
} else if ("SELLER_REFUSE_BUYER".equals(value)) {
return new WriteCellData<>("卖家拒绝退款");
} else if ("SELLER_SEND_GOODS".equals(value)) {
return new WriteCellData<>("卖家已发货");
} else if ("WAIT_SELLER_AGREE".equals(value)) {
return new WriteCellData<>("买家已经申请退款,等待卖家同意");
} else if ("WAIT_BUYER_RETURN_GOODS".equals(value)) {
return new WriteCellData<>("卖家已经同意退款,等待买家退货");
} else if ("WAIT_SELLER_CONFIRM_GOODS".equals(value)) {
return new WriteCellData<>("买家已经退货,等待卖家确认收货");
} else {
return new WriteCellData<>("");
}
}
}
售后状态的转换器,其它字段类型转换以此类推,在实体类中字段后加上转换器的类
converter = AfterSalesStatusConverter.class
映射层查询
List<TradeExcel> exportOrderAllList(@Param("filter") Map filter);
<select id="exportOrderAllList" resultType="com.zhdj.mall.common.pojo.dto.systrade.TradeExcel">
SELECT
o.oid,
o.tid,
o.shipping_status as shippingStatus,
o.aftersales_status as afterSalesStatus,
FROM_UNIXTIME(o.pay_time) as payTime,
FROM_UNIXTIME(o.modified_time) as modifiedTime,
o.complaints_status as complaintsStatus,
o.payment,
o.trade_goods_memo as tradeGoodsMemo,
t.shipping_type as shippingType,
t.need_invoice as needInvoice,
t.is_invoice_done as isInvoiceDone,
t.invoice_name as invoiceName,
t.receiver_address as receiverAddress,
FROM_UNIXTIME(t.created_time) as createdTime,
FROM_UNIXTIME(t.confirm_time) as confirmTime,
if (t.last_urge_delivery > 0,FROM_UNIXTIME( t.last_urge_delivery ),null) AS lastUrgeDelivery,
si.company_name as companyName,
s.open_type as openType,
c.name as marketName,
a.user_type as userType
FROM
yyds AS o
LEFT JOIN
LEFT JOIN
LEFT JOIN
left join
left join
where 1
<if test="filter.disabled != null">
and t.disabled = #{filter.disabled}
</if>
<if test="filter.payment != null">
and t.payment = #{filter.payment}
</if>
<if test="filter.tid != null">
and t.tid = #{filter.tid}
</if>
<if test="filter.customerId != null">
and t.customer_id in
<foreach collection="filter.customerId" item="cId" open="(" separator="," close=")">
#{cId}
</foreach>
</if>
<if test="filter.needInvoice != null">
and t.need_invoice = #{filter.needInvoice}
</if>
<if test="filter.isInvoiceDone != null">
and t.is_invoice_done = #{filter.isInvoiceDone}
</if>
<if test="filter.catIdList != null">
and o.cat_id in
<foreach collection="filter.catIdList" item="catId" open="(" separator="," close=")">
#{catId}
</foreach>
</if>
<if test="filter.shopIdList != null">
and t.shop_id in
<foreach collection="filter.shopIdList" item="shopId" open="(" separator="," close=")">
#{shopId}
</if>
<if test="filter.status != null">
and t.status in
<foreach collection="filter.status" item="status" open="(" separator="," close=")">
#{status}
</foreach>
</if>
<if test="filter.payStatus != null">
and t.pay_status in
<foreach collection="filter.payStatus" item="payStatus" open="(" separator="," close=")">
#{payStatus}
</foreach>
</if>
<if test="filter.shippingStatus != null">
and t.shipping_status in
<foreach collection="filter.shippingStatus" item="shippingStatus" open="(" separator="," close=")">
#{shippingStatus}
</foreach>
</if>
<if test="filter.cancelStatus != null">
and t.cancel_status in
<foreach collection="filter.cancelStatus" item="cancelStatus" open="(" separator="," close=")">
#{cancelStatus}
</foreach>
</if>
<if test="filter.createdTimeStart != null and filter.createdTimeEnd != null">
and t.created_time <![CDATA[>=]]> #{filter.createdTimeStart} and t.created_time <![CDATA[<=]]> #{filter.createdTimeEnd}
</if>
<if test="filter.tradeType != null">
and t.trade_type = #{filter.tradeType}
</if>
<if test="filter.openType != null">
and s.open_type = #{filter.openType}
</if>
<if test="filter.userType != null">
and a.user_type = #{filter.userType}
</if>
<if test="filter.orderField != null">
order by ${filter.orderField} ${filter.order}
</if>
</select>
SERVICE层
@Override
public void exportAll(HttpServletResponse response, HashMap<String, Object> params) {
try {
Map<String, Object> searchCondition = getSearchConditionMap(params);
List<TradeExcel> list = systradeOrderMapper.exportOrderAllList(searchCondition);
list.forEach(this::getTradeFormat);
String fileName = new String("销售列表" + cn.hutool.core.date.DateUtil.format(new Date(), "yyyyMMddHHmmss"));
EasyExcelUtils.export(list, TradeExcel.class, fileName, "订单明细");
} catch (Exception e) {
throw new RRException(e.getMessage());
}
}
控制层
@GetMapping("export")
@ApiOperation(value = "导出")
@ApiImplicitParams({
@ApiImplicitParam(name = "tid", value = "订单编号", paramType = "query", dataType = "String"),
@ApiImplicitParam(name = "payment", value = "实付金额", paramType = "query", dataType = "String"),
@ApiImplicitParam(name = "startDate", value = "查询开始时间", paramType = "query", dataType = "String"),
@ApiImplicitParam(name = "endDate", value = "查询结束时间", paramType = "query", dataType = "String"),
@ApiImplicitParam(name = "comStatus", value = "订单状态", paramType = "query", dataType = "int", required = true),
@ApiImplicitParam(name = "receiverName", value = "收货人姓名", paramType = "query", dataType = "string"),
@ApiImplicitParam(name = "catId", value = "三级类目查找(多个类目用,区分)", paramType = "query", dataType = "string"),
@ApiImplicitParam(name = "payStatus", value = "支付状态", paramType = "query", dataType = "string"),
@ApiImplicitParam(name = "order", value = "排序方式", paramType = "query", dataType = "string"),
@ApiImplicitParam(name = "orderField", value = "排序字段", paramType = "query", dataType = "string"),
})
public void exportAll(HttpServletResponse response, @ApiIgnore @RequestParam HashMap<String, Object> params){
tradeService.exportAll(response ,params);
}
筛选查询优化
private void setCatName(TradeExcel tradeExcel, List<CatEntity> categoryList) {
var ThreeLevelCatInfo = categoryList.stream().filter(c -> c.getCatId().equals(tradeExcel.getCatId())).findAny().orElse(null);
if (ThreeLevelCatInfo != null) {
tradeExcel.setThreeLevelCatName(ThreeLevelCatInfo.getCatName());
var TwoLevelCatInfo = categoryList.stream().filter(c -> c.getCatId().equals(ThreeLevelCatInfo.getParentId())).findAny().orElse(null);
if (TwoLevelCatInfo != null) {
tradeExcel.setTwoLevelCatName(TwoLevelCatInfo.getCatName());
var OneLevelCatInfo = categoryList.stream().filter(c -> c.getCatId().equals(TwoLevelCatInfo.getParentId())).findAny().orElse(null);
if (OneLevelCatInfo != null) {
tradeExcel.setOneLevelCatName(OneLevelCatInfo.getCatName());
}
}
}
}
总结:
本文主要列举了开发过程中常用的导出列表以及操作数据库的方法及相关配置。应该可以应付百分之八十以上的需求了吧。之后有时间,会进行补充的,比如自定义插件、大批量数据的写法等。
最后
若文中有错误或者遗漏之处,还望指出,共同进步!