Some of my domains are defined as Java 8 ZonedDateTime, LocalDate or Instant.
I am using jxls reader 2.03 to import Data. However, I have problem to read Date cell value from excel to my ZonedDateTime domain property.
I have tried to register a new converter class, however seems to me, cannot make it work. My code is like below:
// 1) Trying this way, cannot read
ConvertUtils.register(new ZonedDateTimeConverter(), java.time.ZonedDateTime.class);
XLSReadStatus readStatus;
log.info("Running excel engine to read " + uploadFileWithPath);
try(InputStream inputXML = new FileInputStream(xmlConfigModel)){
ReaderConfig.getInstance().setSkipErrors( true );
ReaderConfig.getInstance().setUseDefaultValuesForPrimitiveTypes( true );
XLSReader mainReader = ReaderBuilder.buildFromXML( inputXML );
//2) Trying second way, cannot read ZonedDateTime either.
mainReader.getConvertUtilsBeanProvider().getConvertUtilsBean().register(new ZonedDateTimeConverter(), java.time.ZonedDateTime.class);
try (InputStream inputXLS = new FileInputStream(uploadFileWithPath)){
Map beans = new HashMap();
beans.put("items", items);
readStatus = mainReader.read( inputXLS, beans);
jpaRepository.save(items);
log.info("read "+ items.size() +" rows data from excel:" + uploadFileWithPath );
}
Finally, after reading the source code of jxls, the way below will works fine. Previous, I had something wrong in my converter.
XLSReader mainReader = ReaderBuilder.buildFromXML( inputXML );
mainReader.getConvertUtilsBeanProvider().getConvertUtilsBean().register(new ZonedDateTimeConverter(), java.time.ZonedDateTime.class);
mainReader.getConvertUtilsBeanProvider().getConvertUtilsBean().register(new LocalDateConverter(), java.time.LocalDate.class);
mainReader.getConvertUtilsBeanProvider().getConvertUtilsBean().register(new InstantConverter(), java.time.Instant.class);
mainReader.getConvertUtilsBeanProvider().getConvertUtilsBean().register(new StringConverter(), java.lang.String.class);
Actually, based on the author's code, I have write five other converters. The date time converter is nothing. But the String converter, it is really save time, as you will always find that text value stored in excel in scientific format(1.7702252508528E10), it is not correct, and not easy to format it back to text in excel file. With the StringConverter, all done automatically, no need come backt to excel file to format(actlly, most of the time, even you do format as text in excel, it is still read as scientific format).
Maybe, later I will see if I can push these back to the original source.