0

Cannot get a text value from a numeric cell while reading numeric data from an xlsx file in apache poi excel read, the following is the code snippet to read data from excel I've mix of columns containing string & numeric data

public String getData(int sheetIndex, int rowNum, int colNum)
{
    String data;
    data = sheet1.getRow(rowNum).getCell(colNum).getStringCellValue().toString();
    return data;
}
1

I hope this line of code helps you

 public String xls2text(InputStream in )throws Exception{  HSSFWorkbook excelWb = new HSSFWorkbook(in);
    StringBuffer result = new StringBuffer(8192);
    int numberOfSheets = excelWb.getNumberOfSheets();
    for (int i = 0; i < numberOfSheets; i++) {
        HSSFSheet sheet = excelWb.getSheetAt(i);
        int numberOfRows = sheet.getPhysicalNumberOfRows();
        if (numberOfRows > 0) {
            if (excelWb.getSheetName(i) != null
                    && excelWb.getSheetName(i).length() != 0) {
                // append sheet name to content
                if (i > 0) {
                    result.append("\n\n");
                }
                result.append(excelWb.getSheetName(i).trim());
                result.append(":\n\n");
            }

             for(int j=0;j<numberOfRows;j++) {
                HSSFRow row = sheet.getRow(i);
                if (row != null) {
                    boolean hasContent = false;
                    int  cellNumber = row.getPhysicalNumberOfCells();
                   for(int k=0;k<cellNumber;k++) {
                        HSSFCell cell = row.getCell(k);
                        String text = null;
                        try {
                            switch (cell.getCellType()) {
                            case HSSFCell.CELL_TYPE_BLANK:
                            case HSSFCell.CELL_TYPE_ERROR:
                                // ignore all blank or error cells
                                break;
                            case HSSFCell.CELL_TYPE_NUMERIC:
                                text = Double.toString(cell
                                        .getNumericCellValue());
                                break;
                            case HSSFCell.CELL_TYPE_BOOLEAN:
                                text = Boolean.toString(cell
                                        .getBooleanCellValue());
                                break;
                            case HSSFCell.CELL_TYPE_STRING:
                            default:
                                text = cell.getStringCellValue();
                                break;
                            }
                        } catch (Exception e) {
                        }
                        if ((text != null) && (text.length() != 0)) {
                            result.append(text.trim());
                            result.append(' ');
                            hasContent = true;
                        }
                    }
                    if (hasContent) {
                        result.append('\n');
                    }
                }
            }
        }
    }
    return result.toString();
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.