easyexcle处理复杂动态单元格合并问题，合并动态行列
2023-12-21 15:31:44
@GetMapping("getAddDelSummaryExport")
@ApiOperation("新增删除比例报表--导出")
@ApiImplicitParams({
@ApiImplicitParam(name = "season", value = "季节", paramType = "query", dataType = "String"),
@ApiImplicitParam(name = "brand", value = "品牌", paramType = "query", dataType = "String"),
@ApiImplicitParam(name = "series", value = "系列", paramType = "query", dataType = "String"),
@ApiImplicitParam(name = "stage", value = "阶段", paramType = "query", dataType = "String")
})
public void getAddDelSummaryExport(@ApiIgnore @RequestParam Map<String, Object> params, HttpServletResponse response) throws Exception {
if(!ParamUtil.paramsIsNotNull(params,"limit") || !ParamUtil.paramsIsNotNull(params,"page")){
params.put("limit", "1000");
params.put("page", "1");
}
List<AddAndDelSummaryDTO> list = styleSummaryService.getAddAndDelReportPage(params).getList();
//标记位
AtomicInteger mergeLineIndex = new AtomicInteger(0);
//合并的列
List<Integer> merge = new ArrayList<>();
//合并的行
List<Integer> lines = new ArrayList<>();
merge.add(mergeLineIndex.intValue());
merge.add(mergeLineIndex.intValue() + 1);
merge.add(mergeLineIndex.intValue() + 2);
lines.add(mergeLineIndex.intValue());
lines.add(mergeLineIndex.intValue() + 1);
List<List<String>> dataList = new ArrayList<>();
AtomicInteger seasonCount = new AtomicInteger();
if(CollectionUtils.isNotEmpty(list)){
Map<String, List<AddAndDelSummaryDTO>> collectMap = list.stream().collect(Collectors.groupingBy(AddAndDelSummaryDTO::getSeason, Collectors.toList()));
Map<String, List<AddAndDelSummaryDTO>> treeMap = new TreeMap<>(collectMap);
MapUtil.sort(treeMap);
treeMap.forEach((season, fooListBySeason) -> {
seasonCount.addAndGet(1);
List<String> itemList = new ArrayList<>();
itemList.add(season);
itemList.add("头版");
itemList.add("二选");
itemList.add("二选");
itemList.add("二选");
itemList.add("二选");
itemList.add("二选");
itemList.add("二选");
itemList.add("二选");
itemList.add("销样");
itemList.add("销样");
itemList.add("销样");
itemList.add("销样");
itemList.add("销样");
itemList.add("销样");
itemList.add("销样");
itemList.add("大货");
itemList.add("大货");
dataList.add(itemList);
itemList = new ArrayList<>();
itemList.add(season);
itemList.add("合计款数");
itemList.add("新增");
itemList.add("新增");
itemList.add("删除");
itemList.add("删除");
itemList.add("续存");
itemList.add("续存");
itemList.add("合计款数");
itemList.add("新增");
itemList.add("新增");
itemList.add("删除");
itemList.add("删除");
itemList.add("续存");
itemList.add("续存");
itemList.add("合计款数");
itemList.add("大货合计款数");
itemList.add("大货数量");
dataList.add(itemList);
itemList = new ArrayList<>();
itemList.add(season);
itemList.add("合计款数");
itemList.add("数量");
itemList.add("新增比例");
itemList.add("数量");
itemList.add("删除比例");
itemList.add("数量");
itemList.add("续存比例");
itemList.add("合计款数");
itemList.add("数量");
itemList.add("新增比例");
itemList.add("数量");
itemList.add("删除比例");
itemList.add("数量");
itemList.add("续存比例");
itemList.add("合计款数");
itemList.add("大货合计款数");
itemList.add("大货数量");
dataList.add(itemList);
AtomicInteger seriesIndex = new AtomicInteger();
fooListBySeason.stream().collect(Collectors.groupingBy(AddAndDelSummaryDTO::getSeries, Collectors.toList())).forEach((series, fooListBySeries) -> {
seriesIndex.addAndGet(1);
AddAndDelSummaryDTO dto = fooListBySeries.stream().filter(oo -> "头版".equals(oo.getStage())).findFirst().isPresent() ? fooListBySeries.stream().filter(oo -> "头版".equals(oo.getStage())).findFirst().get() : null;
AddAndDelSummaryDTO dto2 = fooListBySeries.stream().filter(oo -> "二选".equals(oo.getStage())).findFirst().isPresent() ? fooListBySeries.stream().filter(oo -> "二选".equals(oo.getStage())).findFirst().get() : null;
AddAndDelSummaryDTO dto3 = fooListBySeries.stream().filter(oo -> "销样".equals(oo.getStage())).findFirst().isPresent() ? fooListBySeries.stream().filter(oo -> "销样".equals(oo.getStage())).findFirst().get() : null;
AddAndDelSummaryDTO dto4 = fooListBySeries.stream().filter(oo -> "大货".equals(oo.getStage())).findFirst().isPresent() ? fooListBySeries.stream().filter(oo -> "大货".equals(oo.getStage())).findFirst().get() : null;
List<String> itemList1 = new ArrayList<>();
itemList1.add(series);
if(dto != null){
itemList1.add(dto.getStyleCount() + "");
}else {
itemList1.add("");
}
if(dto2 != null){
itemList1.add(dto2.getAddCount() + "");
itemList1.add(dto2.getAddProportion() + "%");
itemList1.add(dto2.getDelCount() + "");
itemList1.add(dto2.getDelProportion() + "%");
itemList1.add(dto2.getXcCount() + "");
itemList1.add(dto2.getXcProportion() + "%");
itemList1.add(dto2.getStyleCount() + "");
}else {
itemList1.add("");
itemList1.add("");
itemList1.add("");
itemList1.add("");
itemList1.add("");
itemList1.add("");
itemList1.add("");
}
if(dto3 != null){
itemList1.add(dto3.getAddCount() + "");
itemList1.add(dto3.getAddProportion() + "%");
itemList1.add(dto3.getDelCount() + "");
itemList1.add(dto3.getDelProportion() + "%");
itemList1.add(dto3.getXcCount() + "");
itemList1.add(dto3.getXcProportion() + "%");
itemList1.add(dto3.getStyleCount() + "");
}else {
itemList1.add("");
itemList1.add("");
itemList1.add("");
itemList1.add("");
itemList1.add("");
itemList1.add("");
itemList1.add("");
}
if(dto4 != null){
itemList1.add(dto4.getStyleCount() + "");
itemList1.add("");
}else {
itemList1.add("");
itemList1.add("");
}
dataList.add(itemList1);
});
//合计
List itemList3 = new ArrayList<>();
itemList3.add("合计");
int aSum = fooListBySeason.stream().filter(oo -> "头版".equals(oo.getStage())).mapToInt(AddAndDelSummaryDTO::getStyleCount).sum();
int bSum = fooListBySeason.stream().filter(oo -> "二选".equals(oo.getStage())).mapToInt(AddAndDelSummaryDTO::getStyleCount).sum();
int bSum1 = fooListBySeason.stream().filter(oo -> "二选".equals(oo.getStage())).mapToInt(AddAndDelSummaryDTO::getAddCount).sum();
int bSum2 = fooListBySeason.stream().filter(oo -> "二选".equals(oo.getStage())).mapToInt(AddAndDelSummaryDTO::getDelCount).sum();
int bSum3 = fooListBySeason.stream().filter(oo -> "二选".equals(oo.getStage())).mapToInt(AddAndDelSummaryDTO::getXcCount).sum();
int cSum = fooListBySeason.stream().filter(oo -> "销样".equals(oo.getStage())).mapToInt(AddAndDelSummaryDTO::getStyleCount).sum();
int cSum1 = fooListBySeason.stream().filter(oo -> "销样".equals(oo.getStage())).mapToInt(AddAndDelSummaryDTO::getAddCount).sum();
int cSum2 = fooListBySeason.stream().filter(oo -> "销样".equals(oo.getStage())).mapToInt(AddAndDelSummaryDTO::getDelCount).sum();
int cSum3 = fooListBySeason.stream().filter(oo -> "销样".equals(oo.getStage())).mapToInt(AddAndDelSummaryDTO::getXcCount).sum();
int dSum = fooListBySeason.stream().filter(oo -> "大货".equals(oo.getStage())).mapToInt(AddAndDelSummaryDTO::getStyleCount).sum();
BigDecimal decimal = BigDecimal.valueOf(0.00);
itemList3.add(aSum + "");
itemList3.add(bSum1 + "");
if(aSum > 0){
decimal = BigDecimal.valueOf((float) 100 * bSum1 / aSum);
}
itemList3.add(decimal.setScale(2, BigDecimal.ROUND_HALF_UP) + "%");
itemList3.add(bSum2 + "");
if(aSum > 0){
decimal = BigDecimal.valueOf((float) 100 * bSum2 / aSum);
}
itemList3.add(decimal.setScale(2, BigDecimal.ROUND_HALF_UP) + "%");
itemList3.add(bSum3 + "");
if(aSum > 0){
decimal = BigDecimal.valueOf((float) 100 * bSum3 / aSum);
}
itemList3.add(decimal.setScale(2, BigDecimal.ROUND_HALF_UP) + "%");
itemList3.add(bSum + "");
itemList3.add(cSum1 + "");
if(bSum > 0){
decimal = BigDecimal.valueOf((float) 100 * cSum1 / bSum);
}
itemList3.add(decimal.setScale(2, BigDecimal.ROUND_HALF_UP) + "%");
itemList3.add(cSum2 + "");
if(bSum > 0){
decimal = BigDecimal.valueOf((float) 100 * cSum2 / bSum);
}
itemList3.add(decimal.setScale(2, BigDecimal.ROUND_HALF_UP) + "%");
itemList3.add(cSum3 + "");
if(bSum > 0){
decimal = BigDecimal.valueOf((float) 100 * cSum3 / bSum);
}
itemList3.add(decimal.setScale(2, BigDecimal.ROUND_HALF_UP) + "%");
itemList3.add(cSum + "");
itemList3.add(dSum + "");
itemList3.add("");
dataList.add(itemList3);
//处理单元格合并逻辑
if(1 < collectMap.size() && collectMap.size() > seasonCount.intValue()){
List itemList2 = new ArrayList<>();
for (int i = 0; i < 18; i++) {
itemList2.add("");
}
dataList.add(itemList2);
mergeLineIndex.addAndGet(5 + seriesIndex.intValue());
merge.add(mergeLineIndex.intValue());
merge.add(mergeLineIndex.intValue() + 1);
merge.add(mergeLineIndex.intValue() + 2);
lines.add(mergeLineIndex.intValue() - 1);
lines.add(mergeLineIndex.intValue());
lines.add(mergeLineIndex.intValue() + 1);
}
});
}
response.setContentType("application/json;charset=utf-8");
response.setCharacterEncoding("UTF-8");
String fileName = URLEncoder.encode("开发新增删除比例统计表-"+ DateUtils.format(new Date()), "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
ExcelWriter excelWriter = null;
try{
excelWriter = EasyExcel.write(response.getOutputStream()).build();
//excelWriter = EasyExcel.write("D:\\2023\\test456.xlsx").build();
if(null != excelWriter) {
//需要合并的列
int[] mergeColumeIndex = {0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17};
// 从那一列开始合并
int mergeRowIndex = 0;
// 内容的策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.index);
HorizontalCellStyleStrategy horizontalCellStyleStrategy =
new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
WriteSheet writeSheet = EasyExcel.writerSheet( "开发新增删除比例统计")
.registerWriteHandler(horizontalCellStyleStrategy)
.registerWriteHandler(new MergeSameRowsStrategy(mergeRowIndex, mergeColumeIndex, merge))
.registerWriteHandler(new MergeSameLinesStrategy(lines))
.build();
excelWriter.write(dataList, writeSheet);
}
}finally {
//关闭流
if(excelWriter != null){
excelWriter.finish();
}
}
}
package com.jack.modules.dsr.common.utils;
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.Data;
import org.apache.commons.collections.CollectionUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import java.util.List;
@Data
public class MergeSameRowsStrategy implements CellWriteHandler{
private int[] mergeColumnIndex;
private int mergeRowIndex;
private List<Integer> merge;
public MergeSameRowsStrategy() {
}
public MergeSameRowsStrategy(int mergeRowIndex, int[] mergeColumnIndex, List<Integer> merge) {
this.mergeRowIndex = mergeRowIndex;
this.mergeColumnIndex = mergeColumnIndex;
this.merge = merge;
}
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
if (CollectionUtils.isNotEmpty(merge)){
// 当前行
int curRowIndex = cell.getRowIndex();
if (merge.contains(curRowIndex)){
// 当前列
int curColIndex = cell.getColumnIndex();
if (curRowIndex > mergeRowIndex) {
for (int i = 0; i < mergeColumnIndex.length; i++) {
if (curColIndex == mergeColumnIndex[i]) {
mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);
break;
}
}
}
}
}
}
/**
* 当前单元格向上合并
*
* @param writeSheetHolder sheet保持对象
* @param cell 当前单元格
* @param curRowIndex 当前行
* @param curColIndex 当前列
*/
private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
// 获取当前行的当前列的数据和上一行的当前列列数据，通过上一行数据是否相同进行合并
Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
if (cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex) == null){
return;
}
Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);
Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
// 比较当前行的第一列的单元格与上一行是否相同，相同合并当前单元格与上一行
if (curData.equals(preData) || ("0.0".equals(preData.toString()) && "".equals(curData))) {
Sheet sheet = writeSheetHolder.getSheet();
List<CellRangeAddress> mergedRegions = sheet.getMergedRegions();
boolean isMerged = false;
for (int i = 0; i < mergedRegions.size() && !isMerged; i++) {
CellRangeAddress cellRangeAddr = mergedRegions.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);
}
}
}
@Override
public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {
}
@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) {
}
}
package com.jack.modules.dsr.common.utils;
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 com.baomidou.mybatisplus.core.toolkit.ObjectUtils;
import lombok.Data;
import org.apache.commons.collections.CollectionUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import java.util.List;
@Data
public class MergeSameLinesStrategy implements CellWriteHandler{
/**
* 要合并的行
*/
private List<Integer> mergeRowsIndex;
/**
* 开始合并的行
*/
private int mergeRowIndex;
/**
* 无参构造
*/
public MergeSameLinesStrategy() {
}
/**
* 有参构造
*
* @param mergeRowIndex 开始合并的行
*/
public MergeSameLinesStrategy(int mergeRowIndex) {
this.mergeRowIndex = mergeRowIndex;
}
/**
* 有参构造
*
* @param mergeRowIndex 开始合并的行
* @param mergeRowsIndex 合并的行
*/
// public MergeSameLinesStrategy(int mergeRowIndex, List<Integer> mergeRowsIndex) {
// this.mergeRowIndex = mergeRowIndex;
// this.mergeRowsIndex = mergeRowsIndex;
// }
public MergeSameLinesStrategy(List<Integer> mergeRowsIndex) {
this.mergeRowsIndex = mergeRowsIndex;
}
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
//合并行（从第一列开始）
//当前行
final int curRowIndex = cell.getRowIndex();
//当前列
final int curColIndex = cell.getColumnIndex();
if (curColIndex > 0) {
if (!ObjectUtils.isEmpty(mergeRowsIndex)) {
for (int i = 0; i < mergeRowsIndex.size(); i++) {
if (curRowIndex == mergeRowsIndex.get(i)) {
mergeWithPrevCol(writeSheetHolder, cell, curRowIndex, curColIndex);
break;
}
}
}
}
}
/**
* 当前单元格向左合并
*
* @param writeSheetHolder writeSheetHolder
* @param cell 当前单元格
* @param curRowIndex 当前行
* @param curColIndex 当前列
*/
private void mergeWithPrevCol(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
//获取当前行的当前列的数据和上一列的当前行行数据，通过上一列数据是否相同进行合并
final Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
final Cell preCell = cell.getSheet().getRow(curRowIndex).getCell(curColIndex - 1);
final Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
// 比较当前行的单元格与上一列是否相同，相同合并当前单元格与上一列
if (curData.equals(preData)) {
final Sheet sheet = writeSheetHolder.getSheet();
final List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
boolean isMerged = false;
for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
final CellRangeAddress cellRangeAddr = mergeRegions.get(i);
// 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元
if (cellRangeAddr.isInRange(curRowIndex, curColIndex - 1)) {
sheet.removeMergedRegion(i);
cellRangeAddr.setLastColumn(curColIndex);
sheet.addMergedRegion(cellRangeAddr);
isMerged = true;
}
}
// 若上一个单元格未被合并，则新增合并单元
if (!isMerged) {
final CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex, curRowIndex, curColIndex - 1, curColIndex);
sheet.addMergedRegion(cellRangeAddress);
}
}
}
@Override
public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {
}
@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) {
}
}
文章来源:https://blog.csdn.net/piyingcheng/article/details/135130336
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。 如若内容造成侵权/违法违规/事实不符，请联系我的编程经验分享网邮箱：veading@qq.com进行投诉反馈，一经查实，立即删除！
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。 如若内容造成侵权/违法违规/事实不符，请联系我的编程经验分享网邮箱：veading@qq.com进行投诉反馈，一经查实，立即删除！