【解析excel】利用easyexcel解析excel

EasyExcel

Java解析、生成Excel比较有名的框架有Apache poi、jxl。但他们都存在一个严重的问题就是非常的耗内存，poi有一套SAX模式的API可以一定程度的解决一些内存溢出的问题，但POI还是有一些缺陷，比如07版Excel解压缩以及解压后存储都是在内存中完成的，内存消耗依然很大。
easyexcel重写了poi对07版Excel的解析，一个3M的excel用POI sax解析依然需要100M左右内存，改用easyexcel可以降低到几M，并且再大的excel也不会出现内存溢出；03版依赖POI的sax模式，在上层做了模型转换的封装，让使用者更加简单方便

POM

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel-core</artifactId>
            <version>3.2.1</version>
            <exclusions>
                <exclusion>
                    <groupId>org.ehcache</groupId>
                    <artifactId>ehcache</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>org.apache.poi</groupId>
                    <artifactId>poi-ooxml</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>org.apache.poi</groupId>
                    <artifactId>poi</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>org.apache.poi</groupId>
                    <artifactId>poi-ooxml-schemas</artifactId>
                </exclusion>
            </exclusions>
        </dependency>

监听类

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.metadata.Cell;
import com.alibaba.excel.metadata.data.ReadCellData;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

public class MyExcelListener extends AnalysisEventListener<Map<Integer, String>> {

    private Map<Integer, String> headMap = new HashMap<>();
    private List<Map<Integer, String>> valList = new ArrayList<>();
    private Map<String, String> headValAndTypeMap = new HashMap<>();
    private List<Map<String, String>> contentValAndTypeList = new ArrayList<>();
    private boolean nameAndTypeFlag;
    private Integer startColumnNum;
    private Integer startRowNum;
    private Integer endColumnNum;
    private Integer endRowNum;
    // sheet中最大的列号,默认为0,不可为null
    private int maxColumnNum;
    // sheet中行数，默认为1，因为从第二行开始读取
    private int maxRowNum = 1;

    @Override
    public void invoke(Map<Integer, String> integerStringMap, AnalysisContext analysisContext) {
        if (integerStringMap == null || integerStringMap.size()==0){
            return;
        }
        // 获取当前行数analysisContext.readRowHolder().getRowIndex() 从1开始(0行进的head方法)
        Integer currentRowNum = analysisContext.readRowHolder().getRowIndex();
        if(currentRowNum<startRowNum-1){
            return;
        }
        // 扫描excel全部内容获取此excel最大列数
        maxColumnNum = Math.max(maxColumnNum,integerStringMap.size());
        // 最大行数
        maxRowNum++;

        // 起始列，Map中小于startColumnNum-1都不需要
        integerStringMap.entrySet().removeIf(entry -> entry.getKey() < startColumnNum-1);

        // 移除值为null的数据
        integerStringMap.entrySet().removeIf(entry -> entry.getValue() == null);

        // 格式化单元格中的数据
        formatExcelValByCellType(integerStringMap,analysisContext.readRowHolder().getCellMap());
        // 本方法从excel第二行开始读,为防止起始不是excel第一行，多读入一行数据
        if (nameAndTypeFlag && (contentValAndTypeList.size() == 0 || contentValAndTypeList.size() == 1)){
            // 如果获取名称和类型,只获取一行数据
            contentValAndTypeList.add(getCellType(integerStringMap,analysisContext.readRowHolder().getCellMap()));
        } else if (endRowNum == null){
            // 未设置结束单元格无结束行数，则读取全部
            valList.add(integerStringMap);
        } else if (valList.size() < endRowNum-1){
            valList.add(integerStringMap);
        }

    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }

    @Override
    public void invokeHead(Map<Integer, ReadCellData<?>> headMap, AnalysisContext context) {
        Set<Map.Entry<Integer, ReadCellData<?>>> entrieSet = headMap.entrySet();
        for (Map.Entry<Integer, ReadCellData<?>> entry : entrieSet) {
            String val = entry.getValue().getType()== CellDataTypeEnum.EMPTY?"":entry.getValue().getStringValue();
            this.headMap.put(entry.getKey(),val);
        }
        if (startRowNum==1 && nameAndTypeFlag && headValAndTypeMap.size() == 0){
            // 如果获取名称和类型,只获取一行数据
            headValAndTypeMap = getCellType(this.headMap,context.readRowHolder().getCellMap());
        }
    }

