CODEWITHSUNDEEP
apache-poixlsxlsx
sunleo
sunleo

Reputation: 10947

XSSFSheet get all cell type values as string

Is there any possiblity to get all types(numeric,date,string etc) as String only.I couldn't find such methods.

sheet.getCell(rowIndex,colIndex) like this ?

InputStream ExcelFileToRead = new FileInputStream(file1);
XSSFWorkbook  wb = new XSSFWorkbook(ExcelFileToRead);
XSSFWorkbook test = new XSSFWorkbook(); 
XSSFSheet sheet = wb.getSheetAt(0);
XSSFRow row;
XSSFCell cell;

Iterator rows = sheet.rowIterator();
String[] Excelarray=new String[26];
int count=0;
Map<String, String> data = new HashMap<String, String>();

while (rows.hasNext())
{
row=(XSSFRow) rows.next();
Iterator cells = row.cellIterator();
while (cells.hasNext())
{
cell=(XSSFCell) cells.next();
if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING)
{
System.out.print(cell.getStringCellValue()+",");
}
else if(cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC)
{
System.out.print(cell.getNumericCellValue()+",");
}
else
{
}
}
System.out.println("----Closed");
}

Upvotes: 3

Views: 22241

Answers (2)

N.Neupane
N.Neupane

Reputation: 291

Yes, It is possible to get all the values in the form of string.

Previously I had used DataFormatter to get the string value but while working with the large files I found it does not work so well.

Here is the required code: -

for (Row row : sheet) {
                DataFormatter dataFormatter = new DataFormatter();
                for (Cell cell : row) {
                    String cellValue = getStringCellValue(cell);
                }
            }

private static String getStringCellValue(Cell cell) {
    try {
        switch (cell.getCellType()) {
            case FORMULA:
                try {
                    return NumberToTextConverter.toText(cell.getNumericCellValue());
                } catch (NumberFormatException e) {
                    return cell.getStringCellValue();
                }
            case NUMERIC:
                return NumberToTextConverter.toText(cell.getNumericCellValue());
            case STRING:
                String cellValue = cell.getStringCellValue().trim();
                String pattern = "\\^\\$?-?([1-9][0-9]{0,2}(,\\d{3})*(\\.\\d{0,2})?|[1-9]\\d*(\\.\\d{0,2})?|0(\\.\\d{0,2})?|(\\.\\d{1,2}))$|^-?\\$?([1-9]\\d{0,2}(,\\d{3})*(\\.\\d{0,2})?|[1-9]\\d*(\\.\\d{0,2})?|0(\\.\\d{0,2})?|(\\.\\d{1,2}))$|^\\(\\$?([1-9]\\d{0,2}(,\\d{3})*(\\.\\d{0,2})?|[1-9]\\d*(\\.\\d{0,2})?|0(\\.\\d{0,2})?|(\\.\\d{1,2}))\\)$";
                if (((Pattern.compile(pattern)).matcher(cellValue)).find()) {
                    return cellValue.replaceAll("[^\\d.]", "");
                }
                return cellValue.trim();
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case ERROR:
                return null;
            default:
                return cell.getStringCellValue();
        }
    } catch (Exception e) {
        if (e.getLocalizedMessage() != null && ConfigReader.isDisplayWarnLog())
            return "";
    }
    return "";
}

It works well. Thank You.

Upvotes: 0

Joshua Moore
Joshua Moore

Reputation: 24776

Someone else already supplied a generic implementation that does what you are looking to do. POI doesn't have anything directly but it's easy enough to make a helper method/class.

Upvotes: 2

Related Questions