526互联

记一次EasyExcel bug排查过程

发布时间 2024-01-01 10:16:40作者: 小艾咪

问题现象

EasyExcel 在读取yyyy年MM月 hh时mm分ss秒格式的时间类单元格时，分位数据会错乱，其值为月份数据

问题复现

首先复现下出现的问题

有如下数据

总人数 合格人数 统计时间
10 8 2023年04月01日 08时00分00秒

其中统计时间按如下格式设置

这里需要注意的是，该单元格存储为日期类型。（easyexcel 读取到的实际为NUMBER,后经Double转日期）

创建对应实体类

package com.alibaba.easyexcel.test.temp;

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
@Data
public class TestData {

    @ExcelProperty(index = 0)
    private String total;

    @ExcelProperty(index = 1)
    private String standard;

    @ExcelProperty(index = 2)
    private String statTime;
}

使用 EasyExcel API 读取测试数据


public class TimeFormatTest {
    private static final Logger logger = LoggerFactory.getLogger(DataFormatterTest.class);

    public static void main(String[] args) {
        EasyExcel.read("D:\\Wxm\\工作簿1.xlsx",TestData.class, new AnalysisEventListener<TestData>() {
            @Override
            public void invoke(TestData data, AnalysisContext context) {
                logger.info("Stat time: {}", data.getStatTime());
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext context) {

            }
        }).doReadAll();
    }
}

此时控制台将输出

[main] INFO com.alibaba.easyexcel.test.temp.DataFormatterTest - Stat time: 2023年4月1日  08时04分00秒

可以看到与我们的实际数据并不一致，年月日时秒都无误，仅分位的值出现了问题。如果数据量比较大很容易发现规律。那就是此时分的值与月份一致。但如果您正在格式化Time 而非 DateTime。如下图所示

那您的分位的值将始终1(也可能是12，根据时间算法有所不同。如果是1899年就是12月，如果是1900年就是1月)。

最新版时间转换方式由DateUtil.getJavaDate()换成了Date.from(getLocalDateTime(date, use1904windowing).atZone(ZoneId.systemDefault()).toInstant()) 会有格式化Time类时间不准确问题。不过这就是另一个值得讨论的问题了，究其原因是1900年前后JDK Date类使用的历法不一样。1900年之前为 Julian 1900年之后为 Gregorian。

问题定位

回归正题，让我们继续定位分位数据错误的问题。正所谓授人以鱼不如授人以渔。此次我们将从表象开始一步步深入bug产生的根源。想直接了解根因和解决方案可直接跳过本章节

首先需要明确的一点是，所有单元格的数据转化为Java bean 属性是都会经过一个转换的过程。从 ExcelProperty 注解中我们能够看到有一个converter()属性，其默认值为AutoConverter.class。那么就可以以此找到其实现的接口Converter进而找到

default T convertToJavaData(ReadConverterContext<?> context) throws Exception {
    return convertToJavaData(context.getReadCellData(), context.getContentProperty(),
        context.getAnalysisContext().currentReadHolder().globalConfiguration());
}

以为实现类巨多，所以我们可以在接口的默认实现上加入断点。在运行时Step into进入具体的实现类。最终此类时间单元格会被StringNumberConverter处理,可定位到如下代码

public String convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty,
    GlobalConfiguration globalConfiguration) {
    // If there are "DateTimeFormat", read as date
    if (contentProperty != null && contentProperty.getDateTimeFormatProperty() != null) {
        return DateUtils.format(cellData.getNumberValue(),
            contentProperty.getDateTimeFormatProperty().getUse1904windowing(),
            contentProperty.getDateTimeFormatProperty().getFormat());
    }
    // If there are "NumberFormat", read as number
    if (contentProperty != null && contentProperty.getNumberFormatProperty() != null) {
        return NumberUtils.format(cellData.getNumberValue(), contentProperty);
    }
    // Excel defines formatting
    boolean hasDataFormatData = cellData.getDataFormatData() != null
        && cellData.getDataFormatData().getIndex() != null && !StringUtils.isEmpty(
        cellData.getDataFormatData().getFormat());
    //此处会被判定为 true
    if (hasDataFormatData) {
        return NumberDataFormatterUtils.format(cellData.getNumberValue(),
            cellData.getDataFormatData().getIndex(), cellData.getDataFormatData().getFormat(), globalConfiguration);
    }
    // Default conversion number
    return NumberUtils.format(cellData.getNumberValue(), contentProperty);
}

最终形成如下调用链

--StringNumberConverter > convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty,
        GlobalConfiguration globalConfiguration):
return NumberDataFormatterUtils.format(cellData.getNumberValue(),
                cellData.getDataFormatData().getIndex(), cellData.getDataFormatData().getFormat(), globalConfiguration);
                |
                |
                V