    private void formatExcelValByCellType(Map<Integer, String> integerStringMap, Map<Integer, Cell> cellMap){
        for (Integer key : integerStringMap.keySet()) {
            ReadCellData cell = (ReadCellData) cellMap.get(key);
            String newVal = MyExcelUtils.getOtherDateFormat(cell,cell.getDataFormatData().getFormat());
            if (newVal!=null && !"".equals(newVal)){
                integerStringMap.put(key,newVal);
            }
        }
    }

    /**
     * eg: 0:张三  0_type:String
     * @param integerStringMap
     * @param cellMap
     * @return
     */
    private Map<String, String> getCellType(Map<Integer, String> integerStringMap, Map<Integer, Cell> cellMap){
        Map<String, String> nameAndTypeMap = new HashMap<>();
        // key取值是 0 1 2 3....
        for (Integer key : integerStringMap.keySet()) {
            nameAndTypeMap.put(String.valueOf(key),integerStringMap.get(key));
            ReadCellData cell = (ReadCellData) cellMap.get(key);
            String cellType = MyExcelUtils.getCellType(cell,integerStringMap.get(key));
            if (cellType!=null && !"".equals(cellType)){
                nameAndTypeMap.put(key+"_type",cellType);
            }
        }
        return nameAndTypeMap;
    }

    public Map<Integer, String> getHeadMap() {
        return headMap;
    }

    public void setHeadMap(Map<Integer, String> headMap) {
        this.headMap = headMap;
    }

    public List<Map<Integer, String>> getValList() {
        return valList;
    }

    public void setValList(List<Map<Integer, String>> valList) {
        this.valList = valList;
    }

    public Integer getStartColumnNum() {
        return startColumnNum;
    }

    public void setStartColumnNum(Integer startColumnNum) {
        this.startColumnNum = startColumnNum;
    }

    public Integer getStartRowNum() {
        return startRowNum;
    }

    public void setStartRowNum(Integer startRowNum) {
        this.startRowNum = startRowNum;
    }

    public Integer getEndColumnNum() {
        return endColumnNum;
    }

    public void setEndColumnNum(Integer endColumnNum) {
        this.endColumnNum = endColumnNum;
    }

    public Integer getEndRowNum() {
        return endRowNum;
    }

    public void setEndRowNum(Integer endRowNum) {
        this.endRowNum = endRowNum;
    }

    public int getMaxColumnNum() {
        return maxColumnNum;
    }

    public void setMaxColumnNum(int maxColumnNum) {
        this.maxColumnNum = maxColumnNum;
    }

    public int getMaxRowNum() {
        return maxRowNum;
    }

    public void setMaxRowNum(int maxRowNum) {
        this.maxRowNum = maxRowNum;
    }

    public boolean isNameAndTypeFlag() {
        return nameAndTypeFlag;
    }

    public void setNameAndTypeFlag(boolean nameAndTypeFlag) {
        this.nameAndTypeFlag = nameAndTypeFlag;
    }

    public Map<String, String> getHeadValAndTypeMap() {
        return headValAndTypeMap;
    }

    public void setHeadValAndTypeMap(Map<String, String> headValAndTypeMap) {
        this.headValAndTypeMap = headValAndTypeMap;
    }

    public List<Map<String, String>> getContentValAndTypeList() {
        return contentValAndTypeList;
    }

    public void setContentValAndTypeList(List<Map<String, String>> contentValAndTypeList) {
        this.contentValAndTypeList = contentValAndTypeList;
    }
}

工具类

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.metadata.data.ReadCellData;
import com.alibaba.excel.read.builder.ExcelReaderBuilder;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.util.DateUtils;
import org.apache.commons.lang.time.DateFormatUtils;

import java.io.DataInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import static com.nature.ysl.zl.excel.MyExcelUtils.SPECTYPESIGN;

public class MyExcelUtils {

    public static final String SPECTYPESIGN = "#@#";
    // 字母
    private static final String MATCH_LETTERS = "[a-zA-Z]+";
    // 匹配字母开头
    private static final String LETTER_START_REGEX = "^[a-zA-Z]*$";
    // 提取单元格的行数
    private static final String ROW_REGEX = "[\\d]+";
    // 匹配中文
    private static final String CHINESETYPE_REGEX = ".*[\\u4e00-\\u9fa5].*";
    // 单元格数值类型
    private static final String NUMBERTYPE_REGEX = "[0|.]+";
    // 匹配除了,.数字之前外的所有符号,0_ 代表整数
    private static final String EXCLUDE_SPECIFIC_REGEX = ".*[^,.#\\d].*";
    private static final String CELL_REGEX = "[A-Z]+[1-9][\\d]*";

