Read excel columns as string even it contains numeric value without formatting using apache-poi

Question

I need help about an issue that try to reading excel values as it looks. Let me explain with example,

In excel my rows with first column (mixed with string values),

10,4
10,2
11.5 //some columns using . notation
someString

When I try to read values using apache-poi outputs: (it replaces "," with "." not wanted)
10.4
10.2
11.5
someString

it should give excatly same output. if it use ".", output should "10.4 if it use "," it should "10,4"

Even I format cells the column as text from excel, still get same output. I don't want to use string replace because input might be 10.4 with real dot.

Here as example code I am using,

    Sheet firstSheet = excel.getSheetAt(0);
    for (Row s : firstSheet) {
        Iterator<Cell> cellIterator = s.iterator();
        while (cellIterator.hasNext()) {
            Cell currentCell = cellIterator.next();
            if (currentCell.getCellType() == CellType.STRING) {
                System.out.print(currentCell.getStringCellValue() + "--");
            } else if (currentCell.getCellType() == CellType.NUMERIC) {
                System.out.print(currentCell.getNumericCellValue() + "--");
                String textValue = NumberToTextConverter.toText(currentCell.getNumericCellValue());
            }
        }
    }

Notes:
Apache-poi version: 4.0
Java: 8.0

Thanks in advance

Axel Richter Axel Richter · Accepted Answer · 2019-09-17T12:44:02

If the need is to get all cell values as string, then you should not get the cell values by CellType. Instead you should using apache poi's DataFormatter. The DataFormatter can have set a Locale. This Locale then determines using what decimal delimiters and thousands separators numeric values are shown.

Example:

import org.apache.poi.ss.usermodel.*;
import java.io.FileInputStream;
import java.util.Locale;

class GetDataFromExcelUsingDataFormatter {

 public static void main(String[] args) throws Exception {

  Workbook workbook = WorkbookFactory.create(new FileInputStream("ExcelExample.xlsx"));

  DataFormatter dataFormatter = new DataFormatter(Locale.GERMANY);
  FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();

  Sheet sheet = workbook.getSheetAt(0);

  for (Row row : sheet) {
   for (Cell cell : row) {
    String cellValue = dataFormatter.formatCellValue(cell, formulaEvaluator);
    System.out.println(cellValue);
   }
  }

  workbook.close();
 }
}

This shows numeric values like 10.4 as 10,4 because of the German locale set.

The Excel file always contains numeric content in en_US locale form. So the content 10.4 will be stored as 10.4 if it is numeric. It is the Excel application then which transforms the content into other locale forms according to the locale of the operating system. That's why apache poi's DataFormatter also needs locale settings.

A Excel workbook either has dot as decimal delimiter or it has comma as decimal delimiter. It cannot have both the same time. So if in the same Excel sheet one cell contains 10.4 and another cell contains 10,4 then one of both must be text. In same Excel sheet 10.4 and 10,4 cannot be numeric the same time. The DataFormatter hands over text content totally untouched. So if 10,4 is the numeric value, then DataFormatterformats it using the Locale. But the 10.4 then must be text and DataFormatter will hand over it untouched as 10.4.

Read excel columns as string even it contains numeric value without formatting using apache-poi

1 Answers