EasyExcel导出动态合并单元格策略

EasyExcel导出动态合并单元格策略

1.导入依赖

   <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>easyexcel</artifactId>
        <version>2.2.6</version>
    </dependency>

2.合并单元格工具类

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 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;

public class ExcelMergeUtil implements CellWriteHandler {
private int[] mergeColumnIndex;
private int mergeRowIndex;

public ExcelMergeUtil() {
}

public ExcelMergeUtil(int mergeRowIndex, int[] mergeColumnIndex) {
    this.mergeRowIndex = mergeRowIndex;
    this.mergeColumnIndex = mergeColumnIndex;
}

@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 relativeRowIndex, Boolean isHead) {

}

@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {

    //当前行
    int curRowIndex = cell.getRowIndex();
    //当前列
    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
 * @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();
    Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);
    Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
    // 将当前单元格数据与上一个单元格数据比较
    Boolean dataBool = preData.equals(curData);
    //此处需要注意：因为我是按照订单号确定是否需要合并的，所以获取每一行第二列数据和上一行第一列数据进行比较，如果相等合并
    Boolean bool = cell.getRow().getCell(0).getStringCellValue().equals(cell.getSheet().getRow(curRowIndex - 1).getCell(0).getStringCellValue());
    if (dataBool && bool) {
        Sheet sheet = writeSheetHolder.getSheet();
        List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
        boolean isMerged = false;
        for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
            CellRangeAddress cellRangeAddr = 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);
        }
    }
}

}

3、导出方法（公司导出的工具类）

public static ExcelWriter getExcelWriterMerge(HttpServletResponse response, String excelName,int mergeRowIndex,int[] mergeColumeIndex) throws Exception{
    response.setContentType("application/vnd.ms-excel");
    response.setCharacterEncoding("utf-8");
    // 这里URLEncoder.encode可以防止中文乱码
    excelName = URLEncoder.encode(excelName, "UTF-8");
    response.setHeader("Content-disposition", "attachment;filename=" + excelName + ExcelTypeEnum.XLSX.getValue());

    ExcelWriter build = EasyExcel.write(response.getOutputStream()).registerWriteHandler(new ExcelMergeUtil(mergeRowIndex, mergeColumeIndex)).build();

    return build;
}

4、业务代码

   try {
            //需要合并的列
            int[] mergeColumeIndex = {0,4,5,6,7,8,9,10};
            // 从那一行开始合并
            int mergeRowIndex = 1;
            //先执行合并策略
            excelWriter = ExcelUtil.getExcelWriterMerge(response, fileName, mergeRowIndex, mergeColumeIndex);
            //业务代码
            for (int i = 0 ; i < totalPage;i++){
                selectOrderDto.setPageNum(i);
                selectOrderDto.setPageSize(10000);
                String sheetName = "sheet"+i;
                PageInfo<ShowOrderByKitchen> page = selectOrderByKitchenId(selectOrderDto);
                List<ShowOrderByKitchen> list = page.getList();
                if (!list.isEmpty()){
                //进行写入操作
                    WriteSheet sheetWriter = EasyExcel.writerSheet(i,sheetName).head(ShowOrderByKitchen.class).build();
                    excelWriter.write(list,sheetWriter);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            log.error("导出订单失败，{}",e);
        }finally {
            // 千万别忘记finish 会帮忙关闭流
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
    }

5、实体

public class ShowOrderByKitchen implements Serializable {

@ExcelProperty(value = "编码",index = 0)
private String orderId;

@ExcelProperty(value = "名称",index = 1)
private String dishName;

@ExcelProperty(value = "价格",index = 2)
private BigDecimal dishPrice;

@ExcelProperty(value = "数量",index = 3)
private Integer dishNumber;

}

6、测试结果

这是根据订单号进行合并，工具类里进行两次判断，合并后就会呈现正确的格式，建议根据哪一列数据合并，就将这列数据放在excel的第一列，否则会出现空指针异常。