Ashok kumar Ashok kumar - 7 months ago 130
Java Question

How to convert the date format from 04-May-2016 to 04/05/2016 using Java Apache POI

I read a .xlsx file and retrieving a date value and it should be entered in to a textbox field.

In excel the cell value is 04/05/2016.

But while fetching the date value from cell, the value comes as 04-May-2016.

How do I convert this 04-May-2016 format to mm/dd/yyy.

Code:

public static String readExcel(String filePath,String fileName,String sheetName,int RowNumber,int ColNumber) throws Exception{

Object result = null;
try
{

sheet=getSheet(filePath, fileName, sheetName);
row=sheet.getRow(RowNumber);

if(row != null)
{
//System.out.println("Row is not empty");
cell= row.getCell(ColNumber);

if(cell!=null)
{
switch (cell.getCellType()) {

case Cell.CELL_TYPE_NUMERIC:// numeric value in excel
if(DateUtil.isCellDateFormatted(cell)){
//DateUtil.truncate(new Date(), java.util.Calendar.DAY_OF_MONTH);

DateFormat formatter = new SimpleDateFormat("dd/MM/yyyy");
result = formatter.format(cell);
System.out.println("Today : " + result);
}
else{
result = new BigDecimal(cell.getNumericCellValue()).toPlainString();
}
break;

case Cell.CELL_TYPE_STRING: // string value in excel
result = cell.getStringCellValue();
break;

case Cell.CELL_TYPE_BOOLEAN: // boolean value in excel
result = cell.getBooleanCellValue();
break;

case Cell.CELL_TYPE_BLANK: // blank value in excel
result = cell.getStringCellValue();
break;

case Cell.CELL_TYPE_ERROR: // Error value in excel
result = cell.getErrorCellValue()+"";
break;


}
}
else
{
return null;
}
}
else
{
//System.out.println("Row is empty");
return null;
}

inputStream.close();
}

catch (Exception ex){
ex.printStackTrace();
}

return result.toString();

}


Console:

java.lang.IllegalArgumentException: Cannot format given Object as a Date
at java.text.DateFormat.format(Unknown Source)
at java.text.Format.format(Unknown Source)
at utility.ExcelUtility.readExcel(ExcelUtility.java:116)


Guide me to reach out.

Answer

To get the date, you can do

Date myDate = cell.getDateCellValue()

and then use SimpleDateFormat like your example :

if(DateUtil.isCellDateFormatted(cell)){
    Date myDate = cell.getDateCellValue();
    DateFormat formatter = new SimpleDateFormat("dd/MM/yyyy");
    String result = formatter.format(myDate);
    System.out.println("Today : " + result);    
}