set Excel cell values of all type using Java

Question

I have a requirement to copy Excel row from one sheet to another. But after copying instead of actual values, I am getting different values. There is no problem with cells having string values, but with numeric values. Excel cell can contain any type of value. My code should accept all of them and copy the same in another sheet. Please help me in this case.

if(row.getCell(getLastCell)!=null && ((row.getCell(getLastCell).toString().equalsIgnoreCase(FAIL))|| (row.getCell(getLastCell).toString().equalsIgnoreCase("Invalid CR Statement"))))
            {
                failuresRow=failuresSheet.createRow(createNewRow++);
                for(int ii=0;ii<=getLastCell;ii++)
                {
                    failuresCell=failuresRow.createCell(ii);
                    failuresCell.setCellValue(row.getCell(ii)+"");
                    failuresCell.setCellType(row.getCell(ii).getCellType());
                    failuresCell.setCellStyle(row.getCell(ii).getCellStyle());
             }

            }

I know that row.getCell(ii)+"" is to convert type Cell to string, but I want to set setCellValue to accept cell which can have any type of data(eg: Date, Bollean, String, Number, .........)

I have tried using DataFormatter and in some other way also as below, but no use.

  failuresCell.setCellValue(df.formatCellValue(row.getCell(ii)));


   switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                failuresCell.setCellValue(cell.getRichStringCellValue().getString());
                break;
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    failuresCell.setCellValue(cell.getDateCellValue());
                } else {
                    failuresCell.setCellValue(cell.getNumericCellValue());
                }
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                failuresCell.setCellValue(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA:
                failuresCell.setCellValuec(cell.getCellFormula());
                break;
            default:
                // some code
        }

If cell have the numeric value 9200278, getting output as 1717. I don't understand where I am doing wrong. Please help me in this case.

Answer 1

This is because you are getting the cell instead of the value. You may use the following method instead of row.getCell(int).toString():

row.getCell(int).getStringCellValue();
row.getCell(int).getNumericCellValue();
and etc.

Just check the type of cell before invoking the method. Here's an example:

if (cellType == HSSFCell.CELL_TYPE_BOOLEAN) {
    row.getCell(int).getBooleanCellValue();
} else if (cellType == HSSFCell.CELL_TYPE_ERROR) {
    row.getCell(int).getErrorCellValue();
} else if (cellType == HSSFCell.CELL_TYPE_FORMULA) {
    row.getCell(int).getCellFormula();
} else if (cellType == HSSFCell.CELL_TYPE_NUMERIC) {
    row.getCell(int).getNumericCellValue();
} else if (cellType == HSSFCell.CELL_TYPE_STRING) {
    row.getCell(int).getStringCellValue();
}

Answer 2

I just set them all to string beforehand.

try {
        FileInputStream fileInputStream = new FileInputStream(excelTemplate);
        HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream);
        for (int i=0; i < workbook.getNumberOfSheets(); i++) {
            for (Row row : workbook.getSheetAt(i)) {
                for (Cell cell : row) {
                    if (cell != null)
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                }
            }
        }

    } catch (IOException e) {
        e.printStackTrace();
    }