SpringBoot+Hutool+thymeleaf完成导出Excel的实现方法
1155
2022-07-25
目录pom版本1.自定义合并单元格 1.1 不合并单元格1.2 合并单元格1.3 写多个sheet1.4 WriteTable
pom版本
http://
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
List
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
/**
* 目标合并列index
*/
private Integer targetColumnIndex;
// 需要开始合并单元格的首行index
private Integer rowIndex;
// exportDataList为待合并目标列的值
public CustomMergeStrategy(List
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
if (CollectionUtils.isEmpty(exportDataList)) {
return new ArrayList<>();
}
List
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
// 写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
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
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
// 需要表头设置为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小时内删除侵权内容。
发表评论
暂时没有评论,来抢沙发吧~