問題現(xiàn)象
EasyExcel 在讀取yyyy年MM月 hh時mm分ss秒格式的時間類單元格時，分位數(shù)據(jù)會錯亂哎垦，其值為月份數(shù)據(jù)
問題復(fù)現(xiàn)
首先復(fù)現(xiàn)下出現(xiàn)的問題
有如下數(shù)據(jù)
| 總?cè)藬?shù) | 合格人數(shù) | 統(tǒng)計時間 |
|---|---|---|
| 10 | 8 | 2023年04月01日 08時00分00秒 |
其中統(tǒng)計時間按如下格式設(shè)置
ex1.png
這里需要注意的是，該單元格存儲為日期類型奉呛。（easyexcel 讀取到的實(shí)際為NUMBER,后經(jīng)Double轉(zhuǎn)日期）
創(chuàng)建對應(yīng)實(shí)體類
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 讀取測試數(shù)據(jù)
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秒
可以看到與我們的實(shí)際數(shù)據(jù)并不一致许昨，年月日時秒都無誤，僅分位的值出現(xiàn)了問題北启。如果數(shù)據(jù)量比較大很容易發(fā)現(xiàn)規(guī)律。那就是此時分的值與月份一致拔第。但如果您正在格式化Time 而非 DateTime咕村。如下圖所示
time.png
那您的分位的值將始終1(也可能是12，根據(jù)時間算法有所不同蚊俺。如果是1899年就是12月懈涛，如果是1900年就是1月)。
最新版時間轉(zhuǎn)換方式由DateUtil.getJavaDate()換成了Date.from(getLocalDateTime(date, use1904windowing).atZone(ZoneId.systemDefault()).toInstant()) 會有格式化Time類時間不準(zhǔn)確問題泳猬。不過這就是另一個值得討論的問題了批钠，究其原因是1900年前后JDK Date類使用的歷法不一樣。1900年之前為 Julian 1900年之后為 Gregorian得封。
問題定位
回歸正題埋心，讓我們繼續(xù)定位分位數(shù)據(jù)錯誤的問題。正所謂授人以魚不如授人以漁呛每。此次我們將從表象開始一步步深入bug產(chǎn)生的根源。想直接了解根因和解決方案可直接跳過本章節(jié)
首先需要明確的一點(diǎn)是坡氯，所有單元格的數(shù)據(jù)轉(zhuǎn)化為Java bean 屬性是都會經(jīng)過一個轉(zhuǎn)換的過程晨横。從 ExcelProperty 注解中我們能夠看到有一個converter()屬性，其默認(rèn)值為AutoConverter.class箫柳。那么就可以以此找到其實(shí)現(xiàn)的接口Converter進(jìn)而找到
default T convertToJavaData(ReadConverterContext<?> context) throws Exception {
return convertToJavaData(context.getReadCellData(), context.getContentProperty(),
context.getAnalysisContext().currentReadHolder().globalConfiguration());
}
以為實(shí)現(xiàn)類巨多手形，所以我們可以在接口的默認(rèn)實(shí)現(xiàn)上加入斷點(diǎn)。在運(yùn)行時Step into進(jìn)入具體的實(shí)現(xiàn)類悯恍。最終此類時間單元格會被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);
}
最終形成如下調(diào)用鏈
--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 的實(shí)現(xiàn)
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); 通過調(diào)試器觀察 dateFormat的值可發(fā)現(xiàn)端倪
debugger.png
可以看到分位上使用的是月占位符库糠。所以接下來需要檢查getFormat(data, dataFormat,dataFormatString) 找到分位置占位符被替換的原因
調(diào)用鏈如下
--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內(nèi)的時間格式轉(zhuǎn)化為 Java 中的時間。excel 內(nèi)時間規(guī)則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();
//@@@@@ 核心字段野建，判斷代表當(dāng)前解析的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') {
//@@@@@ 當(dāng)解析到 h 根據(jù)excel 的規(guī)則属划。接下來的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)成立進(jìn)入此代碼塊唯鸭，mIsMonth
//@@@@@ 再次被置為 true,下次解析時 m會被解析為月導(dǎo)致時間錯位
} 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。
其實(shí)大多情況下 yyyy-MM-dd HH:mmss 或 yyyy/MM/dd HH:mm:ss比較多肿孵。這兩種情況
Character.isLetter(c) 不成立會走else分支唠粥。
問題修復(fù)
那么如何解決，筆者這里提供一種方式（注意這里僅提供臨時解決方案停做，請遵循其對應(yīng)的開源協(xié)議進(jìn)行操作）
注意此方式僅能解決兩個 m 的情況晤愧，mmm(月英文縮寫)，mmmm(月英文全稱)無法解決等其他情況請自行解決蛉腌。這里拋磚引玉官份，有其他需要可自行實(shí)現(xiàn)
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();
//@@@@ 用于標(biāo)記是否在尋找緊跟在 h/hh 后的分鐘標(biāo)記
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 標(biāo)記出現(xiàn)尋找緊跟其后的分鐘標(biāo)記置為true
findingMinuteMark = true;
} else if (c == 'm' || c == 'M') {
if (findingMinuteMark) {
sb.append('m');
ms.add(sb.length() - 1);
//@@@@ 判斷下一位是否也為m,這里僅找了一個。實(shí)際上excel支持最多 4 位m,根據(jù)實(shí)際情況烙丛，可額外處理舅巷。
if (j + 1 < chars.length && (chars[j + 1] == 'm' || chars[j + 1] == 'M')) {
sb.append('m');
j++;
}
//@@@@ 分標(biāo)記已經(jīng)出現(xiàn)尋找緊跟h其后的分鐘標(biāo)記重新置為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();
//@@@@ 其他標(biāo)記出現(xiàn)尋找緊跟h其后的分鐘標(biāo)記置為false
findingMinuteMark = false;
} else if (Character.isLetter(c)) {
if (c == 'y' || c == 'Y') {
sb.append('y');
ms.clear();
//@@@@ 其他標(biāo)記出現(xiàn)尋找緊跟h其后的分鐘標(biāo)記置為false
findingMinuteMark = false;
} else if (c == 'd' || c == 'D') {
sb.append('d');
ms.clear();
//@@@@ 其他標(biāo)記出現(xiàn)尋找緊跟h其后的分鐘標(biāo)記置為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")
