Poji changed String 3/30/2021 to 3/30/21

[vaquarkhan]

I have date 3/30/2021 which i am reading as String using Poji when its coming into POJO its coming like 3/30/21 .
Now issue is when i convert this 3/30/21 . to java.sql.date before store into MYSQL its coming like 0021-03-30.

How can i read full 3/30/2021 using poji ?

[ozlerhakan]

Let me understand clearly, you have a date column and you map it as String as follows:

@ExcelCellName("COLUMN_DATE")
String date;

and you get the value as "3/30/21" right?

Normally It shouldn't be this way since it's of type String. Could you give me examples?

[vaquarkhan]

I have date column in xlsx

Now here is my POJO

  @ExcelCell(21)
String startDate;
@ExcelCell(22)
String endDate;

Service code :
List resource = Poiji.fromExcel(new File(SAMPLE_XLSX_FILE_PATH), Resource.class);

When i am debugging found inside list value is change once read by Poiji ,its working fine with POI api

Once list populated i am sending into Springboot Repository and inside calling JDBC template to insert records
So when going to insert i am converting this String date to Java.sql.date and insert.

SInce Poiji reading 3/30/21 conversion adding wrong values into db

Following method is my conversion method

private  static java.sql.Date stringToDate(String str){
    if (null==str) return null;
    if (str.isBlank()||str.isEmpty()) return null;
    SimpleDateFormat format = new SimpleDateFormat("MM/dd/yyyy");//yyyyMMdd
    Date parsed = null;
    try {
	    parsed = format.parse(str);
    } catch (ParseException e) {
	  // TODO Auto-generated catch block
        e.printStackTrace();
    }
    return new java.sql.Date(parsed.getTime());
  }


public static void main (String args[]) {
    String str="3/30/2021";
    java.sql.Date date=stringToDate( str);
    System.out.println(date);
}

[ozlerhakan]

Could you please share with me an excel file with those few examples?

[vaquarkhan]

You can try with this data

[ozlerhakan]

I need your sample excel file @vaquarkhan , otherwise I tried but couldn't produce this case for the date values like '3/30/2021'

[vaquarkhan]

sample.xlsx
here you go

[ozlerhakan]

Thanks @vaquarkhan , Now I see that there is a style in these columns and poi by itself gets their format index as 14 and theirs format string as 'm/d/yy'. You can debug all the cells of an excel file via https://github.com/ozlerhakan/poiji#debug-cells-formats.

What you need to do is you can convert the format string of the format index via https://github.com/ozlerhakan/poiji#modify-cells-formats.

You can use these steps:

PoijiNumberFormat numberFormat = new PoijiNumberFormat();
numberFormat.putNumberFormat((short) 14, "dd/mm/yyyy");

PoijiOptions options = PoijiOptions.PoijiOptionsBuilder.settings()
        .poijiNumberFormat(numberFormat)
        .build();

and here it is:

BTW when I opened it up on my local the columns look like this:

[vaquarkhan]

Thanks let me check

[ozlerhakan]

you can use 3.1.3 of Poiji @vaquarkhan , thank you for your contribution :)