Java利用EasyExcel实现合并单元格

目录pom版本1.自定义合并单元格 1.1 不合并单元格1.2 合并单元格1.3 写多个sheet1.4 WriteTable

pom版本

http://

com.alibaba

easyexcel

2.2.7

1.自定义合并单元格

在某些业务场景中可能会有合并单元格的需求，下面具体来说明如何实现

1.1 不合并单元格

先来看下不合并单元格的代码写法，简单复习下

public static void writeExcel() {

// 写excel的路径，当前项目路径下

String fileName = getPath();

// 构建ExcelWriter

ExcelWriter excelWriter = EasyExcel.write(fileName).excelType(ExcelTypeEnum.XLSX).build();

// 构建sheet

WriteSheet writeSheet = EasyExcel.writerSheet("模板1").head(DemoData.class).build();

// 写sheet

excelWriter.write(data1(), writeSheet);

excelWriter.finish();

}

private static String getPath() {

return System.getProperty("user.dir") + "/" + System.currentTimeMillis() + ".xlsx";

}

private static List data1() {

List list = Lists.newArrayList();

for (int i = 0; i < 3; i++) {

DemoData data = new DemoData();

data.setString("字符串" + 1);

data.setDate(new Date());

data.setDoubleData(0.56);

list.add(data);

}

for (int i = 0; i < 3; i++) {

DemoData data = new DemoData();

data.setString("字符串" + 2);

data.setDate(new Date());

data.setDoubleData(0.56);

list.add(data);

}

for (int i = 0; i < 4; i++) {

DemoData data = new DemoData();

data.setString("字符串" + 3);

data.setDate(new Date());

data.setDoubleData(0.57);

list.add(data);

}

return list;

}

public static void main(String[] args) {

writeExcel();

}

打开输出的excel文件后如下，可以看到单元格没有合并。现在打算将第一列字符串标题相同的合并

1.2 合并单元格

// 自定义合并策略 该类继承了AbstractMergeStrategy抽象合并策略，需要重写merge()方法

public static class CustomMergeStrategy extends AbstractMergeStrategy {

/**

* 分组，每几行合并一次

*/

private List exportFieldGroupCountList;

/**

* 目标合并列index

*/

private Integer targetColumnIndex;

// 需要开始合并单元格的首行index

private Integer rowIndex;

// exportDataList为待合并目标列的值

public CustomMergeStrategy(List exportDataList, Integer targetColumnIndex) {

this.exportFieldGroupCountList = getGroupCountList(exportDataList);

this.targetColumnIndex = targetColumnIndex;

}

@Override

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

if (null == rowIndex) {

rowIndex = cell.getRowIndex();

}

// 仅从首行以及目标列的单元格开始合并，忽略其他

if (cell.getRowIndex() == rowIndex && cell.getColumnIndex() == targetColumnIndex) {

mergeGroupColumn(sheet);

}

}

private void mergeGroupColumn(Sheet sheet) {

int rowCount = rowIndex;

for (Integer count : exportFieldGroupCountList) {

if(count == 1) {

rowCount += count;

continue ;

}

// 合并单元格

CellRangeAddress cellRangeAddress = new CellRangeAddress(rowCount, rowCount + count - 1, targetColumnIndex, targetColumnIndex);

sheet.addMergedRegionUnsafe(cellRangeAddress);

rowCount += count;

}

}

// 该方法将目标列根据值是否相同连续可合并，存储可合并的行数

private List getGroupCountList(List exportDataList){

if (CollectionUtils.isEmpty(exportDataList)) {

return new ArrayList<>();

}

List groupCountList = new ArrayList<>();

int count = 1;

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

if (exportDataList.get(i).equals(exportDataList.get(i - 1))) {

count++;

} else {

groupCountList.add(count);

count = 1;

}

}

// 处理完最后一条后

groupCountList.add(count);

return groupCountList;

}

}

// 修改WriteSheet的代码如下

