# 一 需求：
有一个需求是根据不同维度去查询数据然后汇总，最后一行数据为合计数据，并且总计那行要合并单元格
# 二 思路
因为有7个维度，不想去写7个查询，然后分析之后发现只有汇总条件是可变的，其它数据一样
然后创建一个视图 在那个视图里汇总数据，后续就算改变字段啥的也方便
以下是**部分关键代码**
```java
String nowDate = DateUtil.format(new Date(), "yyyy-MM-dd");
String paramDate = DateUtil.format(vo.getDate(), "yyyy-MM-dd");
int date = DateUtil.compare(DateUtil.parseDate(nowDate), DateUtil.parseDate(paramDate));
String sql=null;
if (vo.getStatisticDim().equals(DimensionEnum.UNITDIM.name())){
//voList=baseMapper.queryStatisticByDim(vo);
sql="t.construct_unit";
}else if (vo.getStatisticDim().equals(DimensionEnum.PROMANAGEDIM.name())){
vo.setConstructUnit(null);
sql="t.discipline";
//voList = baseMapper.queryByDimAndDiscipline(vo);
}else if (vo.getStatisticDim().equals(DimensionEnum.UNITPROMANAGERDIM.name())){
sql="t.construct_unit,t.project_implementation_manager_primary";
//voList = baseMapper.queryByUNITPROMANAGERDIM(vo);
}else if (vo.getStatisticDim().equals(DimensionEnum.UNITCITYCONSTRUCTDIM.name())){
sql="t.construct_unit,t.district,t.construction_unit";
}else if (vo.getStatisticDim().equals(DimensionEnum.PROVINCEPROMANAGERDIM.name())){
vo.setConstructUnit(null);
sql="t.project_management_manager_primary";
}else if (vo.getStatisticDim().equals(DimensionEnum.UNITCONSTRUCTDIM.name())){
sql="t.construct_unit,t.construction_unit";
}else if (vo.getStatisticDim().equals(DimensionEnum.UNITPROCONSTRUCTDIM.name())){
sql="t.construct_unit,t.construction_unit,t.project_name";
}
if (date<0){
// // TODO: 2024/3/23 查历史数据
}else {
voList = baseMapper.queryByDimAndUnit(vo,sql);
}
```
**xml**
```java
select rownum as index ,a.* from (
select
${sql},
sum(case when t.completion_report_completion_time is not null then 1 else 0 end) as videoFinishScale,
sum(case when t.completion_report_completion_time is not null and sysdate-t.completion_report_completion_time
<![CDATA[ <= ]]> 10 and (t.acceptance_opinion <![CDATA[<>]]> '通过' or t.acceptance_opinion is null) then 1 else 0
end) as videoTenunfinishScale,
sum(case when t.completion_report_completion_time is not null and sysdate-t.completion_report_completion_time
<![CDATA[ <= ]]> 20 and (t.acceptance_opinion <![CDATA[<>]]> '通过' or t.acceptance_opinion is null) then 1 else 0
end) as videoTwnunfinishScale,
sum(case when t.completion_report_completion_time is not null and sysdate-t.completion_report_completion_time
<![CDATA[ <= ]]> 30 and (t.acceptance_opinion <![CDATA[<>]]> '通过' or t.acceptance_opinion is null) then 1 else 0
end) as videoThiunfinishScale,
sum(case when t.completion_report_completion_time is not null and sysdate-t.completion_report_completion_time >
30 and (t.acceptance_opinion <![CDATA[<>]]> '通过' or t.acceptance_opinion is null) then 1 else 0 end) as
videoUnfinishScale,
(sum(case when t.completion_report_completion_time is not null and sysdate-t.completion_report_completion_time >
30 and (t.acceptance_opinion <![CDATA[<>]]> '通过' or t.acceptance_opinion is null) then 1 else 0 end) /
decode(sum(case when t.completion_report_completion_time is not null then 1 else 0 end),0,1,sum(case when
t.completion_report_completion_time is not null then 1 else 0 end))) * 100 as videoUnfinishRate,
sum(case when t.completion_report_completion_time is not null and sysdate-t.completion_report_completion_time >
30 and t.acceptance_opinion = '通过' then 1 else 0 end) as videoUnfinishCount,
sum(case when t.completion_report_completion_time is not null and t.acceptance_opinion = '通过' then 1 else 0 end)
as videofinishCount,
(sum(case when t.completion_report_completion_time is not null and t.acceptance_opinion = '通过' then 1 else 0
end) / decode(sum(case when t.completion_report_completion_time is not null then 1 else 0 end),0,1,sum(case when
t.completion_report_completion_time is not null then 1 else 0 end))) * 100 as videoRate,
sum(case when t.acceptance_opinion = '通过' then 1 else 0 end) as videoCount,
sum(case when t.acceptance_opinion = '通过' and (t.acceptance_failed_times = 0 or t.acceptance_failed_times is
null) then 1 else 0 end) as videoPass
from video_statistic_view t
where 1=1<if test="vo.constructUnit != null and vo.constructUnit !='' ">and t.construct_unit in
<foreach collection="vo.constructUnit" item="unit" open="(" separator="," close=")">
#{unit}
</foreach>
</if>
<if test="vo.projectImplementationManagerPrimary != null and vo.projectImplementationManagerPrimary != ''">
and t.project_implementation_manager_primary like concat('%', #{vo.projectImplementationManagerPrimary},'%')
</if>
<if test="vo.district != null and vo.district != ''">
and t.district like concat('%', #{vo.district},'%')
</if>
<if test="vo.constructionUnit != null and vo.constructionUnit != ''">
and t.construction_unit like concat('%', #{vo.constructionUnit},'%')
</if>
<if test="vo.projectManagementManagerPrimary != null and vo.projectManagementManagerPrimary != ''">
and t.project_management_manager_primary like concat('%', #{vo.projectManagementManagerPrimary},'%')
</if>
<if test="vo.projectName != null and vo.projectName != ''">
and t.project_name like concat('%', #{vo.projectName},'%')
</if>
group by ${ sql}
) a order by index
```
# 三 easyexcel导出
## 3.1 根据维度导出
之前用过注解的方式导出，考虑到这次比较复杂一点就用模版填充的方式导出 ，刚做需求后面也很可能会改动，到时候也简单一点
```java
// 根据维度查出数据
List<VideoStatisticVO> voList1 = this.getVideoStatisticsByDim(vo, new ArrayList<>());
List<VideoStatisticVO> voList=this.getVideoStatisticCount(voList1);
String excelName=null;
int num=1;
if (vo.getStatisticDim().equals(DimensionEnum.UNITDIM.name())){
excelName="建设单位维度汇总";
}else if (vo.getStatisticDim().equals(DimensionEnum.PROMANAGEDIM.name())){
excelName="项目管理专业维度汇总";
}else if (vo.getStatisticDim().equals(DimensionEnum.UNITPROMANAGERDIM.name())){
excelName="建设单位项目经理维度汇总";
num = 2;
}else if (vo.getStatisticDim().equals(DimensionEnum.UNITCITYCONSTRUCTDIM.name())){
excelName="建设单位区县、施工单位维度汇总";
num = 3;
}else if (vo.getStatisticDim().equals(DimensionEnum.PROVINCEPROMANAGERDIM.name())){
excelName="省管项目经理汇总";
}else if (vo.getStatisticDim().equals(DimensionEnum.UNITCONSTRUCTDIM.name())){
excelName="建设单位、施工单位汇总";
num = 2;
}else if (vo.getStatisticDim().equals(DimensionEnum.UNITPROCONSTRUCTDIM.name())){
excelName="建设单位、项目、施工单位汇总";
num = 3;
}
try {
ClassPathResource classPathResource = null;
classPathResource = new ClassPathResource("/template/"+excelName+".xlsx");
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
String fileName = URLEncoder
.encode(excelName + "-" + DateUtil.format(DateUtil.date(), DatePattern.PURE_DATE_PATTERN), "UTF-8")
.replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
Map<String, Object> templateParamMap = new HashMap<>();
LpsRemarkDicItem dicItem = new LpsRemarkDicItem();
dicItem.setCode(vo.getStatisticDim());
List<LpsRemarkDicItem> dicItems = dicItemService.selectListByCode(dicItem);
StringBuilder builder = new StringBuilder();
if (dicItems !=null && dicItems.size()>0){
for (int i = 0; i < dicItems.size(); i++) {
builder.append(i+1).append(". ").append(dicItems.get(i).getDetail()).append("\r\n");
}
}
ExcelFillCellMergePrevColUtils cellMergePrevColUtils = new ExcelFillCellMergePrevColUtils();
// 合并总计行
cellMergePrevColUtils.add(voList.size()+2 ,0,num);
// 创建ExcelWriterBuilder
ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(response.getOutputStream())
.withTemplate(classPathResource.getInputStream());
ExcelWriter excelWriter = excelWriterBuilder.autoCloseStream(Boolean.FALSE)
// 列合并
.registerWriteHandler(cellMergePrevColUtils).build();
WriteSheet writeSheet1 = EasyExcel.writerSheet().build();
/**
* 模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"\{","\}"代替
* {} 代表普通变量 {.} 代表是list的变量
* WriteSheet writeSheet1 = EasyExcel.writerSheet(sheetName).build();容易报空指针异常
* 获取不到数据，可以选择不写或者升级版本3.0.1以上
*/
// 填写配置，forceNewRow true表示自动创建一行，后面的数据后移
FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
excelWriter.fill(voList,fillConfig,writeSheet1);
templateParamMap.put("title","QIP视频验收推进进展（"+excelName+"）");
templateParamMap.put("remark",builder.toString());
//填写数据
excelWriter.fill(templateParamMap,writeSheet1);
// 关闭填写
excelWriter.finish();
}catch (Exception e){}
```
## 3.2 不同数据导入到不同sheet中
这里也是采用模版导入，这里也犯了很多错误，下面会统一总结
```java
InputStream templateStream = classPathResource.getInputStream();
// 1 设置响应格式
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
String fileName = URLEncoder
.encode("视频验收汇总清单" + "-" + DateUtil.format(DateUtil.date(), DatePattern.PURE_DATE_PATTERN), "UTF-8")
.replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
// 2 创建一个XSSFWorkbook对象
XSSFWorkbook workbook = new XSSFWorkbook(templateStream);
//写到流里
ByteArrayOutputStream bos1 = new ByteArrayOutputStream();
workbook.write(bos1);
byte[] bArray1 = bos1.toByteArray();
InputStream is1 = new ByteArrayInputStream(bArray1);
ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream())
.withTemplate(is1).build();
FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
// 循环处理每个sheet页
for (int i = 0; i < workbook.getNumberOfSheets()-2; i++) {
XSSFSheet sheet = workbook.getSheetAt(i);
String sheetName = sheet.getSheetName();
String byExcelName = DimensionEnum.getNameByStatus(sheetName);
vo.setStatisticDim(byExcelName);
// 查询数据
// 处理数据填充等操作
List<VideoStatisticVO> voList1 = this.getVideoStatisticsByDim(vo, new ArrayList<>());
List<VideoStatisticVO> voList = this.getVideoStatisticCount(voList1);
Map<String, Object> templateParamMap = new HashMap<>();
LpsRemarkDicItem dicItem = new LpsRemarkDicItem();
dicItem.setCode(vo.getStatisticDim());
List<LpsRemarkDicItem> dicItems = dicItemService.selectListByCode(dicItem);
StringBuilder builder = new StringBuilder();
if (dicItems !=null && dicItems.size()>0){
for (int k = 0; k < dicItems.size(); k++) {
builder.append(k+1).append(". ").append(dicItems.get(k).getDetail()).append("\r\n");
}
}
ExcelFillCellMergePrevColUtils cellMergePrevColUtils = new ExcelFillCellMergePrevColUtils();
// 合并总计行
int num =1;
if (byExcelName.equals(DimensionEnum.UNITPROMANAGERDIM.name()) || byExcelName.equals(DimensionEnum.UNITCONSTRUCTDIM.name()) ){
num = 2 ;
}else if(byExcelName.equals(DimensionEnum.UNITCITYCONSTRUCTDIM.name()) || byExcelName.equals(DimensionEnum.UNITPROCONSTRUCTDIM.name())) {
num = 3 ;
}
cellMergePrevColUtils.add(voList.size()+2 ,0,num);
// 创建基于模板的WriteSheet对象
WriteSheet writeSheet = EasyExcel.writerSheet(i).registerWriteHandler(cellMergePrevColUtils).build();
// 填充数据到指定sheet页
templateParamMap.put("title","QIP视频验收推进进展（"+sheetName+"）");
templateParamMap.put("remark",builder.toString());
//填写数据
excelWriter.fill(voList, fillConfig, writeSheet);
excelWriter.fill(templateParamMap,writeSheet);
}
// 处理第8 ，9sheet页数据
WriteSheet writeSheet8= EasyExcel.writerSheet(workbook.getSheetName(7)).build();
WriteSheet writeSheet9= EasyExcel.writerSheet(workbook.getSheetName(8)).build();
List<VideoStatisticDTO> sheet8List = this.selectData(vo);
excelWriter.fill(sheet8List,fillConfig,writeSheet8);
excelWriter.fill(sheet8List,fillConfig,writeSheet9);
//关闭流
excelWriter.finish();
bos1.close();
is1.close();
```
然后是列合并的工具类
```java
//列合并工具类
public class ExcelFillCellMergePrevColUtils implements CellWriteHandler {
private static final String KEY ="%s-%s";
//所有的合并信息都存在了这个map里面
Map<String, Integer> mergeInfo = new HashMap<>();
public ExcelFillCellMergePrevColUtils() {
}
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {
}
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) {
}
@Override
public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {
}
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
//当前行
int curRowIndex = cell.getRowIndex();
//当前列
int curColIndex = cell.getColumnIndex();
Integer num = mergeInfo.get(String.format(KEY, curRowIndex, curColIndex));
if(null != num){
// 合并最后一行 ,列
mergeWithPrevCol(writeSheetHolder, cell, curRowIndex, curColIndex,num);
}
}
public void mergeWithPrevCol(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex, int num) {
Sheet sheet = writeSheetHolder.getSheet();
CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex, curRowIndex, curColIndex, curColIndex + num);
sheet.addMergedRegion(cellRangeAddress);
}
//num从第几列开始增加多少列,(6,2,7)代表的意思就是第6行的第2列至第2+7也就是9列开始合并
public void add (int curRowIndex, int curColIndex , int num){
mergeInfo.put(String.format(KEY, curRowIndex, curColIndex),num);
}
}
```
# 四 总结错误
这里遇到了几个问题，总结一下
1 以流的形式输出文件，controller层返回格式为void
2 WriteSheet writeSheet1 = EasyExcel.writerSheet(sheetName).build();以这样方式写时报错，报dofill方法哪里空指针异常 没找到问题，可能是当时idea缓存，也有可能是版本低于3.0.0
3 分多个sheet页导出时忘记绑定模版报fill方法无法绑定模版
```java
ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream())
.withTemplate(is1).build();
.withTemplate(is1)忘记写了
```
4 多个sheet页数据无法一次性导出，这里是先转成流最后一起输出
# 五 反思
在模版填充这里其实还可以动态表头模版导出数据的，这样就不用创建那么多模版了，后续需要优化，然后代码写法问题