    private static final Pattern date_ptrn1 = Pattern.compile("^\\[\\$\\-.*?\\]");
    private static final Pattern date_ptrn2 = Pattern.compile("^\\[[a-zA-Z]+\\]");
    private static final Pattern date_ptrn4 = Pattern.compile("^\\[([hH]+|[mM]+|[sS]+)\\]");
    private static final Pattern date_ptrn5 = Pattern.compile("^\\[DBNum(1|2|3)\\]");

    /**
     * 获取excel文件所有的sheet页名称
     *
     * @param ins
     * @return
     */
    public static List<String> getSheetList(InputStream ins) {
        List<String> resList = new ArrayList<>();
        ExcelReaderBuilder excelReaderBuilder = EasyExcel.read(ins);
        ExcelReader excelReader = excelReaderBuilder.build();
        List<ReadSheet> sheetList = excelReader.excelExecutor().sheetList();
        sheetList.forEach(e -> resList.add(e.getSheetName()));
        return resList;
    }
    /**
     * 获取sheet种的内容，前rowNum行，null则查询全部
     * @param ins
     * @param sheetName
     * @param rowNum
     * @return
     */
    public static List<List<String>> getSheetData(InputStream ins, String sheetName, Integer rowNum) {
        if (rowNum != null && rowNum == 0){
            return null;
        }
        InputStream is = null;
        is = new DataInputStream(ins);
        MyExcelListener listen = new MyExcelListener();
        // 设置读取的行数
        listen.setEndRowNum(rowNum);

        ExcelReaderBuilder read = EasyExcel.read(is, listen);
        read.sheet(sheetName).doRead();
        // 读取sheet中最大的列数
        int maxColumnNum = listen.getMaxColumnNum();
        // 第一行数据(也就是表头数据)，下标从0开始
        Map<Integer, String> headMap = listen.getHeadMap();
        // 其余数据，下标从0开始
        List<Map<Integer, String>> valList = listen.getValList();

        // 为还原excel原始样式，以最大列数为约束，遍历headMap、valList，获取不到的数据以空填充
        // 如果rowNum不为空，开始填充数据
        rowNum = rowNum == null?listen.getMaxRowNum():rowNum;
        List<List<String>> resList = new ArrayList();
        for (int i = 0; i < rowNum; i++) {
            List<String> list = new ArrayList<>();
            for (int j = 0; j < maxColumnNum; j++) {
                if (i == 0){
                    // 如果不存在默认返回空
                    list.add(headMap.getOrDefault(j,""));
                }else {
                    list.add(valList.get(i-1).getOrDefault(j,""));
                }
            }
            resList.add(list);
        }
        return resList;
    }

    /**
     * 部分格式数据处理
     * @param cell
     * @param formatVal
     * @return
     */
    public static String getOtherDateFormat(ReadCellData cell, String formatVal){

        String newFormatStr = MyExcelTypeEnum.getFormatType(formatVal);

        if (cell.getDataFormatData().getIndex() == 22) {// excel显示格式为：2012/1/20 23:00
            return DateFormatUtils.format(doubleToDate(cell.getNumberValue().doubleValue()), "yyyy/M/d H:mm");
        } else if (cell.getDataFormatData().getIndex() == 30) {
            return DateFormatUtils.format(doubleToDate(cell.getNumberValue().doubleValue()), "M/d/yy");
        }
        if (StringUtils.isEmpty(newFormatStr)){
            return null;
        }
        String dateStr = DateFormatUtils.format(doubleToDate(cell.getNumberValue().doubleValue()),newFormatStr);

        if (StringUtils.contains(dateStr,SPECTYPESIGN)){
            // 二〇〇五年一月十五日
            return convertNumberToChineseDate(dateStr,SPECTYPESIGN);
        } else if (StringUtils.contains(newFormatStr," aa")) {
            // 2011/1/3 6:00 AM
            return DateFormatUtils.format(doubleToDate(cell.getNumberValue().doubleValue()), newFormatStr, Locale.ENGLISH);
        } else if (StringUtils.contains(newFormatStr,"MMM")) {
            // J 、J-23
            return getEnglishDate(cell.getNumberValue().doubleValue(),newFormatStr);
        }
        return dateStr;
    }


    /**
     * 将yyyy年-MM月-dd日 格式日期转换成中文格式
     * 例：2000-1-1 --> 二〇〇〇年一月一日
     */
    public static String convertNumberToChineseDate(String date,String splitStr) {
        if (date == null || "".equals(date)) {
            return date;
        }

        try {
            String[] dates = date.split(splitStr);
            StringBuilder chineseDateSbu = new StringBuilder();
            for (int i = 0; i < dates.length; i++) {
                chineseDateSbu.append(formatDigit(dates[i]));
            }

            return chineseDateSbu.toString();
        } catch (Exception e) {
            return null;
        }
    }

