How to Read Numeric Strings in Excel Cells as String (Not Numbers)

How can I read numeric strings in Excel cells as string (not numbers)?

I had same problem. I did cell.setCellType(Cell.CELL_TYPE_STRING); before reading the string value, which solved the problem regardless of how the user formatted the cell.

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

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.

Problem in reading Numeric value form Excel file using Apache POI

The Cell.getNumericCellValue() gets numeric cell values exact as they are stored. But Excel always uses only 15 significant digits for numeric values. So the exact value 0.14200000000000002 (having 17 significant digits) will be 0.142 for Excel.

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.formatCellValue method gets all cell values as strings by formatting them the same kind as Excel will do. So if Excel shows 0.142 as the cell value then also DataFormatter will get 0.142 even if the exact numeric cell value is 0.14200000000000002.

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();
  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();
 }
}

Read Excel Numeric cell values as they are in Java

This question comes up a lot on Stack Overflow, so you'd be well advised to look through many of the similar ones to see there answers!

Excel stores almost all numbers in the file format as floating point values, which is why POI will give you back a double for a numeric cell as that's what was really there. If you want it to look like it does in Excel, you need to apply the formatting rules defined against the cell to the number. Thus, you want to do exactly the same thing as in my answer here. To quote:

What you want to do is use the DataFormatter class. 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.

For your case, I'd assume that the numeric cells have an integer formatting rule applied to them. If you ask DataFormatter to format those cells, it'll give you back a string with the integer string in it.

All you need to do is:

// Only need one of these
DataFormatter fmt = new DataFormatter();

// Once per cell
String valueAsSeenInExcel = fmt.formatCellValue(cell);

---

Related Topics