1:需求结果
2:分析:
主要是分析哪些列需要合并,然后采用代码:
CellRangeAddress callRangeAddressn = new CellRangeAddress(rowNum-j, rowNum-1, 0, 0); sheet.addMergedRegion(callRangeAddressn);
进行合并。
一些动态行或者列,自己定义一个变量进行记录,判断到哪些地方开始合并。
3:代码
public String exportSaleDayReport(DaySaleReportInput daySaleReportInput) throws IOException {
// 查询所有的产品
List<NameAndParamDTO> nameAndParamDTOS = materialService.getAllMaterial(daySaleReportInput.getProductIds());
int productSize = nameAndParamDTOS.size();
// 第一行合并5+size的列
// 1.创建工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("销售日报表");
sheet.setColumnWidth(0, 20*256);
sheet.setColumnWidth(1, 40*256);
sheet.setColumnWidth(2, 20*256);
sheet.setColumnWidth(3, 20*256);
sheet.setColumnWidth(4, 20*256);
sheet.setColumnWidth(5, 20*256);
sheet.setColumnWidth(6, 20*256);
sheet.setColumnWidth(7, 20*256);
sheet.setColumnWidth(8, 20*256);
sheet.setColumnWidth(9, 20*256);
// 第一行标题
CellRangeAddress callRangeAddress01 = new CellRangeAddress(0, 0, 0, 4+productSize);
// 第二行标题
HSSFRow row2 = sheet.createRow(2);
HSSFRow row3 = sheet.createRow(1);
// 区域(合并)
CellRangeAddress callRangeAddress21 = new CellRangeAddress(1, 2, 0, 0);
sheet.addMergedRegion(callRangeAddress21);
// 客户(合并)
CellRangeAddress callRangeAddress22 = new CellRangeAddress(1, 2, 1, 1);
sheet.addMergedRegion(callRangeAddress22);
// 区间发货
CellRangeAddress callRangeAddress23 = new CellRangeAddress(1, 1, 2, 1+productSize);
sheet.addMergedRegion(callRangeAddress23);
// 产品(产品是动态查询出来的)
for (int i=0;i<nameAndParamDTOS.size();i++){
//CellRangeAddress callRangeAddress33 = new CellRangeAddress(2, 2, 2+i, 2+i);
//sheet.addMergedRegion(callRangeAddress33);
HSSFCell cell2 = row2.createCell(2+i);
cell2.setCellValue(nameAndParamDTOS.get(i).getName());
}
// 区间累计
CellRangeAddress callRangeAddress24 = new CellRangeAddress(1, 2, 2+productSize, 2+productSize);
sheet.addMergedRegion(callRangeAddress24);
// 分户月累
CellRangeAddress callRangeAddress25 = new CellRangeAddress(1, 1, 3+productSize, 4+productSize);
sheet.addMergedRegion(callRangeAddress25);
// 包装
//CellRangeAddress callRangeAddress34 = new CellRangeAddress(2, 2, 3+productSize, 3+productSize);
// sheet.addMergedRegion(callRangeAddress34);
// 散装
//CellRangeAddress callRangeAddress35 = new CellRangeAddress(2, 2, 4+productSize, 4+productSize);
//sheet.addMergedRegion(callRangeAddress35);
// 标题样式
HSSFCellStyle telStyle1 = workbook.createCellStyle();
telStyle1.setAlignment(HorizontalAlignment.CENTER);
telStyle1.setVerticalAlignment(VerticalAlignment.CENTER);
HSSFFont font = workbook.createFont();
font.setFontName("黑体");
font.setFontHeightInPoints((short) 16);//设置字体大小
telStyle1.setFont(font);
//font.setBold(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
HSSFCellStyle telStyle2 = workbook.createCellStyle();
telStyle2.setAlignment(HorizontalAlignment.CENTER);
telStyle2.setVerticalAlignment(VerticalAlignment.CENTER);
HSSFFont font2 = workbook.createFont();
font2.setFontName("黑体");
font2.setFontHeightInPoints((short) 12);//设置字体大小
telStyle2.setFont(font2);
HSSFCellStyle telStyle3 = workbook.createCellStyle();
DataFormat dataFormat = workbook.createDataFormat();
telStyle3.setDataFormat(dataFormat.getFormat("0.00"));
telStyle3.setAlignment(HorizontalAlignment.CENTER);
telStyle3.setVerticalAlignment(VerticalAlignment.CENTER);
sheet.addMergedRegion(callRangeAddress01);
HSSFRow row1 = sheet.createRow(0);
HSSFCell cell1 = row1.createCell(0);
// 加载单元格样式
cell1.setCellStyle(telStyle1);
cell1.setCellValue((StringUtils.isBlank(daySaleReportInput.getStartDate())?"2019-11-01 00:00:00":daySaleReportInput.getStartDate())+" 至 "+(StringUtils.isBlank(daySaleReportInput.getEndDate())?Times.toString(LocalDateTime.now()):daySaleReportInput.getEndDate())+"销售日报表");
// 二级标题
HSSFCell cell3 = row3.createCell(0);
cell3.setCellValue("区域");
cell3.setCellStyle(telStyle2);
HSSFCell cell4 = row3.createCell(1);
cell4.setCellValue("客户");
cell4.setCellStyle(telStyle2);
HSSFCell cell5 = row3.createCell(2);
cell5.setCellValue("区间发货");
cell5.setCellStyle(telStyle2);
HSSFCell cell6 = row3.createCell(2+productSize);
cell6.setCellStyle(telStyle2);
cell6.setCellValue("区间累计");
HSSFCell cell7 = row3.createCell(3+productSize);
cell7.setCellValue("分户月累");
cell7.setCellStyle(telStyle2);
HSSFCell cell8 = row2.createCell(3+productSize);
cell8.setCellValue("包装");
cell8.setCellStyle(telStyle2);
HSSFCell cell9 = row2.createCell(4+productSize);
cell9.setCellValue("散装");
cell9.setCellStyle(telStyle2);
// 查询赋值
List<DaySaleReportDTO> daySaleReportDTOList = gaugeOrderService.getDaySaleReport(daySaleReportInput.getOrganizationId(),daySaleReportInput.getStartDate(),daySaleReportInput.getEndDate(),daySaleReportInput.getCustomerIds(),daySaleReportInput.getDistrictIds(),daySaleReportInput.getProductIds());
// 查询产品的销售量
for (DaySaleReportDTO daySaleReportDTO:daySaleReportDTOList){
// 查询散装袋装月积累
BigDecimal bulkCounts = gaugeOrderMapper.getBulkBagCounts(daySaleReportDTO.getCustomerId(),daySaleReportDTO.getDistrictId(),daySaleReportInput.getProductIds(),0,daySaleReportInput.getStartDate(),daySaleReportInput.getEndDate());
BigDecimal bagsCounts = gaugeOrderMapper.getBulkBagCounts(daySaleReportDTO.getCustomerId(),daySaleReportDTO.getDistrictId(),daySaleReportInput.getProductIds(),1,daySaleReportInput.getStartDate(),daySaleReportInput.getEndDate());
daySaleReportDTO.setBagCounts(bagsCounts);
daySaleReportDTO.setBulkCounts(bulkCounts);
// 查询产品的数量
List<NameAndParamDTO> proList = Lists.newArrayList();
for (NameAndParamDTO nameAndParamDTO:nameAndParamDTOS){
BigDecimal counts = gaugeOrderMapper.getProductDaySale(daySaleReportDTO.getCustomerId(),daySaleReportDTO.getDistrictId(),nameAndParamDTO.getId(),daySaleReportInput.getStartDate(),daySaleReportInput.getEndDate());
nameAndParamDTO.setCounts(counts);
NameAndParamDTO nameAndParamDTO1 = new NameAndParamDTO();
BeanUtils.copyProperties(nameAndParamDTO,nameAndParamDTO1);
proList.add(nameAndParamDTO1);
}
// 添加集合
daySaleReportDTO.setNameAndCountList(proList);
}
// 遍历集合插入到
int rowNum = 3;
String districtName = "";
/*if (daySaleReportDTOList.size()>0)
districtName= daySaleReportDTOList.get(0).getDistrictName();*/
int j = 0;
for (int i=0;i<daySaleReportDTOList.size();i++){
HSSFRow rown = sheet.createRow(rowNum);
if (districtName == ""||!districtName.equals(daySaleReportDTOList.get(i).getDistrictName())){
if (j>1){
// 合并单元格
CellRangeAddress callRangeAddressn = new CellRangeAddress(rowNum-j, rowNum-1, 0, 0);
sheet.addMergedRegion(callRangeAddressn);
}
j = 0;
districtName = daySaleReportDTOList.get(i).getDistrictName();
HSSFCell celln0 =rown.createCell(0);
celln0.setCellValue(daySaleReportDTOList.get(i).getDistrictName());
celln0.setCellStyle(telStyle3);
}
HSSFCell celln1 =rown.createCell(1);
celln1.setCellValue(daySaleReportDTOList.get(i).getCustomerName());
celln1.setCellStyle(telStyle3);
// 产品
for(int m=0;m<daySaleReportDTOList.get(i).getNameAndCountList().size();m++){
HSSFCell celln2 =rown.createCell(2+m);
celln2.setCellValue(Double.parseDouble(daySaleReportDTOList.get(i).getNameAndCountList().get(m).getCounts().toString()));
celln2.setCellStyle(telStyle3);
}
// 区间累计
HSSFCell celln3 =rown.createCell(2+productSize);
celln3.setCellValue(Double.parseDouble(daySaleReportDTOList.get(i).getTotalCounts().toString()));
celln3.setCellStyle(telStyle3);
// 包装
HSSFCell celln4 =rown.createCell(3+productSize);
celln4.setCellValue(Double.parseDouble(daySaleReportDTOList.get(i).getBagCounts().toString()));
celln4.setCellStyle(telStyle3);
// 散装
HSSFCell celln5 =rown.createCell(4+productSize);
celln5.setCellValue(Double.parseDouble(daySaleReportDTOList.get(i).getBulkCounts().toString()));
celln5.setCellStyle(telStyle3);
rowNum++;
j++;
// 如果是最后一个判断是否合并
if (i==daySaleReportDTOList.size()-1){
if (j>1){
// 合并单元格
CellRangeAddress callRangeAddressn = new CellRangeAddress(rowNum-j, rowNum-1, 0, 0);
sheet.addMergedRegion(callRangeAddressn);
}
}
}
// 合计
CellRangeAddress callRangeAddressn1 = new CellRangeAddress(rowNum, rowNum, 0, 1);
sheet.addMergedRegion(callRangeAddressn1);
HSSFRow rown1 = sheet.createRow(rowNum);
HSSFCell cellt1 =rown1.createCell(0);
cellt1.setCellValue("合计");
cellt1.setCellStyle(telStyle1);
// 数量总计
// 合计
// 总的
BigDecimal total = gaugeOrderMapper.getPackTotal(daySaleReportInput.getStartDate(),daySaleReportInput.getEndDate(),null,daySaleReportInput.getCustomerIds(),daySaleReportInput.getDistrictIds(),daySaleReportInput.getProductIds());
// 袋装
BigDecimal bagTotal = gaugeOrderMapper.getPackTotal(daySaleReportInput.getStartDate(),daySaleReportInput.getEndDate(),1,daySaleReportInput.getCustomerIds(),daySaleReportInput.getDistrictIds(),daySaleReportInput.getProductIds());
// 散装
BigDecimal bulkTotal = gaugeOrderMapper.getPackTotal(daySaleReportInput.getStartDate(),daySaleReportInput.getEndDate(),0,daySaleReportInput.getCustomerIds(),daySaleReportInput.getDistrictIds(),daySaleReportInput.getProductIds());
// 分产品统计
for (NameAndParamDTO nameAndParamDTO:nameAndParamDTOS){
List<Long> proId = Lists.newArrayList();
proId.add(nameAndParamDTO.getId());
BigDecimal productCounts = gaugeOrderMapper.getPackTotal(daySaleReportInput.getStartDate(),daySaleReportInput.getEndDate(),null,daySaleReportInput.getCustomerIds(),daySaleReportInput.getDistrictIds(),proId);
nameAndParamDTO.setCounts(productCounts);
}
for(int k=0;k<nameAndParamDTOS.size();k++){
HSSFCell cellnt =rown1.createCell(2+k);
cellnt.setCellValue(nameAndParamDTOS.get(k).getCounts().toString());
cellnt.setCellStyle(telStyle3);
}
// 区间累计
HSSFCell cellnt1 =rown1.createCell(2+productSize);
cellnt1.setCellValue(total.toString());
cellnt1.setCellStyle(telStyle3);
// 包装
HSSFCell cellnt2 =rown1.createCell(3+productSize);
cellnt2.setCellValue(bagTotal.toString());
cellnt2.setCellStyle(telStyle3);
// 散装
HSSFCell cellnt3 =rown1.createCell(4+productSize);
cellnt3.setCellValue(bulkTotal.toString());
cellnt3.setCellStyle(telStyle3);
// 输出文件
Long unixTimestamp = Times.toUnixTimestamp(LocalDateTime.now());
String mapping = coreProperties.getFile().getMapping();
String excelName = DirectoryName.DOWNLOAD_EXCEL_DIRECTORY + "/" + "销售日报表" + unixTimestamp + ".xls";
String filePath = mapping + excelName;//coreProperties.getAddress() +
String fileName = coreProperties.getFile().getLocation() + excelName;
File file = new File(fileName);
FileOutputStream fout = new FileOutputStream(file);
workbook.write(fout);
fout.close();
return filePath;
}