EasyExcel导出多列相同合并处理以及单列相同合并处理
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.1.1</version>
</dependency>
其次,基本思路就是,通过继承CellWriteHandler接口编写合并策略(主要就是实现该方法afterCellDispose,该方法在对单元格的所有操作完成后调用),再构建表格处理对象ExcelWriter时,将所需合并策略传入即可。
一、多列相同合并
1、需求效果图
2、控制层
package com.hango.project.formulation.controller;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.hango.common.core.domain.R;
import com.hango.common.core.web.controller.BaseController;
import com.hango.common.excel.core.ExcelResult;
import com.hango.common.excel.utils.ExcelUtil;
import com.hango.common.log.annotation.Log;
import com.hango.common.log.enums.BusinessType;
import com.hango.project.formulation.domain.ZhgkProjectMonthPlanTaskEntity;
import com.hango.project.formulation.domain.bo.ZhgkProjectMonthPlanTaskBo;
import com.hango.project.formulation.domain.bo.ZhgkProjectYearPlanBo;
import com.hango.project.formulation.domain.vo.ZhgkProjectDayPlanTemplateVo;
import com.hango.project.formulation.domain.vo.ZhgkProjectDayPlanExportVo;
import com.hango.project.formulation.domain.vo.ZhgkProjectMonthPlanTaskVo;
import com.hango.project.formulation.listener.DayPlanExcelListener;
import com.hango.project.formulation.listener.ExcelMergeCustomerCellHandler;
import com.hango.project.formulation.listener.MonthPlanExcelListener;
import com.hango.project.formulation.service.ZhgkProjectMonthPlanTaskService;
import lombok.RequiredArgsConstructor;
import org.springframework.validation.annotation.Validated;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import javax.annotation.Resource;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.time.YearMonth;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
@Validated
@RequiredArgsConstructor
@RestController
@RequestMapping("/zhgkProjectMonthPlanTask")
public class ZhgkProjectMonthPlanTaskController extends BaseController {
@Resource
private ZhgkProjectMonthPlanTaskService zhgkProjectMonthPlanTaskService;
/**
* 下载月计划模板
*/
// @SaCheckPermission("zhgkProjectMonthPlanTask:monthPlanExport")
@Log(title = "下载月计划模板", businessType = BusinessType.EXPORT)
@PostMapping("/monthPlanTemplateExport")
public void monthPlanTemplateExport(@Validated @RequestBody ZhgkProjectYearPlanBo bo, HttpServletResponse response) throws IOException {
ServletOutputStream outputStream = response.getOutputStream();
// 根据条件在数据库查询数据
List<ZhgkProjectDayPlanExportVo> list = zhgkProjectMonthPlanTaskService.monthPlanTemplateExport(bo);
try {
this.setExcelResponseProp(response, "月计划模板");
EasyExcel.write(outputStream, ZhgkProjectDayPlanExportVo.class)
.excelType(ExcelTypeEnum.XLSX)
.sheet("月计划模板")
.registerWriteHandler(new ExcelMergeCustomerCellHandler(new int[]{0}, 0))
.doWrite(list);
} catch (IOException e) {
throw new RuntimeException(e);
} finally {
outputStream.flush();
outputStream.close();
}
// ExcelUtil.exportExcel(list, "月计划模板", ZhgkProjectDayPlanExportVo.class, response);
}
/**
* 设置响应结果
*
* @param response 响应结果对象
* @param rawFileName 文件名
* @throws UnsupportedEncodingException 不支持编码异常
*/
private void setExcelResponseProp(HttpServletResponse response, String rawFileName) throws UnsupportedEncodingException {
//设置内容类型
response.setContentType("application/vnd.vnd.ms-excel");
//设置编码格式
response.setCharacterEncoding("utf-8");
//设置导出文件名称(避免乱码)
String fileName = URLEncoder.encode(rawFileName.concat(".xlsx"), "UTF-8");
// 设置响应头
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName);
}
}
4、自定义导出类模板
package com.hango.project.formulation.domain.vo;
import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentLoopMerge;
import com.alibaba.excel.annotation.write.style.ContentStyle;
import com.alibaba.excel.enums.BooleanEnum;
import com.alibaba.excel.enums.poi.BorderStyleEnum;
import com.alibaba.excel.enums.poi.HorizontalAlignmentEnum;
import com.alibaba.excel.enums.poi.VerticalAlignmentEnum;
import lombok.Data;
import java.io.Serializable;
import java.math.BigDecimal;
//项目月计划任务分解表
@ExcelIgnoreUnannotated
@Data
public class ZhgkProjectDayPlanExportVo implements Serializable {
/**
* 月份
*/
@ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
@ExcelProperty(value = "月份")
protected String month;
/**
* 主要任务名称
*/
@ColumnWidth(value = 18)
@ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
@ExcelProperty(value = "主要任务名称")
protected String mainTaskName;
/**
* 主要工作量(月计划)
*/
@ColumnWidth(value = 15)
@ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
@ExcelProperty(value = "主要工作量")
protected BigDecimal mainWorkload;
}
6、自定义多列相同合并策略处理类
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import java.util.List;
/**
* @ClassName:ExcelMergeCustomerCellHandler
* @Description:easyexcel合并策略处理类,多列相同合并策略
* @Version:1.0
* @Date:2023/9/7 09:36:52
*/
@Data
@NoArgsConstructor
@AllArgsConstructor
@Slf4j
public class ExcelCellByMultiHandler implements CellWriteHandler {
/**
* 用第几行开始合并 ,默认为1,因为第0行是标题,EasyExcel 的默认也是
*/
private int mergeRowIndex = 1;
/**
* 参与合并的列,起始列位置,默认为0
*/
private int mergeColumnStart = 0;
/**
* 参与合并的列,最后一列的位置,默认为4
*/
private int mergeColumnEnd = 4;
/**
* 默认初始合并区域大小,标头有合并的,则需要指定标头合并后的合并区域大小,没有的话就是0
*/
private int defaultMergeRegionSize = 0;
public ExcelCellByMultiHandler(int defaultMergeRegionSize) {
this.defaultMergeRegionSize = defaultMergeRegionSize;
}
@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();
if (curRowIndex > mergeRowIndex && curColIndex >= mergeColumnStart) {
// 读取到mergeColumnEnd列,开始合并
if (curColIndex == mergeColumnEnd) {
mergeWithPrevRow(writeSheetHolder, cell, curRowIndex);
}
}
}
/**
* @Description: 合并比较多列值是否相同
* @Date: 2023/9/8 16:31
* @Param writeSheetHolder: 通过此对象获取sheet等信息
* @Param cell: 当前单元格
* @Param curRowIndex: 当前行
* @return: void
**/
private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex) {
StringBuilder curString = new StringBuilder();
StringBuilder preString = new StringBuilder();
try {
for (int i = mergeColumnStart; i <= mergeColumnEnd; i++) {
Cell curCell = cell.getSheet().getRow(curRowIndex).getCell(i);
Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(i);
//根据单元格类型获取单元格的值
Object curData = curCell.getCellType() == CellType.STRING ? curCell.getStringCellValue() : curCell.getNumericCellValue();
Object preData = preCell.getCellType() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
curString.append(curData);
preString.append(preData);
}
// 比较当前行的单元格与上一行是否相同,相同合并当前单元格与上一行
Sheet sheet = writeSheetHolder.getSheet();
// 获取合并信息
List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
if (curString.toString().equals(preString.toString())) {
boolean isMerged = false;
// 下标
int index = 0;
for (int i = 0; i < mergeRegions.size(); i++) {
CellRangeAddress cellRangeAddr = mergeRegions.get(i);
// 若上一个单元格已经被合并,则先移出原有的合并单元,再重新添加合并单元
if (cellRangeAddr.isInRange(curRowIndex - 1, index)) {
//这个移除的要保证是也被合并的那个
sheet.removeMergedRegion(defaultMergeRegionSize);
cellRangeAddr.setLastRow(curRowIndex);
sheet.addMergedRegion(cellRangeAddr);
isMerged = true;
}
index++;
// 这个是为了在默认合并单元大小后,再重置index值,index表示的就是列,这样才能保证进行isInRange判断的时候能够对应上
if (index > defaultMergeRegionSize-1) {
index = 0;
}
}
if (!isMerged) {
for (int i = 0; i <= mergeColumnEnd; i++) {
CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, i, i);
sheet.addMergedRegion(cellRangeAddress);
}
}
}else { //不相等则更新默认合并区域大小
defaultMergeRegionSize = mergeRegions.size();
}
} catch (Exception e) {
log.error("仓库管理设备出入库统计合并单元格当前str==========" + curString, e);
log.error("仓库管理设备出入库统计合并单元格上一次str==========" + preString, e);
}
}
}
同多列合并一样,要保证excelList集合结果中仓库名称相同的连在一起,即 需要合并的列对应的字段相同的记录需要在一起。
自定义单列相同合并策略
package vip.dtcloud.common;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import java.util.List;
/**
* @ClassName:ExcelMergeCustomerCellHandler
* @Description:easyexcel合并策略处理类,单列相同合并策略
* @Version:1.0
* @Date:2023/9/7 09:36:52
*/
@Data
@NoArgsConstructor
@AllArgsConstructor
public class ExcelMergeCustomerCellHandler implements CellWriteHandler {
/**
* 一级合并的列,从0开始算
*/
private int[] mergeColIndex;
/**
* 从指定的行开始合并,从0开始算
*/
private int mergeRowIndex;
/**
* 在单元格上的所有操作完成后调用,遍历每一个单元格,判断是否需要向上合并
*/
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
// 获取当前单元格行下标
int currRowIndex = cell.getRowIndex();
// 获取当前单元格列下标
int currColIndex = cell.getColumnIndex();
// 判断是否大于指定行下标,如果大于则判断列是否也在指定的需要的合并单元列集合中
if (currRowIndex > mergeRowIndex) {
for (int i = 0; i < mergeColIndex.length; i++) {
if (currColIndex == mergeColIndex[i]) {
if (currColIndex <= 18) {
// 一级合并唯一标识
Object currLevelOneCode = cell.getRow().getCell(0).getStringCellValue();
Object preLevelOneCode = cell.getSheet().getRow(currRowIndex - 1).getCell(0).getStringCellValue();
// 判断两条数据的是否是同一集合,只有同一集合的数据才能合并单元格
if (preLevelOneCode.equals(currLevelOneCode)) {
// 如果都符合条件,则向上合并单元格
mergeWithPrevRow(writeSheetHolder, cell, currRowIndex, currColIndex);
break;
}
} else {
// 一级合并唯一标识
Object currLevelOneCode = cell.getRow().getCell(0).getStringCellValue();
Object preLevelOneCode = cell.getSheet().getRow(currRowIndex - 1).getCell(0).getStringCellValue();
// 二级合并唯一标识
Object currLevelTwoCode = cell.getRow().getCell(19).getStringCellValue();
Object preLevelTwoCode = cell.getSheet().getRow(currRowIndex - 1).getCell(19).getStringCellValue();
if (preLevelOneCode.equals(currLevelOneCode) && preLevelTwoCode.equals(currLevelTwoCode)) {
// 如果都符合条件,则向上合并单元格
mergeWithPrevRow(writeSheetHolder, cell, currRowIndex, currColIndex);
break;
}
}
}
}
}
}
/**
* 当前单元格向上合并
*
* @param writeSheetHolder 表格处理句柄
* @param cell 当前单元格
* @param currRowIndex 当前行
* @param currColIndex 当前列
*/
private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int currRowIndex, int currColIndex) {
// 获取当前单元格数值
Object currData = cell.getCellType() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
// 获取当前单元格正上方的单元格对象
Cell preCell = cell.getSheet().getRow(currRowIndex - 1).getCell(currColIndex);
// 获取当前单元格正上方的单元格的数值
Object preData = preCell.getCellType() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
// 将当前单元格数值与其正上方单元格的数值比较
if (preData.equals(currData)) {
Sheet sheet = writeSheetHolder.getSheet();
List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
// 当前单元格的正上方单元格是否是已合并单元格
boolean isMerged = false;
for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
CellRangeAddress address = mergeRegions.get(i);
// 若上一个单元格已经被合并,则先移出原有的合并单元,再重新添加合并单元
if (address.isInRange(currRowIndex - 1, currColIndex)) {
sheet.removeMergedRegion(i);
address.setLastRow(currRowIndex);
sheet.addMergedRegion(address);
isMerged = true;
}
}
// 若上一个单元格未被合并,则新增合并单元
if (!isMerged) {
CellRangeAddress cellRangeAddress = new CellRangeAddress(currRowIndex - 1, currRowIndex, currColIndex, currColIndex);
sheet.addMergedRegion(cellRangeAddress);
}
}
}
}