就拿一个生成简单的日报的表格来演示:
首先,我们先引入我们的依赖:
<!--easyExcel-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.1</version>
</dependency>
接下来在我们的进行我们的data映射,实际上的每一个属性即对应每一行的属性:
/**
* 日报生成对应实体类
* @author Cocowwy
* @create 2021-01-01-16:04
*/
@Data
@Builder
public class TodayTradeData {
@ExcelProperty("订单ID")
private Long tradeId;
@ExcelProperty("商品名称")
private String tradeBody;
@ExcelProperty("商品详细信息")
private String tradeMsg;
@ExcelProperty("下单时间")
private String initTime;
@ExcelProperty("总付款金额")
private Double totalMoney;
@ExcelProperty("收货名")
private String receiptName;
@ExcelProperty("收货手机号")
private String receiptPhone;
@ExcelProperty("收货地址")
private String receiptAddress;
@ExcelProperty("状态")
private String status;
}
接着打开我们的controller:
/**
* Excel文件下载接口 生成订单结果的Excel文件
* @author Cocowwy
* @create 2021-01-01-15:43
*/
@RestController
@RequestMapping("/downLoad")
public class FileController {
@Autowired
TradeService tradeService;
@GetMapping("/downDayTrade/{status}")
public void downDayTrade(HttpServletResponse response,@PathVariable("status") String status) throws IOException {
// 这里注意 有同学反应使用swagger 会导致各种问题,请直接用浏览器或者用postman
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
String fileName = URLEncoder.encode(DateUtil.today() + "订单已付款汇总", "UTF-8")
.replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
EasyExcel.write(response.getOutputStream(), TodayTradeData.class).sheet("模板").doWrite(dayDownPayTrade(status));
}
// 这一步是塞入data List<TodayTradeData> 就是每一行的数据放在一个集合里面
private List<TodayTradeData> dayDownPayTrade(String status) {
// 获取今日 00:00 到 23:59:59.99 之间的订单 使用mybatis-plus获取结果集
List<Trade> tradeList = tradeService.lambdaQuery()
.eq(Trade::getTradeStatus, status)
.between(Trade::getInitTime, LocalDateTimeUtil.beginOfDay(LocalDateTimeUtil.now()),
LocalDateTimeUtil.endOfDay(LocalDateTimeUtil.now())).list();
//将数据库查出来的数据映射到打印的结果集里面
List<TodayTradeData> list = new ArrayList<>();
for (int i = 0; i < tradeList.size(); i++) {
list.add(TodayTradeData
.builder()
.tradeId(tradeList.get(i).getTradeId())
.tradeBody(tradeList.get(i).getTradeBody())
.initTime(LocalDateTimeUtil.format(tradeList.get(i).getInitTime(), "yyyy-MM-dd HH:mm:ss"))
.totalMoney(tradeList.get(i).getTatalMoney())
.receiptName(tradeList.get(i).getReceiptName())
.receiptAddress(tradeList.get(i).getReceiptAddress())
.receiptPhone(tradeList.get(i).getReceiptPhone())
.status(status)
.build());
}
return list;
}
}
接着在浏览器发送GET请求即可