step1:
实现SheetWriteHandler
package com.example.demo.excel;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
public class FreezeAndFilter implements SheetWriteHandler {
public int colSplit = 0, rowSplit = 1, leftmostColumn = 0, topRow = 1;
public String autoFilterRange = "1:1";
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
Sheet sheet = writeSheetHolder.getSheet();
sheet.createFreezePane(colSplit, rowSplit, leftmostColumn, topRow);
sheet.setAutoFilter(CellRangeAddress.valueOf(autoFilterRange));
}
}
step2:
如何使用:
@GetMapping("download")
public void download(HttpServletResponse response) throws IOException {
// 这里注意 有同学反应使用swagger 会导致各种问题,请直接用浏览器或者用postman
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
String fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
int[] mergeRowIndex = {0,1};
EasyExcel.write(response.getOutputStream(), TableBaseInfo.class)
.registerWriteHandler(new ExcelFillCellMergeStrategy(1, mergeRowIndex))
.registerWriteHandler(new FreezeAndFilter()).sheet("模板").doWrite(data());
}