Java?EasyExcel實現合并相同內容單元格與動態(tài)標題功能
更新時間：2023年12月20日 08:43:11 作者：myprince003
這篇文章主要為大家詳細介紹了Java?EasyExcel如何實現合并相同內容單元格與動態(tài)標題功能,文中的示例代碼講解詳細,有需要的小伙伴可以參考下
一、最初版本
導出的結果：
對應實體類代碼：
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentLoopMerge;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import lombok.*;
import java.io.Serializable;
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@Data
@ContentRowHeight(30)
@HeadRowHeight(40)
@ColumnWidth(25)
public class StudentExportVo implements Serializable {
private static final long serialVersionUID = -5809782578272943999L;
@ExcelProperty(value = "學校", order = 1)
@ContentLoopMerge(eachRow = 3)
private String school;
@ExcelProperty(value = "姓名", order = 2)
private String name;
@ExcelProperty(value = "性別", order = 3)
private String sex;
@ExcelProperty(value = "年齡", order = 4)
private String age;
@ExcelProperty(value = "城市", order = 5)
private String city;
@ExcelProperty(value = "備注", order = 6)
private String remarks;
}
對應業(yè)務代碼：
@ApiOperation(value = "導出學生信息", notes = "導出學生信息")
@PostMapping("/exportStudent")
public void exportStudent(@RequestBody String str, HttpServletResponse response) {
List<StudentExportVo> list = this.getStudentExportVos();
ExcelUtils.writeExcel(response, StudentExportVo.class, list, "導出學生信息", "sheet1");
}
//數據制造
private List<StudentExportVo> getStudentExportVos() {
List<StudentExportVo> list = new ArrayList<>();
StudentExportVo v1 = new StudentExportVo();
v1.setSchool("北京大學");
v1.setName("張三");
v1.setSex("男");
v1.setAge("20");
v1.setCity("北京");
v1.setRemarks("無");
list.add(v1);
StudentExportVo v2 = new StudentExportVo();
v2.setSchool("北京大學");
v2.setName("李四");
v2.setSex("男");
v2.setAge("22");
v2.setCity("上海");
v2.setRemarks("無");
list.add(v2);
StudentExportVo v3 = new StudentExportVo();
v3.setSchool("北京大學");
v3.setName("王五");
v3.setSex("女");
v3.setAge("22");
v3.setCity("青島");
v3.setRemarks("無");
list.add(v3);
StudentExportVo v4 = new StudentExportVo();
v4.setSchool("清華大學");
v4.setName("趙六");
v4.setSex("女");
v4.setAge("21");
v4.setCity("重慶");
v4.setRemarks("無");
list.add(v4);
StudentExportVo v5 = new StudentExportVo();
v5.setSchool("武漢大學");
v5.setName("王強");
v5.setSex("男");
v5.setAge("24");
v5.setCity("長沙");
v5.setRemarks("無");
list.add(v5);
StudentExportVo v6 = new StudentExportVo();
v6.setSchool("武漢大學");
v6.setName("趙燕");
v6.setSex("女");
v6.setAge("21");
v6.setCity("深圳");
v6.setRemarks("無");
list.add(v6);
StudentExportVo v7 = new StudentExportVo();
v7.setSchool("廈門大學");
v7.setName("陸仟");
v7.setSex("女");
v7.setAge("21");
v7.setCity("廣州");
v7.setRemarks("無");
list.add(v7);
return list;
}
二、使用注解的版本
導出的結果：
對應實體類代碼：
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@Data
@ContentRowHeight(30)
@HeadRowHeight(40)
@ColumnWidth(25)
public class StudentExportVo implements Serializable {
private static final long serialVersionUID = -5809782578272943999L;
@ExcelProperty(value = {"學生信息","學校"}, order = 1)
@ContentLoopMerge(eachRow = 3)
private String school;
@ExcelProperty(value = {"學生信息","姓名"}, order = 2)
private String name;
@ExcelProperty(value = {"學生信息","性別"}, order = 3)
private String sex;
@ExcelProperty(value = {"學生信息","年齡"}, order = 4)
private String age;
@ExcelProperty(value = {"學生信息","城市"}, order = 5)
private String city;
@ExcelProperty(value = {"學生信息","備注"}, order = 6)
private String remarks;
}
對應業(yè)務代碼：同上
@ContentLoopMerge(eachRow = 3) 可以合并單元格，但是他是按指定行數合并，并不能實現內容相同的合并
@ExcelProperty(value = {“學生信息”,“備注”}，能實現多個標題，但標題是固定的，不是動態(tài)的
三、自定義改造
導出的結果：
對應實體類代碼：
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@Data
@ContentRowHeight(30)
@HeadRowHeight(40)
@ColumnWidth(25)
public class StudentExportVo implements Serializable {
private static final long serialVersionUID = -5809782578272943999L;
@ExcelProperty(value = {"學生信息","學校"}, order = 1)
//@ContentLoopMerge(eachRow = 3)
private String school;
@ExcelProperty(value = {"學生信息","姓名"}, order = 2)
private String name;
@ExcelProperty(value = {"學生信息","性別"}, order = 3)
private String sex;
@ExcelProperty(value = {"學生信息","年齡"}, order = 4)
private String age;
@ExcelProperty(value = {"學生信息","城市"}, order = 5)
private String city;
@ExcelProperty(value = {"學生信息","備注"}, order = 6)
private String remarks;
}
對應業(yè)務代碼：
@ApiOperation(value = "導出學生信息", notes = "導出學生信息")
@PostMapping("/exportStudent")
public void exportStudent(@RequestBody String dynamicTitle, HttpServletResponse response) {
List<StudentExportVo> list = this.getStudentExportVos();
try {
String fileName = "學生信息";
fileName = URLEncoder.encode(fileName, "UTF-8");
response.setContentType("application/json;charset=utf-8");
response.setCharacterEncoding("utf-8");
response.addHeader("Pargam", "no-cache");
response.addHeader("Cache-Control", "no-cache");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
ServletOutputStream output = response.getOutputStream();
//需要合并的列
int[] mergeColumeIndex = {0};
// 從第二行后開始合并
int mergeRowIndex = 2;
//設置動態(tài)標題
List<List<String>> headers = this.getHeaders("學生信息" + dynamicTitle);
// 頭的策略
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
// 背景設置為白色
headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
/*WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints((short)20);
headWriteCellStyle.setWriteFont(headWriteFont);*/
// 內容的策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
// 這里需要指定 FillPatternType 為FillPatternType.SOLID_FOREGROUND 不然無法顯示背景顏色.頭默認了 FillPatternType所以可以不指定
//contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
// 背景綠色
//contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
//設置 自動換行
contentWriteCellStyle.setWrapped(true);
//設置 垂直居中
contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//設置 水平居中
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
/*WriteFont contentWriteFont = new WriteFont();
// 字體大小
contentWriteFont.setFontHeightInPoints((short)20);
contentWriteCellStyle.setWriteFont(contentWriteFont);*/
// 這個策略是 頭是頭的樣式 內容是內容的樣式 其他的策略可以自己實現
HorizontalCellStyleStrategy horizontalCellStyleStrategy =
new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
EasyExcel.write(output, StudentExportVo.class)
.sheet("學生信息")
.head(headers)
.registerWriteHandler(new ExcelMergeHandler(mergeRowIndex, mergeColumeIndex))
.registerWriteHandler(horizontalCellStyleStrategy)
// .registerWriteHandler(new SimpleColumnWidthStyleStrategy(30))
.registerWriteHandler(new AbstractColumnWidthStyleStrategy() {
@Override
protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
Sheet sheet = writeSheetHolder.getSheet();
int columnIndex = cell.getColumnIndex();
if(columnIndex == 5){
// 列寬100
sheet.setColumnWidth(columnIndex, 10000);
}else {
// 列寬50
sheet.setColumnWidth(columnIndex, 5000);
}
// 行高40
sheet.setDefaultRowHeight((short) 4000);
}
})
.doWrite(list);
output.flush();
} catch (IOException e) {
log.error(e.getMessage(), e);
}
}
private List<List<String>> getHeaders(String dynamicTitle) {
List<List<String>> headers=new ArrayList<>();
List<String> schoolHead=new ArrayList<>();
schoolHead.add(dynamicTitle);
schoolHead.add("學校");
List<String> nameHead=new ArrayList<>();
nameHead.add(dynamicTitle);
nameHead.add("姓名");
List<String> sexHead=new ArrayList<>();
sexHead.add(dynamicTitle);
sexHead.add("性別");
List<String> ageHead=new ArrayList<>();
ageHead.add(dynamicTitle);
ageHead.add("年齡");
List<String> cityHead=new ArrayList<>();
cityHead.add(dynamicTitle);
cityHead.add("城市");
List<String> remarksHead=new ArrayList<>();
remarksHead.add(dynamicTitle);
remarksHead.add("備注");
headers.add(schoolHead);
headers.add(nameHead);
headers.add(sexHead);
headers.add(ageHead);
headers.add(cityHead);
headers.add(remarksHead);
return headers;
}
//數據制造
private List<StudentExportVo> getStudentExportVos() {
List<StudentExportVo> list = new ArrayList<>();
StudentExportVo v1 = new StudentExportVo();
v1.setSchool("北京大學");
v1.setName("張三");
v1.setSex("男");
v1.setAge("20");
v1.setCity("北京");
v1.setRemarks("無");
list.add(v1);
StudentExportVo v2 = new StudentExportVo();
v2.setSchool("北京大學");
v2.setName("李四");
v2.setSex("男");
v2.setAge("22");
v2.setCity("上海");
v2.setRemarks("無");
list.add(v2);
StudentExportVo v3 = new StudentExportVo();
v3.setSchool("北京大學");
v3.setName("王五");
v3.setSex("女");
v3.setAge("22");
v3.setCity("青島");
v3.setRemarks("無");
list.add(v3);
StudentExportVo v4 = new StudentExportVo();
v4.setSchool("清華大學");
v4.setName("趙六");
v4.setSex("女");
v4.setAge("21");
v4.setCity("重慶");
v4.setRemarks("無");
list.add(v4);
StudentExportVo v5 = new StudentExportVo();
v5.setSchool("武漢大學");
v5.setName("王強");
v5.setSex("男");
v5.setAge("24");
v5.setCity("長沙");
v5.setRemarks("無");
list.add(v5);
StudentExportVo v6 = new StudentExportVo();
v6.setSchool("武漢大學");
v6.setName("趙燕");
v6.setSex("女");
v6.setAge("21");
v6.setCity("深圳");
v6.setRemarks("無");
list.add(v6);
StudentExportVo v7 = new StudentExportVo();
v7.setSchool("廈門大學");
v7.setName("陸仟");
v7.setSex("女");
v7.setAge("21");
v7.setCity("廣州");
v7.setRemarks("無");
list.add(v7);
return list;
}
合并單元格相同內容處理類：ExcelMergeHandler
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import java.util.List;
public class ExcelMergeHandler implements CellWriteHandler {
private int[] mergeColumnIndex;
private int mergeRowIndex;
public ExcelMergeHandler() {
}
public ExcelMergeHandler(int mergeRowIndex, int[] mergeColumnIndex) {
this.mergeRowIndex = mergeRowIndex;
this.mergeColumnIndex = mergeColumnIndex;
}
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
}
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
}
@Override
public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
}
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
//當前行
int curRowIndex = cell.getRowIndex();
//當前列
int curColIndex = cell.getColumnIndex();
if (curRowIndex > mergeRowIndex) {
for (int i = 0; i < mergeColumnIndex.length; i++) {
if (curColIndex == mergeColumnIndex[i]) {
mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);
break;
}
}
}
}
/**
* 當前單元格向上合并
*
* @param writeSheetHolder
* @param cell 當前單元格
* @param curRowIndex 當前行
* @param curColIndex 當前列
*/
private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);
Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
// 將當前單元格數據與上一個單元格數據比較
Boolean dataBool = preData.equals(curData);
//此處需要注意：因為我是按照工程名稱確定是否需要合并的，所以獲取每一行第二列數據和上一行第一列數據進行比較，如果相等合并，getCell里面的值，是工程名稱所在列的下標
String s1 = cell.getRow().getCell(0).getStringCellValue();
String s2 = cell.getSheet().getRow(curRowIndex - 1).getCell(0).getStringCellValue();
/*BigDecimal d1 = new BigDecimal(cell.getRow().getCell(0).getNumericCellValue());
BigDecimal d2 = new BigDecimal(cell.getSheet().getRow(curRowIndex - 1).getCell(0).getNumericCellValue());*/
Boolean bool = s1.compareTo(s2) == 0 ? true:false;
// 原始的
// Boolean bool = cell.getRow().getCell(1).getStringCellValue().equals(cell.getSheet().getRow(curRowIndex - 1).getCell(1).getStringCellValue());
if (dataBool && bool) {
Sheet sheet = writeSheetHolder.getSheet();
List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
boolean isMerged = false;
for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
CellRangeAddress cellRangeAddr = mergeRegions.get(i);
// 若上一個單元格已經被合并，則先移出原有的合并單元，再重新添加合并單元
if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {
sheet.removeMergedRegion(i);
cellRangeAddr.setLastRow(curRowIndex);
sheet.addMergedRegion(cellRangeAddr);
isMerged = true;
}
}
// 若上一個單元格未被合并，則新增合并單元
if (!isMerged) {
CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex);
sheet.addMergedRegion(cellRangeAddress);
}
}
}
}
總結：
1、使用自定義合并相同內容單元格時，實體類中的注解@ContentLoopMerge需要去掉，要不然會受到影響
2、該段自定義代碼中如內容合并、設置動態(tài)標題、內容、以及每個列的寬度及字體都是可以根據自定義策略來進行設置的，具體用法可以參考代碼注釋說明，根據需要進行使用
以上就是Java EasyExcel實現合并相同內容單元格與動態(tài)標題功能的詳細內容，更多關于Java EasyExcel合并單元格的資料請關注腳本之家其它相關文章！
相關文章
這篇文章主要介紹了使用Java 8中的Stream遍歷樹形結構,我們可以使用Java8中的Stream流一次性把數據查出來,然后通過流式處理,我們一起來看看,代碼實現為了實現簡單,就模擬查看數據庫所有數據到List里面,需要的朋友可以參考下2023-08-08
下面小編就為大家分享一篇淺談在頁面中獲取到ModelAndView綁定的值方法，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2018-03-03
這篇文章主要為大家詳細介紹了基于JavaMail實現郵件發(fā)送功能，具有一定的參考價值，感興趣的小伙伴們可以參考一下2018-03-03
IDEA Maven Mybatis generator 自動生成代碼(實例講解)
下面小編就為大家分享一篇IDEA Maven Mybatis generator 自動生成代碼的實例講解，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2017-12-12
在 MyBatis 中,懶加載（Lazy Loading）和預加載（Eager Loading）是兩種不同的查詢策略,用于控制關聯(lián)對象的加載時機和方式,本文給大家詳細介紹了具體處理方式,需要的朋友可以參考下2025-11-11
這篇文章主要通過示例為大家詳細介紹一下介紹了SpringBoot參數校驗中@Valid的使用方法，文中的示例代碼講解詳細，需要的可以參考一下2022-06-06
這篇文章主要為大家詳細介紹了java實現掃雷小游戲，文中示例代碼介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們可以參考一下2020-01-01
這幾天看SFTP資料時，無意中看到了Ganymed SSH-2，寫了個簡單demo，通過，感覺挺好用的，下面就和大家分享下。需要的朋友可以過來參考參考2013-08-08
SpringBoot之那些注入不了的Spring占位符(${}表達式)問題
這篇文章主要介紹了SpringBoot之那些注入不了的Spring占位符(${}表達式)問題，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2023-04-04
這篇文章主要介紹了Java 中Flyway的使用詳解,本文給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下2020-07-07