前言
功能展示
功能类似这样：
一、为什么选择Alibaba EasyExcel
优势：
EasyExcel 重写了poi对07版Excel的解析，并且再大的excel也不会出现内存溢出
导入EasyExcel依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.1.5</version>
</dependency>
二、easyexcel 注解合并单元格
@ContentLoopMerge：标注在字段上
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface ContentLoopMerge {
/**
* 合并行
*
* @return
*/
int eachRow() default 1;
/**
* 合并列
*
* @return
*/
int columnExtend() default 1;
}
OnceAbsoluteMerge：标注在类上
@Target({ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface OnceAbsoluteMerge {
/**
* 初始行
*
* @return
*/
int firstRowIndex() default -1;
/**
* 最后一行
*
* @return
*/
int lastRowIndex() default -1;
/**
* 初始列
*
* @return
*/
int firstColumnIndex() default -1;
/**
* 最后一列
*
* @return
*/
int lastColumnIndex() default -1;
}
2.1 @ContentLoopMerge示例
@Data
public class Demo implements Serializable {
@ExcelProperty(value = "商户名称", index = 0)
@ColumnWidth(15)
@ContentStyle(verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.CENTER)
private String appName;
@ExcelProperty(value = "城市名称", index = 1)
@ColumnWidth(15)
@ContentStyle(verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.CENTER)
@ContentLoopMerge(eachRow = 2, columnExtend = 3)
private String cityName;
@ExcelProperty(value = "区域名称", index = 2)
@ColumnWidth(15)
@ContentStyle(verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.CENTER)
private String regionName;
@ExcelProperty(value = "商圈名称", index = 3)
@ColumnWidth(15)
@ContentStyle(verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.CENTER)
private String businessAreaName;
@ExcelProperty(value = "楼盘名称", index = 4)
@ColumnWidth(15)
@ContentStyle(verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.CENTER)
private String gardenName;
@ExcelProperty(value = "楼栋名称", index = 5)
@ColumnWidth(15)
@ContentStyle(verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.CENTER)
private String buildingName;
@ExcelProperty(value = "单元名称", index = 6)
@ColumnWidth(15)
@ContentStyle(verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.CENTER)
private String unitName;
@ExcelProperty(value = "价格", index = 7)
@ColumnWidth(15)
@ContentStyle(verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.CENTER)
private Integer price;
}
2.2 @OnceAbsoluteMerge示例
@Data
@OnceAbsoluteMerge(firstRowIndex = 1, lastRowIndex = 3 , firstColumnIndex = 1 , lastColumnIndex = 3)
public class Demo implements Serializable {
@ExcelProperty(value = "商户名称", index = 0)
@ColumnWidth(15)
@ContentStyle(verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.CENTER)
private String appName;
@ExcelProperty(value = "城市名称", index = 1)
@ColumnWidth(15)
@ContentStyle(verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.CENTER)
private String cityName;
@ExcelProperty(value = "区域名称", index = 2)
@ColumnWidth(15)
@ContentStyle(verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.CENTER)
private String regionName;
@ExcelProperty(value = "商圈名称", index = 3)
@ColumnWidth(15)
@ContentStyle(verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.CENTER)
private String businessAreaName;
@ExcelProperty(value = "楼盘名称", index = 4)
@ColumnWidth(15)
@ContentStyle(verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.CENTER)
private String gardenName;
@ExcelProperty(value = "楼栋名称", index = 5)
@ColumnWidth(15)
@ContentStyle(verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.CENTER)
private String buildingName;
@ExcelProperty(value = "单元名称", index = 6)
@ColumnWidth(15)
@ContentStyle(verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.CENTER)
private String unitName;
@ExcelProperty(value = "价格", index = 7)
@ColumnWidth(15)
@ContentStyle(verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.CENTER)
private Integer price;
}
注意：
- @ContentLoopMerge(eachRow = 2, columnExtend = 3) 可以合并单元格，但是他是按指定行数和指定列数去合并，并不能实现内容相同的合并。
- @OnceAbsoluteMerge(firstRowIndex = 1, lastRowIndex = 3 , firstColumnIndex = 1 , lastColumnIndex = 3) 可以合并单元格，但是他是按指定行数和指定列数去合并，并不能实现内容相同的合并。
- @ExcelProperty(value = {"学生信息","备注"}，能实现多个标题，但标题是固定的，不是动态的。
三、自定义改造
3.1 导出的实体类，也就是表头
@Data
public class Demo implements Serializable {
@ExcelProperty(value = "商户名称", index = 0)
@ColumnWidth(15)
@ContentStyle(verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.CENTER)
private String appName;
@ExcelProperty(value = "城市名称", index = 1)
@ColumnWidth(15)
@ContentStyle(verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.CENTER)
private String cityName;
@ExcelProperty(value = "区域名称", index = 2)
@ColumnWidth(15)
@ContentStyle(verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.CENTER)
private String regionName;
@ExcelProperty(value = "商圈名称", index = 3)
@ColumnWidth(15)
@ContentStyle(verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.CENTER)
private String businessAreaName;
@ExcelProperty(value = "楼盘名称", index = 4)
@ColumnWidth(15)
@ContentStyle(verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.CENTER)
private String gardenName;
@ExcelProperty(value = "楼栋名称", index = 5)
@ColumnWidth(15)
@ContentStyle(verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.CENTER)
private String buildingName;
@ExcelProperty(value = "单元名称", index = 6)
@ColumnWidth(15)
@ContentStyle(verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.CENTER)
private String unitName;
@ExcelProperty(value = "价格", index = 7)
@ColumnWidth(15)
@ContentStyle(verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.CENTER)
private Integer price;
}
3.2 行合并工具类
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import java.util.List;
/**
* @Author: huangyibo
* @Date: 2023/3/30 9:45
* @Description: Excel单元格行合并处理策略
*/
public class ExcelFillCellRowMergeStrategy implements CellWriteHandler {
//数组存放这一行需要合并那几列 [0,1,2] 在这mergeRowIndex行中合并 0、1、2列
private int[] mergeColumnIndex;
// 存放需要向上合并的行
private int mergeRowIndex;
// 不要合并的行
private Integer noMergeRowIndex;
public ExcelFillCellRowMergeStrategy() {
}
public ExcelFillCellRowMergeStrategy(int mergeRowIndex, int[] mergeColumnIndex) {
this.mergeRowIndex = mergeRowIndex;
this.mergeColumnIndex = mergeColumnIndex;
}
public ExcelFillCellRowMergeStrategy(int mergeRowIndex, int[] mergeColumnIndex, Integer noMergeRowIndex) {
this.mergeColumnIndex = mergeColumnIndex;
this.mergeRowIndex = mergeRowIndex;
this.noMergeRowIndex = noMergeRowIndex;
}
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
}
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
}
@Override
public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, WriteCellData<?> cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
}
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
int curRowIndex = cell.getRowIndex();
int curColIndex = cell.getColumnIndex();
if (curRowIndex > mergeRowIndex) {
for (int i = 0; i < mergeColumnIndex.length; i++) {
if (curColIndex == mergeColumnIndex[i]) {
mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);
break;
}
}
}
}
/**
* 当前单元格向上合并
* .
* @param writeSheetHolder writeSheetHolder
* @param cell 当前单元格
* @param curRowIndex 当前行
* @param curColIndex 当前列
*/
private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
Object curData = cell.getCellType() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
Row preRow = cell.getSheet().getRow(curRowIndex - 1);
if (preRow == null) {
// 当获取不到上一行数据时，使用缓存sheet中数据
preRow = writeSheetHolder.getCachedSheet().getRow(curRowIndex - 1);
}
Cell preCell = preRow.getCell(curColIndex);
Object preData = preCell.getCellType() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
//不需要合并的列直接跳出
if ((noMergeRowIndex != null) && noMergeRowIndex == (curRowIndex - 1)){
return;
}
// 将当前单元格数据与上一个单元格数据比较
boolean dataBool = preData.equals(curData);
//此处需要注意：所以获取每一行第一列数据和上一行第一列数据进行比较，如果相等合并
boolean equals = cell.getRow().getCell(0).getStringCellValue().equals(cell.getSheet().getRow(curRowIndex - 1).getCell(0).getStringCellValue());
if (dataBool && equals) {
Sheet sheet = writeSheetHolder.getSheet();
List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
boolean isMerged = false;
for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
CellRangeAddress cellRangeAddr = mergeRegions.get(i);
// 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元
if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {
sheet.removeMergedRegion(i);
cellRangeAddr.setLastRow(curRowIndex);
sheet.addMergedRegion(cellRangeAddr);
isMerged = true;
}
}
// 若上一个单元格未被合并，则新增合并单元
if (!isMerged) {
CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex);
sheet.addMergedRegion(cellRangeAddress);
}
}
}
}
3.3 列合并的工具类
@Data
@AllArgsConstructor
public class CellLineRange {
/**
* 起始列
*/
private int firstCol;
/**
* 结束列
*/
private int lastCol;
}
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import java.util.List;
/**
* @Author: huangyibo
* @Date: 2023/3/30 10:30
* @Description: Excel单元格列合并处理策略
*/
public class ExcelFillCelColumnMergeStrategy implements CellWriteHandler {
//自定义合并单元格的列 如果想合并 第4列和第5例 、第6列和第7例： [CellLineRange(firstCol=3, lastCol=4), CellLineRange(firstCol=5, lastCol=6)]
private List<CellLineRange> cellLineRangeList;
//自定义合并单元格的开始的行 一般来说填表头行高0 表示从表头下每列开始合并 ：如表头行高位为3则 int mergeRowIndex = 2 ;
private int mergeRowIndex;
public ExcelFillCelColumnMergeStrategy(int mergeRowIndex, List<CellLineRange> cellLineRangeList) {
this.mergeRowIndex=mergeRowIndex;
this.cellLineRangeList=cellLineRangeList;
}
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {
}
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) {
}
@Override
public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, WriteCellData<?> cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
}
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer integer, Boolean aBoolean) {
//当前单元格的行数
int curRowIndex = cell.getRowIndex();
// 当前单元格的列数
int curColIndex = cell.getColumnIndex();
if (curRowIndex > mergeRowIndex) {
for (int i = 0; i < cellLineRangeList.size(); i++) {
if (curColIndex > cellLineRangeList.get(i).getFirstCol()&&curColIndex<=cellLineRangeList.get(i).getLastCol()) {
//单元格数据处理
mergeWithLeftLine(writeSheetHolder, cell, curRowIndex, curColIndex);
break;
}
}
}
}
/**
* @description 当前单元格向左合并
*/
private void mergeWithLeftLine(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
//当前单元格中数据
Object curData = cell.getCellType() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
//获取当前单元格的左面一个单元格
Cell leftCell = cell.getSheet().getRow(curRowIndex).getCell(curColIndex - 1);
//获取当前单元格的左面一个单元格中的数据
Object leftData = leftCell.getCellType() == CellType.STRING ? leftCell.getStringCellValue() : leftCell.getNumericCellValue();
// 将当前单元格数据与左侧一个单元格数据比较
if (leftData.equals(curData)) {
//获取当前sheet页
Sheet sheet = writeSheetHolder.getSheet();
//得到所有的合并单元格
List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
//是否合并
boolean isMerged = false;
for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
//CellRangeAddress POI合并单元格
//CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol)
//例子：CellRangeAddress（2, 6000, 3, 3）；
//第2行起 第6000行终止 第3列开始 第3列结束。
CellRangeAddress cellRangeAddr = mergeRegions.get(i);
// cellRangeAddr.isInRange(int rowInd, int colInd)确定给定坐标是否在此范围的范围内。
// 若左侧一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元
if (cellRangeAddr.isInRange(curRowIndex, curColIndex - 1)) {
sheet.removeMergedRegion(i);
cellRangeAddr.setLastColumn(curColIndex);
sheet.addMergedRegion(cellRangeAddr);
isMerged = true;
}
}
// 若左侧一个单元格未被合并，则新增合并单元
if (!isMerged) {
CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex , curRowIndex, curColIndex- 1, curColIndex);
sheet.addMergedRegion(cellRangeAddress);
}
}
}
}
3.4 EasyExcelUtil 工具类
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.kfang.web.price.manager.excel.ImportDataAbstractListener;
import com.kfang.web.price.manager.excel.ImportDataReturnVo;
import org.springframework.util.CollectionUtils;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Base64;
import java.util.List;
/**
* @author: huangyibo
* @Date: 2022/3/24 20:11
* @Description: EasyExcelUtil 工具类
*/
public class EasyExcelUtil {
/**
* 下载文件时，针对不同浏览器，进行附件名的编码
*
* @param filename 下载文件名
*
* @param agent 客户端浏览器
*
* @return 编码后的下载附件名
* @throws IOException
*/
public static String encodeDownloadFilename(String filename, String agent)
throws IOException {
if (agent.contains("Firefox")) { // 火狐浏览器
filename = "=?UTF-8?B?"
+ Base64.getEncoder().encode(filename.getBytes("utf-8"))
+ "?=";
filename = filename.replaceAll("\r\n", "");
} else { // IE及其他浏览器
filename = URLEncoder.encode(filename, "utf-8");
filename = filename.replace("+"," ");
}
return filename;
}
/**
* 设置导出Excel格式
* @param response
* @param request
* @param filename
* @throws IOException
*/
public static void setExportExcelFormat(HttpServletResponse response, HttpServletRequest request, String filename) throws IOException {
String agent = request.getHeader("user-agent");//获得游览器
filename = filename + ".xlsx";
String downloadFilename = encodeDownloadFilename(filename, agent); //使用工具类解决文件乱码的问题
response.setCharacterEncoding("UTF-8");
// 设置响应输出的头类型
response.setHeader("content-Type", "application/vnd.ms-excel");
// 下载文件的默认名称
response.setHeader("Content-Disposition", "attachment;filename="+downloadFilename);
response.setHeader("Pragma", "public");
response.setHeader("Cache-Control", "no-store");
response.addHeader("Cache-Control", "max-age=0");
}
/**
* 读取导入excel数据(单sheet页读取)
* @param file 上传文件
* @param clazz 导入实体类型
* @param excelListener 数据导入监听器
* @return ImportDataReturnVo<?>
* @throws IOException
*/
public static ImportDataReturnVo<?> read(MultipartFile file, Class<?> clazz, ImportDataAbstractListener<?> excelListener) throws IOException {
EasyExcel.read(file.getInputStream(), clazz, excelListener).sheet().doRead();
return excelListener.getResult();
}
/**
* 读取导入excel数据(读取全部sheet页)
* @param file 上传文件
* @param clazz 导入实体类型
* @param excelListener 数据导入监听器
* @return ImportDataReturnVo<?>
* @throws IOException
*/
public static ImportDataReturnVo<?> readAll(MultipartFile file, Class<?> clazz, ImportDataAbstractListener<?> excelListener) throws IOException {
EasyExcel.read(file.getInputStream(), clazz, excelListener).doReadAll();
return excelListener.getResult();
}
/**
* 读取导入excel数据(读取指定sheet页)
* @param file 上传文件
* @param clazz 导入实体类型
* @param excelListener 数据导入监听器
* @param sheetNumList 指定的sheetNum
* @return ImportDataReturnVo<?>
* @throws IOException
*/
public static ImportDataReturnVo<?> readSheet(MultipartFile file, Class<?> clazz, ImportDataAbstractListener<?> excelListener, List<Integer> sheetNumList) throws IOException {
ExcelReader excelReader = EasyExcel.read(file.getInputStream()).build();
// 读取不同的数据, 需要使用不同的head 和Listener
List<ReadSheet> readSheetList = new ArrayList<>();
sheetNumList.forEach(sheetNum -> {
// readSheet参数设置读取sheet的序号
ReadSheet readSheet = EasyExcel.readSheet(sheetNum).head(clazz).registerReadListener(excelListener).build();
readSheetList.add(readSheet);
});
// 这里注意 一定要把readSheetList一起传进去，不然有个问题就是03版的excel 会读取多次，浪费性能
excelReader.read(readSheetList);
// 这里千万别忘记关闭，读的时候会创建临时文件，到时磁盘会崩的
excelReader.finish();
return excelListener.getResult();
}
/**
* 将数据写入Excel并写入流中
* @param outputStream 输出流
* @param clazz 导出实体类型
* @param list 导出数据集合
* @param sheetName sheet页名称
*/
public static void writeExcel2Stream(ByteArrayOutputStream outputStream, Class<?> clazz, List<?> list, String sheetName){
EasyExcel.write(outputStream, clazz).sheet(sheetName).doWrite(list);
}
/**
* 单sheet页写入Excel
* @param outputStream 输出流
* @param clazz 导出实体类型
* @param list 导出数据集合
* @param sheetName sheet页名称
*/
public static void writeExcel(ServletOutputStream outputStream, Class<?> clazz, List<?> list, String sheetName) {
EasyExcel.write(outputStream, clazz)
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
.sheet(sheetName)
.doWrite(list);
}
/**
* 单sheet页写入Excel-合并策略
* @param outputStream 输出流
* @param clazz 导出实体类型
* @param list 导出数据集合
* @param sheetName sheet页名称
* @param cellWriteHandlerList 合并策略集合
*/
public static void writeExcelMerge(ServletOutputStream outputStream, Class<?> clazz, List<?> list, String sheetName, List<CellWriteHandler> cellWriteHandlerList) {
ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(outputStream, clazz)
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy());
if(!CollectionUtils.isEmpty(cellWriteHandlerList)){
for (CellWriteHandler cellWriteHandler : cellWriteHandlerList) {
excelWriterBuilder.registerWriteHandler(cellWriteHandler);
}
}
excelWriterBuilder.sheet(sheetName).doWrite(list);
}
/**
* 多sheet页写入Excel
* @param excelWriter excelWriter写出对象
* @param clazz 导出实体类型
* @param list 导出数据集合
* @param num sheet页码
* @param sheetName sheet页名称
*/
public static void writerSheetExcel(ExcelWriter excelWriter, Class<?> clazz, List<?> list, Integer num, String sheetName) {
WriteSheet writeSheet = EasyExcel.writerSheet(num, sheetName).head(clazz)
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();
excelWriter.write(list, writeSheet);
}
/**
* 多sheet页写入Excel-合并策略
* @param excelWriter excelWriter写出对象
* @param clazz 导出实体类型
* @param list 导出数据集合
* @param num sheet页码
* @param sheetName sheet页名称
* @param cellWriteHandlerList 合并策略集合
*/
public static void writerSheetExcelMerge(ExcelWriter excelWriter, Class<?> clazz, List<?> list, Integer num, String sheetName, List<CellWriteHandler> cellWriteHandlerList) {
ExcelWriterSheetBuilder excelWriterSheetBuilder = EasyExcel.writerSheet(num, sheetName).head(clazz)
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy());
if(!CollectionUtils.isEmpty(cellWriteHandlerList)){
for (CellWriteHandler cellWriteHandler : cellWriteHandlerList) {
excelWriterSheetBuilder.registerWriteHandler(cellWriteHandler);
}
}
WriteSheet writeSheet = excelWriterSheetBuilder.build();
excelWriter.write(list, writeSheet);
}
}
3.5 单sheet页写入Excel-合并策略
- ExcelFillCellRowMergeStrategy和ExcelFillCelColumnMergeStrategy可以单独使用，也可以组合使用，这里演示组合使用
@Lazy
@RestController
@RequestMapping(value = "/demo", produces = {"application/json;charset=UTF-8"})
@Slf4j
@Api(tags = ApiSwaggerTags.DICT_ROOM_RULE_APPRAISAL,value = "规则管理")
public class DictRoomRuleController extends WebBaseController {
@PostMapping(value = "/exportMerge")
@ApiOperation(value = "导出测试信息")
@ApiResponses({
@ApiResponse(message = "导出测试信息", code = 200, response = Boolean.class)
})
public void exportMerge(HttpServletResponse response, HttpServletRequest request) {
try {
ServletOutputStream outputStream = response.getOutputStream();
List<Demo> list = new ArrayList<>();
buildData(list);
EasyExcelUtil.setExportExcelFormat(response, request,"测试数据");
//需要合并的列
int[] mergeColumnIndex = {0, 1, 2, 3, 4};
//设置第几行开始合并
int mergeRowIndex = 1;
// Excel单元格行合并处理策略
ExcelFillCellRowMergeStrategy rowMergeStrategy = new ExcelFillCellRowMergeStrategy(mergeRowIndex, mergeColumnIndex);
//列合并的工具实体类
ArrayList<CellLineRange> cellLineRanges=new ArrayList<>();
cellLineRanges.add(new CellLineRange(0,2));
// Excel单元格列合并处理策略
ExcelFillCelColumnMergeStrategy celColumnMergeStrategy = new ExcelFillCelColumnMergeStrategy(0, cellLineRanges);
List<CellWriteHandler> cellWriteHandlerList = Stream.of(celColumnMergeStrategy, rowMergeStrategy).collect(Collectors.toList());
EasyExcelUtil.writeExcelMerge(outputStream, Demo.class, list, "测试数据", cellWriteHandlerList);
outputStream.flush();
outputStream.close();
} catch (Exception e) {
log.error("楼盘估价数据导出excel Exception", e);
}
}
/**
* 构建数据
* @param list
*/
private void buildData (List<Demo> list){
for (int i = 0; i < 5; i++) {
Demo demo = new Demo();
demo.setAppName("app1");
demo.setCityName("app1");
demo.setRegionName("app1");
demo.setBusinessAreaName("深大");
demo.setGardenName("大冲国际中心");
demo.setBuildingName("一期");
demo.setUnitName("A座");
demo.setPrice(100000 + i);
list.add(demo);
}
for (int i = 0; i < 2; i++) {
Demo demo = new Demo();
demo.setAppName("app2");
demo.setCityName("深圳");
demo.setRegionName("南山区");
demo.setBusinessAreaName("前海湾");
demo.setGardenName("前海中心大厦");
demo.setBuildingName("一期");
demo.setUnitName("B座");
demo.setPrice(100000 + i);
list.add(demo);
}
for (int i = 0; i < 2; i++) {
Demo demo = new Demo();
demo.setAppName("深圳");
demo.setCityName("深圳");
demo.setRegionName("深圳");
demo.setBusinessAreaName("后海");
demo.setGardenName("中国华润大厦");
demo.setBuildingName("中国华润大厦");
demo.setUnitName("A座");
demo.setPrice(100000 + i);
list.add(demo);
}
for (int i = 0; i < 1; i++) {
Demo demo = new Demo();
demo.setAppName("app3");
demo.setCityName("深圳");
demo.setRegionName("宝安区");
demo.setBusinessAreaName("壹方城");
demo.setGardenName("壹方中心");
demo.setBuildingName("一期");
demo.setUnitName("A座");
demo.setPrice(100000 + i);
list.add(demo);
}
}
}
3.6 多sheet页写入Excel-合并策略
- ExcelFillCellRowMergeStrategy和ExcelFillCelColumnMergeStrategy可以单独使用，也可以组合使用，这里演示组合使用
@Lazy
@RestController
@RequestMapping(value = "/demo", produces = {"application/json;charset=UTF-8"})
@Slf4j
@Api(tags = ApiSwaggerTags.DICT_ROOM_RULE_APPRAISAL,value = "规则管理")
public class DictRoomRuleController extends WebBaseController {
@PostMapping(value = "/exportMergeTest")
@ApiOperation(value = "导出测试信息")
@ApiResponses({
@ApiResponse(message = "导出测试信息", code = 200, response = Boolean.class)
})
public void exportMergeTest(HttpServletResponse response, HttpServletRequest request) {
try {
ServletOutputStream outputStream = response.getOutputStream();
List<List<Demo>> list = new ArrayList<>();
buildListData(list);
EasyExcelUtil.setExportExcelFormat(response, request,"KF楼盘估价");
//需要合并的列
int[] mergeColumnIndex = {0, 1, 2, 3, 4};
//设置第几行开始合并
int mergeRowIndex = 1;
// Excel单元格行合并处理策略
ExcelFillCellRowMergeStrategy rowMergeStrategy = new ExcelFillCellRowMergeStrategy(mergeRowIndex, mergeColumnIndex);
//必须放到循环外，否则会刷新流
ExcelWriter excelWriter = EasyExcel.write(outputStream).build();
for (int i = 0; i < list.size(); i++) {
//sheet页码
int num = 1 + 1;
//列合并的工具实体类
ArrayList<CellLineRange> cellLineRanges=new ArrayList<>();
cellLineRanges.add(new CellLineRange(0,2));
// Excel单元格列合并处理策略
ExcelFillCelColumnMergeStrategy celColumnMergeStrategy = new ExcelFillCelColumnMergeStrategy(0, cellLineRanges);
List<CellWriteHandler> cellWriteHandlerList = Stream.of(celColumnMergeStrategy, rowMergeStrategy).collect(Collectors.toList());
EasyExcelUtil.writerSheetExcelMerge(excelWriter, Demo.class, list.get(i), num, "楼盘估价"+num, cellWriteHandlerList);
}
//刷新流
excelWriter.finish();
outputStream.flush();
outputStream.close();
} catch (Exception e) {
log.error("楼盘估价数据导出excel Exception", e);
}
}
/**
* 构建数据
* @param list
*/
private void buildListData (List<List<Demo>> list){
List<Demo> list1 = new ArrayList<>();
List<Demo> list2 = new ArrayList<>();
for (int i = 0; i < 5; i++) {
Demo demo = new Demo();
demo.setAppName("app1");
demo.setCityName("app1");
demo.setRegionName("app1");
demo.setBusinessAreaName("深大");
demo.setGardenName("大冲国际中心");
demo.setBuildingName("一期");
demo.setUnitName("A座");
demo.setPrice(100000 + i);
list1.add(demo);
}
for (int i = 0; i < 2; i++) {
Demo demo = new Demo();
demo.setAppName("app2");
demo.setCityName("深圳");
demo.setRegionName("南山区");
demo.setBusinessAreaName("前海湾");
demo.setGardenName("前海中心大厦");
demo.setBuildingName("一期");
demo.setUnitName("B座");
demo.setPrice(100000 + i);
list1.add(demo);
}
for (int i = 0; i < 2; i++) {
Demo demo = new Demo();
demo.setAppName("深圳");
demo.setCityName("深圳");
demo.setRegionName("深圳");
demo.setBusinessAreaName("后海");
demo.setGardenName("中国华润大厦");
demo.setBuildingName("中国华润大厦");
demo.setUnitName("A座");
demo.setPrice(100000 + i);
list2.add(demo);
}
for (int i = 0; i < 1; i++) {
Demo demo = new Demo();
demo.setAppName("app3");
demo.setCityName("深圳");
demo.setRegionName("宝安区");
demo.setBusinessAreaName("壹方城");
demo.setGardenName("壹方中心");
demo.setBuildingName("一期");
demo.setUnitName("A座");
demo.setPrice(100000 + i);
list2.add(demo);
}
list.add(list1);
list.add(list2);
}
}
3.7 动态标题
- 生成动态标题
//生成动态标题
private List<List<String>> getHeaders(String dynamicTitle) {
List<List<String>> headers=new ArrayList<>();
List<String> schoolHead=new ArrayList<>();
schoolHead.add(dynamicTitle);
schoolHead.add("学校");
List<String> nameHead=new ArrayList<>();
nameHead.add(dynamicTitle);
nameHead.add("姓名");
List<String> sexHead=new ArrayList<>();
sexHead.add(dynamicTitle);
sexHead.add("性别");
List<String> ageHead=new ArrayList<>();
ageHead.add(dynamicTitle);
ageHead.add("年龄");
List<String> cityHead=new ArrayList<>();
cityHead.add(dynamicTitle);
cityHead.add("城市");
List<String> remarksHead=new ArrayList<>();
remarksHead.add(dynamicTitle);
remarksHead.add("备注");
headers.add(schoolHead);
headers.add(nameHead);
headers.add(sexHead);
headers.add(ageHead);
headers.add(cityHead);
headers.add(remarksHead);
return headers;
}
- 设置动态标题
//设置动态标题
List<List<String>> headers = this.getHeaders("学生信息" + dynamicTitle);
EasyExcel.write(outputStream, clazz)
.sheet(sheetName)
.head(headers)
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
.doWrite(list);
参考：
https://easyexcel.opensource.alibaba.com/index.html
https://blog.csdn.net/weixin_48788601/article/details/127323681
https://blog.csdn.net/qq_39032203/article/details/125405411
https://huaweicloud.csdn.net/63876534dacf622b8df8b399.html
https://www.freesion.com/article/90581470133/
https://blog.csdn.net/weixin_43296313/article/details/124814674
https://blog.csdn.net/weixin_55383903/article/details/122624251
