目录
Maven依赖
<!--easy excel-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.3.2</version>
</dependency>
<!-- excel工具 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
<!--lombok-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.16.10</version>
<scope>provided</scope>
</dependency>
<!--工具集-->
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.8.12</version>
</dependency>
实体类
import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import lombok.Data;
import java.io.Serializable;
import java.util.Date;
/**
* entity实体类
*
* @Package_Name
* @Author Leslie Lee
* @TIME
* @Version 1.0
*/
@Data
@ExcelIgnoreUnannotated //忽略未标注的字段
public class EntityDemo implements Serializable {
private static final long serialVersionUID = 1L;
@ExcelProperty("姓名")
private String name;//
private String age;//
@ExcelProperty("申请日期")
@DateTimeFormat("yyyy-MM-dd")//时间格式
private Date applicationDate;//申请日期
}
表头列宽自适应处理器
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import java.util.List;
/**
* 表头自动列宽
*
* @Package_Name
* @Author Leslie Lee
* @TIME
* @Version 1.0
*/
public class ExcelWidthStyleStrategy extends AbstractColumnWidthStyleStrategy {
// 统计setColumnWidth被调用多少次
private static int count = 0;
@Override
protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head,
Integer relativeRowIndex, Boolean isHead) {
// 简单设置
Sheet sheet = writeSheetHolder.getSheet();
sheet.setColumnWidth(cell.getColumnIndex(), 5000);
}
}
行列冻结处理器
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;
/**
* excel导出,冻结列和行 处理器
*
* @Package_Name
* @Author Leslie Lee
* @TIME
* @Version 1.0
*/
public class FreezeRowColHandler implements SheetWriteHandler {
/**
* colSplit:表示要冻结的列数;
* rowSplit:表示要冻结的行数;
* firstCellNum:表示被固定列右边第一列的列号;
* firstRollNum :表示被固定行下边第一列的行号;
*/
private int colSplit = 0, rowSplit = 1, firstCellNum = 0, firstRollNum = 0;
/* 空参构造,不传值默认冻结一行 */
public FreezeRowColHandler() {
}
public FreezeRowColHandler(int colSplit, int rowSplit, int firstCellNum, int firstRollNum) {
this.colSplit = colSplit;
this.rowSplit = rowSplit;
this.firstCellNum = firstCellNum;
this.firstRollNum = firstRollNum;
}
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
Sheet sheet = writeSheetHolder.getSheet();
sheet.createFreezePane(colSplit, rowSplit, firstCellNum, firstRollNum);
}
}
合并单元格处理器
import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.ReflectUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import java.lang.reflect.Field;
import java.util.List;
/**
* easyExcel合并单元格
*
* @Package_Name
* @Author Leslie Lee
* @TIME
* @Version 1.0
*/
@Slf4j
public class ExcelCellMergeStrategy implements SheetWriteHandler {
private List<CellRangeAddress> addressList;
public ExcelCellMergeStrategy() {
}
public ExcelCellMergeStrategy(List<CellRangeAddress> addressList) {
this.addressList = addressList;
}
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
/* 执行合并 */
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
Sheet sheet = writeSheetHolder.getSheet();
//打印合并信息
// log.info("ExcelCellMergeStrategy|start|合并单元格|sheet名称:{}|开始执行", sheet.getSheetName());
for (CellRangeAddress address : addressList) {
sheet.addMergedRegionUnsafe(address);
}
}
/**
* 合并单元格策略,根据首列相同,合并指定列数
*
* @param mergeColumnIndexes 合并列数 前{x}列
* @param tableHeaderRows 表头行数
* @param list 导出的数据
* @param <T> 导出数据-实体类
* @return 合并范围
*/
public static <T> List<CellRangeAddress> getOneColAddressList(int mergeColumnIndexes, int tableHeaderRows, List<T> list) {
List<CellRangeAddress> addressList = CollUtil.newArrayList();
if (CollUtil.isNotEmpty(list)) {
Class<?> aClass = list.get(0).getClass();
Field f = null;
Field[] fields = aClass.getDeclaredFields();
for (Field field : fields) {
if (field.isAnnotationPresent(ExcelProperty.class)) {
f = field;
break;
}
}
if (f != null) {
//list长度,最大行数
int size = list.size();
//防止下标越界使用
int i2 = size - 2;
for (int i = 0; i < size; i++) {
//开始行 从 不合并标题
int firstRow = i + tableHeaderRows;
//结束行
int lastRow = firstRow;
//开始列,结束列
//int firstCol,lastCol = 0;
String value = StrUtil.toString(ReflectUtil.getFieldValue(list.get(i), f));
boolean flag = true;
while (flag) {
//最后一行不再进行匹配,直接退出
if (i > i2) {
break;
}
String value1 = StrUtil.toString(ReflectUtil.getFieldValue(list.get(i + 1), f));
//值相同则合并 -> 合并行数+1 下标+1遍历跳过相同行
if (StrUtil.equals(value1, value)) {
i++;
lastRow++;
} else {
flag = false;
}
if (i == size) {
flag = false;
}
}
if (lastRow != firstRow) {
for (int i1 = 0; i1 < mergeColumnIndexes; i1++) {
addressList.add(new CellRangeAddress(firstRow, lastRow, i1, i1));
}
}
}
}
}
return addressList;
}
}
工具类
import com.alibaba.excel.EasyExcel;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;
/**
* @Package_Name
* @Author Leslie Lee
* @TIME
* @Version 1.0
*/
public class EasyExcelUtils<T> {
/**
* 分片导出
*
* @param response
* @param list
* @param sheetName
* @throws IOException
*/
public static void downloadFragmentation2(HttpServletResponse response, List<?> list, String sheetName, int mergeColumnIndexs, int hdrRow) throws IOException {
Class<?> clazz = list.get(0).getClass();
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
// URLEncoder.encode 防止中文乱码 和easyexcel没有关系
String fileName = URLEncoder.encode(sheetName, "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
// 定义每次写入的数据量
int batchSize = 2000;
// 定义写入的起始位置
int start = 0;
// 定义写入的结束位置
int end = 0;
while (end < list.size()) {
// 计算下一次写入的结束位置
end = Math.min(start + batchSize, list.size());
// 获取本次写入的数据
List<?> subList = list.subList(start, end);
// 写入数据
EasyExcel.write(response.getOutputStream(), clazz)
/* 列宽自适应 */
.registerWriteHandler(new ExcelWidthStyleStrategy())
/* 行列冻结 */
.registerWriteHandler(new FreezeRowColHandler())
/* 单元格合并 */
.registerWriteHandler(new ExcelCellMergeStrategy(getOneColAddressList(mergeColumnIndexs,hdrRow,list)))
.sheet(sheetName).doWrite(list);
// 更新起始位置
start = end;
}
}
}
Leslie Lee 随笔