    /**
     * 截取后的年月日转换为中文
     * 例1：2000 --> 二〇〇〇
     * 例1：10 --> 十
     */
    public static String formatDigit(String sign) {
        if (sign == null || "".equals(sign)) {
            return null;
        }

        char[] signChars = sign.toCharArray();
        StringBuilder chinese = new StringBuilder();
        if (signChars.length > 4 || signChars.length < 3) {
            for (char c : signChars) {
                chinese.append(MyChineseNumberEnum.getValue(String.valueOf(c)));
            }
        } else {
            if (sign.startsWith("0")){
                for (char c : signChars) {
                    chinese.append(MyChineseNumberEnum.getValue(String.valueOf(c)));
                }
            }else {
                if (sign.equals(MyChineseNumberEnum.getValue(sign))){
                    String subStr = sign.substring(0,sign.length()-1);
                    String unitStr = sign.substring(sign.length()-1);
                    chinese.append(MyChineseNumberEnum.getValue(subStr)+unitStr);
                }else {
                    chinese.append(MyChineseNumberEnum.getValue(sign));
                }
            }
        }

        return chinese.toString();
    }

    public static String getCellType(ReadCellData cell, String cellVal){
        if (isStringTypeFromCell(cell,cellVal)){
            return "STRING";
        }
        switch (cell.getType()) {
            case NUMBER:
                String formatVal = cell.getDataFormatData().getFormat();
                // 将excel的类型处理成java可以识别的类型
                String newFormat = MyExcelTypeEnum.getFormatType(formatVal);
                if (newFormat == null || "".equals(newFormat)){
                    newFormat = formatJavaType(formatVal);
                }

                if (DateUtils.isADateFormat(cell.getDataFormatData().getIndex(),formatVal)) {
                    // 将数据格式化
                    String originVal = getOtherDateFormat(cell,formatVal);
                    if (!StringUtils.isEmpty(formatVal)
                            && (isChineseType(newFormat) || isChineseType(originVal) )) {
                        return "STRING";
                    }
                    // 暂时简单处理，如果是包含h:mm:ss 默认按照时间格式处理，剩下默认按照时间戳处理
                    if (StringUtils.contains(formatVal, "mm:ss")) {
                        return "TIMESTAMP";
                    } else {
                        return "DATE";
                    }
                } else if (StringUtils.equalsIgnoreCase("General",newFormat)) {
                    return "STRING";
                } else {
                    // 自定义的事件类型
                    String otherDateType = getOtherDateType(cell.getDataFormatData().getIndex());
                    if (!StringUtils.isEmpty(otherDateType)){
                        return otherDateType;
                    }
                    if (!StringUtils.isEmpty(newFormat) && newFormat.matches(NUMBERTYPE_REGEX)) {
                        if (StringUtils.equals("0", newFormat) || !newFormat.contains(".")) {
                            return "NUMBER";
                        } else {
                            return "DOUBLE";
                        }
                    } else if (!StringUtils.isEmpty(newFormat) && newFormat.matches(EXCLUDE_SPECIFIC_REGEX)) {
                        // 解决货币之类的数据类型判断如 ￥14
                        return "STRING";
                    } else {
                        return "DOUBLE";
                    }
                }
            case STRING:
                // 首行如果是表头汉字，全部都是string
                String format = cell.getDataFormatData().getFormat();
                if (StringUtils.equalsIgnoreCase("General", format)) {
                    return "STRING";
                } else if (!StringUtils.isEmpty(format) && format.matches(NUMBERTYPE_REGEX)) {
                    if (StringUtils.equals("0", format)) {
                        return "NUMBER";
                    } else {
                        return "DOUBLE";
                    }
                } else if (isChineseType(MyExcelTypeEnum.getFormatType((format)))) {
                    return "STRING";
                } else if (StringUtils.containsIgnoreCase(format, "mm:ss")) {
                    return "TIMESTAMP";
                } else if (StringUtils.containsIgnoreCase(format, "yyyy")
                        || StringUtils.containsIgnoreCase(format, "mm")
                        || StringUtils.containsIgnoreCase(format, "dd")) {
                    return "DATE";
                } else {
                    return "STRING";
                }
            case BOOLEAN:
                return "BOOLEAN";
            default:
                return "STRING";
        }
    }

