Without further ado, let’s get straight to the code.
Import dependencies
<dependencies>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel-core</artifactId>
<version>3.1.0</version>
<scope>compile</scope>
</dependency>
</dependencies>
Build EasyExcel, a tool for exporting large files
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;
/**
* Export tool class
* @author
*/
public class EasyExcelUtils {
/**
* Export public methods
*
* @param datas The data set to be exported
* @param clazz exported entity
* @param fileName export file name
* @throwsIOException
*/
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");
//Set file name
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 field entity class
Excel field entity class
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 = "Order Number")
@NumberFormat("0")
private Long tid;
@ExcelProperty(value = "Sub-order number")
@NumberFormat("0")
private Long oid;
@ExcelProperty(value = "Merchant")
private String shopName;
@ExcelProperty(value = "Company Name")
private String companyName;
@ExcelProperty(value = "Supermarket manager name")
private String accountName;
@ExcelProperty(value = "Supermarket manager's phone number")
private String accountPhone;
@ExcelIgnore
@ExcelProperty(value = "Store Type")
private String openType;
@ExcelIgnore
@ExcelProperty(value = "User Type")
private String userType;
@ExcelProperty(value = "Transaction Type")
private String tradeTypeTitle;
@ExcelProperty(value = "Shipping method", converter = ShippingTypeConverter.class)
private String shippingType;
@ExcelProperty(value = "item weight")
private BigDecimal totalWeight;
@ExcelProperty(value = "Payment Status", converter = PayStatusConverter.class)
private String payStatus;
@ExcelProperty(value = "Logistics delivery status", converter = ShippingStatusConverter.class)
private String shippingStatus;
@ExcelProperty(value = "After-sales status", converter = AfterSalesStatusConverter.class)
private String afterSalesStatus;
@ExcelProperty(value = "Whether the buyer has rated it", converter = BuyerRateConverter.class)
private String buyerRate;
@ExcelProperty(value = "Order Status", converter = StatusConverter.class)
private String status;
@ExcelProperty(value = "Cancel order status", converter = CancelStatusConverter.class)
private String cancelStatus;
@ExcelProperty(value = "Reason for order cancellation")
private String cancelReason;
@ExcelProperty(value = "Order Complaint Status", converter = ComplaintsStatusConverter.class)
private String complaintsStatus;
@ExcelProperty(value = "Actual amount payable")
private BigDecimal payment;
@ExcelProperty(value = "Actual amount paid")
private BigDecimal payedFee;
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
@ExcelProperty(value = "Creation time")
private Date createdTime;
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
@ExcelProperty(value = "Seller confirmation time")
private Date confirmTime;
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
@ExcelProperty(value = "Payment Time")
private Date payTime;
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
@ExcelProperty(value = "shipping time")
private Date consignTime;
}
Export field type converter
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<>("Refund successful");
} else if ("CLOSED".equals(value)) {
return new WriteCellData<>("Refund Closed");
} else if ("REFUNDING".equals(value)) {
return new WriteCellData<>("Refund in progress");
} else if ("SELLER_REFUSE_BUYER".equals(value)) {
return new WriteCellData<>("Seller refuses refund");
} else if ("SELLER_SEND_GOODS".equals(value)) {
return new WriteCellData<>("Seller has shipped");
} else if ("WAIT_SELLER_AGREE".equals(value)) {
return new WriteCellData<>("The buyer has applied for a refund and is waiting for the seller's approval");
} else if ("WAIT_BUYER_RETURN_GOODS".equals(value)) {
return new WriteCellData<>("The seller has agreed to refund and is waiting for the buyer to return the goods");
} else if ("WAIT_SELLER_CONFIRM_GOODS".equals(value)) {
return new WriteCellData<>("The buyer has returned the goods and is waiting for the seller to confirm receipt");
} else {
return new WriteCellData<>("");
}
}
}
After-sales status converter, other field type conversion and so on, add the converter class after the field in the entity class
converter = AfterSalesStatusConverter.class
Mapping layer query
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 layer
@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("Sales List" + cn.hutool.core.date.DateUtil.format(new Date(), "yyyyMMddHHmmss"));
EasyExcelUtils.export(list, TradeExcel.class, fileName, "Order Details");
} catch (Exception e) {
throw new RRException(e.getMessage());
}
}
Control layer
@GetMapping("export")
@ApiOperation(value = "export")
@ApiImplicitParams({
@ApiImplicitParam(name = "tid", value = "Order Number", paramType = "query", dataType = "String"),
@ApiImplicitParam(name = "payment", value = "actual payment amount", paramType = "query", dataType = "String"),
@ApiImplicitParam(name = "startDate", value = "Query start time", paramType = "query", dataType = "String"),
@ApiImplicitParam(name = "endDate", value = "Query end time", paramType = "query", dataType = "String"),
@ApiImplicitParam(name = "comStatus", value = "Order Status", paramType = "query", dataType = "int", required = true),
@ApiImplicitParam(name = "receiverName", value = "recipient name", paramType = "query", dataType = "string"),
@ApiImplicitParam(name = "catId", value = "Third-level category search (for multiple categories, distinguish)", paramType = "query", dataType = "string"),
@ApiImplicitParam(name = "payStatus", value = "Payment Status", paramType = "query", dataType = "string"),
@ApiImplicitParam(name = "order", value = "Sort", paramType = "query", dataType = "string"),
@ApiImplicitParam(name = "orderField", value = "OrderField", paramType = "query", dataType = "string"),
})
public void exportAll(HttpServletResponse response, @ApiIgnore @RequestParam HashMap<String, Object> params){
tradeService.exportAll(response,params);
}
Filter query optimization
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());
}
}
}
}
Summary:
This article mainly lists the export lists commonly used in the development process, as well as methods of operating databases and related configurations. It should be able to meet more than 80% of the needs. When I have time, I will add more, such as custom plug-ins, how to write large batches of data, etc.
Finally
If there are any errors or omissions in the article, please point them out so we can make progress together!