目录
1 Maven依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.8</version>
</dependency>
<!--hutool工具包-->
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.5.1</version>
</dependency>2 FreezeRowColModel
冻结行和列信息。
package com.xudongbase.easyexcel.model;
import com.xudongbase.easyexcel.model.common.SheetModel;
import lombok.Getter;
/**
* 冻结行和列信息
*
* @author xudongmaster
*/
@Getter
public class FreezeRowColModel extends SheetModel {
/**
* 显示行号
*/
private int displayRowIndex;
/**
* 显示列号
*/
private int displayColIndex;
/**
* 冻结列数
*/
private int freezeColNum;
/**
* 冻结行数
*/
private int freezeRowNum;
private FreezeRowColModel() {
}
/**
* 创建冻结行信息
*
* @param sheetName sheet页名称
* @param freezeRowNum 冻结行数
* @return
*/
public static FreezeRowColModel createFreezeRowModel(String sheetName, int freezeRowNum) {
return createFreezeRowColModel(sheetName, freezeRowNum, 0, freezeRowNum, 0);
}
/**
* 创建冻结列信息
*
* @param sheetName sheet页名称
* @param freezeColNum 冻结列数
* @return
*/
public static FreezeRowColModel createFreezeColModel(String sheetName, int freezeColNum) {
return createFreezeRowColModel(sheetName, 0, freezeColNum, 0, freezeColNum);
}
/**
* 创建冻结行列信息
*
* @param sheetName sheet页名称
* @param freezeRowNum 冻结行数
* @param freezeColNum 冻结列数
* @return
*/
public static FreezeRowColModel createFreezeRowColModel(String sheetName, int freezeRowNum, int freezeColNum) {
return createFreezeRowColModel(sheetName, freezeRowNum, freezeColNum, freezeRowNum, freezeColNum);
}
/**
* 创建冻结行列信息
*
* @param sheetName sheet页名称
* @param displayRowIndex 显示行号
* @param displayColIndex 显示列号
* @param freezeRowNum 冻结行数
* @param freezeColNum 冻结列数
* @return
*/
public static FreezeRowColModel createFreezeRowColModel(String sheetName, int freezeRowNum, int freezeColNum
, int displayRowIndex, int displayColIndex) {
FreezeRowColModel freezeRowColModel = new FreezeRowColModel();
//sheet页名称
freezeRowColModel.sheetName = sheetName;
//显示行号
freezeRowColModel.displayRowIndex = displayRowIndex;
//显示列号
freezeRowColModel.displayColIndex = displayColIndex;
//冻结行数
freezeRowColModel.freezeRowNum = freezeRowNum;
//冻结列数
freezeRowColModel.freezeColNum = freezeColNum;
return freezeRowColModel;
}
}
3 CustomFreezeRowColHandler
自定义冻结行和列处理器。
package com.xudongbase.easyexcel.handler;
import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import com.xudongbase.easyexcel.model.FreezeRowColModel;
import org.apache.poi.ss.usermodel.Sheet;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;
/**
* 自定义冻结行和列处理器
*
* @author xudongmaster
*/
public class CustomFreezeRowColHandler implements SheetWriteHandler {
/**
* sheet页名称列表
*/
private List<String> sheetNameList;
/**
* 冻结行和列信息
*/
private List<FreezeRowColModel> freezeList = new ArrayList<>();
public CustomFreezeRowColHandler(List<FreezeRowColModel> freezeList) {
if (CollUtil.isEmpty(freezeList)) {
return;
}
this.freezeList = freezeList.stream().filter(x ->
StrUtil.isNotBlank(x.getSheetName()) && x.getDisplayRowIndex() >= 0 && x.getDisplayColIndex() >= 0
&& x.getFreezeColNum() >= 0 && x.getFreezeRowNum() >= 0)
.collect(Collectors.toList());
sheetNameList = this.freezeList.stream().map(x -> x.getSheetName()).distinct().collect(Collectors.toList());
}
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
/**
* sheet页创建之后调用
*
* @param writeWorkbookHolder
* @param writeSheetHolder
*/
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
Sheet sheet = writeSheetHolder.getSheet();
//不需要冻结行列，或者当前sheet页不需要冻结行列
if (CollUtil.isEmpty(freezeList) || sheetNameList.contains(sheet.getSheetName()) == false) {
return;
}
//获取当前sheet的冻结行列
List<FreezeRowColModel> sheetFreezeList = freezeList.stream().filter(x ->
StrUtil.equals(x.getSheetName(), sheet.getSheetName())
).collect(Collectors.toList());
//当前sheet页不需要冻结行列
if (CollUtil.isEmpty(sheetFreezeList)) {
return;
}
for (FreezeRowColModel freezeRowColModel : sheetFreezeList) {
//冻结行数
int freezeRowNum = freezeRowColModel.getFreezeRowNum();
//冻结列数
int freezeColNum = freezeRowColModel.getFreezeColNum();
//显示行号
int displayRowIndex = freezeRowColModel.getDisplayRowIndex();
//显示行索引不能为冻结行
if (displayRowIndex < freezeRowNum) {
displayRowIndex = freezeRowNum;
}
//显示列号
int displayColIndex = freezeRowColModel.getDisplayColIndex();
//显示列索引不能为冻结列
if (displayColIndex < freezeColNum) {
displayColIndex = freezeColNum;
}
sheet.createFreezePane(freezeColNum, freezeRowNum, displayColIndex, displayRowIndex);
}
//删除冻结行列信息
freezeList.removeAll(sheetFreezeList);
sheetNameList = freezeList.stream().map(x -> x.getSheetName()).distinct().collect(Collectors.toList());
}
}4 调试代码
/**
* 测试冻结行和列
*/
@Test
public void testFreezeRowCol() {
try {
File file = new File("D:/easyexcel/testFreezeRowCol.xlsx");
FileUtil.createNewFile(file);
//生成表格数据
List<List<Object>> dataList = new ArrayList<>();
dataList.add(new ArrayList<>(Arrays.asList(new Object[]{"表头11", "表头2", "表头3", "表头4"})));
dataList.add(new ArrayList<>(Arrays.asList(new Object[]{"表头1", "表头2", "表头3", "表头4"})));
dataList.add(new ArrayList<>(Arrays.asList(new Object[]{"表头31", "表头2", "表头3", "表头4"})));
List<FreezeRowColModel> freezeList = new ArrayList<>();
//导出文件
String fileName = new String("文件名称.xls".getBytes(), "UTF-8");
String sheet1Name = "模板";
String sheet2Name = "模板2";
String sheet3Name = "模板3";
freezeList.add(FreezeRowColModel.createFreezeRowColModel(sheet1Name, 1, 2));
freezeList.add(FreezeRowColModel.createFreezeRowModel(sheet2Name, 1));
freezeList.add(FreezeRowColModel.createFreezeColModel(sheet3Name, 1));
FileOutputStream fileOutputStream = new FileOutputStream(file);
ExcelWriter excelWriter = EasyExcel.write(fileOutputStream).registerWriteHandler(new CustomFreezeRowColHandler(freezeList)).build();
WriteSheet writeSheet = EasyExcel.writerSheet(sheet1Name).build();
excelWriter.write(dataList, writeSheet);
WriteSheet writeSheet2 = EasyExcel.writerSheet(sheet2Name).build();
excelWriter.write(dataList, writeSheet2);
WriteSheet writeSheet3 = EasyExcel.writerSheet(sheet3Name).build();
excelWriter.write(dataList, writeSheet3);
// 千万别忘记finish 会帮忙关闭流
excelWriter.finish();
} catch (Exception e) {
e.printStackTrace();
}
}5 调试结果
注：
觉得这篇博客写的不错的可以前往Gitee点个Star，源码请前往Gitee的xudongbase的easyexcel分支。
扫一扫
1037
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