public static void writeExcel() {

String fileName = getPath();

ExcelWriter excelWriter = EasyExcel.write(fileName).excelType(ExcelTypeEnum.XLSX).build();

List demoDataList = data1();

// 写sheet的时候注册相应的自定义合并单元格策略

WriteSheet writeSheet = EasyExcel.writerSheet("模板1").head(DemoData.class)

.registerWriteHandler(new CustomMergeStrategy(demoDataList.stream().map(DemoData::getString).collect(Collectors.toList()), 0))

.build();

excelWriter.write(demoDataList, writeSheet);

excelWriter.finish();

}

打开输出的excel文件后如下，可以看到第一列有相同值的单元格已经合并了，成功实现

同理若要合并第三列的数据，则可以在注册一个sheet写处理器，代码如下

public static void writeExcel() {

String fileName = getPath();

ExcelWriter excelWriter = EasyExcel.write(fileName).excelType(ExcelTypeEnum.XLSX).build();

List demoDataList = data1();

WriteSheet writeSheet = EasyExcel.writerSheet("模板1").head(DemoData.class)

.registerWriteHandler(new CustomMergeStrategy(demoDataList.stream().map(DemoData::getString).collect(Collectors.toList()), 0))

.registerWriteHandler(new CustomMergeStrategy(demoDataList.stream().map(o -> o.getDoubleData().toString()).collect(Collectors.toList()), 2))

.build();

excelWriter.write(demoDataList, writeSheet);

excelWriter.finish();

}

excel打开如下：

1.3 写多个sheet

public static void writeExcel() {

String fileName = getPath();

ExcelWriter excelWriter = EasyExcel.write(fileName).excelType(ExcelTypeEnum.XLSX).build();

List demoDataList = data1();

WriteSheet writeSheet = EasyExcel.writerSheet("模板1").head(DemoData.class)

.registerWriteHandler(new CustomMergeStrategy(demoDataList.stream().map(DemoData::getString).collect(Collectors.toList()), 0))

.registerWriteHandler(new CustomMergeStrategy(demoDataList.stream().map(o -> o.getDoubleData().toString()).collect(Collectors.toList()), 2))

.build();

excelWriter.write(demoDataList, writeSheet);

WriteSheet writeSheet1 = EasyExcel.writerSheet("模板2").head(DemoData.class).build();

excelWriter.write(data1(), writeSheet1);

excelWriter.finish();

}

输出excel可以看到已经有两个sheet了

1.4 WriteTable

若业务需求要求在同一个sheet中写多个表，就需要用到WriteTable了。只定义一个WriteSheet，有几个表就定义几个WriteTable

public static void writeExcel01() {

String fileName = getPath();

ExcelWriter excelWriter = EasyExcel.write(fileNaJlpejhme).excelType(ExcelTypeEnum.XLSX).build();

WriteSheet writeSheet = EasyExcel.writerSheet("模板").needHead(Boolean.FALSE).build();

List demoDataList = data1();

// 需要表头设置为true，WriteTable一些属性会继承自WriteSheet

WriteTable writeTable = EasyExcel.writerTable(1).head(DemoData.class).needHead(Boolean.TRUE)

.registerWriteHandler(new CustomMergeStrategy(demoDataList.stream().map(DemoData::getString).collect(Collectors.toList()), 0))

.registerWriteHandler(new CustomMergeStrategy(demoDataList.stream().map(o -> o.getDoubleData().toString()).collect(Collectors.toList()), 2))

.build();

excelWriter.write(demoDataList, writeSheet, writeTable);

http://WriteTable writeTable1 = EasyExcel.writerTable(2).head(DemoData.class).needHead(Boolean.TRUE).build();

excelWriter.write(data1(), writeSheet, writeTable1);

excelWriter.finish();

}

打开excel表格如下

以上就是java利用EasyExcel实现合并单元格的详细内容，更多关于Java EasyExcel合并单元格的资料请关注我们其它相关文章！

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