    /**
     * 用于解析excel表头及类型
     * @param ins
     * @param sheetName
     * @param startCell 起始单元格 默认A1
     * @param endCell 结束单元格
     * @return
     */
    public static List<Map<String,String>> getSheetColumnNameAndType(InputStream ins, String sheetName, String startCell, String endCell){
        List<Map<String,String>> resList = new ArrayList<>();
        // 提起起始行数列数，结束行数列数

        Integer startColumnNum = excelCellToColumnNum(startCell);
        Integer startRowNum = excelCellToRowNum(startCell);
        Integer endColumnNum = excelCellToColumnNum(endCell);
        Integer endRowNum = excelCellToColumnNum(endCell);

        InputStream is = null;
        is = new DataInputStream(ins);
        MyExcelListener listen = new MyExcelListener();
        // 获取字段名称和类型
        listen.setNameAndTypeFlag(true);
        // 设置读取的列片区
        listen.setStartColumnNum(startColumnNum);
        listen.setEndColumnNum(endColumnNum);
        // 设置读取的行片区
        listen.setStartRowNum(startRowNum);
        listen.setEndRowNum(endRowNum);

        ExcelReaderBuilder read = EasyExcel.read(is, listen);
        read.sheet(sheetName).doRead();

        // 如果endColumnNum==null则无结束单元格，maxColumn就是excel最大的列，反之则取endColumnNum
        Integer maxColumn = endColumnNum != null ? endColumnNum : listen.getMaxColumnNum();

        // 组装首行内容 （受到其实单元格及结束单元格约束） headMap从0开始
        // 第一行数据(也就是表头数据)，下标从0开始，startRowNum默认1（A1）
        Map<String, String> headMap = listen.getHeadValAndTypeMap();
        // 第二行数据，下标从0开始。包含内容及类型
        List<Map<String, String>>  valList = listen.getContentValAndTypeList();

        Map<String, String> resHeadMap = null;
        String type = "STRING";
        for (int i = startColumnNum-1; i < maxColumn; i++) {
            resHeadMap = new HashMap<>();
            if (startRowNum==1){
                // 起始行数是1 head就会有数据，且是需要的数据
                if (valList.size()>0 && !StringUtils.isEmpty(valList.get(0).get(i+"_type"))){
                    type = valList.get(0).get(i+"_type");
                } else if (!StringUtils.isEmpty(headMap.get(i+"_type"))) {
                    type = headMap.get(i+"_type");
                }
                resHeadMap.put("columnName",headMap.getOrDefault(String.valueOf(i),""));
                resHeadMap.put("columnType",type);
                resList.add(resHeadMap);
            }else if(startRowNum>1 && valList.size()>0){
                // 起始行数是1 head中的数据并不是需要的数据，则从valList取 0 和 1 分别做 头和内容
                if (valList.size()>1 && valList.get(1)!=null && !StringUtils.isEmpty(valList.get(1).get(i+"_type"))){
                    type = valList.get(1).get(i+"_type");
                } else if (!StringUtils.isEmpty(valList.get(0).get(i+"_type"))) {
                    type = valList.get(0).get(i+"_type");
                }
                resHeadMap.put("columnName",valList.get(0).getOrDefault(String.valueOf(i),""));
                resHeadMap.put("columnType",type);
                resList.add(resHeadMap);
            }

        }

        return resList;
    }

    public static String checkExcelCellString(String startCell, String endCell) {

        // 起始单元格校验
        if (!StringUtils.isEmpty(startCell)) {
            if (!checkExcelCellSpecs(startCell)) {
                // 起始单元格不符合规范
                return "起始单元格不符合规范";
            }
        } else {
            // 起始单元格不得为空
            return "起始单元格不得为空";
        }

        // 结束单元格但如果不为空，则需要合法校验
        if (!StringUtils.isEmpty(endCell)) {
            if (!checkExcelCellSpecs(endCell)) {
                // 结束单元格不符合规范
                return "结束单元格不符合规范";
            }
        }

        // 单元格全部合法后，进行逻辑约束校验，起始单元格后的数字要小于等于结束单元格后缀
        // 列数校验 结束列数 >= 起始列数
        Integer startColumnNum = excelCellToColumnNum(startCell);
        Integer endColumnNum = excelCellToColumnNum(endCell);

        // 行数校验 结束行数 >= 起始行数
        Integer startRowNum = excelCellToRowNum(startCell);
        Integer endRowNum = excelCellToRowNum(endCell);

        if (endColumnNum != null) {
            if (startColumnNum > endColumnNum){
                return "起始单元格列必须小于等于结束起始单元格列";
            }else if (startRowNum > endRowNum){
                return "起始单元格行必须小于等于结束起始单元格行";
            }
        }
        return "";
    }

    /**
     * 检验单元格合法性
     * @param excelCell
     * @return
     */
    private static boolean checkExcelCellSpecs(String excelCell) {

        return !StringUtils.isEmpty(excelCell) && excelCell.matches(CELL_REGEX);
    }

