需求背景：
? ? ? ? 導(dǎo)出excel的同時(shí)凍結(jié)表頭和前兩列基礎(chǔ)信息，方便導(dǎo)出后用戶查看信息。
一、技術(shù)選型：
? ? ? ? easyExcel的自定義寫策略處理：SheetWriteHandler
二、方案設(shè)計(jì)：（基于實(shí)現(xiàn) SheetWriteHandler 接口）
? ? ? ? 1、重寫afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder)方法
? ? ? ? 2、通過?writeSheetHolder.getSheet() 獲取 sheet，通過 sheet.createFreezePane() 方法設(shè)置導(dǎo)出excel指定凍結(jié)行和列，通過?sheet.setAutoFilter() 給指定導(dǎo)出的excel單元格設(shè)置自動(dòng)篩選器.
三、代碼實(shí)現(xiàn)：
3.1:pom.xml
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.3.2</version>
</dependency>
3.2: API
@ApiOperation(value = "導(dǎo)出")
@GetMapping(value = "/export")
public void exportExcel(HttpServletResponse response,@RequestBody TestParam param) {
service.export(response, param);
}3.3:工具類 IEasyExcelServicey
import com.alibaba.excel.write.handler.WriteHandler;
import javax.servlet.http.HttpServletResponse;
import java.util.List;
/**
* @author c
*/
public interface IEasyExcelService {
/**
* 導(dǎo)出excel方法
*
* @param exportData 需要導(dǎo)出的數(shù)據(jù)
* @param response response
* @param tClass 導(dǎo)出excel的字段實(shí)體類
* @param fileName 文件名字
* @param sheetName sheet名字
*/
<T> void exportExcel(List<T> exportData, HttpServletResponse response, Class<T> tClass, String fileName, String sheetName);
/**
* 導(dǎo)出excel方法 (攜帶自定義策略)
* @param exportData 需要導(dǎo)出的數(shù)據(jù)
* @param response HttpServletResponse
* @param tClass 導(dǎo)出excel的字段實(shí)體類
* @param fileName 文件名字
* @param sheetName sheet名字
* @param writeHandler 自定義策略（可擴(kuò)展多個(gè)）
* @param <T> T
*/
<T> void exportExcelWithHandler(List<T> exportData, HttpServletResponse response, Class<T> tClass, String fileName, String sheetName, WriteHandler writeHandler);
}?工具類：EasyExcelServiceImpl
@Slf4j
public class EasyExcelServiceImpl implements IEasyExcelService {
/**
* 本地轉(zhuǎn)：response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileNameEncoder + ".xlsx");
* @param exportData 需要導(dǎo)出的數(shù)據(jù)
* @param response response
* @param tClass 導(dǎo)出excel的字段實(shí)體類
* @param fileName 文件名字
* @param sheetName sheet名字
* @param <T> T
*/
@Override
public <T> void exportExcel(List<T> exportData, HttpServletResponse response, Class<T> tClass, String fileName, String sheetName){
try{
// 使用swagger 會(huì)導(dǎo)致各種問題，直接用瀏覽器或者用postman
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
// fileName encoder
String fileNameEncoder = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileNameEncoder + ".xlsx");
response.setHeader("Access-Control-Expose-Headers", "Content-disposition");
// write to excel
EasyExcelFactory.write(response.getOutputStream(), tClass)
.autoCloseStream(Boolean.FALSE)
.sheet(sheetName)
.doWrite(exportData);
}catch (Exception e){
log.error("EasyExcelServiceImpl->exportExcel error, message is :{}", e.getMessage());
}
}
@Override
public <T> void exportExcelWithHandler(List<T> exportData, HttpServletResponse response, Class<T> tClass, String fileName, String sheetName, WriteHandler writeHandler){
try{
// 使用swagger 會(huì)導(dǎo)致各種問題，直接用瀏覽器或者用postman
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
// fileName encoder
String fileNameEncoder = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileNameEncoder + ".xlsx");
response.setHeader("Access-Control-Expose-Headers", "Content-disposition");
// write to excel
EasyExcelFactory.write(response.getOutputStream(), tClass)
.autoCloseStream(Boolean.FALSE)
// 自定義策略(支持?jǐn)U展多個(gè))
.registerWriteHandler(writeHandler)
.sheet(sheetName)
.doWrite(exportData);
}catch (Exception e){
log.error("EasyExcelServiceImpl->exportExcel error, message is :{}", e.getMessage());
}
}
}3.4: 自定義Handle:（自定義設(shè)置導(dǎo)出excel設(shè)置凍結(jié)列和列以及是否自動(dòng)加篩選器）
/**
* easyExcel：export handle
* freeze row and col with set auto filter range
* @author c
* @date: 2024-1-5 13:44:26
*/
public class FreezeRowColHandler implements SheetWriteHandler {
private final FreezeRowColOptions options;
public FreezeRowColHandler(FreezeRowColOptions options) {
this.options = options;
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
Sheet sheet = writeSheetHolder.getSheet();
sheet.createFreezePane(options.getColSplit(), options.getRowSplit(), options.getLeftmostColumn(), options.getTopRow());
if (null != options.getAutoFilterRange()) {
sheet.setAutoFilter(CellRangeAddress.valueOf(options.getAutoFilterRange()));
}
}
}參數(shù)：FreezeRowColOptions
@Data
@AllArgsConstructor
@NoArgsConstructor
public class FreezeRowColOptions {
/**
* Horizontal position of split
*/
private Integer colSplit;
/**
* Vertical position of split
*/
private Integer rowSplit;
/**
* Left column visible in right pane
*/
private Integer leftmostColumn;
/**
* Top row visible in bottom pane
*/
private Integer topRow;
/**
* auto filter range
*/
private String autoFilterRange;
}參數(shù)解釋和示例：
四個(gè)參數(shù)分別代表:
colSplit:表示要凍結(jié)的列數(shù)；
rowSplit:表示要凍結(jié)的行數(shù)；
leftmostColumn:表示被固定列右邊第一列的列號；
topRow:表示被固定行下邊第一列的行號;
舉例：
CreateFreezePane(0,1,0,1):凍結(jié)第一行,凍結(jié)行下側(cè)第一行的左邊框顯示“2”
CreateFreezePane(1,0,1,0):凍結(jié)第一列，凍結(jié)列右側(cè)的第一列為B列
CreateFreezePane(2,0,5,0):凍結(jié)左側(cè)兩列，凍結(jié)列右側(cè)的第一列為F列可以自定義 FreezeRowColConstant 方便維護(hù)，如下
/**
* handle constant：export FreezeRowColConstant
* freeze row and col with set auto filter range constant
* @author c
* @date: 2024-1-5 14:22:21
*/
public class FreezeRowColConstant {
/**
* TEST_ONE export
*/
public static final FreezeRowColOptions TEST_ONE = new FreezeRowColOptions(2, 2, 0, 0, "A2:BH2");
/**
* TEST_TWO export
*/
public static final FreezeRowColOptions TEST_TWO = new FreezeRowColOptions(2, 2, 0, 0, "A2:AC2");
/**
* TEST_THREE export
*/
public static final FreezeRowColOptions TEST_THREE = new FreezeRowColOptions(2, 2, 0, 0, "A2:T2");
}ITestExportService：(這里可以根據(jù)自己的業(yè)務(wù)進(jìn)行自定義，本文定義這個(gè)是因?yàn)樵谕粋€(gè)業(yè)務(wù)里面有幾個(gè)類似的導(dǎo)出可以共用這個(gè)導(dǎo)出接口)
public interface ITestExportService {
/**
* export data
* @param response HttpServletResponse
* @param param export common param
* @throws BaseException ex
*/
default void exportData(HttpServletResponse response, TestParam param) {}
}
下面給出其中一個(gè)導(dǎo)出的實(shí)現(xiàn)方法：
@Service
@Slf4j
public class TestOneServiceImpl implements ITestExportService {
private static final String FILE_NAME_TEST = "測試";
private final IEasyExcelService easyExcelService;
public TestOneServiceImpl (IEasyExcelService easyExcelService) {
this.easyExcelService = easyExcelService;
}
@Override
public void exportData(HttpServletResponse response, TestParam param) {
// 這里是根據(jù)查詢參數(shù)param獲取需要導(dǎo)出的數(shù)據(jù)
// get data
List<DemoExcelData> data = this.ExportData(param);
// export：調(diào)用 exportExcelWithHandler 導(dǎo)出
easyExcelService.exportExcelWithHandler(data, response, DemoExcelData.class, FILE_NAME_TEST, FILE_NAME_TEST,
new FreezeRowColHandler(FreezeRowColConstant.TEST_ONE));
}
}實(shí)現(xiàn)效果：(都加上了自動(dòng)篩選器?)
下面是示例，在TestOneServiceImpl 方法中調(diào)用exportExcelWithHandler方法的時(shí)候?qū)嶋H用的是FreezeRowColConstant.TEST_ONE，其中?FreezeRowColConstant.TEST_ONE 設(shè)置的是?new FreezeRowColOptions(2, 2, 0, 0, "A2:BH2")，其含義是：
? ? ? ? 第一個(gè)參數(shù)：表示凍結(jié)前兩行
? ? ? ? 第二個(gè)參數(shù)：表示凍結(jié)前兩列?
????????"A2:BH2"：表示單元格"A2:BH2"區(qū)間設(shè)置添加自動(dòng)篩選器
表頭固定效果：
?前兩列固定效果：
相關(guān)文章推薦：
【EasyExcel】導(dǎo)出excel并支持自定義設(shè)置數(shù)據(jù)行背景顏色等_easyexcel rrg背景-CSDN博客文章來源：http://www.zghlxwxcb.cn/news/detail-821492.html
?? ? ? ???????????????????????????如果對你有幫助，給博主一個(gè)免費(fèi)的點(diǎn)贊以示鼓勵(lì)
????????????????????????????????????????????????歡迎各位??點(diǎn)贊??評論收藏??文章來源地址http://www.zghlxwxcb.cn/news/detail-821492.html
到了這里，關(guān)于【EasyExcel】導(dǎo)出excel凍結(jié)表頭和凍結(jié)指定列并支持篩選器的文章就介紹完了。如果您還想了解更多內(nèi)容，請?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
