1 设置web 输出流：
fileName 为文件名称

/*** web 输出流** @param fileName* @param response* @return* @throws Exception*/
private OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {try {// response.setContentType("application/vnd.ms-excel");response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");response.setCharacterEncoding("utf8");response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, StandardCharsets.UTF_8.name()) + ".xlsx");response.setHeader("Pragma", "public");response.setHeader("Cache-Control", "no-store");response.addHeader("Cache-Control", "max-age=0");return response.getOutputStream();} catch (IOException e) {throw new Exception("导出excel表格失败!", e);}
}

2 定义ExcelWriter：

ExcelWriter writer = null;
try {
writer = EasyExcel.write(getOutputStream(“这是文件名称”, response)).excelType(ExcelTypeEnum.XLSX).build();writer.finish();
} catch (Exception ex) {log.warn("导出异常!");if (writer != null) {writer.finish();}
}

2.3 定义excel 表头：

List<List<String>> header // 根据业务需求定义表头

2.4 定义excel 表格内容：

List<List<String>> data // 根据业务需求定义表格内容 可以泛型为Object

2.5 定义要合并的列下标：
如定义第1列需要进行合并

List<Integer> set = new ArrayList<>();
set.add(0);

2.6 定义WriteSheet：
1： 写入第一个sheet

WriteSheet sheet = EasyExcel.writerSheet(1, “这是sheet的名称”)).head(header)//开启列的合并.registerWriteHandler(new MergeStrategyTravelRankConfig(data.size(), set, Boolean.TRUE))// 开启列的宽度计算.registerWriteHandler(new AutoWidthStyleStrategy()).build();
// 写入sheet
writer.write(data, sheet);

完整方法：

ExcelWriter writer = null;
try {List<List<String>> data =;// 根据业务需求定义表格内容 可以泛型为Object//要合并的列List<Integer> set = new ArrayList<>();set.add(1);writer = EasyExcel.write(getOutputStream(“这是文件名称”, response)).excelType(ExcelTypeEnum.XLSX).build();List<List<String>> header =; // 根据业务需求定义表头WriteSheet sheet = EasyExcel.writerSheet(1,“这是sheet的名称”).head(header)//开启列的合并.registerWriteHandler(new MergeStrategyTravelRankConfig(data.size(), set, Boolean.TRUE))// 开启列的宽度计算.registerWriteHandler(new AutoWidthStyleStrategy()).build();writer.write(data, sheet);writer.finish();
} catch (Exception ex) {log.warn("导出异常!");if (writer != null) {writer.finish();}
}

2.7 自定义行,列合并方法：

