Get String value, when I read from excel with Date type (apache poi)

Question

I have an xlsx file that I am reading with - Apache POI Library.

For example, In some row, I have such cells values:

01-Sep-13 | 15136.00| Matt|......

---

My goal is: Read all cells in the rows as String values. But as I see, I can't read 01-Sep-13 as string, it only represents like Date type () with cell.getDateCellValue();

---

1) Could I read somehow 01-Sep-13 as string: "01-Sep-13"; 2) Could I convert Date value to string in case I have different date patterns (ddMMyyyy) and (dd-MMM-yy);

Code:

When I iterate over rows and cells, Apache POI analyzes each cell type:

String normalizeCellType(Cell cell) {

    switch (cell.getCellType()) {
        case Cell.CELL_TYPE_STRING:
           return cell.getRichStringCellValue().getString());            
        case Cell.CELL_TYPE_NUMERIC:
            if (DateUtil.isCellDateFormatted(cell)) {

                Date date = cell.getDateCellValue(); ????????????
                System.out.println(date);

            } else {

                ....
            }
            break;
        case Cell.CELL_TYPE_BOOLEAN:
           return ....
        case Cell.CELL_TYPE_FORMULA:
           return ....
        default:
            System.out.println();
    }
}

Select

Answer 1

You have 2 options:

• Use Java's SimpleDateFormat class to format the returned Date as a String in the format of your choice. You can have a SimpleDateFormat instance for each different format you need.
• Use Apache POI's DataFormatter class to format the the Cell directly.

Countless

Thanks method a lot.In the class DateFormatter + .formatCellValue(cell) - returns string representation of cell – Select

Answer 2

You can get the return value for cell#getDateCellValue() and use SimpleDateFormat to covert it to a String instance

 // Create an instance of SimpleDateFormat used for formatting 
    // the string representation of date (month/day/year)
    DateFormat df = new SimpleDateFormat("dd/MM/yyyy");

    // Get the date today using cell.getDateCellValue() 
    Date today = cell.getDateCellValue()       
    // Using DateFormat format method we can create a string 
    // representation of a date with the defined format.
    String reportDate = df.format(today);

You can get the value of Date in String format - 31/10/2013. Hope this helps

Homochromatic

Answer 3

Here are the answers for you questions:

1) Could I read somehow 01-Sep-13 as string: "01-Sep-13";

Yes you can. You need to set the Cell type to String before reading it. Something like this:

.
.
.
int fcell = row.getFirstCellNum();// first cell number of excel
int lcell = row.getLastCellNum(); //last cell number of excel
while (rows.hasNext()) {
row = (XSSFRow) rows.next();//increment the row iterator
for (int i = fcell; i < lcell; i++) {
    row.getCell(i).setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING);
    .
    .
    ..//processing
    .
    .
    }
}

2.)Could I convert Date value to string in case I have different date patterns (ddMMyyyy) and (dd-MMM-yy);

Since the first answer is already giving you the String value. Still to convert a String value to date you can use SimpleDateFormat as @rgettman and @Keerthi suggested

Hope this will help...:)

Belicia

Answer 4

DateFormat df = new SimpleDateFormat("yyyy/MM/dd");
java.util.Date d =  row1.getCell(i).getDateCellValue();
String buy_date = df.format(d);
System.out.println("date is :- "+ buy_date);

output will be

date is :- 2014/08/01

Now it will give date in String format which you can store in your database.

Federalist

Answer 5

Apache POI has the functionality to do this for you, you just need to call it! The class you need to use is DataFormatter. You pass this a cell, and it does its best to return you a string containing what Excel would show you for that cell. If you pass it a string cell, you'll get the string back. If you pass it a numeric cell with formatting rules applied, it will format the number based on them and give you the string back. If you pass it a "date" cell (numeric cell with date formatting rules), it'll format it as a date and give you the string back

You can see it in action with this round-trip bit of code:

 DataFormat df = workBook.createDataFormat();
 CellStyle dateStyle = wb.createCellStye();
 setDataFormat.setDataFormat(df.getFormat("dd-mmm-yy"));

 Cell cell = row.createCell(0);
 cell.setCellStyle(dateStyle);

 // Set it to be a date
 Calendar c = Calendar.getInstance();
 c.set(2013,9-1,1); // Don't forget months are 0 based on Calendar
 cell.setCellValue( c.getTime() );

 // Get it formatted as a string
 DataFormatter formatter = new DataFormatter();
 String cellAsStr = formatter.formatCellValue(cell);

 // cellAsStr will now be "01-Sep-13"
 // based on the format rules applied to the cell

Clayborne

Answer 6

My goal is: Read all cells in the rows as String values. But as I see, I can't read 01-Sep-13 as string, it only represents like Date type () with cell.getDateCellValue();

You can use XSSFExcelExtractor or ExcelExtractor to get correctly formatted cell value.

Note: - 1) XSSFExcelExtractor is for XLSX file 2) ExcelExtractor is for XLS file

Murk

Answer 7

It is so easy to read string values in excel sheet using java. this is the my way..

Workbook wb=Workbook.getWorkbook(file);
        Sheet s= wb.getSheet(1);
        int row=s.getRows();
        int col=s.getColumns();
        for(int i=1;i<row;i++){
            for(int j=0;j<col;j++){
                Cell c=s.getCell(j,i);
                String MyString=c.getContents().toString();
                System.out.println(MyString);
               }
          }

Ludlow

Answer 8

This reference gives the best detailed solution for this as per the second option provided by rgettman

Code snippet copied as below, hope it helps Ref: https://www.baeldung.com/java-apache-poi-cell-string-value

Cell cell = // a numeric cell with value of 1.234 and format rule "0.00"

DataFormatter formatter = new DataFormatter();
String strValue = formatter.formatCellValue(cell);

Ricardo