Java?EasyExcel實(shí)現(xiàn)合并相同內(nèi)容單元格與動(dòng)態(tài)標(biāo)題功能
更新時(shí)間：2023年12月20日 08:43:11 作者：myprince003
這篇文章主要為大家詳細(xì)介紹了Java?EasyExcel如何實(shí)現(xiàn)合并相同內(nèi)容單元格與動(dòng)態(tài)標(biāo)題功能,文中的示例代碼講解詳細(xì),有需要的小伙伴可以參考下
一、最初版本
導(dǎo)出的結(jié)果：
對(duì)應(yīng)實(shí)體類(lè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 = "學(xué)校", 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;
}
對(duì)應(yīng)業(yè)務(wù)代碼：
@ApiOperation(value = "導(dǎo)出學(xué)生信息", notes = "導(dǎo)出學(xué)生信息")
@PostMapping("/exportStudent")
public void exportStudent(@RequestBody String str, HttpServletResponse response) {
List<StudentExportVo> list = this.getStudentExportVos();
ExcelUtils.writeExcel(response, StudentExportVo.class, list, "導(dǎo)出學(xué)生信息", "sheet1");
}
//數(shù)據(jù)制造
private List<StudentExportVo> getStudentExportVos() {
List<StudentExportVo> list = new ArrayList<>();
StudentExportVo v1 = new StudentExportVo();
v1.setSchool("北京大學(xué)");
v1.setName("張三");
v1.setSex("男");
v1.setAge("20");
v1.setCity("北京");
v1.setRemarks("無(wú)");
list.add(v1);
StudentExportVo v2 = new StudentExportVo();
v2.setSchool("北京大學(xué)");
v2.setName("李四");
v2.setSex("男");
v2.setAge("22");
v2.setCity("上海");
v2.setRemarks("無(wú)");
list.add(v2);
StudentExportVo v3 = new StudentExportVo();
v3.setSchool("北京大學(xué)");
v3.setName("王五");
v3.setSex("女");
v3.setAge("22");
v3.setCity("青島");
v3.setRemarks("無(wú)");
list.add(v3);
StudentExportVo v4 = new StudentExportVo();
v4.setSchool("清華大學(xué)");
v4.setName("趙六");
v4.setSex("女");
v4.setAge("21");
v4.setCity("重慶");
v4.setRemarks("無(wú)");
list.add(v4);
StudentExportVo v5 = new StudentExportVo();
v5.setSchool("武漢大學(xué)");
v5.setName("王強(qiáng)");
v5.setSex("男");
v5.setAge("24");
v5.setCity("長(zhǎng)沙");
v5.setRemarks("無(wú)");
list.add(v5);
StudentExportVo v6 = new StudentExportVo();
v6.setSchool("武漢大學(xué)");
v6.setName("趙燕");
v6.setSex("女");
v6.setAge("21");
v6.setCity("深圳");
v6.setRemarks("無(wú)");
list.add(v6);
StudentExportVo v7 = new StudentExportVo();
v7.setSchool("廈門(mén)大學(xué)");
v7.setName("陸仟");
v7.setSex("女");
v7.setAge("21");
v7.setCity("廣州");
v7.setRemarks("無(wú)");
list.add(v7);
return list;
}
二、使用注解的版本
導(dǎo)出的結(jié)果：
對(duì)應(yīng)實(shí)體類(lè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 = {"學(xué)生信息","學(xué)校"}, order = 1)
@ContentLoopMerge(eachRow = 3)
private String school;
@ExcelProperty(value = {"學(xué)生信息","姓名"}, order = 2)
private String name;
@ExcelProperty(value = {"學(xué)生信息","性別"}, order = 3)
private String sex;
@ExcelProperty(value = {"學(xué)生信息","年齡"}, order = 4)
private String age;
@ExcelProperty(value = {"學(xué)生信息","城市"}, order = 5)
private String city;
@ExcelProperty(value = {"學(xué)生信息","備注"}, order = 6)
private String remarks;
}
對(duì)應(yīng)業(yè)務(wù)代碼：同上
@ContentLoopMerge(eachRow = 3) 可以合并單元格，但是他是按指定行數(shù)合并，并不能實(shí)現(xiàn)內(nèi)容相同的合并
@ExcelProperty(value = {“學(xué)生信息”,“備注”}，能實(shí)現(xiàn)多個(gè)標(biāo)題，但標(biāo)題是固定的，不是動(dòng)態(tài)的
三、自定義改造
導(dǎo)出的結(jié)果：
對(duì)應(yīng)實(shí)體類(lè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 = {"學(xué)生信息","學(xué)校"}, order = 1)
//@ContentLoopMerge(eachRow = 3)
private String school;
@ExcelProperty(value = {"學(xué)生信息","姓名"}, order = 2)
private String name;
@ExcelProperty(value = {"學(xué)生信息","性別"}, order = 3)
private String sex;
@ExcelProperty(value = {"學(xué)生信息","年齡"}, order = 4)
private String age;
@ExcelProperty(value = {"學(xué)生信息","城市"}, order = 5)
private String city;
@ExcelProperty(value = {"學(xué)生信息","備注"}, order = 6)
private String remarks;
}
對(duì)應(yīng)業(yè)務(wù)代碼：
@ApiOperation(value = "導(dǎo)出學(xué)生信息", notes = "導(dǎo)出學(xué)生信息")
@PostMapping("/exportStudent")
public void exportStudent(@RequestBody String dynamicTitle, HttpServletResponse response) {
List<StudentExportVo> list = this.getStudentExportVos();
try {
String fileName = "學(xué)生信息";
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};
// 從第二行后開(kāi)始合并
int mergeRowIndex = 2;
//設(shè)置動(dòng)態(tài)標(biāo)題
List<List<String>> headers = this.getHeaders("學(xué)生信息" + dynamicTitle);
// 頭的策略
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
// 背景設(shè)置為白色
headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
/*WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints((short)20);
headWriteCellStyle.setWriteFont(headWriteFont);*/
// 內(nèi)容的策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
// 這里需要指定 FillPatternType 為FillPatternType.SOLID_FOREGROUND 不然無(wú)法顯示背景顏色.頭默認(rèn)了 FillPatternType所以可以不指定
//contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
// 背景綠色
//contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
//設(shè)置 自動(dòng)換行
contentWriteCellStyle.setWrapped(true);
//設(shè)置 垂直居中
contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//設(shè)置 水平居中
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
/*WriteFont contentWriteFont = new WriteFont();
// 字體大小
contentWriteFont.setFontHeightInPoints((short)20);
contentWriteCellStyle.setWriteFont(contentWriteFont);*/
// 這個(gè)策略是 頭是頭的樣式 內(nèi)容是內(nèi)容的樣式 其他的策略可以自己實(shí)現(xiàn)
HorizontalCellStyleStrategy horizontalCellStyleStrategy =
new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
EasyExcel.write(output, StudentExportVo.class)
.sheet("學(xué)生信息")
.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("學(xué)校");
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;
}
//數(shù)據(jù)制造
private List<StudentExportVo> getStudentExportVos() {
List<StudentExportVo> list = new ArrayList<>();
StudentExportVo v1 = new StudentExportVo();
v1.setSchool("北京大學(xué)");
v1.setName("張三");
v1.setSex("男");
v1.setAge("20");
v1.setCity("北京");
v1.setRemarks("無(wú)");
list.add(v1);
StudentExportVo v2 = new StudentExportVo();
v2.setSchool("北京大學(xué)");
v2.setName("李四");
v2.setSex("男");
v2.setAge("22");
v2.setCity("上海");
v2.setRemarks("無(wú)");
list.add(v2);
StudentExportVo v3 = new StudentExportVo();
v3.setSchool("北京大學(xué)");
v3.setName("王五");
v3.setSex("女");
v3.setAge("22");
v3.setCity("青島");
v3.setRemarks("無(wú)");
list.add(v3);
StudentExportVo v4 = new StudentExportVo();
v4.setSchool("清華大學(xué)");
v4.setName("趙六");
v4.setSex("女");
v4.setAge("21");
v4.setCity("重慶");
v4.setRemarks("無(wú)");
list.add(v4);
StudentExportVo v5 = new StudentExportVo();
v5.setSchool("武漢大學(xué)");
v5.setName("王強(qiáng)");
v5.setSex("男");
v5.setAge("24");
v5.setCity("長(zhǎng)沙");
v5.setRemarks("無(wú)");
list.add(v5);
StudentExportVo v6 = new StudentExportVo();
v6.setSchool("武漢大學(xué)");
v6.setName("趙燕");
v6.setSex("女");
v6.setAge("21");
v6.setCity("深圳");
v6.setRemarks("無(wú)");
list.add(v6);
StudentExportVo v7 = new StudentExportVo();
v7.setSchool("廈門(mén)大學(xué)");
v7.setName("陸仟");
v7.setSex("女");
v7.setAge("21");
v7.setCity("廣州");
v7.setRemarks("無(wú)");
list.add(v7);
return list;
}
合并單元格相同內(nèi)容處理類(lèi)：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) {
//當(dāng)前行
int curRowIndex = cell.getRowIndex();
//當(dāng)前列
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;
}
}
}
}
/**
* 當(dāng)前單元格向上合并
*
* @param writeSheetHolder
* @param cell 當(dāng)前單元格
* @param curRowIndex 當(dāng)前行
* @param curColIndex 當(dāng)前列
*/
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();
// 將當(dāng)前單元格數(shù)據(jù)與上一個(gè)單元格數(shù)據(jù)比較
Boolean dataBool = preData.equals(curData);
//此處需要注意：因?yàn)槲沂前凑展こ堂Q(chēng)確定是否需要合并的，所以獲取每一行第二列數(shù)據(jù)和上一行第一列數(shù)據(jù)進(jìn)行比較，如果相等合并，getCell里面的值，是工程名稱(chēng)所在列的下標(biāo)
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);
// 若上一個(gè)單元格已經(jīng)被合并，則先移出原有的合并單元，再重新添加合并單元
if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {
sheet.removeMergedRegion(i);
cellRangeAddr.setLastRow(curRowIndex);
sheet.addMergedRegion(cellRangeAddr);
isMerged = true;
}
}
// 若上一個(gè)單元格未被合并，則新增合并單元
if (!isMerged) {
CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex);
sheet.addMergedRegion(cellRangeAddress);
}
}
}
}
總結(jié)：
1、使用自定義合并相同內(nèi)容單元格時(shí)，實(shí)體類(lèi)中的注解@ContentLoopMerge需要去掉，要不然會(huì)受到影響
2、該段自定義代碼中如內(nèi)容合并、設(shè)置動(dòng)態(tài)標(biāo)題、內(nèi)容、以及每個(gè)列的寬度及字體都是可以根據(jù)自定義策略來(lái)進(jìn)行設(shè)置的，具體用法可以參考代碼注釋說(shuō)明，根據(jù)需要進(jìn)行使用
以上就是Java EasyExcel實(shí)現(xiàn)合并相同內(nèi)容單元格與動(dòng)態(tài)標(biāo)題功能的詳細(xì)內(nèi)容，更多關(guān)于Java EasyExcel合并單元格的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！
您可能感興趣的文章:
- Java使用easyExcel導(dǎo)出excel數(shù)據(jù)案例
- Java使用EasyExcel動(dòng)態(tài)添加自增序號(hào)列
- Java中Easyexcel?實(shí)現(xiàn)批量插入圖片功能
- Java利用EasyExcel實(shí)現(xiàn)合并單元格
- Java使用EasyExcel進(jìn)行單元格合并的問(wèn)題詳解
- Java?easyExcel的復(fù)雜表頭多級(jí)表頭導(dǎo)入
- Java利用EasyExcel解析動(dòng)態(tài)表頭及導(dǎo)出實(shí)現(xiàn)過(guò)程
- Java使用EasyExcel實(shí)現(xiàn)Excel的導(dǎo)入導(dǎo)出
- Java EasyExcel實(shí)現(xiàn)導(dǎo)出多sheet并設(shè)置單元格樣式
- Java實(shí)現(xiàn)讀取Excel文件功能(EasyExcel初使用)
相關(guān)文章
如何使用Java 8 中的 Stream 遍歷樹(shù)形結(jié)構(gòu)
這篇文章主要介紹了使用Java 8中的Stream遍歷樹(shù)形結(jié)構(gòu),我們可以使用Java8中的Stream流一次性把數(shù)據(jù)查出來(lái),然后通過(guò)流式處理,我們一起來(lái)看看,代碼實(shí)現(xiàn)為了實(shí)現(xiàn)簡(jiǎn)單,就模擬查看數(shù)據(jù)庫(kù)所有數(shù)據(jù)到List里面,需要的朋友可以參考下2023-08-08
淺談在頁(yè)面中獲取到ModelAndView綁定的值方法
下面小編就為大家分享一篇淺談在頁(yè)面中獲取到ModelAndView綁定的值方法，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2018-03-03
基于JavaMail實(shí)現(xiàn)郵件發(fā)送
這篇文章主要為大家詳細(xì)介紹了基于JavaMail實(shí)現(xiàn)郵件發(fā)送功能，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2018-03-03
IDEA Maven Mybatis generator 自動(dòng)生成代碼(實(shí)例講解)
下面小編就為大家分享一篇IDEA Maven Mybatis generator 自動(dòng)生成代碼的實(shí)例講解，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2017-12-12
在 MyBatis 中,懶加載（Lazy Loading）和預(yù)加載（Eager Loading）是兩種不同的查詢策略,用于控制關(guān)聯(lián)對(duì)象的加載時(shí)機(jī)和方式,本文給大家詳細(xì)介紹了具體處理方式,需要的朋友可以參考下2025-11-11
SpringBoot參數(shù)校驗(yàn)之@Valid的使用詳解
這篇文章主要通過(guò)示例為大家詳細(xì)介紹一下介紹了SpringBoot參數(shù)校驗(yàn)中@Valid的使用方法，文中的示例代碼講解詳細(xì)，需要的可以參考一下2022-06-06
這篇文章主要為大家詳細(xì)介紹了java實(shí)現(xiàn)掃雷小游戲，文中示例代碼介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2020-01-01
這幾天看SFTP資料時(shí)，無(wú)意中看到了Ganymed SSH-2，寫(xiě)了個(gè)簡(jiǎn)單demo，通過(guò)，感覺(jué)挺好用的，下面就和大家分享下。需要的朋友可以過(guò)來(lái)參考參考2013-08-08
SpringBoot之那些注入不了的Spring占位符(${}表達(dá)式)問(wèn)題
這篇文章主要介紹了SpringBoot之那些注入不了的Spring占位符(${}表達(dá)式)問(wèn)題，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2023-04-04
這篇文章主要介紹了Java 中Flyway的使用詳解,本文給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2020-07-07