Excel导入导出工具类（多sheet、多表头、单元格下拉选择、根据列名匹配转为List）

本文链接：https://blog.csdn.net/weixin_43830468/article/details/122614869

一、多sheet

通过配置动态生成多个工作表(sheet)，自定义sheet的名称，如下效果图：

二、多表头

通过配置生成多表头，效果图如下：

主要核心代码：

// 赋值后，执行合并单元格
log.trace("合并单元格 Row[{}->{}], Col[{}->{}]", i.getFirstRow(), i.getLastRow(), i.getFirstCol(), i.getLastCol());
CellRangeAddress cra = new CellRangeAddress(i.getFirstRow(), i.getLastRow(), i.getFirstCol(), i.getLastCol());
sheet.addMergedRegion(cra);
RegionUtil.setBorderBottom(BorderStyle.THIN, cra, sheet); // 下边框
RegionUtil.setBorderLeft(BorderStyle.THIN, cra, sheet); // 左边框
RegionUtil.setBorderRight(BorderStyle.THIN, cra, sheet); // 有边框
RegionUtil.setBorderTop(BorderStyle.THIN, cra, sheet); // 上边框

三、单元格下拉选择

下载模板中，单元格支持下拉选择，效果图：

主要核心代码：

private void appendCellConstraint(List<ExcelColumnConfig> columns, Sheet sheet) {
        int maxRow = SpreadsheetVersion.EXCEL2007.getLastRowIndex(); //65536

        for (int i = 0; i < columns.size(); i++) {
            ExcelColumnConfig config = columns.get(i);
            List<String> constraintVal = config.getMapper().columnConstraint();
            if (Objects.isNull(constraintVal) || CollectionUtils.isEmpty(constraintVal)) continue;

            if (constraintVal.size() > 60) {
                log.warn("下拉选项({})过多 = {}, skip...", config.getDataLabel(), constraintVal.size());
                continue;
            }

            XSSFDataValidationHelper helper = new XSSFDataValidationHelper((XSSFSheet) sheet);
            DataValidationConstraint constraint = helper.createExplicitListConstraint(constraintVal.toArray(new String[constraintVal.size()]));
            CellRangeAddressList regions = new CellRangeAddressList(0, maxRow, i, i);

            sheet.addValidationData(helper.createValidation(constraint, regions));
            log.debug("为第[{}]列增加了下拉约束{}", i, constraintVal);
        }
    }

4、根据列名匹配转为List<?>

模板列名（单表头）不需要固定顺序，且列名可多可少，通过列名配置字段（匹配不上的列名不做任何处理），转为Map<String,List<?>>,其中key为sheet名称，List<?>中?可以为map,如果想转为具体实体类，采用json转为class即可。

主要核心代码：

/**
     * 备注：
     * 1、目前只处理单表头，根据标题名称获取取配置信息
     * 2、如果是多表头，根据列头获取配置顺序，并表示与Excel列顺序
     */
    private List<ExcelColumnConfig> findConfigByExcelColumnName(Row row, List<ExcelColumnConfig> configs, int titleRows, String sheetName) {
        if (1 != titleRows) {
            AtomicInteger ao = new AtomicInteger(0);
            return configs.stream().peek(i -> i.setExcelColumnOrder(ao.getAndIncrement())).collect(Collectors.toList());
        }

        List<ExcelColumnConfig> result = Lists.newArrayList();
        for (int cellIndex = 0; cellIndex < row.getLastCellNum(); cellIndex++) {
            Cell cell = row.getCell(cellIndex);
            String cellRawStrVal = Objects.toString(cell, null);
            // 把前后空格去掉
            cellRawStrVal = !Strings.isNullOrEmpty(cellRawStrVal) ? cellRawStrVal.trim() : null;
            if (Strings.isNullOrEmpty(cellRawStrVal)) continue;

            // FIXME:_Atomic 这里有点低效，没细看，总体为N*M循环
            // 去掉后缀后再判断，避免模板存在后缀:(必填)
            String finalCellRawStrVal = cellRawStrVal;
            Optional<ExcelColumnConfig> columnConfigOpt = configs.stream()
                .filter(i -> {
                    String dataLabel = StringUtils.substringBefore(i.getDataLabel(), KEY_EXCEL_COLUMN_SUFFIX);
                    String cellVal = StringUtils.substringBefore(finalCellRawStrVal, KEY_EXCEL_COLUMN_SUFFIX);
                    return dataLabel.equals(cellVal);
                }).findFirst();
            if (!columnConfigOpt.isPresent()) {
                log.debug("{}-->[{}]未在配置中，跳过", sheetName, cellRawStrVal);
                continue;
            }

            ExcelColumnConfig columnConfig = columnConfigOpt.get();
            columnConfig.setExcelColumnOrder(cellIndex);
            result.add(columnConfig);
        }

        Set<String> columnNames = result.stream().map(ExcelColumnConfig::getDataLabel).collect(Collectors.toSet());
        if (columnNames.size() != result.size()) {
            log.error("标题列名存在重复，不能完成操作");
            throw BusinessException.withMessage(ErrorCode.ERR_10001, "标题列名存在重复，不能完成操作");
        }

        return result;
    }

工具类代码：

package com.excel.app.utils;

import com.excel.app.utils.exception.BusinessException;
import com.excel.app.utils.exception.ErrorCode;
import com.google.common.base.Strings;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;

import lombok.Getter;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.collections.MapUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.SpreadsheetVersion;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.*;

import java.io.*;
import java.math.BigDecimal;
import java.util.*;
import java.util.stream.Collectors;

import static org.apache.poi.ss.usermodel.HorizontalAlignment.*;

/**
 * 多sheet的excel工具类
 * １、通过注解方式导入导出
 * 2、调用模板，并在模板基础上进行赋值
 * 3、动态创建表头（多表头）与数值导出excel数据
 * 4、根据表头列名匹配获取excel数据
 */
@Slf4j @Getter
public class ExportExcelUtil extends BaseExcelTransformToList {
    /**
     * 工作薄对象
     */
    private Workbook wb;

    /**
     * 样式列表
     */
    private Map<String, CellStyle> styles;

    /**
     * 计算sheet头部行号,每个sheet都重新初始化
     */
    private int rownum;

    Map<String, Integer> rownumMap = new LinkedHashMap<>();

    /**
     * 构造函数（用于包含头部的模板）
     *
     * @param tplHeaderRownum 模板头部占用行数
     * @param tplPath         模板路径文件
     * @param mapList         数据列表信息，根据模板内容只用于动态拼装数据
     */
    public ExportExcelUtil(int tplHeaderRownum, int tplColunm, boolean isCreateRow, String tplPath, Map<String, List<List<Object>>> mapList) {
        try {
            File newFile = new File(tplPath);
            InputStream is = new FileInputStream(newFile);// 将excel文件转为输入流
            XSSFWorkbook workbook = new XSSFWorkbook(is);
            this.wb = new SXSSFWorkbook(workbook);//重新new以后，模板内容就读取不到
        } catch (Exception e) {
            e.printStackTrace();
        }
        setData(tplHeaderRownum, tplColunm, isCreateRow, mapList);
    }

    /**
     * 构造函数（用于模板）
     *
     * @param tplHeaderRownum 模板头部占用行数
     * @param tplColunm       模板列数占用列数
     * @param isCreateRow     是否动态添加行