EasyExcel-web导出篇

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<>();

    // 每行每列都会进入
    @Override
    protected 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));
                }

            }
            // 合并行 + 1
            mergeRange.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() {
    }

    @Override
    protected 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;
                }
            }
        }
    }
}