import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.merge.AbstractMergeStrategy;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;/*** @Description TODO* @Version 1.0*/
public class MergeStrategyTravelRankConfig extends AbstractMergeStrategy {// 合并的列编号，从0开始，指定的index或自己按字段顺序数private List<Integer> mergeCellIndex = new ArrayList<>();// 数据集大小，用于区别结束行位置private Integer maxRow = 0;//是否开启行合并private Boolean isMergeRow;private Integer startRowNum = 1;private MergeStrategyTravelRankConfig() {}public MergeStrategyTravelRankConfig(Integer maxRow, List<Integer> mergeCellIndex, Boolean isMergeRow) {this.mergeCellIndex = mergeCellIndex;this.maxRow = maxRow;this.isMergeRow = isMergeRow;}// 记录上一次合并的信息private Map<Integer, MergeRange> lastRow = new HashMap<>();private int lastRowIndex = 0;private String lastRowValue = "";private Map<String, MergeRange> mapData = new HashMap<>();// 每行每列都会进入@Overrideprotected void merge(Sheet sheet, Cell cell, Head head, int relativeRowIndex) {int currentCellIndex = cell.getColumnIndex();int currentRowIndex = cell.getRowIndex();// 判断该列是否需要合并if (mergeCellIndex.contains(currentCellIndex)) {String currentCellValue = cell.getStringCellValue();if (!lastRow.containsKey(currentCellIndex)) {// 记录首行起始位置lastRow.put(currentCellIndex, new MergeRange(currentCellValue, currentRowIndex, currentRowIndex, currentCellIndex, currentCellIndex));int preCellIndex = currentCellIndex - 1;if (preCellIndex >= 0) {// 处理列合并起始位置MergeRange mergeRangePre = lastRow.get(preCellIndex);if (mergeRangePre != null) {if (currentCellValue.equals(mergeRangePre.lastValue) && currentCellIndex == mergeCellIndex.size() - 1) {MergeRange cellMerge = mapData.get(currentCellValue);lastRow.put(currentCellIndex, new MergeRange(currentCellValue, currentRowIndex, currentRowIndex, cellMerge.startCell, currentCellIndex));} else if (currentCellValue.equals(mergeRangePre.lastValue) && !mapData.containsKey(currentCellValue)) {mapData.put(currentCellValue, mergeRangePre);}}}lastRowIndex = currentRowIndex;lastRowValue = currentCellValue;return;}//行合并if (null != isMergeRow && isMergeRow) {if (!currentCellValue.equals(lastRowValue) && !currentCellValue.contains("其中")) {// 触发上一行的列合并// 简易写法---直接取上一行最后一个cell 的单元格MergeRange mergeRangePreRowCell = lastRow.get(mergeCellIndex.size() - 1);if (mergeRangePreRowCell.startCell != mergeRangePreRowCell.endCell) {sheet.addMergedRegionUnsafe(new CellRangeAddress(mergeRangePreRowCell.startRow, mergeRangePreRowCell.endRow, mergeRangePreRowCell.startCell, mergeRangePreRowCell.endCell));// 复原lastRow.put(mergeCellIndex.size() - 1, new MergeRange(mergeRangePreRowCell.lastValue, mergeRangePreRowCell.startRow, mergeRangePreRowCell.endRow, mergeCellIndex.size() - 1, mergeRangePreRowCell.endCell));}}}//对比.MergeRange mergeRange = lastRow.get(currentCellIndex);if (!(mergeRange.lastValue != null && mergeRange.lastValue.equals(currentCellValue))) {// 更新当前列起始位置lastRow.put(currentCellIndex, new MergeRange(currentCellValue, currentRowIndex, currentRowIndex, currentCellIndex, currentCellIndex));if (!currentCellValue.contains("其中") && !mapData.containsKey(currentCellValue)) {mapData.put(currentCellValue, lastRow.get(currentCellIndex));}int preCellIndex = currentCellIndex - 1;if (preCellIndex >= 0) {// 处理列合并起始位置MergeRange mergeRangePre = lastRow.get(preCellIndex);if (mergeRangePre != null) {if (currentCellValue.equals(mergeRangePre.lastValue) && currentCellIndex == mergeCellIndex.size() - 1) {MergeRange cellMerge = mapData.get(currentCellValue);lastRow.put(currentCellIndex, new MergeRange(currentCellValue, currentRowIndex, currentRowIndex, cellMerge.startCell, currentCellIndex));}}}// 结束的位置触发下合并.// 同行同列不能合并，会抛异常if (mergeRange.startRow != mergeRange.endRow || mergeRange.startCell != mergeRange.endCell) {sheet.addMergedRegionUnsafe(new CellRangeAddress(mergeRange.startRow, mergeRange.endRow, mergeRange.startCell, mergeRange.endCell));}}// 合并行 + 1mergeRange.endRow += 1;// 结束的位置触发下最后一次没完成的合并if (relativeRowIndex == maxRow - startRowNum) {MergeRange lastMergeRange = lastRow.get(currentCellIndex);// 同行同列不能合并if (lastMergeRange.startRow != lastMergeRange.endRow || lastMergeRange.startCell != lastMergeRange.endCell) {sheet.addMergedRegionUnsafe(new CellRangeAddress(lastMergeRange.startRow, lastMergeRange.endRow, lastMergeRange.startCell, lastMergeRange.endCell));}}lastRowIndex = currentRowIndex;lastRowValue = currentCellValue;}}class MergeRange {int startRow;int endRow;int startCell;int endCell;String lastValue;MergeRange(String lastValue, int startRow, int endRow, int startCell, int endCell) {this.startRow = startRow;this.endRow = endRow;this.startCell = startCell;this.endCell = endCell;this.lastValue = lastValue;}}
}

2.8 自定义列宽方法：

import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.util.CollectionUtils;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import org.apache.poi.ss.usermodel.Cell;import java.util.HashMap;
import java.util.List;
import java.util.Map;/*** @Description TODO
* @Version 1.0*/
public class AutoWidthStyleStrategy extends AbstractColumnWidthStyleStrategy {private Map<Integer, Map<Integer, Integer>> cache = new HashMap<>(8);public AutoWidthStyleStrategy() {}@Overrideprotected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);if (needSetWidth) {Map<Integer, Integer> maxColumnWidthMap = this.cache.get(writeSheetHolder.getSheetNo());if (CollectionUtils.isEmpty(maxColumnWidthMap)) {maxColumnWidthMap = new HashMap<>(16);this.cache.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);}Integer columnWidth = this.dataLength(cellDataList, cell, isHead);if (columnWidth >= 0) {if (columnWidth > 255) {columnWidth = 255;} else if (columnWidth < 12) {columnWidth = 12;}Integer maxColumnWidth = (Integer) ((Map) maxColumnWidthMap).get(cell.getColumnIndex());if (maxColumnWidth == null || columnWidth > maxColumnWidth) {maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);}}}}private Integer dataLength(List<CellData> cellDataList, Cell cell, Boolean isHead) {if (isHead) {return cell.getStringCellValue().getBytes().length;} else {CellData cellData = cellDataList.get(0);CellDataTypeEnum type = cellData.getType();if (type == null) {return -1;} else {switch (type) {case STRING:return cellData.getStringValue().getBytes().length;case BOOLEAN:return cellData.getBooleanValue().toString().getBytes().length;case NUMBER:return cellData.getNumberValue().toString().getBytes().length + 3;default:return -1;}}}}
}