普通不合并单元格
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();
}
合并单元格
// 自定义合并策略 该类继承了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();
}
同理若要合并第三列的数据，则可以在注册一个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();
}
1.3 写多个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);
WriteSheet writeSheet1 = EasyExcel.writerSheet("模板2").head(DemoData.class).build();
excelWriter.write(data1(), writeSheet1);
excelWriter.finish();
}
1.4 WriteTable
若业务需求要求在同一个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();
}
本文内容来源于网络，版权归原作者所有，此博客不拥有其著作权，亦不承担相应法律责任。如侵犯了您的权益，请联系我，将立刻删除。