导出固定模板
基类，使用@Excel注解引入，name对应的excel上的列名称，列的顺序跟进当前属性的顺序
public class TicketFundsInfo implements Serializable {
private static final long serialVersionUID = -7996895499520522564L;
@ApiModelProperty("名称")
@Excel(name = "名称")
private String name;
@ApiModelProperty("时间")
@Excel(name = "时间")
private String gmtCreate;
@ApiModelProperty("订单号")
@Excel(name = "订单号")
private String outTradeNo;
@ApiModelProperty("类型")
@Excel(name = "类型")
private String type;
@ApiModelProperty("支付方式")
@Excel(name = "支付方式")
private String payType;
@ApiModelProperty("金额")
@Excel(name = "金额")
private String totalFee;
}
固定模板导出很简单，直接拿到数据调用到出即可
Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("资金流水", "资金流水", ExcelType.XSSF), TicketFundsInfo.class, ticketFundsInfos);
导出文件类型通过ExcelType来设置，ExcelType.HSSF=xls(默认)，ExcelType.XSSF=xlsx
//获取数据
List<TicketFundsInfo> ticketFundsInfos = financeService.capitalFlow(input);
Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("资金流水", "资金流水", ExcelType.XSSF), TicketFundsInfo.class, ticketFundsInfos);
try {
Sheet sheet = workbook.getSheet("资金流水");
for (int i = 0; i < 6; i++) {
//调整每一列宽度
sheet.autoSizeColumn((short) i);
// 解决自动设置列宽中文失效的问题
sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 16 / 10);
}
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("资金流水.xlsx", "UTF-8"));
response.setHeader("Connection", "close");
response.setDateHeader("Expires", 0);
workbook.write(response.getOutputStream());
} catch (Exception e) {
throw new IOException(e.getMessage());
}
导出结果示例图
导出动态数据
如下图中间模块根据实际情况动态变化，不是固定的
easypoi 支持动态自定义列表导出，主要是通过ExcelExportEntity来设置列的信息以及合并单元格
可以把整个sheet理解成一个Map,你需要设置好sheet的每一列(ExcelExportEntity excelentity)，列名称就是Map的key;如果这个key下面需要合并多个列，通过设置excelentity.setList(List entityList),entityList就是合并列的对象集合
只要把列设置好了，剩下的就是填充内容，填充内容的时候要注意对应的key，还有value类型是集合，保持一致的数据结构就可以
//获取数据
List<EveryDayExcel> everyDayExcelList = null;
//设置Excel列集合
List<ExcelExportEntity> modelList = new ArrayList<ExcelExportEntity>();
//列设置
ExcelExportEntity excelentity;
//第一列名称
excelentity = new ExcelExportEntity("时间", "time");
modelList.add(excelentity);
//类型分组
Map<Integer, List<EveryDayExcel>> excelMap = everyDayExcelList.stream().collect(Collectors.groupingBy(EveryDayExcel::getTicketType));
Set<Map.Entry<Integer, List<EveryDayExcel>>> entries = excelMap.entrySet();
//数组记录每列下的合计数据
Integer[] totalWriteOffNum = new Integer[entries.size()];
Integer[] totalTicketNum = new Integer[entries.size()];
BigDecimal[] totalTotalFee = new BigDecimal[entries.size()];
BigDecimal[] totalCashFee = new BigDecimal[entries.size()];
BigDecimal[] totalAliPayFee = new BigDecimal[entries.size()];
BigDecimal[] totalWeChatFee = new BigDecimal[entries.size()];
//支付统计
BigDecimal cash = new BigDecimal(0);
BigDecimal aliPay = new BigDecimal(0);
BigDecimal wePay = new BigDecimal(0);
BigDecimal totalAmount = new BigDecimal(0);
int i =0;
Map<Integer, List<EveryDayExcel>> mapExcelList = new HashMap<>();
for (Map.Entry<Integer, List<EveryDayExcel>> map : entries) {
Integer ticketType = map.getKey();
String name = "";
if (1 == ticketType) {
name = "全价票";
}
if (2 == ticketType) {
name = "优惠票";
}
if (3 == ticketType) {
name = "赠票";
}
//记录下i对应的数据集合
mapExcelList.put(i,map.getValue());
//添加下一列名称
excelentity = new ExcelExportEntity(name, "name"+i);
//当前列下合并列的列名称
List<ExcelExportEntity> child = new ArrayList<ExcelExportEntity>();
child.add(new ExcelExportEntity("张数", "ticketNum"+i));
child.add(new ExcelExportEntity("金额", "totalFee"+i));
child.add(new ExcelExportEntity("现金金额", "cashFee"+i));
child.add(new ExcelExportEntity("支付宝金额", "aliPayFee"+i));
child.add(new ExcelExportEntity("微信金额", "weChatFee"+i));
excelentity.setList(child);
modelList.add(excelentity);
i++;
}
//添加新的一列
excelentity = new ExcelExportEntity("支付渠道", "buyChannel");
//当前列下合并的列对应名称
List<ExcelExportEntity> child = new ArrayList<ExcelExportEntity>();
child.add(new ExcelExportEntity("现金", "cash"));
child.add(new ExcelExportEntity("支付宝", "aliPay"));
child.add(new ExcelExportEntity("微信", "wePay"));
excelentity.setList(child);
modelList.add(excelentity);
//添加最后一列
modelList.add(new ExcelExportEntity("合计", "totalFee"));
//填充数据
//时间分组
Map<String, List<EveryDayExcel>> gmtCreateMap = everyDayExcelList.stream().collect(Collectors.groupingBy(EveryDayExcel::getGmtCreate));
Set<String> gmtCreateSet = gmtCreateMap.keySet();
List<Map<String, Object>> dataList = new ArrayList<>();
Set<Map.Entry<Integer, List<EveryDayExcel>>> excelListEntries = mapExcelList.entrySet();
for (String gmtCreate : gmtCreateSet) {
BigDecimal totalCash = new BigDecimal(0);
BigDecimal totalAliPay = new BigDecimal(0);
BigDecimal totalWePay = new BigDecimal(0);
BigDecimal totalFee = new BigDecimal(0);
Map<String, Object> map = new HashMap<>();
//第一列赋值
map.put("time", gmtCreate);
for(Map.Entry<Integer, List<EveryDayExcel>> excelListMap : excelListEntries) {
Integer key = excelListMap.getKey();
List<EveryDayExcel> excelList = excelListMap.getValue();
Boolean haveGmtCreate1 = false;
Map<String, Object> map1 = new HashMap<>();
List<Map<String, Object>> listMap1 = new ArrayList<>();
for (EveryDayExcel everyDayExcel : excelList) {
if (gmtCreate.equals(everyDayExcel.getGmtCreate())) {
haveGmtCreate1 = true;
map1.put("ticketNum"+key, everyDayExcel.getTicketNum());
map1.put("totalFee"+key, everyDayExcel.getTotalFee().doubleValue() / 100);
map1.put("cashFee"+key,everyDayExcel.getCashFee().doubleValue()/100);
map1.put("aliPayFee"+key,everyDayExcel.getAlipayFee().doubleValue()/100);
map1.put("weChatFee"+key,everyDayExcel.getWeChatFee().doubleValue()/100);
totalCash = totalCash.add(new BigDecimal(everyDayExcel.getCashFee().doubleValue() / 100));
totalAliPay = totalAliPay.add(new BigDecimal(everyDayExcel.getAlipayFee().doubleValue() / 100));
totalWePay = totalWePay.add(new BigDecimal(everyDayExcel.getWeChatFee().doubleValue() / 100));
//最后一行总计
if(null==totalWriteOffNum[key]){
totalWriteOffNum[key] = everyDayExcel.getWriteOffNum();
}else{
totalWriteOffNum[key] = totalWriteOffNum[key]+ everyDayExcel.getWriteOffNum();
}
if(null==totalTicketNum[key]){
totalTicketNum[key] = everyDayExcel.getTicketNum();
}else{
totalTicketNum[key] = totalTicketNum[key]+ everyDayExcel.getTicketNum();
}
if(null==totalTotalFee[key]){
totalTotalFee[key] = new BigDecimal(everyDayExcel.getTotalFee().doubleValue() / 100);
}else{
totalTotalFee[key] = totalTotalFee[key].add(new BigDecimal(everyDayExcel.getTotalFee().doubleValue() / 100));
}
if(null==totalCashFee[key]){
totalCashFee[key] = new BigDecimal(everyDayExcel.getCashFee().doubleValue() / 100);
}else{
totalCashFee[key] = totalCashFee[key].add(new BigDecimal(everyDayExcel.getCashFee().doubleValue() / 100));
}
if(null==totalAliPayFee[key]){
totalAliPayFee[key] = new BigDecimal(everyDayExcel.getAlipayFee().doubleValue() / 100);
}else{
totalAliPayFee[key] = totalAliPayFee[key].add(new BigDecimal(everyDayExcel.getAlipayFee().doubleValue() / 100));
}
if(null==totalWeChatFee[key]){
totalWeChatFee[key] = new BigDecimal(everyDayExcel.getWeChatFee().doubleValue() / 100);
}else{
totalWeChatFee[key] = totalWeChatFee[key].add(new BigDecimal(everyDayExcel.getWeChatFee().doubleValue() / 100));
}
break;
}
}
if (!haveGmtCreate1) {
map1.put("ticketNum"+key, 0);
map1.put("totalFee"+key, new BigDecimal(0));
map1.put("cashFee"+key,new BigDecimal(0));
map1.put("aliPayFee"+key,new BigDecimal(0));
map1.put("weChatFee"+key,new BigDecimal(0));
}
listMap1.add(map1);
map.put("name"+key, listMap1);
}
List<Map<String, Object>> buyChannelMapList = new ArrayList<>();
Map<String, Object> buyChannelMap = new HashMap<>();
buyChannelMap.put("cash", df.format(totalCash));
buyChannelMap.put("aliPay", df.format(totalAliPay));
buyChannelMap.put("wePay", df.format(totalWePay));
buyChannelMapList.add(buyChannelMap);
cash = cash.add(totalCash);
aliPay = aliPay.add(totalAliPay);
wePay = wePay.add(totalWePay);
map.put("buyChannel", buyChannelMapList);
totalFee = totalFee.add(totalCash).add(totalAliPay).add(totalWePay);
map.put("totalFee", df.format(totalFee));
totalAmount = totalAmount.add(totalFee);
dataList.add(map);
}
//最后一行合计
Map<String, Object> lastMap = new HashMap<>();
lastMap.put("time", "合计");
for(Map.Entry<Integer, List<EveryDayExcel>> excelListMap : excelListEntries) {
Integer key = excelListMap.getKey();
Map<String, Object> mapType = new HashMap<>();
List<Map<String, Object>> listMap1 = new ArrayList<>();
mapType.put("writeOffNum"+key, totalWriteOffNum[key]);
mapType.put("ticketNum"+key, totalTicketNum[key]);
mapType.put("totalFee"+key, df.format(totalTotalFee[key]));
mapType.put("cashFee"+key,df.format(totalCashFee[key]));
mapType.put("aliPayFee"+key,df.format(totalAliPayFee[key]));
mapType.put("weChatFee"+key,df.format(totalWeChatFee[key]));
listMap1.add(mapType);
lastMap.put("name"+key,listMap1);
}
//支付渠道
List<Map<String, Object>> buyChannelMapList = new ArrayList<>();
Map<String, Object> buyChannelMap = new HashMap<>();
buyChannelMap.put("cash", df.format(cash));
buyChannelMap.put("aliPay", df.format(aliPay));
buyChannelMap.put("wePay", df.format(wePay));
buyChannelMapList.add(buyChannelMap);
lastMap.put("buyChannel",buyChannelMapList);
lastMap.put("totalFee",df.format(totalAmount));
dataList.sort(Comparator.comparing(item -> item.get("time").toString()));
dataList.add(lastMap);
return ExcelExportUtil.exportExcel(new ExportParams(excelTitle+"每日销售额", "每日销售额"), modelList,
dataList);
扫一扫
3831
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
