Java使用EasyExcel进行单元格合并的问题详解

目录1.项目场景：2.问题描述3.原因分析：5.总结总结

1.项目场景：

简介：报销单导出要根据指定的excel模板去自动替换对应，然后重新生成一份新的excel。在给定的excel模板中，有部分字段进行了单元格合并，如下所示。

2.问题描述

由于一张报销单可能存在多条报销内容，可以看到，当超过模板中预先给定的一条时，则会自动换行，但换行时并不会自动依照模板中的样式进行单元格合并，如下所示。

3.原因分析：

首先可以直观的看到excel进行数据插入并自动换行的时候，换行的数据并没有按照上一行的样式进行自动合并。于是便想着用代码把这几列手动合并，然后再加上边框样式就可以解决了。

4.解决方案：

需要注意的是，按照以上的思路，直接进行单元格合并，然后加上边框并不能直接解决问题。需要将后边空的每一个单元格先创建出来，然后将其一块合并才可以解决，创建单元格代码在下方CustomCellWriteHandler类中说明。

这也算是耗费一整天时间踩的坑。。。

public static void outExcelBalance(String modelFile, String newFile, Map map, List fillData, HttpServletResponse response, String fileName){

//定义model模板中默认的行数

int firstRow = 7; //excel中表示第八行，即模板中默认的一条

int lastRow = 7;

InputStream is = null;

File file = new File(modelFile);

File file1 = new File(newFile);

//String file1Name = file1.getName();

BufferedInputStream bis = null;

try {

if (!file.exists()) {

copyFileUsingjava7Files(file, file1);

}

//TODO 单元格样式

Set rowsBorderSet= new HashSet<>();

CustomCellWriteHandler customCellWriteHandler = null;

//TODO 单元格合并

List cellRangeAddresss = new ArrayList<>();

if (ListUtils.isNotNull(fillData)){

if (fillData.size() > 1){

//合并每条报销单的第3-10列

for (int i = 1; i < fillData.size(); i++) {

firstRow++;

lastRow++;

cellRangeAddresss.add(new CellRangeAddress(firstRow, lastRow, 2, 9));

cellRangeAddresss.add(new CellRangeAddress(firstRow, lastRow, 10, 11));

rowsBorderSet.add(firstRow);

}

}

}

customCellWriteHandler = new CustomCellWriteHandler(rowsBorderSet);

MyMergeStrategy myMergeStrategy = new MyMergeStrategy(cellRangeAddresss);

ExcelWriter excelWriter = EasyExcel.write(newFile)

//注册单元格式

.regihttp://sterWriteHandler(customCellWriteHandler)

//注册合并策略

.registerWriteHandler(myMergeStrategy)

.withTemplate(modelFile).build();

WriteSheet writeSheet = EasyExcel.writerSheet().build();

FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();

if (!ListUtil.listIsEmpty(fillData)){

excelWriter.fill(fillData, fillConfig, writeSheet);

//excelWriter.fill(fillData, fillConfig, writeSheet);

}

excelWriter.fill(map, writeSheet);

excelWriter.finish();

response.setHeader("content-type", "text/plain");

response.setHeader("content-type", "application/x-msdownload;");

response.setContentType("text/plain; charset=utf-8");

response.setHeader("Content-Disposition", "attachment; http://filename=" + new String(fileName.getBytes("utf-8"),"ISO8859-1"));

byte[] buff = new byte[1024];

OutputStream os = null;

os = response.getOutputStream();

bis = new BufferedInputStream(new FileInputStream(file1));

int i = bis.read(buff);

while (i != -1) {

os.write(buff, 0, buff.length);

os.flush();

i = bis.read(buff);

}

}

catch (Exception e){

LOGGER.error(e.getMessage());

}

finally {

if (bis != null) {

try {

bis.close();

} catch (IOException e) {

e.printStackTrace();

}

}

// 删除生成文件

/*if (file1.exists()) {

file1.delete();

}*/

}

}

单元格合并MyMergeStrategy类代码：

public class MyMergeStrategy extends AbstractMergeStrategy {

//合并坐标集合

private List cellRangeAddresss;

//构造

public MyMergeStrategy(List cellRangeAddresss) {

this.cellRangeAddresss = cellRangeAddresss;

}

@Override

protected void merge(Sheet sheet, Cell cell, Head head, Integer integer) {

if (ListUtils.isNotNull(cellRangeAddresss)) {

if (cell.getRowIndex() == 7 ) {

for (CellRangeAddress item : cellRangeAddresss) {

sheet.addMergedRegionUnsafe(item);

}

}

}

}

}

