Java导出excel数据最后一行合计
java导出excel数据最后一行如何进行合计操作啊?运用的是@ResponseExcel注解,是直接生成excel文件的,只需要创建一个vo类接收list中的数据就行,查阅了好多资料,都是用的poi和自定义拦截器搞得,,现在遇到这种问题,,真的不知道怎么解决,求解!
因为一些数据的原因,我把部分字段和类换了名字
//ExcelPerformanceDto类
@Data
@ApiModel(value = "导出医生绩效Excel")
public class InquiryExcelPerformanceDto {
@ExcelProperty("姓名")
@ColumnWidth(20)
@ApiModelProperty("姓名")
private String staffName;
@ExcelProperty("身份证号")
@ColumnWidth(20)
@ApiModelProperty("身份证号")
private String staffIdCard;
@ExcelProperty("职称")
@ColumnWidth(20)
@ApiModelProperty("职称")
private String staffTitle;
@ExcelProperty("类型")
@ColumnWidth(20)
@ApiModelProperty("类型")
private String serviceProcess;
@ExcelProperty("科室")
@ColumnWidth(20)
@ApiModelProperty("科室")
private String staffDepartName;
@ExcelProperty("次数")
@ColumnWidth(20)
@ApiModelProperty("次数")
private Integer staffNum;
@ExcelProperty("金额/元")
@ColumnWidth(20)
@ApiModelProperty("金额")
private BigDecimal payment;
@ExcelProperty("总金额/元")
@ColumnWidth(20)
@ApiModelProperty("总金额")
private BigDecimal comepay;
}
// Controller层
@PostMapping("/excelExportPerformance")
@ApiOperation(value = "Excel数据导出")
@ResponseExcel(name = "数据导出")
public List<ExcelPerformanceDto> excelInquiryExport(@RequestBody PerformanceModel performanceModel) {
return InfoService.exportPerformance(performanceModel);
}
//Service层
List<ExcelPerformanceDto> exportPerformance(PerformanceModel inquiryPerformanceModel);
//实现类层
@Override
public List<ExcelPerformanceDto> exportPerformance(PerformanceModel performanceModel) {
//查询状态已结束的问诊单数据
List<PerformanceVo> PerformanceVos = InfoService.findPerformance(performanceModel);
List<InquiryExcelPerformanceDto> list = new ArrayList<>();
//根据医生身份证号分组
Map<String, List<InquiryPerformanceVo>> staffList = inquiryPerformanceVos.stream().collect(Collectors.groupingBy(InquiryPerformanceVo::getStaffIdCard));
staffList.keySet().forEach(staff->{
Map<Integer, List<InquiryPerformanceVo>> service = staffList.get(staff).stream().collect(Collectors.groupingBy(InquiryPerformanceVo::getServiceProcess));
service.keySet().forEach(dp->{
Map<String, List<InquiryPerformanceVo>> dpName = service.get(dp).stream().collect(Collectors.groupingBy(InquiryPerformanceVo::getStaffDepartName));
dpName.keySet().forEach(dpn->{
InquiryExcelPerformanceDto performanceDto = new InquiryExcelPerformanceDto();
//设置excel 医生姓名
performanceDto.setStaffName(dpName.get(dpn).get(0).getStaffName());
//身份证号码
performanceDto.setStaffIdCard(dpName.get(dpn).get(0).getStaffIdCard());
//职称
performanceDto.setStaffTitle(dpName.get(dpn).get(0).getStaffTitle());
//出诊类型
performanceDto.setServiceProcess(ServiceProcessPerformanceEnum.getProcessName(dpName.get(dpn).get(0).getServiceProcess()));
//出诊科室
performanceDto.setStaffDepartName(dpName.get(dpn).get(0).getStaffDepartName());
//出诊次数
performanceDto.setStaffNum(dpName.get(dpn).get(0).getStaffNum());
//金额
performanceDto.setPayment(dpName.get(dpn).get(0).getPayment());
//总金额
performanceDto.setComepay(dpName.get(dpn).stream().map(InquiryPerformanceVo::getPayment).reduce(BigDecimal::add).get());
list.add(performanceDto);
});
});
});
需要导成这样的:
目前就卡在了合计 时间段 统计人这里 不知道怎么在下面加单元格了,希望有各位能帮我提供个思路,谢谢各位了
已经解决了,献上代码
@SneakyThrows
@Override
public void exportPerformance(InquiryPerformanceModel inquiryPerformanceModel, HttpServletResponse response) {
//查询状态已结束、已回复的问诊单
List<InquiryPerformanceVo> vos = inquiryInfoService.findPerformance(inquiryPerformanceModel);
List<InquiryExcelPerformanceDto> list = vos.stream().map(v -> {
InquiryExcelPerformanceDto dto = new InquiryExcelPerformanceDto();
dto.setServiceProcess(ServiceProcessPerformanceEnum.getProcessName(v.getServiceProcess()));
dto.setComepay(v.getPayment());
BeanUtils.copyProperties(v, dto);
return dto;
}).collect(Collectors.toList());
if (list.size() == 0) throw new ServiceException("暂无数据");
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
response.addHeader("Content-Disposition", "attachment;filename=" + "医生绩效管理" + ".xlsx");
//医生绩效模板，docker中已jar运行，则流读取文件
InputStream in = this.getClass().getClassLoader().getResourceAsStream("excelTemplate.xlsx");
ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).withTemplate(in).build();
WriteSheet writeSheet = EasyExcel.writerSheet().build();
// 直接写入数据
excelWriter.fill(list, writeSheet);
// 写入list之前的数据
Map<String, Object> map = new HashMap<String, Object>();
String begin = DateUtil.format(DateUtil.beginOfMonth(DateUtil.parse(inquiryPerformanceModel.getMoth(), "yyyy-MM")), "yyyy-MM-dd");
String end = DateUtil.format(DateUtil.endOfMonth(DateUtil.parse(inquiryPerformanceModel.getMoth(), "yyyy-MM")), "yyyy-MM-dd");
map.put("time", "统计时间段：" + begin + "至" + end);
excelWriter.fill(map, writeSheet);
// list 后面还有个统计 想办法手动写入
// 这里偷懒直接用list 也可以用对象
List<List<Object>> totalListList = new ArrayList<>();
List<Object> totalList1 = new ArrayList<>();
totalList1.add("合计");
// 第二列
totalList1.add(null);
totalList1.add(null);
totalList1.add(null);
totalList1.add(null);
totalList1.add(list.stream().mapToInt(InquiryExcelPerformanceDto::getStaffNum).sum());
totalList1.add(list.stream().filter(i -> ObjectUtil.isNotEmpty(i.getPayment())).collect(Collectors.toList()).stream()
.map(InquiryExcelPerformanceDto::getPayment).reduce(BigDecimal.ZERO, BigDecimal::add));
totalList1.add(list.stream().filter(i -> ObjectUtil.isNotEmpty(i.getComepay())).collect(Collectors.toList()).stream()
.map(InquiryExcelPerformanceDto::getComepay).reduce(BigDecimal.ZERO, BigDecimal::add));
totalListList.add(totalList1);
List<Object> totalList = new ArrayList<>();
totalList.add(null);
// 第二列
totalList.add("统计人：" + SecurityUtils.getName());
totalList.add(null);
totalList.add(null);
totalList.add(null);
totalList.add(null);
totalList.add(null);
totalList.add("统计时间：" + DateUtil.today());
totalListList.add(totalList);
// 这里是write 别和fill 搞错了
ExcelWriter write = excelWriter.write(totalListList, writeSheet);
write.finish();
}
拿到 行列数 然后进行合并就OK了