Java使用EasyExcel動態(tài)添加自增序號列

第Java使用EasyExcel動態(tài)添加自增序號列本文將介紹如何通過使用EasyExcel自定義攔截器實(shí)現(xiàn)在最終的Excel文件中新增一列自增的序號列，最終的效果如下：

此外，本文所使用的完整代碼示例已上傳到GitHub。

實(shí)現(xiàn)

本文主要是通過自定義一個繼承AbstractRowWriteHandler的攔截器來實(shí)現(xiàn)在最終導(dǎo)出的結(jié)果中新增序號列，通過修改源碼中保存頭部標(biāo)題的Map內(nèi)容來給自己添加的序號列留出位置，先展示最終的代碼：

*自定義excel行處理器,增加序號列

*@authorbutterfly

*@date2025-09-05

@Component

publicclassAddNoHandlerextendsAbstractRowWriteHandler{

privatebooleaninit=true;

@Override

publicvoidbeforeRowCreate(WriteSheetHolderwriteSheetHolder,WriteTableHolderwriteTableHolder,

IntegerrowIndex,IntegerrelativeRowIndex,BooleanisHead){

if(init){

//修改存儲頭部及對應(yīng)字段信息的map,將其中的內(nèi)容均右移一位,給新增的序列號預(yù)留為第一列

ExcelWriteHeadPropertyexcelWriteHeadProperty=writeSheetHolder.excelWriteHeadProperty();

MapInteger,HeadheadMap=excelWriteHeadProperty.getHeadMap();

MapInteger,ExcelContentPropertycontentMap=excelWriteHeadProperty.getContentPropertyMap();

intsize=headMap.size();

for(intcurrent=size;currentcurrent--){

intprevious=current-1;

headMap.put(current,headMap.get(previous));

contentMap.put(current,contentMap.get(previous));

//空出第一列

headMap.remove(0);

contentMap.remove(0);

//只需要修改一次map即可,故使用init變量進(jìn)行控制

init=false;

@Override

publicvoidafterRowCreate(WriteSheetHolderwriteSheetHolder,WriteTableHolderwriteTableHolder,Rowrow,

IntegerrelativeRowIndex,BooleanisHead){

//在行創(chuàng)建完成后添加序號列

Cellcell=row.createCell(0);

introwNum=row.getRowNum();

if(rowNum==0){

cell.setCellValue(ExcelConstant.TITLE);

}else{

cell.setCellValue(rowNum);

@Override

publicvoidafterRowDispose(WriteSheetHolderwriteSheetHolder,WriteTableHolderwriteTableHolder,Rowrow,

IntegerrelativeRowIndex,BooleanisHead){

if(row.getLastCellNum()1){

//將自定義新增的序號列的樣式設(shè)置與默認(rèn)的樣式一致

row.getCell(0).setCellStyle(row.getCell(1).getCellStyle());

}

afterRowCreate和afterRowDispose方法中的內(nèi)容很好理解，一個用于控制控制新增序號列的內(nèi)容，一個用于控制新增列的樣式。而beforeRowCreate中的內(nèi)容則稍微復(fù)雜，主要用于給新增的序號列空出位置。同時，由于beforeRowCreate會在每次創(chuàng)建行時均會被調(diào)用，但是原始的存儲頭部信息的Map只需要修改一次，因此這里通過使用init變量控制只會修改一次。

思路

通過查看com.alibaba.excel.write.executor.ExcelWriteAddExecutor類中的addOneRowOfDataToExcel方法源碼，可以看到在新增行的前后會分別調(diào)用beforeRowCreate和afterRowCreate方法，并且在一行數(shù)據(jù)添加完成后會調(diào)用afterRowDispose，這也是上述攔截器生效的原理，源碼如下：

privatevoidaddOneRowOfDataToExcel(ObjectoneRowData,intn,intrelativeRowIndex,

MapInteger,FieldsortedAllFiledMap){

//行數(shù)據(jù)為空,直接返回

if(oneRowData==null){

return;

//創(chuàng)建數(shù)據(jù)行對象,同時分別在創(chuàng)建行前后調(diào)用攔截器

WriteHandlerUtils.beforeRowCreate(writeContext,n,relativeRowIndex,Boolean.FALSE);

Rowrow=WorkBookUtil.createRow(writeContext.writeSheetHolder().getSheet(),n);

WriteHandlerUtils.afterRowCreate(writeContext,row,relativeRowIndex,Boolean.FALSE);

//將實(shí)體數(shù)據(jù)內(nèi)容填充到行中

if(oneRowDatainstanceofList){

addBasicTypeToExcel((List)oneRowData,row,relativeRowIndex);

}else{

//下面會繼續(xù)查看這個方法

addJavaObjectToExcel(oneRowData,row,relativeRowIndex,sortedAllFiledMap);

//行創(chuàng)建完成后,調(diào)用相應(yīng)攔截器

WriteHandlerUtils.afterRowDispose(writeContext,row,relativeRowIndex,Boolean.FALSE);

而之所以我們修改headMap和contentMap的內(nèi)容就可以實(shí)現(xiàn)最終效果，只需要繼續(xù)查看該類中addJavaObjectToExcel方法的代碼即可知道原因：

privatevoidaddJavaObjectToExcel(ObjectoneRowData,Rowrow,intrelativeRowIndex,

MapInteger,FieldsortedAllFiledMap){

WriteHoldercurrentWriteHolder=writeContext.currentWriteHolder();

//將自己的實(shí)體數(shù)據(jù)映射到beanMap

BeanMapbeanMap=BeanMap.create(oneRowData);

SetStringbeanMapHandledSet=newHashSetString

intcellIndex=0;

//Ifit'saclassitneedstobecastbytype

if(HeadKindEnum.CLASS.equals(writeContext.currentWriteHolder().excelWriteHeadProperty().getHeadKind())){

//我們修改的就是這里的headMap和contentPropertyMap內(nèi)容

MapInteger,HeadheadMap=writeContext.currentWriteHolder().excelWriteHeadProperty().getHeadMap();

MapInteger,ExcelContentPropertycontentPropertyMap=

writeContext.currentWriteHolder().excelWriteHeadProperty().getContentPropertyMap();

//遍歷所有的列頭,插入數(shù)據(jù)

for(Map.EntryInteger,ExcelContentPropertyentry:contentPropertyMap.entrySet()){

//獲取cell的下標(biāo),后續(xù)將內(nèi)容插入指定的列

cellIndex=entry.getKey();

ExcelContentPropertyexcelContentProperty=entry.getValue();

Stringname=excelContentProperty.getField().getName();

if(!beanMap.containsKey(name)){

continue;

//控制單元格的內(nèi)容

Headhead=headMap.get(cellIndex);

WriteHandlerUtils.beforeCellCreate(writeContext,row,head,cellIndex,

relativeRowIndex,Boolean.FALSE);

Cellcell=WorkBookUtil.createCell(row,cellIndex);

WriteHandlerUtils.afterCellCreate(writeContext,cell,head,relativeRowIndex,Boolean.FALSE);

Objectvalue=beanMap.get(name);

CellDatacellData=converterAndSet(currentWriteHolder,excelContentProperty.getField().getType(),

cell,value,excelContentProperty,head,relativeRowIndex);

WriteHandlerUtils.afterCellDispose(writeContext,cellData,cell,head,

relativeRowIndex,Boolean.FALSE);

beanMapHandledSet.add(name);

//省略了后面無關(guān)的內(nèi)容

其它

通過以上自定義的攔截器，就可以寫一個簡單的demo進(jìn)行測試：

*Excel下載控制器

*@authorbutterfly

*@date2025-09-05

@RestController

publicclassExcelController{

*添加序號列測試

*@paramresponseresponse

@GetMapping("/col")

publicvoidcol(HttpServletResponseresponse){

try{

ListStudentstudents=getStudentList();

EasyExcel.write(response.getOutputStream(),Student.class)

.registerWriteHandler(newAddNoHandler())

.sheet()

.doWrite(students);

}catch(Exceptione){

System.out.println(ExcelConstant.DOWNLOAD_FAILED);

*生成學(xué)生列表

*@return學(xué)生列表

privateListStudentgetStudentList(){

returnArrays.asList(

newStudent("2025090101","張三",19),

newStudent("2025090102","李四",18),

newStudent("2025090103","王二",20)

然后是前端的測試代碼：

!DOCTYPEhtml

htmllang="en"

head

metacharset="UTF-8"

title文件下載測試/title

scriptsrc="/axios/0.21.2/axios.min.js"/script

/head

body

button添加序號列測試/button

script

functioncol(){

download('http://localhost:8080/col','col.xlsx')

functiondownload(url,name){

axios({

url:url,

responseType:'blob'

}).then((re