    /*
     * 提取单元格字母对应列,如果colStr==null，则返回null
     * A1 -> 1、B1 -> 2、C1 -> 3
     */
    public static Integer excelCellToColumnNum(String colStr) {

        Integer result = null;
        if (!StringUtils.isEmpty(colStr)) {
            result = 0;
            int length = colStr.length();
            int j = 0;
            int num = 0;
            for (int i = 0; i < length; i++) {
                char ch = colStr.charAt(length - i - 1);
                if (String.valueOf(ch).matches(LETTER_START_REGEX)) {
                    num = ch - 'A' + 1;
                    num *= Math.pow(26, j);
                    j++;
                    result += num;
                }
            }
        }
        return result;
    }

    /**
     * 提取单元格对应行返回值：Integer
     * 提取单元格字母对应行,如果colStr==null，则返回null
     * A1 -> 1、B2 -> 2、C3 -> 3
     */
    public static Integer excelCellToRowNum(String colStr) {
        String numStr = excelCellToRowString(colStr);
        if (StringUtils.isEmpty(numStr)) {
            return null;
        }
        return Integer.parseInt(numStr);
    }
    /**
     * 提取单元格对应行返回值：string
     * @param colStr
     * @return
     */
    public static String excelCellToRowString(String colStr) {
        String res = null;
        if (!StringUtils.isEmpty(colStr)) {
            Matcher matcher = Pattern.compile(ROW_REGEX).matcher(colStr);
            if (matcher.find()) {
                res = matcher.group();
            }
        }
        return res;
    }

    /**
     * 英文缩写的日期处理
     * @param numericCellValue
     * @param formatVal
     * @return
     */
    private static String getEnglishDate(double numericCellValue, String formatVal){
        String dateStr = DateFormatUtils.format(doubleToDate(numericCellValue), formatVal, Locale.ENGLISH);
        if (StringUtils.equals(formatVal,"MMMMM")){
            // excel显示格式为：F
            return StringUtils.substring(dateStr,0,1);
        } else if (StringUtils.equals(formatVal,"MMMMM-yy")) {
            // excel显示格式为：F-23
            String letters = null;
            Matcher matcher = Pattern.compile(MATCH_LETTERS).matcher(dateStr);
            if (matcher.find()) {
                letters = matcher.group();
            }
            if (!StringUtils.isEmpty(letters) && !StringUtils.isEmpty(dateStr)){
                return StringUtils.replace(dateStr,letters,StringUtils.substring(dateStr,0,1));
            }
            return dateStr;
        }
        return dateStr;
    }

    private static boolean isStringTypeFromCell(ReadCellData cell, String cellValue){
        // 如果数据中含有中文，则直接返回string格式
        if (!StringUtils.isEmpty(cellValue) && cellValue.matches(CHINESETYPE_REGEX)){
            return true;
        }
        String newFormatStr = MyExcelTypeEnum.getFormatKey(cell.getDataFormatData().getFormat());
        if (StringUtils.containsIgnoreCase(newFormatStr,"EN") || StringUtils.containsIgnoreCase(cellValue,"AM")
                || StringUtils.containsIgnoreCase(cellValue,"PM")){
            return true;
        }
        short shortNum = cell.getDataFormatData().getIndex();


        switch (shortNum){
            case 46:
                // 1184426:00:00
                return true;
        }
        return false;
    }

    private static boolean isChineseType(String param){
        if (!StringUtils.isEmpty(param)){
            if(param.matches(CHINESETYPE_REGEX)
                    || param.contains("E")
                    || param.contains("MMMM")){
                return true;
            }
        }
        return false;
    }

    private static String getOtherDateType(short shortNum){
        switch (shortNum) {
            case 14:
            case 30:
                return "DATE";
            case 31:
            case 57:
                return "TIMESTAMP";
        }
        return null;
    }


    private static String formatJavaType(String formatVal){
        String fs = formatVal;
        int length = formatVal.length();
        StringBuilder sb = new StringBuilder(length);

        int separatorIndex;
        for(separatorIndex = 0; separatorIndex < length; ++separatorIndex) {
            char c = fs.charAt(separatorIndex);
            if (separatorIndex < length - 1) {
                char nc = fs.charAt(separatorIndex + 1);
                if (c == '\\') {
                    switch (nc) {
                        case ' ':
                        case ',':
                        case '-':
                        case '.':
                        case '\\':
                            continue;
                    }
                } else if (c == ';' && nc == '@') {
                    ++separatorIndex;
                    continue;
                }
            }

            sb.append(c);
        }

        fs = sb.toString();
        // excel设置单元格格式 使用数值
        fs = StringUtils.replace(fs,"0_ ","0");
        fs = StringUtils.replace(fs,"0_)","0");

        if (date_ptrn4.matcher(fs).matches()) {
            return fs;
        } else {
            fs = date_ptrn5.matcher(fs).replaceAll("");
            fs = date_ptrn1.matcher(fs).replaceAll("");
            fs = date_ptrn2.matcher(fs).replaceAll("");
            separatorIndex = fs.indexOf(59);
            if (0 < separatorIndex && separatorIndex < fs.length() - 1) {
                fs = fs.substring(0, separatorIndex);
            }
            return fs;
        }
    }