单元格样式CustomCellWriteHandler类代码：

public class CustomCellWriteHandler implements CellWriteHandler {

private static final Logger LOGGER = LoggerFactory.getLogger(CustomCellWriteHandler.class);

//标黄行宽集合

private Set rowIndexs;

//构造

public CustomCellWriteHandler(Set rowIndexs) {

this.rowIndexs = rowIndexs;

}

public CustomCellWriteHandler() {

}

@Override

public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {

LOGGER.info("beforeCellCreate~~~~");

}

@Override

public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {

LOGGER.info("afterCellCreate~~~~");

}

@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 cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {

//获取工作簿

// HSSFWorkbook wb = new HSSFWorkbook();

// //获取sheet

// HSSFSheet sheet = wb.createSheet();

// HSSFRow row = sheet.createRow();

// HSSFCellStyle style = wb.createCellStyle();

// 这里可以对cell进行任何操作

if (CollbLPRzfectionUtils.isNotEmpty(rowIndexs)) {

Workbook workbook = writeSheetHolder.getSheet().getWorkbook();

CellStyle cellStyle = workbook.createCellStyle();

Sheet sheet = writeSheetHolder.getSheet();

cellStyle.setAlignment(new HSSFWorkbook().createCellStyle().getAlignment());

cellStyle.setBorderBottom(BorderStyle.THIN); //下边框

cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());

cellStyle.setBorderLeft(BorderStyle.THIN);//左边框

cellStyle.setBorderTop(BorderStyle.THIN);//上边框

cellStyle.setBorderRight(BorderStyle.THIN);//右边框

cellStyle.setWrapText(true);//自动换行

//字体

// Font cellFont = workbook.createFont();

// cellFont.setBold(true);

// cellStyle.setFont(cellFont);

// //标黄,要一起设置

// cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); //设置前景填充样式

// cellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());//前景填充色

if (rowIndexs.contains(cell.getRowIndex())) {

Row row = null;

//循环创建空白单元格

for (int i = 0; i < rowIndexs.size(); i++) {

for (Integer rowIndex : rowIndexs){

//创建4-10列的空白格

row = sheet.getRow(rowIndex.intValue());

if (row == null){

row = sheet.createRow(rowIndex.intValue());

}

for (int j = 3; j <= 9; j++) {

//获取8行的cell列

cell = row.createCell(j);

cell.setCellStyle(cellStyle);

cell.setCellValue(" ");

LOGGER.info("第{}行，第{}列创建空白格。", cell.getRowIndex(), j);

}

//创建12列的红白格

cell = row.createCell(11);

cell.setCellStyle(cellStyle);

cell.setCellValue(" ");

LOGGER.info("第{}行，第11列创建空白格。", cell.getRowIndex());

//创建21列的空白格

cell = row.createCell(21);

cell.setCellStyle(cellStyle);

cell.setCellValue(" ");

LOGGER.info("第{}行，第21列创建空白格。", cell.getRowIndex());

}

}

}

}

}

}

5.总结

核心步骤：

1.

//创建单元格样式

CustomCellWriteHandler customCellWriteHandler = new CustomCellWriteHandler(参数按需给定);

2.

//单元格进行合并

List cellRangeAddresss = new ArrayList<>();

//例如：从firstRow行到lastRow行的2列到9列合并

cellRangeAddresss.add(new CellRangeAddress(firstRow, lastRow, 2, 9));

cellRangeAddresss.add(new CellRangeAddress(firstRow, lastRow, 10, 11));

MyMergeStrategy myMergeStrategy = new MyMergeStrategy(cellRangeAddresss);

3.

//注册以上两种策略

ExcelWriter excelWriter = EasyExcel.write(newFile)

//注册单元格式

.registerWriteHandler(customCellWriteHandler)

//注册合并策略

.registerWriteHandler(myMergeStrategy)

.withTemplate(modelFile).build();

说明：刚开始修复的时候，并没有想过后边每个空的单元格需要先创建出来，才可以进行合并。一直以为是工具类的问题，后来不断的翻阅解决方案，看到有说需要先进行创建空白单元格，然后再进行合并，最终完美解决了。

关于代码部分，由于是业务代码，中间夹杂了许多不需要的。

总结

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。