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檔案後如下，可以看到單元格沒有合併。現在打算將第一列字串標題相同的合併
1.2 合併單元格
// 自定義合併策略 該類繼承了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開啟如下：
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();
}輸出excel可以看到已經有兩個sheet了
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();
}開啟excel表格如下
結語
如有錯誤或者優化點歡迎指出，本文參考自EasyExcel文件