EasyExce复杂场景应用（动态合并标题,填充导出联合使用）
文章目录提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档
- 前言
- 一、EasyExcel是什么？
- 二、使用步骤
- 1.引入库
- 2.组装填充数据
- 3.填充数据
- 4.拦截器（实现动态合并重点）
- 5.组织导出数据
- 6.导出效果数据
- 总结
前言
提示：这里可以添加本文要记录的大概内容：
提示：以下是本篇文章正文内容，下面案例可供参考
一、EasyExcel是什么？EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel。
github地址: https://github.com/alibaba/easyexcel
官方文档: https://www.yuque.com/easyexcel/doc/easyexcel
B站视频: https://www.bilibili.com/video/BV1Ff4y1U7Qc
二、使用步骤 1.引入库代码如下（示例）：
2.组装填充数据com.alibaba easyexcel ${easyexcel.version}
代码如下（示例）：
ListevaluateOpinionVoList = empEvaluateDetailBizService .getEvaluateCapacityVoList(evaluateInstancePo, queryDto, t.getTargetUserId()); if (CollectionUtils.isNotEmpty(evaluateOpinionVoList)) { String finalName = evaluateOpinionVoList.stream().map(EvaluateOpinionVo::getEvaluateUserName) .collect(Collectors.joining("n")); String finalRoleName = t.getEmpRoleName() + "n"; String finalOrgName = t.getEmpOrgName() + "n"; paramMap.put("finalName", finalName); paramMap.put("finalId", Joiner.on("n") .join(evaluateOpinionVoList.stream().map(EvaluateOpinionVo::getEvaluateUserId) .collect(Collectors.toList()))); paramMap.put("finalOrgName", finalOrgName); paramMap.put("finalRoleName", finalRoleName); } paramMap.put("title", evaluateInstancePo.getEvaluateName()); paramMap.put("targetName", t.getTargetUserName()); paramMap.put("targetId", String.valueOf(t.getTargetUserId())); if (ExamineEnum.AbilityModeCode.LEAD.getCode().equals(templateInstancePo.getAbilityMode())) { paramMap.put("targetOrgName", t.getOrgName()); paramMap.put("targetRoleName", t.getRoleName()); } else { paramMap.put("targetOrgName", t.getEmpOrgName()); paramMap.put("targetRoleName", t.getEmpRoleName()); }
该处模拟数据查找的数据，不用纠结代码。
3.填充数据代码如下（示例）：
WriteSheet sheet = EasyExcel
.writerSheet(t.getEvaluateInstanceId() + t.getTargetUserName() + t.getTargetUserId())
.registerWriteHandler(
new ExcelFillCellMergeStrategy(mergeColumnIndex, mergeRowIndex, standardColumnIndex))
.build();
我这边业务场景比较复杂。填充多个模板就预先定义了多个空白目标，sheet名称按规则命名
ByteArrayOutputStream bos = new ByteArrayOutputStream();
//原模板只有一个sheet，通过poi复制出需要的sheet个数的模板
XSSFWorkbook workbook = new XSSFWorkbook(in);
if (CollectionUtils.isNotEmpty(sheetName)) {
// workbook.setSheetName(0, sheetName.get(0));
//复制空白sheet
for (int j = 0; j < sheetName.size(); j++) {
workbook.createSheet(sheetName.get(j));
}
}
if (CollectionUtils.isNotEmpty(targetPos)) {
//设置模板的第一个sheet的名称
workbook.setSheetName(0, "sheet1");
for (int i = 0; i < targetPos.size() - 1; i++) {
//复制模板，得到第i个sheet
workbook.cloneSheet(0,
targetPos.get(i).getEvaluateInstanceId() + targetPos.get(i).getTargetUserName() + targetPos
.get(i).getTargetUserId());
}
//克隆sheet小于1就不用操作
if (targetPos.size() > 1) {
workbook.removeSheetAt(0);
workbook.cloneSheet(sheetName.size(),
targetPos.get(targetPos.size() - 1).getEvaluateInstanceId() + targetPos
.get(targetPos.size() - 1).getTargetUserName() + targetPos.get(targetPos.size() - 1)
.getTargetUserId());
}
if (targetPos.size() == 1) {
workbook.cloneSheet(0, targetPos.get(targetPos.size() - 1).getEvaluateInstanceId() + targetPos
.get(targetPos.size() - 1).getTargetUserName() + targetPos.get(targetPos.size() - 1)
.getTargetUserId());
workbook.removeSheetAt(0);
}
}
填充多个模板需要预先定义sheet不然会报错哦
4.拦截器（实现动态合并重点）代码如下（示例）：
public class ExcelFillCellMergeStrategy implements CellWriteHandler {
private int[] mergeColumnIndex;
private int mergeRowIndex;
private int standardColumnIndex;
private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);
Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
// 获取上一行第0列单元格和本行第0列单元格
Cell preRowFirstCell = cell.getSheet().getRow(curRowIndex - 1).getCell(standardColumnIndex);
Object preRowFirstData = preRowFirstCell.getCellTypeEnum() == CellType.STRING ? preRowFirstCell.getStringCellValue() : preRowFirstCell.getNumericCellValue();
Cell curRowFirstCell = cell.getSheet().getRow(curRowIndex).getCell(standardColumnIndex);
Object curRowFirstData = curRowFirstCell.getCellTypeEnum() == CellType.STRING ? curRowFirstCell.getStringCellValue() : curRowFirstCell.getNumericCellValue();
// 当前列不为标杆列，根据标杆列上下两个单元格值是否一致来合并
// 当前列为标杆列并且上下两个单元格一致则合并
if ((curColIndex == standardColumnIndex && preData.equals(curData)) || (curColIndex != standardColumnIndex && preRowFirstData.equals(curRowFirstData) && preData.equals(curData)) && preData != "") {
Sheet sheet = writeSheetHolder.getSheet();
List mergeRegions = sheet.getMergedRegions();
boolean isMerged = false;
for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
CellRangeAddress cellRangeAddr = (CellRangeAddress) mergeRegions.get(i);
// 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元
if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {
sheet.removeMergedRegion(i);
cellRangeAddr.setLastRow(curRowIndex);
sheet.addMergedRegion(cellRangeAddr);
isMerged = true;
}
}
// 若上一个单元格未被合并，则新增合并单元
if (!isMerged) {
CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex);
sheet.addMergedRegion(cellRangeAddress);
}
}
}
public ExcelFillCellMergeStrategy() {
}
public ExcelFillCellMergeStrategy(int[] mergeColumnIndex, int mergeRowIndex, int standardColumnIndex) {
this.mergeColumnIndex = mergeColumnIndex;
this.mergeRowIndex = mergeRowIndex;
this.standardColumnIndex = standardColumnIndex;
}
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
}
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
}
@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 list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
//当前行
int curRowIndex = cell.getRowIndex();
//当前列
int curColIndex = cell.getColumnIndex();
Sheet sheet = writeSheetHolder.getSheet();
Workbook workbook = sheet.getWorkbook();
// xlsx格式，如果是老版本格式的话就用 HSSFRichTextString
XSSFRichTextString richString = new XSSFRichTextString(cell.getStringCellValue());
if (!StringUtils.isBlank(String.valueOf(richString))) {
Font font = workbook.createFont();
if (cell.getStringCellValue().equals("负责人")) {
font.setBold(true);
font.setFontHeightInPoints((short) 10);
richString.applyFont(font);
// 再设置回每个单元格里
cell.setCellValue(richString);
//遍历循环前列同行的数据设置样式
for (int i = 1; i < curColIndex; i++) {
Cell preCell = cell.getSheet().getRow(curRowIndex).getCell(curColIndex - i);
XSSFRichTextString str = new XSSFRichTextString(preCell.getStringCellValue());
if (StringUtils.isBlank(String.valueOf(str))) {
continue;
}
str.applyFont(font);
// 再设置回每个单元格里
preCell.setCellValue(str);
}
} else if (cell.getStringCellValue().equals("参与人") || cell.getStringCellValue().equals("未参与")) {
font.setColor(IndexedColors.GREY_50_PERCENT.getIndex());
richString.applyFont(font);
// 再设置回每个单元格里
cell.setCellValue(richString);
//遍历循环前列同行的数据设置样式
for (int j = 1; j < curColIndex; j++) {
Cell preCell = cell.getSheet().getRow(curRowIndex).getCell(curColIndex - j);
XSSFRichTextString str = new XSSFRichTextString(preCell.getStringCellValue());
if (StringUtils.isBlank(String.valueOf(str))) {
continue;
}
str.applyFont(font);
// 再设置回每个单元格里
preCell.setCellValue(str);
}
}
}
if (mergeColumnIndex == null) {
return;
}
if (curRowIndex > mergeRowIndex) {
for (int i = 0; i < mergeColumnIndex.length; i++) {
if (curColIndex == mergeColumnIndex[i]) {
mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);
break;
}
}
}
}
}
就是导出数据前对规则进行一些拦截
5.组织导出数据代码如下（示例）：
ArrayListvos = new ArrayList<>(); //从团队名称开始组装数据 targetDetailSelfList.forEach(s -> { List carrerEvadata = s.getCarrerEvadata(); if (CollectionUtils.isEmpty(carrerEvadata)) { carrerEvadata.addAll(careerTeamValueVos); } for (int i = 0; i < carrerEvadata.size(); i++) { //事项名称 CarrerEvadataVo carrerEvadataVo1 = new CarrerEvadataVo(); carrerEvadataVo1.setName(carrerEvadata.get(i).getTeamName()); carrerEvadataVo1.setAbilt(s.getDimensionName()); carrerEvadataVo1.setSelfEvaResult(s.getSelfEvaluateResult()); carrerEvadataVo1.setSelfEvaReason(s.getSelfEvaluateComment()); if (CollectionUtils.isNotEmpty(evaluateOpinionVoList)) { StringBuffer finalEvaResult = new StringBuffer(); StringBuffer finalEvaReason = new StringBuffer(); evaluateOpinionVoList.forEach(e -> { for (int d = 0; d < e.getResultDetailPoList().size(); d++) { if (s.getExamineScoreRuleDimensionInstanceId() .equals(e.getResultDetailPoList().get(d) .getExamineScoreRuleDimensionInstanceId())) { if (evaluateOpinionVoList.size() > 1) { finalEvaResult .append(e.getEvaluateUserName() + ": " + e.getResultDetailPoList() .get(d).getEvaluateGrade() + "n"); finalEvaReason .append(e.getEvaluateUserName() + ": " + e.getResultDetailPoList() .get(d).getDimensionOpinion() + "n"); } else { finalEvaResult .append(e.getResultDetailPoList().get(d).getEvaluateGrade() + "n"); finalEvaReason.append(e.getResultDetailPoList().get(d).getDimensionOpinion() + "n"); } carrerEvadataVo1.setFinalEvaResult(finalEvaResult.toString()); carrerEvadataVo1.setFinalEvaReason(finalEvaReason.toString()); break; } } }); } vos.add(carrerEvadataVo1); ArrayList newList = new ArrayList<>(); Integer count = 0; initData(carrerEvadata.get(i).getTeamValueTaskList(), newList, count); //Collections.reverse(newList); for (int j = 0; j < newList.size(); j++) { CarrerEvadataVo carrerEvadataVo3 = new CarrerEvadataVo(); if (newList.get(j).getLevel() == 1) { carrerEvadataVo3.setFirstTaskName(newList.get(j).getTaskName()); } else if (newList.get(j).getLevel() == 2) { carrerEvadataVo3.setSecondTaskName(newList.get(j).getTaskName()); } else { carrerEvadataVo3.setThirdTaskName(newList.get(j).getTaskName()); } carrerEvadataVo3.setType(Joiner.on(",") .join(newList.get(j).getValueTypeList().stream().map(ValueTypeVo::getTagName) .collect(Collectors.toList()))); carrerEvadataVo3.setRelation(Joiner.on(",") .join(newList.get(j).getOrgList().stream().map(OrgVo::getName) .collect(Collectors.toList()))); carrerEvadataVo3.setTarget(newList.get(j).getValueGoal()); carrerEvadataVo3.setResult(newList.get(j).getAchievement()); carrerEvadataVo3.setRole(newList.get(j).getRoleName()); carrerEvadataVo3.setAbilt(s.getDimensionName()); carrerEvadataVo3.setSelfEvaResult(s.getSelfEvaluateResult()); carrerEvadataVo3.setSelfEvaReason(s.getSelfEvaluateComment()); if (CollectionUtils.isNotEmpty(evaluateOpinionVoList)) { StringBuffer finalEvaResult = new StringBuffer(); StringBuffer finalEvaReason = new StringBuffer(); evaluateOpinionVoList.forEach(e -> { for (int d = 0; d < e.getResultDetailPoList().size(); d++) { if (s.getExamineScoreRuleDimensionInstanceId() .equals(e.getResultDetailPoList().get(d) .getExamineScoreRuleDimensionInstanceId())) { if (evaluateOpinionVoList.size() > 1) { finalEvaResult.append(e.getEvaluateUserName() + ": " + e .getResultDetailPoList().get(d).getEvaluateGrade() + "n"); finalEvaReason.append(e.getEvaluateUserName() + ": " + e .getResultDetailPoList().get(d).getDimensionOpinion() + "n"); } else { finalEvaResult .append(e.getResultDetailPoList().get(d).getEvaluateGrade() + "n"); finalEvaReason .append(e.getResultDetailPoList().get(d).getDimensionOpinion() + "n"); } carrerEvadataVo3.setFinalEvaResult(finalEvaResult.toString()); carrerEvadataVo3.setFinalEvaReason(finalEvaReason.toString()); break; } } }); } vos.add(carrerEvadataVo3); } } }); excelWriter.fill(paramMap, sheet); excelWriter.fill(vos, sheet);
最后导出填充一起执行
6.导出效果数据效果如下（示例）：
比较潦草多多见谅
总结提示：这里对文章进行总结：
以上就是今天要讲的内容，本文仅仅简单介绍了easyExcel的使用，而easyExcel提供了大量能使我们快速便捷地处理数据的函数和方法。!
