EasyExcel使用

HILY
1,162 阅读1分钟
  1. 多sheet
//生成excel
ExcelWriter excelWriter = EasyExcel.write(fullFileName)
        .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();
int sheetCount = 0;
for (int i = 1; i <= orgLevelToExcelDataDtoMap.size(); i++) {
    if (orgLevelToExcelDataDtoMap.containsKey(i)) {
        List<?> humanEffectDtoList = orgLevelToExcelDataDtoMap.get(i);
        if (!CollectionUtils.isEmpty(humanEffectDtoList)) {
            try {
                String sheetName = HumEffectStatisticExcelSheetNameEnum.getSheetName(i);
                ExcelWriterSheetBuilder excelWriterSheetBuilder = EasyExcel
                        .writerSheet(sheetCount++, sheetName)
                        .head(DepDataConstant.HUMAN_EFFECT_EXCEL_HEAD_CLASS_MAP.get(i))
                        .includeColumnFiledNames(DepDataConstant.HUMAN_EFFECT_EXCEL_COLUMN_FILED_MAP.get(i))
                        .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy());
                if (i == 6) {
                    excelWriterSheetBuilder.registerWriteHandler(
                            new EasyExcelMergeStrategy(humanEffectDtoList.size(), 0, 0, 1, 2, 3, 4, 5));
                }
                WriteSheet writeSheet = excelWriterSheetBuilder.build();
                excelWriter.write(humanEffectDtoList, writeSheet);
            } catch (Exception e) {
                throw new RuntimeException(e);
            }
        }
    }
}
excelWriter.finish();
  1. 行合并策略
package *.strategy;

import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.merge.AbstractMergeStrategy;
import org.apache.commons.collections4.map.HashedMap;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

import java.util.Arrays;
import java.util.HashSet;
import java.util.Map;
import java.util.Set;

public class EasyExcelMergeStrategy extends AbstractMergeStrategy {
    // 禁用无参
    private EasyExcelMergeStrategy() {
    }

    // 合并的列编号，从0开始，指定的index或自己按字段顺序数
    private final Set<Integer> mergeCellIndex = new HashSet<>();

    // 数据集大小，用于区别结束行位置
    private Integer maxRow = 0;

    //行合并的基准列：即按那列的值相等合并行,null走默认行合并策略
    private Integer mergeStandardCellIndex = null;

    public EasyExcelMergeStrategy(Integer maxRow, Integer mergeStandardCellIndex, int... mergeCellIndex) {
        Arrays.stream(mergeCellIndex).forEach(this.mergeCellIndex::add);
        this.maxRow = maxRow;
        this.mergeStandardCellIndex = mergeStandardCellIndex;
    }

    //记录上一次合并的信息
    private final Map<Integer, MergeRange> lastRow = new HashedMap<>();

    // 每行每列都会进入，绝对不要在这写循环
    @Override
    protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
        int currentCellIndex = cell.getColumnIndex();
        // 判断该行是否需要合并
        if (mergeCellIndex.contains(currentCellIndex)) {
            String currentCellValue;
            if (mergeStandardCellIndex == null) {
                currentCellValue = new DataFormatter().formatCellValue(cell);
            } else {
                //此处固定比较第一列数据相同的进行行合并
                currentCellValue = new DataFormatter().formatCellValue(cell.getRow().getCell(mergeStandardCellIndex));
            }
            int currentRowIndex = cell.getRowIndex();
            if (!lastRow.containsKey(currentCellIndex)) {
                // 记录首行起始位置
                lastRow.put(currentCellIndex, new MergeRange(currentCellValue, currentRowIndex, currentRowIndex, currentCellIndex, currentCellIndex));
                return;
            }
            //有上行这列的值了，拿来对比.
            MergeRange mergeRange = lastRow.get(currentCellIndex);
            if (!(mergeRange.lastValue != null && mergeRange.lastValue.equals(currentCellValue))) {
                // 结束的位置触发下合并.
                // 同行同列不能合并，会抛异常
                if (mergeRange.startRow != mergeRange.endRow || mergeRange.startCell != mergeRange.endCell) {
                    sheet.addMergedRegionUnsafe(new CellRangeAddress(mergeRange.startRow, mergeRange.endRow, mergeRange.startCell, mergeRange.endCell));
                }
                // 更新当前列起始位置
                lastRow.put(currentCellIndex, new MergeRange(currentCellValue, currentRowIndex, currentRowIndex, currentCellIndex, currentCellIndex));
            }
            // 合并行 + 1
            mergeRange.endRow += 1;
            // 结束的位置触发下最后一次没完成的合并
            if (relativeRowIndex.equals(maxRow - 1)) {
                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
                    ));
                }
            }
        }
    }
}

class MergeRange {
    public int startRow;
    public int endRow;
    public int startCell;
    public int endCell;
    public 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;
    }
}

使用：

