记第一次写了两天的导出接口
背景:导出的Excel中存在合并单元格,居中,合并单元格的宽度需要根据实际内容动态循环,效果如下:
其中类似于神娱(平台类型),T380(型号类型)都是动态的,在系统页面可以进行增加的
在这之前先看下数据库的数据结构:
数据情况如下:
先观察下每个下标的规律:excel中分别有神娱,爵册,阿东府三个平台,每个平台的列数分别是
|4|21|24|24|,每个平台里的日销量,到日供货价下标,分别是|4-6|25-27|49-51|
每个平台的开始下标和结束下表分别如图|0-3|4-24|25-48|49-72|,A1,B1分别代表合并单元格的开始列下标,结束列下标,1,2,3分别代表每个平台,共循环三次
循环型号:
每个平台型号下标分别如下:
将基础数据分组结构如下:
代码如下:
//创建poi导出数据对象
SXSSFWorkbook sxssfWorkbook=new SXSSFWorkbook();
//创建sheet页
SXSSFSheet sheet=sxssfWorkbook.createSheet("京东自营产品日报数据");
//创建表头
SXSSFRow headRow=sheet.createRow(0);
//创建每个平台的日销售量,日销售额,日供货额,型号
SXSSFRow dataRow = sheet.createRow(1);
//创建每个平台的数量,销售额,供货额
SXSSFRow dataRow1 = sheet.createRow(2);
//设置表头信息
SXSSFCell cell1 = headRow.createCell(0);
cell1.setCellValue("日期");
CellRangeAddress region1=new CellRangeAddress(0,2,0,0);
sheet.addMergedRegion(region1);
SXSSFCell cell2 = headRow.createCell(1);
cell2.setCellValue("日总销售量");
CellRangeAddress region2=new CellRangeAddress(0,2,1,1);
sheet.addMergedRegion(region2);
SXSSFCell cell3 =headRow.createCell(2);
cell3.setCellValue("日总销售额");
CellRangeAddress region3=new CellRangeAddress(0,2,2,2);
sheet.addMergedRegion(region3);
SXSSFCell cell4 =headRow.createCell(3);
cell4.setCellValue("日总供货额");
CellRangeAddress region4=new CellRangeAddress(0,2,3,3);
sheet.addMergedRegion(region4);
List<RunFxProductDailyDTO> runFxProductDailyDTOList=new ArrayList<>();
List<RunFxProductDaily> list = this.selectRunFxProductDailyList(runFxProductDaily);
//根据日期分组
Map<Date, List<RunFxProductDaily>> listMap = list.stream().collect(Collectors.groupingBy(RunFxProductDaily::getDate));
listMap.keySet().forEach(x-> {
List<RunFxProductDaily> productDailies = listMap.get(x);
RunFxProductDailyDTO runFxProductDailyDTO = new RunFxProductDailyDTO();
runFxProductDailyDTO.setDate(productDailies.get(0).getDate());
long num = productDailies.stream().filter(c -> c.getIsCalculate().equals("0")).mapToLong(a -> a.getSaleNum() == null ? 0 : a.getSaleNum()).sum();
runFxProductDailyDTO.setNumTotal(num);
BigDecimal price = productDailies.stream().map(b -> b.getSalePrcie() == null ? BigDecimal.ZERO : b.getSalePrcie()).reduce(BigDecimal.ZERO, BigDecimal::add);
runFxProductDailyDTO.setPriceTotal(price);
BigDecimal givePrice = productDailies.stream().map(b -> b.getSupplyPrice() == null ? BigDecimal.ZERO : b.getSupplyPrice()).reduce(BigDecimal.ZERO, BigDecimal::add);
runFxProductDailyDTO.setSupplyTotal(givePrice);
//将每个日期的型号数据再次根据平台分组
List<RunFxBaseProductDTO> runFxProductDailyList = new ArrayList<>();
Map<String, List<RunFxProductDaily>> platformListMap = productDailies.stream().collect(Collectors.groupingBy(RunFxProductDaily::getPlatformType));
platformListMap.keySet().forEach(y -> {
List<RunFxProductDaily> platformModelDailies = platformListMap.get(y);
RunFxBaseProductDTO runFxBaseProductDTO = new RunFxBaseProductDTO();
runFxBaseProductDTO.setPlatformType(platformModelDailies.get(0).getPlatformType());
long saleNum = platformModelDailies.stream().filter(c -> c.getIsCalculate().equals("0")).mapToLong(a -> a.getSaleNum() == null ? 0 : a.getSaleNum()).sum();
runFxBaseProductDTO.setSaleNumDay(saleNum);
BigDecimal salePrice = platformModelDailies.stream().map(b -> b.getSalePrcie() == null ? BigDecimal.ZERO : b.getSalePrcie()).reduce(BigDecimal.ZERO, BigDecimal::add);
runFxBaseProductDTO.setSalePrcieDay(salePrice);
BigDecimal supplyPrice = platformModelDailies.stream().map(b -> b.getSupplyPrice() == null ? BigDecimal.ZERO : b.getSupplyPrice()).reduce(BigDecimal.ZERO, BigDecimal::add);
runFxBaseProductDTO.setSupplyPriceDay(supplyPrice);
runFxBaseProductDTO.setRunFxProductDailies(platformModelDailies);
runFxProductDailyList.add(runFxBaseProductDTO);
});
runFxProductDailyDTO.setRunFxProductDailyList(runFxProductDailyList);
runFxProductDailyDTOList.add(runFxProductDailyDTO);
});
List<RunFxProductDailyDTO> dailyDTOList = runFxProductDailyDTOList.stream().sorted(Comparator.comparing(RunFxProductDailyDTO::getDate).reversed()).collect(Collectors.toList());
RunFxProductDailyDTO runFxProductDailyDTO = dailyDTOList.get(0);
//单个日期的所有平台条数
List<RunFxBaseProductDTO> runFxProductDailyList = runFxProductDailyDTO.getRunFxProductDailyList().stream().sorted(Comparator.comparing(RunFxBaseProductDTO::getPlatformType)).collect(Collectors.toList());
//合并行的开始下标和结束下标
int colA = 0;
int colB = 0;
for (int i = 0; i < runFxProductDailyList.size(); i++) {
String platformType = runFxProductDailyList.get(i).getPlatformType();
//平台名称
String name = null;
if (platformType.equals("1")) {
name = "神娱";
colA = 4;
} else if (platformType.equals("2")) {
name = "爵册";
colA = colB + 1;
} else if (platformType.equals("3")) {
name = "阿东府";
colA = colB + 1;
}
//该平台的几个型号
List<RunFxProductDaily> runFxProductDailies = runFxProductDailyList.get(i).getRunFxProductDailies();
//每个平台的型号个数
int modelNum = runFxProductDailies.size();
//每个平台所占用的列数
int modelCols = 3 * modelNum + 2;
//神娱/爵册/阿东府这一列名
SXSSFCell cell5 = headRow.createCell(colA);
cell5.setCellValue(name);
//居中
CellStyle cellStyle = sxssfWorkbook.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cell5.setCellStyle(cellStyle);
//平台合并单元格
CellRangeAddress region5 = new CellRangeAddress(0, 0, colA, colA + modelCols);
sheet.addMergedRegion(region5);
//赋值最后一次的值
colB = colA + modelCols;
SXSSFCell cell6 = dataRow.createCell(colA);
cell6.setCellValue("日销售量");
CellRangeAddress region6 = new CellRangeAddress(1, 2, colA, colA);
sheet.addMergedRegion(region6);
SXSSFCell cell7 = dataRow.createCell(colA+1);
cell7.setCellValue("日销售额");
CellRangeAddress region7 = new CellRangeAddress(1, 2, colA+1, colA + 1);
sheet.addMergedRegion(region7);
SXSSFCell cell8 = dataRow.createCell(colA+2);
cell8.setCellValue("日供货额");
CellRangeAddress region8 = new CellRangeAddress(1, 2, colA+2, colA + 2);
sheet.addMergedRegion(region8);
int colC=0;int colD=0;
int lastCol=colA+2;
//循环型号
for (int j = 0; j < runFxProductDailies.size(); j++) {
if (j == 0) {
colC = lastCol + 1;
} else {
colC = colD + 1;
}
SXSSFCell cell9 = dataRow.createCell(colC);
cell9.setCellValue(runFxProductDailies.get(j).getModelName());
//居中
CellStyle cellStyle1 = sxssfWorkbook.createCellStyle();
cellStyle1.setAlignment(HorizontalAlignment.CENTER);
cell9.setCellStyle(cellStyle1);
CellRangeAddress region9 = new CellRangeAddress(1, 1, colC, colC + 2);
sheet.addMergedRegion(region9);
colD = colC + 2;
//循环增加数量,销售额,供货价
SXSSFCell cell10 = dataRow1.createCell(colC);
cell10.setCellValue("数量");
CellStyle cellStyle2 = sxssfWorkbook.createCellStyle();
cellStyle2.setAlignment(HorizontalAlignment.CENTER);
cell10.setCellStyle(cellStyle2);
SXSSFCell cell11 = dataRow1.createCell(colC + 1);
cell11.setCellValue("销售额");
CellStyle cellStyle3 = sxssfWorkbook.createCellStyle();
cellStyle3.setAlignment(HorizontalAlignment.CENTER);
cell11.setCellStyle(cellStyle3);
SXSSFCell cell12 = dataRow1.createCell(colC + 2);
cell12.setCellValue("供货价");
CellStyle cellStyle4 = sxssfWorkbook.createCellStyle();
cellStyle4.setAlignment(HorizontalAlignment.CENTER);
cell12.setCellStyle(cellStyle4);
}
}
//循环填数据,和以上逻辑类似,AA是A的下面的第二次使用,其它字母一样,相对应的都指同一位置
for (int i = 0; i < dailyDTOList.size(); i++) {
RunFxProductDailyDTO fxProductDailyDTO = dailyDTOList.get(i);
//塞数据,从第4行开始
SXSSFRow dataRow2 = sheet.createRow(i+3);
dataRow2.createCell(0).setCellValue(DateUtils.dateTime(fxProductDailyDTO.getDate()));
dataRow2.createCell(1).setCellValue(fxProductDailyDTO.getNumTotal());
dataRow2.createCell(2).setCellValue(fxProductDailyDTO.getPriceTotal().doubleValue());
dataRow2.createCell(3).setCellValue(fxProductDailyDTO.getSupplyTotal().doubleValue());
int colAA = 0;
int colBB = 0;
//几个型号的数据
List<RunFxBaseProductDTO> runFxBaseProductDTOList = fxProductDailyDTO.getRunFxProductDailyList().stream().sorted(Comparator.comparing(RunFxBaseProductDTO::getPlatformType)).collect(Collectors.toList());
for (int j = 0; j < runFxBaseProductDTOList.size(); j++) {
RunFxBaseProductDTO runFxBaseProductDTO = runFxBaseProductDTOList.get(j);
String platformType = runFxBaseProductDTO.getPlatformType();
List<RunFxProductDaily> runFxProductDailies = runFxBaseProductDTO.getRunFxProductDailies();
int modelNum= runFxProductDailies.size();
if (platformType.equals("1")) {
colAA = 4;
} else if (platformType.equals("2")) {
colAA = colBB + 1;
} else if (platformType.equals("3")) {
colAA = colBB + 1;
}
dataRow2.createCell(colAA).setCellValue(runFxBaseProductDTO.getSaleNumDay());
dataRow2.createCell(colAA + 1).setCellValue(runFxBaseProductDTO.getSalePrcieDay().doubleValue());
dataRow2.createCell(colAA + 2).setCellValue(runFxBaseProductDTO.getSupplyPriceDay().doubleValue());
colBB = colAA + 3 * modelNum + 2;
//循环型号个数
int colCC=0;int colDD=0;
int lastCol=colAA+2;
//循环型号
for (int jj = 0; jj < runFxProductDailies.size(); jj++) {
RunFxProductDaily fxProductDaily = runFxProductDailies.get(jj);
if (jj == 0) {
colCC = lastCol + 1;
} else {
colCC = colDD + 1;
}
dataRow2.createCell(colCC).setCellValue(fxProductDaily.getSaleNum());
dataRow2.createCell(colCC + 1).setCellValue(fxProductDaily.getSalePrcie().doubleValue());
dataRow2.createCell(colCC + 2).setCellValue(fxProductDaily.getSupplyPrice().doubleValue());
colDD = colCC + 2;
}
}
}
// 下载导出
String filename="分销销售日报数据";
// 设置头信息
response.setCharacterEncoding("UTF-8");
response.setContentType("application/vnd.ms-excel");
//一定要设置成xlsx格式
response.setHeader("Content-Disposition","attachment;filename="+ URLEncoder.encode(filename+".xlsx","UTF-8"));
//创建一个输出流
ServletOutputStream outputStream=response.getOutputStream();
//写入数据
sxssfWorkbook.write(outputStream);
// 关闭
outputStream.close();
sxssfWorkbook.close();
注意:数据结构返回的数据都要一置,否则会导致导出的型号顺序不一致
例如:每一天都有所有的型号数据,那么每一天都应该有这些型号的数据,并且定死顺序
写在最后:遇到困难,戒焦躁,静下心,一步一步来,终会成为更好的自己.一起加油.
1191
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