--NumberDataFormatterUtils > format(BigDecimal data, Short dataFormat, String dataFormatString,
        GlobalConfiguration globalConfiguration):
return format(data, dataFormat, dataFormatString, globalConfiguration.getUse1904windowing(),
            globalConfiguration.getLocale(), globalConfiguration.getUseScientificFormat());
                |
                |
                V
--NumberDataFormatterUtils > format(BigDecimal data, Short dataFormat, String dataFormatString, Boolean use1904windowing,
        Locale locale, Boolean useScientificFormat):
return dataFormatter.format(data, dataFormat, dataFormatString);
                |
                |
                V
--DataFormatter > format(BigDecimal data, Short dataFormat, String dataFormatString):
 return getFormattedDateString(data.doubleValue(), dataFormat, dataFormatString);
                |
                |
                V

此时再来看 getFormattedDateString 的实现

private String getFormattedDateString(Double data, Short dataFormat, String dataFormatString) {
    Format dateFormat = getFormat(data, dataFormat, dataFormatString);
    if (dateFormat instanceof ExcelStyleDateFormatter) {
        // Hint about the raw excel value
        ((ExcelStyleDateFormatter)dateFormat).setDateToBeFormatted(data);
    }
    return performDateFormatting(DateUtils.getJavaDate(data, use1904windowing), dateFormat);
}

越过 Format dateFormat = getFormat(data, dataFormat,dataFormatString); 通过调试器观察 dateFormat的值可发现端倪

可以看到分位上使用的是月占位符。所以接下来需要检查getFormat(data, dataFormat,dataFormatString) 找到分位置占位符被替换的原因

调用链如下

--DataFormatter > getFormattedDateString(Double data, Short dataFormat, String dataFormatString):
Format dateFormat = getFormat(data, dataFormat,dataFormatString);
                |
                |
                V
--DataFormatter > getFormat(Double data, Short dataFormat, String dataFormatString):
format = createFormat(dataFormat, formatStr);
                |
                |
                V