String sheetName = HumEffectStatisticExcelSheetNameEnum.getSheetName(i);
ExcelWriterSheetBuilder excelWriterSheetBuilder = EasyExcel
        .writerSheet(sheetCount++, sheetName)
        .head(DepDataConstant.HUMAN_EFFECT_EXCEL_HEAD_CLASS_MAP.get(i))
        .includeColumnFiledNames(DepDataConstant.HUMAN_EFFECT_EXCEL_COLUMN_FILED_MAP.get(i))
        .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
        .registerWriteHandler(new CustomizeWriteHandler());
if (i == 6 || i == 7) {
    excelWriterSheetBuilder.registerWriteHandler(
            new EasyExcelMergeStrategy(humanEffectDtoList.size(), 0, 1, 2, 3, 4, 5));
}
  1. null处理默认值

  • 转换器实现---需要每个字段都指定转换器

    如：BigDecimal类型为空时，导出为 “-”

package *.service;

import com.alibaba.excel.converters.NullableObjectConverter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.data.ReadCellData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.metadata.property.ExcelContentProperty;

import java.math.BigDecimal;
import java.util.Objects;

public class NullConverter implements NullableObjectConverter<BigDecimal> {
    @Override
    public Class<BigDecimal> supportJavaTypeKey() {
        return BigDecimal.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.NUMBER;
    }

    @Override
    public BigDecimal convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        return cellData.getNumberValue();
    }

    @Override
    public WriteCellData<?> convertToExcelData(BigDecimal integer, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration)
            throws Exception {
        if (Objects.isNull(integer)) {
            return new WriteCellData<>("-");
        } else {
            return new WriteCellData<>(integer);
        }
    }
}

@ExcelProperty(value = {"人效"},converter = NullConverter.class)
  • 单元格处理器实现
package *.handler;

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 java.util.List;


public class CustomizeWriteHandler implements CellWriteHandler {
    @Override
    public void beforeCellCreate(
            WriteSheetHolder writeSheetHolder,
            WriteTableHolder writeTableHolder,
            Row row,
            Head head,
            Integer columnIndex,
            Integer relativeRowIndex,
            Boolean isHead
    ) {
        CellWriteHandler.super.beforeCellCreate(writeSheetHolder, writeTableHolder, row, head, columnIndex, relativeRowIndex, isHead);
    }

    @Override
    public void afterCellCreate(
            WriteSheetHolder writeSheetHolder,
            WriteTableHolder writeTableHolder,
            Cell cell,
            Head head,
            Integer relativeRowIndex,
            Boolean isHead
    ) {
        CellWriteHandler.super.afterCellCreate(writeSheetHolder, writeTableHolder, cell, head, relativeRowIndex, isHead);
    }

    @Override
    public void afterCellDataConverted(
            WriteSheetHolder writeSheetHolder,
            WriteTableHolder writeTableHolder,
            WriteCellData<?> cellData,
            Cell cell,
            Head head,
            Integer relativeRowIndex,
            Boolean isHead
    ) {
        CellWriteHandler.super.afterCellDataConverted(writeSheetHolder, writeTableHolder, cellData, cell, head, relativeRowIndex, isHead);
    }

    @Override
    public void afterCellDispose(
            WriteSheetHolder writeSheetHolder,
            WriteTableHolder writeTableHolder,
            List<WriteCellData<?>> cellDataList,
            Cell cell,
            Head head,
            Integer relativeRowIndex,
            Boolean isHead
    ) {
        if (!isHead && cell.getCellType() == CellType.BLANK) {
            cell.setCellValue("-");
        }
    }
}

String sheetName = HumEffectStatisticExcelSheetNameEnum.getSheetName(i);
ExcelWriterSheetBuilder excelWriterSheetBuilder = EasyExcel
        .writerSheet(sheetCount++, sheetName)
        .head(DepDataConstant.HUMAN_EFFECT_EXCEL_HEAD_CLASS_MAP.get(i))
        .includeColumnFiledNames(DepDataConstant.HUMAN_EFFECT_EXCEL_COLUMN_FILED_MAP.get(i))
        .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
        .registerWriteHandler(new CustomizeWriteHandler());
if (i == 6 || i == 7) {
    excelWriterSheetBuilder.registerWriteHandler(
            new EasyExcelMergeStrategy(humanEffectDtoList.size(), 0, 1, 2, 3, 4, 5));
}
  • 加密
// 可查看不能修改
Workbook workbook = excelWriter.writeContext().writeWorkbookHolder().getWorkbook();  
//获取sheet页数量  
int numberOfSheets = workbook.getNumberOfSheets();  
//遍历加密  
for (int i = 0; i < numberOfSheets; i++) {  
Sheet sheetAt = workbook.getSheetAt(i);  
sheetAt.protectSheet("123456");  
}

// 没有密码不能查看
ExcelWriter writer = EasyExcel.write().file(response.getOutputStream())
                     .autoCloseStream(Boolean.FALSE)
                     .password("123456")
                     .build();
avatar
文章
阅读
粉丝