    private static Date doubleToDate(Double date){
        Calendar base = Calendar.getInstance();
        base.set(1899, 11, 30, 0, 0, 0);
        base.add(Calendar.DATE, date.intValue());
        base.add(Calendar.MILLISECOND,(int)((date % 1) * 24 * 60 * 60 * 1000));
        return base.getTime();
    }



}
enum MyChineseNumberEnum {

    /**
     * 0
     */
    ZERO("0", "〇"),
    ZERO_DOUBLE("00", "〇〇"),

    /**
     * 1
     */
    ONE("1", "一"),

    /**
     * 2
     */
    TWO("2", "二"),

    /**
     * 3
     */
    THREE("3", "三"),

    /**
     * 4
     */
    FOUR("4", "四"),

    /**
     * 5
     */
    FIVE("5", "五"),

    /**
     * 6
     */
    SIX("6", "六"),

    /**
     * 7
     */
    SEVEN("7", "七"),

    /**
     * 8
     */
    EIGHT("8", "八"),

    /**
     * 9
     */
    NINE("9", "九"),

    /**
     * 10
     */
    TEN("10", "十"),

    /**
     *
     */
    ELEVEN("11", "十一"),

    /**
     *
     */
    TWELVE("12", "十二"),

    /**
     *
     */
    THIRTEEN("13", "十三"),

    /**
     *
     */
    FOURTEEN("14", "十四"),

    /**
     *
     */
    FIFTEEN("15", "十五"),

    /**
     *
     */
    SIXTEEN("16", "十六"),

    /**
     *
     */
    SEVENTEEN("17", "十七"),

    /**
     *
     */
    EIGHTEEN("18", "十八"),

    /**
     *
     */
    NINETEEN("19", "十九"),

    /**
     *
     */
    TWENTY("20", "二十"),

    /**
     *
     */
    TWENTY_ONE("21", "二十一"),

    /**
     *
     */
    TWENTY_TWO("22", "二十二"),

    /**
     *
     */
    TWENTY_THREE("23", "二十三"),

    /**
     *
     */
    TWENTY_FOUR("24", "二十四"),

    /**
     *
     */
    TWENTY_FIVE("25", "二十五"),

    /**
     *
     */
    TWENTY_SIX("26", "二十六"),

    /**
     *
     */
    TWENTY_SEVEN("27", "二十七"),

    /**
     *
     */
    TWENTY_EIGHT("28", "二十八"),

    /**
     *
     */
    TWENTY_NINE("29", "二十九"),

    /**
     *
     */
    THIRTY("30", "三十"),

    /**
     *
     */
    THIRTY_ONE("31", "三十一"),
    ;

    private final String numberCode;
    private final String chineseCode;

    MyChineseNumberEnum(String numberCode, String chineseCode) {
        this.numberCode = numberCode;
        this.chineseCode = chineseCode;
    }

    public String getNumberCode() {
        return numberCode;
    }

    public String getChineseCode() {
        return chineseCode;
    }

    static final Map<String, String> SEND_METHOD_MAP = new HashMap<>();

    static {
        for (MyChineseNumberEnum code : MyChineseNumberEnum.values()) {
            SEND_METHOD_MAP.put(code.getNumberCode(), code.getChineseCode());
        }
    }

    public static String getValue(String key) {
        return SEND_METHOD_MAP.getOrDefault(key,key);
    }
}
enum MyExcelTypeEnum {

    // &$# 为二〇〇五年一月十五日格式数据的再次格式化标识
    DATE_CAP_1("[DBNum1][$-804]yyyy\"年\"m\"月\"d\"日\"","yyyy年"+SPECTYPESIGN+"M月"+SPECTYPESIGN+"dd日"),
    DATE_CAP_2("[DBNum1][$-804]yyyy\"年\"m\"月\"","yyyy年"+SPECTYPESIGN+"M月"),
    DATE_CAP_3("[DBNum1][$-804]m\"月\"d\"日\"","M月"+SPECTYPESIGN+"dd日"),
    DATE_CAP_4("[DBNum1]h\"时\"mm\"分\"","H时"+SPECTYPESIGN+"mm分"),
    DATE_CAP_5("[DBNum1]上午/下午h\"时\"mm\"分\"","aa"+SPECTYPESIGN+"h时"+SPECTYPESIGN+"mm分"),

