Java利用EasyExcel实现合并单元格
目录
pom版本
1.自定义合并单元格
1.1 不合并单元格
1.2 合并单元格
1.3 写多个sheet
1.4 WriteTable
pom版本 <dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.7</version>
</dependency>
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<DemoData> data1() {
List<DemoData> 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文件后如下，可以看到单元格没有合并。现在打算将第一列字符串标题相同的合并
// 自定义合并策略 该类继承了AbstractMergeStrategy抽象合并策略，需要重写merge()方法
public static class CustomMergeStrategy extends AbstractMergeStrategy {
/**
* 分组，每几行合并一次
*/
private List<Integer> exportFieldGroupCountList;
/**
* 目标合并列index
*/
private Integer targetColumnIndex;
// 需要开始合并单元格的首行index
private Integer rowIndex;
// exportDataList为待合并目标列的值
public CustomMergeStrategy(List<String> 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<Integer> getGroupCountList(List<String> exportDataList){
if (CollectionUtils.isEmpty(exportDataList)) {
return new ArrayList<>();
}
List<Integer> 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<DemoData> 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<DemoData> 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打开如下：
public static void writeExcel() {
String fileName = getPath();
ExcelWriter excelWriter = EasyExcel.write(fileName).excelType(ExcelTypeEnum.XLSX).build();
List<DemoData> 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了
若业务需求要求在同一个sheet中写多个表，就需要用到WriteTable了。只定义一个WriteSheet，有几个表就定义几个WriteTable
public static void writeExcel01() {
String fileName = getPath();
ExcelWriter excelWriter = EasyExcel.write(fileName).excelType(ExcelTypeEnum.XLSX).build();
WriteSheet writeSheet = EasyExcel.writerSheet("模板").needHead(Boolean.FALSE).build();
List<DemoData> 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);
WriteTable writeTable1 = EasyExcel.writerTable(2).head(DemoData.class).needHead(Boolean.TRUE).build();
excelWriter.write(data1(), writeSheet, writeTable1);
excelWriter.finish();
}
打开excel表格如下
以上就是Java利用EasyExcel实现合并单元格的详细内容，更多关于Java EasyExcel合并单元格的资料请关注软件开发网其它相关文章！
