<!-- easypoi 核心依赖包 -->
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>4.0.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>4.0.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>4.0.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
package org.linlinjava.litemall.admin.util;
import java.lang.annotation.Documented;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
import java.lang.reflect.Field;
import java.lang.reflect.ParameterizedType;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Comparator;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Set;
import java.util.stream.Collectors;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
public class ExcelUtil<T> {
private static final Logger log = LoggerFactory.getLogger(ExcelUtil.class);
private Class<T> tClass;
public int pageSize = 10000;
private int total;
public CellStyle bodyStyle;
public CellStyle headStyle;
public CellStyle SpecialStyle;
public ExcelUtil() {
}
public Workbook getWork(List<T> list) throws NoSuchFieldException {
Workbook wb = new HSSFWorkbook();
this.fillDefaultStyle(wb);
this.tClass = (Class)((ParameterizedType)this.getClass().getGenericSuperclass()).getActualTypeArguments()[0];
ExcelUtil.Describe classDescribe = (ExcelUtil.Describe)this.tClass.getAnnotation(ExcelUtil.Describe.class);
String tableName;
if (classDescribe != null) {
tableName = classDescribe.value();
} else {
tableName = this.tClass.getName();
}
List<ExcelUtil<T>.sortDescribe> sortDescribes = this.getHeadData();
this.total = (list == null ? 0 : list.size() + this.pageSize - 1) / this.pageSize;
if (this.total > 0) {
for(int i = 0; i < this.total; ++i) {
List<T> pageList = (List)list.stream().skip((long)(this.pageSize * i)).limit((long)this.pageSize).collect(Collectors.toList());
Sheet sheet = wb.createSheet(tableName + i);
this.setHead(sheet, sortDescribes);
int bodyIndex = 1;
for(Iterator var11 = pageList.iterator(); var11.hasNext(); ++bodyIndex) {
T item = (T) var11.next();
Row bodyRow = sheet.createRow(bodyIndex);
int bodyCellIndex = 0;
Iterator var15 = sortDescribes.iterator();
while(var15.hasNext()) {
ExcelUtil<T>.sortDescribe entity = (ExcelUtil.sortDescribe)var15.next();
Field field = this.tClass.getDeclaredField(entity.getName());
Cell cell = bodyRow.createCell(bodyCellIndex);
cell.setCellStyle(this.bodyStyle);
String getMethodName = "get" + this.toFirstLetterUpperCase(field.getName());
ExcelUtil.Describe describe = entity.getDescribe();
try {
Object obj = this.tClass.getMethod(getMethodName).invoke(item);
if (obj != null) {
if (describe.isAmount()) {
Long amount = Long.parseLong(obj.toString());
cell.setCellValue(String.format(describe.amountFormat(), amount.doubleValue() / 100.0D));
} else if (describe.isDate()) {
SimpleDateFormat sdf = new SimpleDateFormat(describe.dateFormat());
cell.setCellValue(sdf.format((Date)obj));
} else {
cell.setCellValue(obj.toString().trim());
}
if (describe.isConcat()) {
cell.setCellValue(cell.getStringCellValue().concat(describe.concatString()));
}
} else {
cell.setCellValue(describe.isNullValue());
}
} catch (Exception var26) {
log.error("ExcelUtil->getWork", var26);
} finally {
++bodyCellIndex;
}
}
}
}
} else {
Sheet sheet = wb.createSheet(tableName);
this.setHead(sheet, sortDescribes);
}
return wb;
}
private void setHead(Sheet sheet, List<ExcelUtil<T>.sortDescribe> sortDescribes) {
Row headRow = sheet.createRow(0);
if (sortDescribes != null && sortDescribes.size() > 0) {
int headIndex = 0;
for(Iterator var5 = sortDescribes.iterator(); var5.hasNext(); ++headIndex) {
ExcelUtil<T>.sortDescribe entity = (ExcelUtil.sortDescribe)var5.next();
sheet.setColumnWidth(headIndex, 252 * entity.getDescribe().width() + 323);
sheet.autoSizeColumn((short)headIndex);
Cell cell = headRow.createCell(headIndex);
cell.setCellValue(entity.getDescribe().value());
cell.setCellStyle(this.headStyle);
}
}
}
private void fillDefaultStyle(Workbook workbook) {
Font font;
if (this.bodyStyle == null) {
this.bodyStyle = workbook.createCellStyle();
font = workbook.createFont();
font.setFontHeightInPoints((short)12);
font.setFontName("新宋体");
this.bodyStyle.setFont(font);
this.bodyStyle.setAlignment(HorizontalAlignment.CENTER);
}
if (this.headStyle == null) {
this.headStyle = workbook.createCellStyle();
font = workbook.createFont();
font.setFontHeightInPoints((short)12);
font.setFontName("新宋体");
font.setBold(true);
this.headStyle.setFont(font);
this.headStyle.setAlignment(HorizontalAlignment.CENTER);
}
if (this.SpecialStyle == null) {
this.SpecialStyle = workbook.createCellStyle();
this.SpecialStyle.setAlignment(HorizontalAlignment.CENTER);
font = workbook.createFont();
font.setFontName("黑体");
font.setFontName("仿宋_GB2312");
font.setBold(true);
font.setFontHeightInPoints((short)12);
this.SpecialStyle.setFont(font);
}
}
public Workbook getWork(List<T> list, HashMap<Integer, Object> info) throws NoSuchFieldException {
Workbook workbook = this.getWork(list);
return this.fileSpecialInfo(workbook, info);
}
private String toFirstLetterUpperCase(String str) {
if (str != null && str.length() >= 2) {
String firstLetter = str.substring(0, 1).toUpperCase();
return firstLetter + str.substring(1);
} else {
return str;
}
}
private List<ExcelUtil<T>.sortDescribe> getHeadData() {
List<ExcelUtil<T>.sortDescribe> result = new ArrayList();
Field[] fields = this.tClass.getDeclaredFields();
if (fields != null && fields.length > 0) {
for(int i = 0; i < fields.length; ++i) {
ExcelUtil.Describe itemDescribe = (ExcelUtil.Describe)fields[i].getAnnotation(ExcelUtil.Describe.class);
if (itemDescribe != null) {
ExcelUtil<T>.sortDescribe describe = new ExcelUtil.sortDescribe();
describe.setIndex(itemDescribe.index());
describe.setDescribe(itemDescribe);
describe.setName(fields[i].getName());
result.add(describe);
}
}
}
List<sortDescribe> asd = result.stream().sorted(Comparator.comparing(sortDescribe::getIndex).reversed()).collect(Collectors.toList());
return asd;
}
private Workbook fileSpecialInfo(Workbook workbook, HashMap<Integer, Object> info) {
if (info != null && info.size() > 0) {
Set set = info.keySet();
Object[] arr = set.toArray();
Arrays.sort(arr);
for(int i = 0; i < workbook.getNumberOfSheets(); ++i) {
Sheet itemSheet = workbook.getSheetAt(i);
Row bodyRow = itemSheet.createRow(itemSheet.getLastRowNum() + 1);
for(int k = 0; k < arr.length; ++k) {
Cell cell;
int leftSum;
if (k == 0) {
cell = bodyRow.createCell(0);
cell.setCellValue(info.get(arr[k]).toString());
leftSum = Integer.parseInt(arr[k].toString()) - 0;
} else if (k + 1 == arr.length) {
cell = bodyRow.createCell(Integer.parseInt(arr[k].toString()));
cell.setCellValue(info.get(arr[k]).toString());
} else {
cell = bodyRow.createCell(Integer.parseInt(arr[k].toString()));
cell.setCellValue(info.get(arr[k]).toString());
leftSum = Integer.parseInt(arr[k + 1].toString()) - Integer.parseInt(arr[k].toString()) - 1;
if (leftSum > 0) {
}
}
cell.setCellStyle(this.SpecialStyle);
}
}
}
return workbook;
}
@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.TYPE, ElementType.FIELD})
public @interface Describe {
String value() default "";
String dateFormat() default "yyyy-MM-dd HH:mm";
boolean isDate() default false;
boolean isAmount() default false;
String amountFormat() default "%.2f";
int index() default 0;
int width() default 35;
boolean isConcat() default false;
String concatString() default "";
String isNullValue() default "";
}
public class sortDescribe {
private ExcelUtil.Describe describe;
private String name;
private Integer index;
public sortDescribe() {
}
public ExcelUtil.Describe getDescribe() {
return this.describe;
}
public String getName() {
return this.name;
}
public Integer getIndex() {
return this.index;
}
public void setDescribe(ExcelUtil.Describe describe) {
this.describe = describe;
}
public void setName(String name) {
this.name = name;
}
public void setIndex(Integer index) {
this.index = index;
}
public boolean equals(Object o) {
if (o == this) {
return true;
} else if (!(o instanceof ExcelUtil.sortDescribe)) {
return false;
} else {
ExcelUtil<?>.sortDescribe other = (ExcelUtil.sortDescribe)o;
if (!other.canEqual(this)) {
return false;
} else {
label47: {
Object this$describe = this.getDescribe();
Object other$describe = other.getDescribe();
if (this$describe == null) {
if (other$describe == null) {
break label47;
}
} else if (this$describe.equals(other$describe)) {
break label47;
}
return false;
}
Object this$name = this.getName();
Object other$name = other.getName();
if (this$name == null) {
if (other$name != null) {
return false;
}
} else if (!this$name.equals(other$name)) {
return false;
}
Object this$index = this.getIndex();
Object other$index = other.getIndex();
if (this$index == null) {
if (other$index != null) {
return false;
}
} else if (!this$index.equals(other$index)) {
return false;
}
return true;
}
}
}
protected boolean canEqual(Object other) {
return other instanceof ExcelUtil.sortDescribe;
}
public int hashCode() {
// int PRIME = true;
int resultx = 1;
Object $describe = this.getDescribe();
int result = resultx * 59 + ($describe == null ? 43 : $describe.hashCode());
Object $name = this.getName();
result = result * 59 + ($name == null ? 43 : $name.hashCode());
Object $index = this.getIndex();
result = result * 59 + ($index == null ? 43 : $index.hashCode());
return result;
}
public String toString() {
return "ExcelUtil.sortDescribe(describe=" + this.getDescribe() + ", name=" + this.getName() + ", index=" + this.getIndex() + ")";
}
}
}
package org.linlinjava.litemall.admin.service.export;
import org.linlinjava.litemall.admin.service.AdminOrderService;
import org.linlinjava.litemall.admin.vo.OrderListResp;
import org.linlinjava.litemall.db.domain.LitemallOrder;
import org.linlinjava.litemall.db.service.LitemallOrderService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
/**
* @ClassName OrderExport
* @Description
* @Auther Gavin
* @Date 2020/12/23 10:07
* @Version 1.0
**/
@Component
public class OrderExport {
@Autowired
private LitemallOrderService orderService;
public List<OrderListResp> getExportData(){
List<OrderListResp> listResps=new ArrayList<>();
for(int i=0;i<100;i++){
OrderListResp orderListResp=new OrderListResp();
orderListResp.setType(1);
orderListResp.setOrderName("name"+i);
orderListResp.setOrderCode("codei"+i);
orderListResp.setInstallDate(new Date());
listResps.add(orderListResp);
}
return listResps;
}
public List<LitemallOrder> exportOrder(Integer userId, String orderSn, LocalDateTime start, LocalDateTime end, List<Short> orderStatusArray, Integer page, Integer limit, String sort, String order) {
List<LitemallOrder> orderList = orderService.querySelective(userId, orderSn, start, end, orderStatusArray, page, limit,
sort, order);
return orderList;
}
}
package org.linlinjava.litemall.admin.vo;
import org.linlinjava.litemall.admin.util.ExcelUtil;
import java.io.Serializable;
import java.util.Date;
//这里加的注解，会默认为工作表的表名
@ExcelUtil.Describe("订单列表")
public class OrderListResp implements Serializable {
private static final long serialVersionUID = 8789848844535006453L;
/**
* 订单类型 0：线上，1：线下
*/
private Integer type;
/**
* 订单类型名称
*/
@ExcelUtil.Describe(value = "订单类型", index = 31)
private String orderName;
/**
* 订单号
*/
@ExcelUtil.Describe(value = "订单号", index = 30)
private String orderCode;
/**
* 安装日期
*/
@ExcelUtil.Describe(value = "安装日期", index = 29, isDate = true, dateFormat = "yyyy-MM-dd")
private Date installDate;
public Integer getType() {
return type;
}
public void setType(Integer type) {
this.type = type;
}
public String getOrderName() {
return orderName;
}
public void setOrderName(String orderName) {
this.orderName = orderName;
}
public String getOrderCode() {
return orderCode;
}
public void setOrderCode(String orderCode) {
this.orderCode = orderCode;
}
public Date getInstallDate() {
return installDate;
}
public void setInstallDate(Date installDate) {
this.installDate = installDate;
}
}
package org.linlinjava.litemall.admin.vo;
import org.linlinjava.litemall.admin.util.ExcelUtil;
import java.io.Serializable;
import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.Date;
/**
* @ClassName ExportOrder
* @Description
* @Auther Gavin
* @Date 2020/12/24 14:30
* @Version 1.0
**/
public class ExportOrder implements Serializable{
private static final long serialVersionUID = 8789848845535006453L;
@ExcelUtil.Describe(value = "用户id")
private Integer userId;
@ExcelUtil.Describe(value = "订单编号")
private String orderSn;
@ExcelUtil.Describe(value = "下单时间",isDate = true, dateFormat = "yyyy-MM-dd HH:mm:ss")
private Date addTime;
@ExcelUtil.Describe(value = "订单状态")
private String orderStatus;
@ExcelUtil.Describe(value = "收货人姓名")
private String consignee;
@ExcelUtil.Describe(value = "收货人手机号")
private String mobile;
@ExcelUtil.Describe(value = "收货具体地址")
private String address;
@ExcelUtil.Describe(value = "商品总费用")
private BigDecimal goodsPrice;
@ExcelUtil.Describe(value = "配送费用")
private BigDecimal freightPrice;
@ExcelUtil.Describe(value = "实付费用")
private BigDecimal actualPrice;
@ExcelUtil.Describe(value = "微信付款编号")
private String payId;
@ExcelUtil.Describe(value = "微信付款时间",isDate = true, dateFormat = "yyyy-MM-dd HH:mm:ss")
private Date payTime;
@ExcelUtil.Describe(value = "发货编号")
private String shipSn;
@ExcelUtil.Describe(value = "发货快递公司")
private String shipChannel;
@ExcelUtil.Describe(value = "用户确认收货时间",isDate = true, dateFormat = "yyyy-MM-dd HH:mm:ss")
private Date confirmTime;
public Integer getUserId() {
return userId;
}
public void setUserId(Integer userId) {
this.userId = userId;
}
public String getOrderSn() {
return orderSn;
}
public void setOrderSn(String orderSn) {
this.orderSn = orderSn;
}
public Date getAddTime() {
return addTime;
}
public void setAddTime(Date addTime) {
this.addTime = addTime;
}
public String getOrderStatus() {
return orderStatus;
}
public void setOrderStatus(String orderStatus) {
this.orderStatus = orderStatus;
}
public String getConsignee() {
return consignee;
}
public void setConsignee(String consignee) {
this.consignee = consignee;
}
public String getMobile() {
return mobile;
}
public void setMobile(String mobile) {
this.mobile = mobile;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public BigDecimal getGoodsPrice() {
return goodsPrice;
}
public void setGoodsPrice(BigDecimal goodsPrice) {
this.goodsPrice = goodsPrice;
}
public BigDecimal getFreightPrice() {
return freightPrice;
}
public void setFreightPrice(BigDecimal freightPrice) {
this.freightPrice = freightPrice;
}
public BigDecimal getActualPrice() {
return actualPrice;
}
public void setActualPrice(BigDecimal actualPrice) {
this.actualPrice = actualPrice;
}
public String getPayId() {
return payId;
}
public void setPayId(String payId) {
this.payId = payId;
}
public Date getPayTime() {
return payTime;
}
public void setPayTime(Date payTime) {
this.payTime = payTime;
}
public String getShipSn() {
return shipSn;
}
public void setShipSn(String shipSn) {
this.shipSn = shipSn;
}
public String getShipChannel() {
return shipChannel;
}
public void setShipChannel(String shipChannel) {
this.shipChannel = shipChannel;
}
public Date getConfirmTime() {
return confirmTime;
}
public void setConfirmTime(Date confirmTime) {
this.confirmTime = confirmTime;
}
}
package org.linlinjava.litemall.admin.web.user;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.apache.poi.ss.usermodel.Workbook;
import org.linlinjava.litemall.admin.service.export.OrderExport;
import org.linlinjava.litemall.admin.util.DateUtils;
import org.linlinjava.litemall.admin.util.ExcelUtil;
import org.linlinjava.litemall.admin.vo.ExportOrder;
import org.linlinjava.litemall.admin.vo.OrderListResp;
import org.linlinjava.litemall.core.util.BeanUtil;
import org.linlinjava.litemall.core.validator.Order;
import org.linlinjava.litemall.core.validator.Sort;
import org.linlinjava.litemall.db.domain.LitemallOrder;
import org.linlinjava.litemall.db.util.OrderUtil;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.format.annotation.DateTimeFormat;
import org.springframework.validation.annotation.Validated;
import org.springframework.web.bind.annotation.*;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;
/**
* @ClassName ExportApi
* @Description
* @Auther Gavin
* @Date 2020/12/22 18:23
* @Version 1.0
**/
@RestController
@RequestMapping("/export")
@Api("导出excel")
public class ExportApi {
@Autowired
private OrderExport orderExport;
/**
*
* @param
* @return
*/
@GetMapping("/exportOrderTest")
public void exportList(HttpServletResponse response) throws IOException, NoSuchFieldException {
//这里调用自己的service方法，得到结果list
// List<OrderListResp> listInfo = reconciliationService.getOrderList(req);
List<OrderListResp> listInfo = orderExport.getExportData();
//调用ExcelUtil.getWork方法
Workbook workbook = (new ExcelUtil<OrderListResp>(){}).getWork(listInfo);
OutputStream output = response.getOutputStream();
response.reset();
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddhhmm");
String filename = "导出列表".concat(sdf.format(new Date(System.currentTimeMillis())));
response.setHeader("Content-disposition", "attachment; filename="+filename+".xlsx");
// 跨域
response.setHeader("Access-Control-Allow-Origin", "*");
response.setContentType("application/msexcel");
workbook.write(output);
output.close();
}
@ApiOperation("订单导出")
@GetMapping("exportOrder")
public void exportOrder(HttpServletResponse response,Integer userId, String orderSn,
@RequestParam(required = false) @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss") LocalDateTime start,
@RequestParam(required = false) @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss") LocalDateTime end,
@RequestParam(required = false) List<Short> orderStatusArray,
@RequestParam(defaultValue = "1") Integer page,
@RequestParam(defaultValue = "10") Integer limit,
@Sort @RequestParam(defaultValue = "add_time") String sort,
@Order @RequestParam(defaultValue = "desc") String order) throws IOException, NoSuchFieldException {
List<LitemallOrder> list = orderExport.exportOrder(userId, orderSn, start, end, orderStatusArray, page, limit, sort, order);
List<ExportOrder> listInfo=new LinkedList<>();
list.stream().forEach(o->{
ExportOrder exportOrder=new ExportOrder();
BeanUtils.copyProperties(o,exportOrder);
String statusName=OrderUtil.orderStatusText(o);
exportOrder.setOrderStatus(statusName);
if(o.getAddTime() !=null){
exportOrder.setAddTime(DateUtils.asDate(o.getAddTime()));
}
if(o.getPayTime() !=null) {
exportOrder.setPayTime(DateUtils.asDate(o.getPayTime()));
}
if(o.getConfirmTime() !=null) {
exportOrder.setConfirmTime(DateUtils.asDate(o.getConfirmTime()));
}
listInfo.add(exportOrder);
});
Workbook workbook = (new ExcelUtil<ExportOrder>(){}).getWork(listInfo);
OutputStream output = response.getOutputStream();
response.reset();
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddhhmm");
String filename = "导出列表".concat(sdf.format(new Date(System.currentTimeMillis())));
response.setHeader("Content-disposition", "attachment; filename="+filename+".xlsx");
// 跨域
response.setHeader("Access-Control-Allow-Origin", "*");
response.setContentType("application/msexcel");
workbook.write(output);
output.close();
}
}