1

I'm trying to read an Excel file with Spring Batch and Spring Batch Excel and cells in date format are read in a different format form the file.

In my file dates are in DD/MM/YYYY and when Spring/POI are reading data org.apache.poi.ss.usermodel.DataFormatter is used and in performDateFormatting method the parameter dateFormat has a pattern of M/d/yy.

Is there a way to force the date pattern when reading ?

My rowmapper configuration is

<bean id="caricaAnagraficheReader" class="org.springframework.batch.extensions.excel.poi.PoiItemReader" scope="step">
    <property name="resource" value="file:#{batchParameters.genericBatchParameters.allegatoNomeCompleto}" />
    <property name="linesToSkip" value="1" />
    <property name="rowMapper">
        <bean class="it.blue.batch.portali.components.CaricaAnagraficheRowMapper" />
    </property>
</bean>

Thank you in advance

M. Deinum
  • 115,695
  • 22
  • 220
  • 224
pivsxii
  • 47
  • 6
  • It will use the date-format from the cell if it is available (if no format it set and only type date it will fallback to the default JDK locale for formatting). You could try to explicitly set the format for the date-cells in your excel sheet. Everything currently is delegated to Apache POI and we allow for some hooks (I'm the author of the excel readers). We could provide an option to set a `Locale` to be used for reading. If you know what the format is for reading, and apparently the writing is important, you could map it to another format whilst writing as well. – M. Deinum Feb 01 '22 at 10:03
  • We created the Excel to be filled so we are aware which format we are expecting. Unfortunately users should fill the value and it can do mistakes. So basically I can check in the rowmapper some additional checks regarding the date format and eventually use the optional Local when implemented. Sounds good ? – pivsxii Feb 01 '22 at 11:08
  • I don't understand your comment at all? If there is an explicit pattern in excel it will be used (according to the Apache POI documentation) if there isn't it will use the date format as available from the system local (or rather the JVM default). Another option is, which we are pondering on, is make the API we have more JDBC like and return the actual type (a `Date` in this case instead of a `String`). The Apache POI implementation does allow for a date-format to be set, so we could investigate that as well (you can set the `Locale` to use and/or date format), so we could investigate that. – M. Deinum Feb 02 '22 at 06:53
  • Before running the batch job (not sure how you are launching things), try doing `LocaleUtil.setUserLocale(your-preffered-locale)`. This should set the `Locale` to use for Apache POI, see if that makes a difference. If not it means there is formatting and the date is formatted accordingly to the format as defined in excel. The only way to get around that would be to create another API exposing the actual datatypes (like numeric, date etc.) akin to the JDBC stuff. Which would be quite a refactoring to do, but it would offer more flexibility I guess. – M. Deinum Feb 02 '22 at 08:21

0 Answers0