    DATE_EN_SHORTHAND_1("d-mmm","d-MMM"),
    DATE_EN_SHORTHAND_2("d-mmm-yy","d-MMM-yy"),
    DATE_EN_SHORTHAND_3("dd\\-mmm\\-yy","d-MMM-yy"),
    DATE_EN_SHORTHAND_4("mmm-yy","MMM-yy"),
    DATE_EN_SHORTHAND_5("mmmm\\-yy","MMMM-yy"),
    DATE_EN_SHORTHAND_6("mmmm-yy","MMMM-yy"),
    DATE_EN_SHORTHAND_7("mmmmm","MMMMM"),
    DATE_EN_SHORTHAND_8("mmmmm\\-yy","MMMMM-yy"),

    DATE_TIME_1("h\"时\"mm\"分\"","H时mm分"),
    DATE_TIME_2("h\"时\"mm\"分\"ss\"秒\"","H时mm分ss秒"),
    DATE_TIME_3("上午/下午h\"时\"mm\"分\"","aah时mm分"),
    DATE_TIME_4("上午/下午h\"时\"mm\"分\"ss\"秒\"","aah时mm分ss秒"),

    DATE_EN_1("yyyy/m/d\\ h:mm\\ AM/PM","yyyy/M/d h:mm aa"),
    DATE_EN_2("h:mm\\ AM/PM","h:mm aa"),
    DATE_EN_3("h:mm:ss\\ AM/PM","h:mm:ss aa");


    String excelType;
    String formatType;

    MyExcelTypeEnum(String excelType, String formatType) {
        this.excelType = excelType;
        this.formatType = formatType;
    }

    public static String getFormatType(String excelType) {
        for (MyExcelTypeEnum excelTypeEnum : MyExcelTypeEnum.values()) {
            if (excelTypeEnum.excelType.equals(excelType)) {
                return excelTypeEnum.formatType;
            }
        }
        return null;
    }

    public static String getFormatKey(String excelType) {
        for (MyExcelTypeEnum excelTypeEnum : MyExcelTypeEnum.values()) {
            if (excelTypeEnum.excelType.equals(excelType)) {
                return excelTypeEnum.name();
            }
        }
        return excelType;
    }
}

class StringUtils{
    public static boolean isEmpty(String str){
        return str==null || "".equals(str);
    };
    public static String replace(String str, String oldStr){
        if (isEmpty(str)) return str;
        return str.replace(oldStr,"");
    };
    public static String replace(String str, String oldStr, String newStr){
        if (isEmpty(str)) return str;
        return str.replace(oldStr,newStr);
    };
    public static boolean containsIgnoreCase(String str, String containsStr){
        if (isEmpty(str)) return false;
        return str.toLowerCase().contains(containsStr.toLowerCase());
    };
    public static boolean equals(String str, String anObject){
        if (isEmpty(str)) return false;
        return str.equals(anObject);
    };
    public static boolean equalsIgnoreCase(String str, String anObject){
        if (isEmpty(str)) return false;
        return str.equalsIgnoreCase(anObject);
    };
    public static boolean contains(String str, String anObject){
        if (isEmpty(str)) return false;
        return str.contains(anObject);
    };
    public static String substring(String str, int beginIndex, int endIndex){
        if (isEmpty(str)) return str;
        return str.substring(beginIndex, endIndex);
    };
}

测试类

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.List;
import java.util.Map;

public class Mytest {
    public static void main(String[] args) throws Exception {

        String filep = "C:\\test.xlsx";
        String sheet = "Sheet1";
        String startCell = "A1";
        String end = null;//"C3";

        File file = new File(filep);
        InputStream ins2 = new FileInputStream(file);

        String checkMsg = MyExcelUtils.checkExcelCellString(startCell,end);
        System.out.println(checkMsg);

        List<Map<String,String>> ll =
                MyExcelUtils.getSheetColumnNameAndType(ins2,sheet,startCell,end);
        ll.forEach(e-> System.out.println("columnName---"+e.get("columnName")+"---"+e.get("columnType")));

    }
}

部分测试结果

备注

excel类型比较多，这里适配的也只是一部分，逐步完善

其他

Chat2DB：https://easyexcel.opensource.alibaba.com/docs/current/
官方网站：https://easyexcel.opensource.alibaba.com/
github地址：https://github.com/alibaba/easyexcel
gitee地址：https://gitee.com/easyexcel/easyexcel