In fact, it's never possible to obtain exactly the formatted cell value with the locale defined when the cell was written. it's due to there locale observer and the fact that the internal locale excel prefix is never reused when formatting afterwards;
Analysis for POI 3.17 (may change since looking how the component is internally done)
for instance : the dateConverted format of the cell style (from CellStyle.getDataFormatString()) for Locale.US with the format dd MMM yyyy hh:mm:ss is :
"[$-0409]dd MMM yyyy hh:mm:ss;@"
where the local excel internal prefix = [$-0409]
It's obtained from DateFormatConverter.localPrefixes private static map.
here is some code to work around this issue :
/**
* Missing method in POI to enable the visualisation asIs of an cell with a
* different locale in a xls document.
*
* @param style
* the cell style localized.
* @return the Locale found using internal locationPrefixes.
*/
private final Locale extractLocaleFromDateCellStyle(final CellStyle style) {
final String reOpenedFormat = style.getDataFormatString();
LOGGER.info("Data Format of CellStyle : " + reOpenedFormat);
Locale locale = getLocaleFromPrefixes(extractPrefixeFromPattern(reOpenedFormat));
LOGGER.info("Found locale : " + locale);
return locale;
}
/**
* Extracts the internal prefix that represent the local of the style.
*
* @param pattern
* the DataFormatString of the cell style.
* @return the prefix found.
*/
private final String extractPrefixeFromPattern(final String pattern) {
Pattern regex = Pattern.compile(REGEX_PREFIX_PATTERN);
Matcher match = regex.matcher(pattern);
if (match.find()) {
LOGGER.info("Found prefix: " + match.group(1));
// return only the prefix
return match.group(1);
}
return null;
}
/**
* Reverse obtain the locale from the internal prefix from
* <code>DateFormatConverter.localePrefixes</code> private static field.
* <p>
* Uses reflection API.
*
* @param prefixes
* the prefixes
* @return the local corresponding tho the prefixes.
*/
public static Locale getLocaleFromPrefixes(final String prefixes) {
try {
@SuppressWarnings("unchecked")
Map<String, String> map = getStaticPrivateInternalMapLocalePrefix();
String localPrefix = null;
// find the language_Country value matching the internal excel
// prefix.
for (Map.Entry<String, String> entry : map.entrySet()) {
LOGGER.info("value : " + entry.getValue() + ", key :"
+ entry.getKey());
if (entry.getValue().equals(prefixes)
&& !StringUtils.isBlank(entry.getKey())) {
localPrefix = entry.getKey();
break;
}
}
// Generate a Locale with language, uppercase(country) info.
LOGGER.info(localPrefix);
if (localPrefix.indexOf('_') > 0) {
String[] languageCountry = localPrefix.split("_");
return new Locale(languageCountry[0],
StringUtils.defaultString(languageCountry[1]
.toUpperCase()));
}
// nothing found.
return null;
// factorized the multiples exceptions.
} catch (Exception e) {
throw new UnsupportedOperationException(e);
}
}
/**
* gets the internal code map for locale used by Excel.
*
* @return the internal map.
* @throws NoSuchFieldException
* if the private field name changes.
* @throws IllegalAccessException
* if the accessible is restricted.
*/
private static Map<String, String> getStaticPrivateInternalMapLocalePrefix()
throws NoSuchFieldException, IllegalAccessException {
// REFLECTION
Class<?> clazz = DateFormatConverter.class;
Field fieldlocalPrefixes = (Field) clazz
.getDeclaredField(DATE_CONVERTER_PRIVATE_PREFIXES_MAP);
// change from private to public.
fieldlocalPrefixes.setAccessible(true);
@SuppressWarnings("unchecked")
Map<String, String> map = (Map<String, String>) fieldlocalPrefixes
.get(clazz);
LOGGER.info("MAP localPrefixes : " + map);
return map;
}
Thus, the following simple code should do the trick.
Note the code is not fully tested with against null values and depends on the version of POI you use until they changed there LOCALE OBSERVER MADNESS :)
....
final CellStyle cellStyle = reopenedCell.getCellStyle();
Locale locale = extractLocaleFromDateCellStyle(cellStyle);
LOGGER.info("FOUND LOCAL : " + locale);
// use the same local from the cell style during writing.
DataFormatter df = new DataFormatter(locale);
String reOpenValue = df.formatCellValue(reopenedCell);
Regards.
