行冻结
sheet.createFreezePane(colSplit, rowSplit, leftmostColumn, topRow);
其中
colSplit水平拆分位置。
rowSplit拆分的垂直位置。
leftmostClumn右窗格中可见的左列。
topRow底部窗格中可见的顶行。
筛选
方法1
sheet.setAutoFilter(CellRangeAddress.valueOf(autoFilterRange));
其中autoFilterRange是范围:比如
/**
* 设置筛选范围
* (参数A2:D2代表从第二行第A列到第二行第J列要作为筛选框的位置)
*/
public String autoFilterRange = "A2:D2";
方法2
sheet.setAutoFilter(new CellRangeAddress(firstRow,lastRow,firstCol,lastCol));
其中:
firstRow第一行的索引
lastRow最后一行(含)的索引必须等于或大于{@code firstRow}
firstCol第一列的索引
lastCol最后一列(含)的索引必须等于或大于{@code firstCol}
具体代码
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;
/**
* @author leishen
*/
public class FreezeAndFilter implements SheetWriteHandler {
/**
* 水平拆分位置
*/
private int colSplit = 0;
/**
* 拆分的垂直位置
*/
private int rowSplit = 2;
/**
* 右窗格中可见的左列
*/
private int leftmostColumn = 0;
/**
* 底部窗格中可见的顶行
*/
private int topRow = 2;
/**
* 设置筛选范围
* (参数A2:D2代表从第二行第A列到第二行第J列要作为筛选框的位置)
*/
public String autoFilterRange = "A2:D2";
@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));
sheet.setAutoFilter(new CellRangeAddress(1,1,0,3));
}
}
导出指定上述类即可:
EasyExcel.write(out,StudentExport.class).registerWriteHandler(new FreezeAndFilter()).sheet("zyp").doWrite(studentList);