--DataFormatter > createFormat(Short dataFormat, String dataFormatString):
return createDateFormat(formatStr

此时查看createDateFormat(String pFormatStr), 核心问题就出在这里。改方法的作用是将excel内的时间格式转化为 Java 中的时间。excel 内时间规则excel time format

核心问题是excel中有这样的约定

If you use "m" immediately after the "h" or "hh" code or immediately before the "ss" code, Excel displays minutes instead of the month.

也就是说excel中，日期和分钟都用m表示，紧跟h或hh后的m需要被解析为分钟，靠近ss前的m会被解析为分钟，其他情况m会被解析为月。接下来在代码中以注释的形式展示问题。带有//@@@@@开头的是展示问题的注释其余为源码自带

    private Format createDateFormat(String pFormatStr) {
        String formatStr = pFormatStr;
        formatStr = formatStr.replaceAll("\\\\-", "-");
        formatStr = formatStr.replaceAll("\\\\,", ",");
        formatStr = formatStr.replaceAll("\\\\\\.", "."); // . is a special regexp char
        formatStr = formatStr.replaceAll("\\\\ ", " ");
        formatStr = formatStr.replaceAll("\\\\/", "/"); // weird: m\\/d\\/yyyy
        formatStr = formatStr.replaceAll(";@", "");
        formatStr = formatStr.replaceAll("\"/\"", "/"); // "/" is escaped for no reason in: mm"/"dd"/"yyyy
        formatStr = formatStr.replace("\"\"", "'"); // replace Excel quoting with Java style quoting
        formatStr = formatStr.replaceAll("\\\\T", "'T'"); // Quote the T is iso8601 style dates
        formatStr = formatStr.replace("\"", "");

        boolean hasAmPm = false;
        Matcher amPmMatcher = amPmPattern.matcher(formatStr);
        while (amPmMatcher.find()) {
            formatStr = amPmMatcher.replaceAll("@");
            hasAmPm = true;
            amPmMatcher = amPmPattern.matcher(formatStr);
        }
        formatStr = formatStr.replaceAll("@", "a");

        Matcher dateMatcher = daysAsText.matcher(formatStr);
        if (dateMatcher.find()) {
            String match = dateMatcher.group(0).toUpperCase(Locale.ROOT).replaceAll("D", "E");
            formatStr = dateMatcher.replaceAll(match);
        }

        // Convert excel date format to SimpleDateFormat.
        // Excel uses lower and upper case 'm' for both minutes and months.
        // From Excel help:
        /*
            The "m" or "mm" code must appear immediately after the "h" or"hh"
            code or immediately before the "ss" code; otherwise, Microsoft
            Excel displays the month instead of minutes."
          */
        StringBuilder sb = new StringBuilder();
        char[] chars = formatStr.toCharArray();
        //@@@@@ 核心字段，判断代表当前解析的m是否为月
        boolean mIsMonth = true;
        List<Integer> ms = new ArrayList<Integer>();
        boolean isElapsed = false;
        //@@@@@ 遍历格式字符串
        for (int j = 0; j < chars.length; j++) {
            char c = chars[j];
            if (c == '\'') {
                sb.append(c);
                j++;

                // skip until the next quote
                while (j < chars.length) {
                    c = chars[j];
                    sb.append(c);
                    if (c == '\'') {
                        break;
                    }
                    j++;
                }
            } else if (c == '[' && !isElapsed) {
                isElapsed = true;
                mIsMonth = false;
                sb.append(c);
            } else if (c == ']' && isElapsed) {
                isElapsed = false;
                sb.append(c);
            } else if (isElapsed) {
                if (c == 'h' || c == 'H') {
                    sb.append('H');
                } else if (c == 'm' || c == 'M') {
                    sb.append('m');
                } else if (c == 's' || c == 'S') {
                    sb.append('s');
                } else {
                    sb.append(c);
                }
            } else if (c == 'h' || c == 'H') {
                //@@@@@ 当解析到 h 根据excel 的规则。接下来的m需要解析为分
                //@@@@@ 所以 mIsMonth 被置为false
                mIsMonth = false;
                if (hasAmPm) {
                    sb.append('h');
                } else {
                    sb.append('H');
                }
            } else if (c == 'm' || c == 'M') {
                //@@@@@ 如果是月，则解析为 M 即Java 中的月
                if (mIsMonth) {
                    sb.append('M');
                    //@@@@@ 同时将此字符所在的索引添加到 ms 中 ms用于之后
                    //@@@@@ 遍历到秒时判断 m 是否在ss前 以将 M 换为 m
                    ms.add(Integer.valueOf(sb.length() - 1));
                } else {
                    sb.append('m');
                }
            } else if (c == 's' || c == 'S') {
                sb.append('s');
                // if 'M' precedes 's' it should be minutes ('m')
                for (int index : ms) {
                    if (sb.charAt(index) == 'M') {
                        sb.replace(index, index + 1, "m");
                    }
                }
                mIsMonth = true;
                ms.clear();
            //@@@@@ 年，日没有特殊处理。核心问题也就出在这里。如果时间格式为
            //@@@@@ yyyy"年"m"月"d"日"  hh"时"mm"分"ss"秒" 那么解析到时
            //@@@@@ 字符时Character.isLetter(c)成立进入此代码块，mIsMonth
            //@@@@@ 再次被置为 true,下次解析时 m会被解析为月导致时间错位
            } else if (Character.isLetter(c)) {
                mIsMonth = true;
                ms.clear();
                if (c == 'y' || c == 'Y') {
                    sb.append('y');
                } else if (c == 'd' || c == 'D') {
                    sb.append('d');
                } else {
                    sb.append(c);
                }
            } else {
                if (Character.isWhitespace(c)) {
                    ms.clear();
                }
                sb.append(c);
            }
        }
        formatStr = sb.toString();

        try {
            return new ExcelStyleDateFormatter(formatStr, dateSymbols);
        } catch (IllegalArgumentException iae) {
            LOGGER.debug("Formatting failed for format {}, falling back", formatStr, iae);
            // the pattern could not be parsed correctly,
            // so fall back to the default number format
            return getDefaultFormat();
        }

    }

也就是说， immediately after the "h" or "hh" 并不是字符上的紧跟 h 和 hh 而是 h 或 hh 后除其他字符外紧跟在后的m。

其实大多情况下 yyyy-MM-dd HH:mmss 或 yyyy/MM/dd HH:mm:ss比较多。这两种情况
Character.isLetter(c) 不成立会走else分支。

问题修复

那么如何解决，笔者这里提供一种方式（注意这里仅提供临时解决方案，请遵循其对应的开源协议进行操作）

注意此方式仅能解决两个 m 的情况，mmm(月英文缩写)，mmmm(月英文全称)无法解决等其他情况请自行解决。这里抛砖引玉，有其他需要可自行实现

private Format createDateFormat(String pFormatStr) {
    String formatStr = pFormatStr;
    formatStr = formatStr.replaceAll("\\\\-", "-");
    formatStr = formatStr.replaceAll("\\\\,", ",");
    formatStr = formatStr.replaceAll("\\\\\\.", "."); // . is a special regexp char
    formatStr = formatStr.replaceAll("\\\\ ", " ");
    formatStr = formatStr.replaceAll("\\\\/", "/"); // weird: m\\/d\\/yyyy
    formatStr = formatStr.replaceAll(";@", "");
    formatStr = formatStr.replaceAll("\"/\"", "/"); // "/" is escaped for no reason in: mm"/"dd"/"yyyy
    formatStr = formatStr.replace("\"\"", "'"); // replace Excel quoting with Java style quoting
    formatStr = formatStr.replaceAll("\\\\T", "'T'"); // Quote the T is iso8601 style dates
    formatStr = formatStr.replace("\"", "");

    boolean hasAmPm = false;
    Matcher amPmMatcher = amPmPattern.matcher(formatStr);
    while (amPmMatcher.find()) {
        formatStr = amPmMatcher.replaceAll("@");
        hasAmPm = true;
        amPmMatcher = amPmPattern.matcher(formatStr);
    }
    formatStr = formatStr.replaceAll("@", "a");

    Matcher dateMatcher = daysAsText.matcher(formatStr);
    if (dateMatcher.find()) {
        String match = dateMatcher.group(0).toUpperCase(Locale.ROOT).replaceAll("D", "E");
        formatStr = dateMatcher.replaceAll(match);
    }

    // Convert excel date format to SimpleDateFormat.
    // Excel uses lower and upper case 'm' for both minutes and months.
    // From Excel help:
    /*
        The "m" or "mm" code must appear immediately after the "h" or"hh"
        code or immediately before the "ss" code; otherwise, Microsoft
        Excel displays the month instead of minutes."
        */
    StringBuilder sb = new StringBuilder();
    char[] chars = formatStr.toCharArray();
    //@@@@ 用于标记是否在寻找紧跟在 h/hh 后的分钟标记
    boolean findingMinuteMark = false;
    List<Integer> ms = new ArrayList<Integer>();
    boolean isElapsed = false;
    for (int j = 0; j < chars.length; j++) {
        char c = chars[j];
        if (c == '\'') {
            sb.append(c);
            j++;

            // skip until the next quote
            while (j < chars.length) {
                c = chars[j];
                sb.append(c);
                if (c == '\'') {
                    break;
                }
                j++;
            }
        } else if (c == '[' && !isElapsed) {
            isElapsed = true;
            sb.append(c);
        } else if (c == ']' && isElapsed) {
            isElapsed = false;
            sb.append(c);
        } else if (isElapsed) {
            if (c == 'h' || c == 'H') {
                sb.append('H');
            } else if (c == 'm' || c == 'M') {
                sb.append('m');
            } else if (c == 's' || c == 'S') {
                sb.append('s');
            } else {
                sb.append(c);
            }
        } else if (c == 'h' || c == 'H') {
            if (hasAmPm) {
                sb.append('h');
            } else {
                sb.append('H');
            }
            ms.clear();
            //@@@@ h 标记出现寻找紧跟其后的分钟标记置为true
            findingMinuteMark = true;
        } else if (c == 'm' || c == 'M') {
            if (findingMinuteMark) {
                sb.append('m');
                ms.add(sb.length() - 1);
                //@@@@ 判断下一位是否也为m,这里仅找了一个。实际上excel支持最多 4 位m,根据实际情况，可额外处理。
                if (j + 1 < chars.length && (chars[j + 1] == 'm' || chars[j + 1] == 'M')) {
                    sb.append('m');
                    j++;
                }
                //@@@@ 分标记已经出现寻找紧跟h其后的分钟标记重新置为false
                findingMinuteMark = false;
            } else {
                sb.append('M');
                ms.add(sb.length() - 1);
            }
        } else if (c == 's' || c == 'S') {
            sb.append('s');
            // if 'M' precedes 's' it should be minutes ('m')
            for (int index : ms) {
                if (sb.charAt(index) == 'M') {
                    sb.replace(index, index + 1, "m");
                }
            }
            ms.clear();
            //@@@@ 其他标记出现寻找紧跟h其后的分钟标记置为false
            findingMinuteMark = false;
        } else if (Character.isLetter(c)) {
            if (c == 'y' || c == 'Y') {
                sb.append('y');
                ms.clear();
                //@@@@ 其他标记出现寻找紧跟h其后的分钟标记置为false
                findingMinuteMark = false;
            } else if (c == 'd' || c == 'D') {
                sb.append('d');
                ms.clear();
                //@@@@ 其他标记出现寻找紧跟h其后的分钟标记置为false
                findingMinuteMark = false;
            } else {
                sb.append(c);
            }
        } else {
            if (Character.isWhitespace(c)) {
                ms.clear();
            }
            sb.append(c);
        }
    }
    formatStr = sb.toString();

    try {
        return new ExcelStyleDateFormatter(formatStr, dateSymbols);
    } catch (IllegalArgumentException iae) {
        LOGGER.debug("Formatting failed for format {}, falling back", formatStr, iae);
        // the pattern could not be parsed correctly,
        // so fall back to the default number format
        return getDefaultFormat();
    }
}

最后

最有效的办法是修改excel模板（条件允许的话）。

花开花败总归尘，print("Hello 2024")