EasyExcel生成多sheet页的excel

一、controller层

@ApiOperation(value = "明细查询导出")
@PostMapping(value = "/SummaryDetailExport")
public void summaryDetailExport(@RequestBody SearchDTO dto, HttpServletResponse response) throws IOException {reportService.deptPackagingSummaryDetailExport(dto, response);
}

二、serviceimpl

@Overridepublic void deptPackagingSummaryDetailExport(SearchDTO dto, HttpServletResponse response) throws IOException {// 这里必须指定需要头，table 会继承sheet的配置，sheet配置了不需要，table 默认也是不需要List<DeptSummaryReq> exportVos = configPackageTaskMapper.DeptPackagingSummary(dto);DecimalFormat df = new DecimalFormat("#.00");response.setContentType("application/vnd.ms-excel;charset=utf-8");response.setCharacterEncoding("utf-8");response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("科室打包明细查询" + ".xlsx", "UTF-8"));ServletOutputStream outputStream = response.getOutputStream();ExcelWriter excelWriter = EasyExcel.write(outputStream).build();//在根据科室id去查询有哪些包，以及价格for (int i = 0; i < exportVos.size(); i++) {dto.setDeptId(exportVos.get(i).getDeptId());List<DeptSummaryReq> detail = configPackageTaskMapper.queryDeptPackagingDetailBydeptId(dto);for (DeptSummaryReq regDetail : detail) {if (regDetail.getPrice() == null) {regDetail.setAmount(0.00);regDetail.setPrice(0.00);} else {regDetail.setAmount(BigDecimal.valueOf(regDetail.getPrice()).multiply(BigDecimal.valueOf(regDetail.getPackageCount())).doubleValue());}}DeptSummaryReq regDetail1 = new DeptSummaryReq();regDetail1.setPackageCode(dto.getStartTime() + "    " + dto.getEndTime() + "    打印时间：" + DateUtil.now());DeptSummaryReq regDetail2 = new DeptSummaryReq();if (dto.getPackageType() == null) {List<String> typeName = recoverPackagesMapper.queryPackageType();regDetail2.setPackageCode("包类型：" + String.join("，", typeName));} else {regDetail2.setPackageCode("包类型：" + String.join("，", dto.getPackageName()));}DeptSummaryReq regDetail = new DeptSummaryReq();regDetail.setPackageCode("科室数量合计:    " + detail.stream().mapToInt(DeptSummaryReq::getPackageCount).sum()+ "       科室金额合计:   ¥" + Double.valueOf(df.format(detail.stream().mapToDouble(DeptSummaryReq::getAmount).sum())));detail.add(regDetail);detail.add(regDetail1);detail.add(regDetail2);//创建合并策略//这里我们上面手动给我们的list添加了3条数据//这3天数据是需要合并单元格显示的，所以我们这里需要new 3个OnceAbsoluteMergeStrategy// 前面2个数字代表的是行，后面两个代表从那一列开始，到那一列结束//比如我们要展示五个字段，那么就是0,4；从0开始到4就一共5列OnceAbsoluteMergeStrategy onceAbsoluteMergeStrategy = new OnceAbsoluteMergeStrategy(detail.size() + 1, detail.size() + 1, 0, 4);OnceAbsoluteMergeStrategy onceAbsoluteMergeStrategy1 = new OnceAbsoluteMergeStrategy(detail.size(), detail.size(), 0, 4);OnceAbsoluteMergeStrategy onceAbsoluteMergeStrategy2 = new OnceAbsoluteMergeStrategy(detail.size() - 1, detail.size() - 1, 0, 4);//创建一个sheetWriteSheet writeSheet = EasyExcel.writerSheet(i, exportVos.get(i).getDeptName()).head(DeptPackagingSummaryDetailExcel.class)//.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).registerWriteHandler(ExcelUtil.writeCenterStyle()).registerWriteHandler(onceAbsoluteMergeStrategy).registerWriteHandler(onceAbsoluteMergeStrategy1).registerWriteHandler(onceAbsoluteMergeStrategy2).build();//将sheet写入excelwriterexcelWriter.write(detail, writeSheet);}if (excelWriter != null) {excelWriter.finish();}}

三、entity

DeptSummaryReq

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentStyle;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import com.alibaba.excel.annotation.write.style.HeadStyle;
import com.alibaba.excel.enums.poi.HorizontalAlignmentEnum;
import com.cloud.common.core.annotation.ExcelParam;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.*;import java.math.BigDecimal;
import java.util.Date;
import java.util.List;@Data
@ColumnWidth(25)//设置列宽
@HeadRowHeight(20)//设置行高
@EqualsAndHashCode
@HeadStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER)//表头样式
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER)//内容样式
@ApiModel(value="DeptSummaryReq", description="导出出参")
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class DeptSummaryReq {private static final long serialVersionUID=1L;@ApiModelProperty(value = "包编码")@ExcelProperty({"明细报表", "包编码"})private String packageCode;@ApiModelProperty(value = "包名称")@ExcelProperty({"明细报表", "包名称"})private String packageName;@ApiModelProperty(name = "数量")@ExcelProperty({"明细报表", "数量"})private int packageCount;@ApiModelProperty(name = "单价")@ExcelProperty({"明细报表", "单价"})private Double price;@ApiModelProperty(name = "金额")@ExcelProperty({"明细报表", "金额"})private Double  amount;@ApiModelProperty(name = "科室id")@ExcelIgnoreprivate Integer deptId;@ApiModelProperty(name = "科室名称")@ExcelIgnoreprivate String deptName;}

四、效果