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

  1. I have excel file with such contents:

    • A1: SomeString

    • A2: 2

    All fields are set to String format.

  2. When I read the file in java using POI, it tells that A2 is in numeric cell format.

  3. The problem is that the value in A2 can be 2 or 2.0 (and I want to be able to distinguish them) so I can't just use .toString().

What can I do to read the value as string?

This question is related to java excel apache-poi

The answer is


We had the same problem and forced our users to format the cells as 'text' before entering the value. That way Excel correctly stores even numbers as text. If the format is changed afterwards Excel only changes the way the value is displayed but does not change the way the value is stored unless the value is entered again (e.g. by pressing return when in the cell).

Whether or not Excel correctly stored the value as text is indicated by the little green triangle that Excel displays in the left upper corner of the cell if it thinks the cell contains a number but is formated as text.


cast to an int then do a .toString(). It is ugly but it works.


When we read the MS Excel's numeric cell value using Apache POI library, it read it as numeric. But sometime we want it to read as string (e.g. phone numbers, etc.). This is how I did it:

  1. Insert a new column with first cell =CONCATENATE("!",D2). I assume D2 is cell id of your phone-number column. Drag new cell up to end.

  2. Now if you read the cell using POI, it will read the formula instead of calculated value. Now do following:

  3. Add another column

  4. Select complete column created in step 1. and choose Edit->COPY

  5. Go to top cell of column created in step 3. and Select Edit->Paste Special

  6. In the opened window, Select "Values" radio button

  7. Select "OK"

  8. Now read using POI API ... after reading in Java ... just remove the first character i.e. "!"


There is a ready-to-use wrapper (some additional optimizations can be applied)

  • it supports numeric and String cells

  • formulas are recognized and handled automatically

  • avoid some boilerplate

     public final class Cell {
    
     private final static DataFormatter FORMATTER = new DataFormatter();
    
     private XSSFCell mCell;
    
     public Cell(@NotNull XSSFCell cell) {
         mCell = cell;
    
         if (isFormula()) {
             XSSFWorkbook book = mCell.getSheet().getWorkbook();
             FormulaEvaluator evaluator = book.getCreationHelper().createFormulaEvaluator();
             mCell = (XSSFCell) evaluator.evaluateInCell(mCell);
         }
     }
    
     /**
      * Get content
      */
     public final int getInt() {
         return (int) getLong();
     }
    
     public final long getLong() {
         return Math.round(getDouble());
     }
    
     public final double getDouble() {
         return mCell.getNumericCellValue();
     }
    
     public final String getString() {
         if (!isString()) {
             return FORMATTER.formatCellValue(mCell);
         }
         return mCell.getStringCellValue();
     }
    
     /**
      * Get properties
      */
     public final boolean isNumber() {
         if (isFormula()) {
             return mCell.getCachedFormulaResultType().equals(CellType.NUMERIC);
         }
         return mCell.getCellType().equals(CellType.NUMERIC);
     }
    
     public final boolean isString() {
         if (isFormula()) {
             return mCell.getCachedFormulaResultType().equals(CellType.STRING);
         }
         return mCell.getCellType().equals(CellType.STRING);
     }
    
     public final boolean isFormula() {
         return mCell.getCellType().equals(CellType.FORMULA);
     }
    
     /**
      * Debug info
      */
     @Override
     public String toString() {
         return getString();
     }
     }
    

The below code worked for me for any type of cell.

InputStream inp =getClass().getResourceAsStream("filename.xls"));
Workbook wb = WorkbookFactory.create(inp);
DataFormatter objDefaultFormat = new DataFormatter();
FormulaEvaluator objFormulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) wb);

Sheet sheet= wb.getSheetAt(0);
Iterator<Row> objIterator = sheet.rowIterator();

while(objIterator.hasNext()){

    Row row = objIterator.next();
    Cell cellValue = row.getCell(0);
    objFormulaEvaluator.evaluate(cellValue); // This will evaluate the cell, And any type of cell will return string value
    String cellValueStr = objDefaultFormat.formatCellValue(cellValue,objFormulaEvaluator);

}

getStringCellValue returns NumberFormatException if the cell type is numeric. If you don't want to change the cell type to string, you can do this.

String rsdata = "";
try {
    rsdata = cell.getStringValue();
} catch (NumberFormatException ex) {
    rsdata = cell.getNumericValue() + "";
}

I also have had a similar issue on a data set of thousands of numbers and I think that I have found a simple way to solve. I needed to get the apostrophe inserted before a number so that a separate DB import always sees the numbers as text. Before this the number 8 would be imported as 8.0.

Solution:

  • Keep all the formatting as General.
  • Here I am assuming numbers are stored in Column A starting at Row 1.
  • Put in the ' in Column B and copy down as many rows as needed. Nothing appears in the worksheet but clicking on the cell you can see the apostophe in the Formula bar.
  • In Column C: =B1&A1.
  • Select all the Cells in Column C and do a Paste Special into Column D using the Values option.

Hey Presto all the numbers but stored as Text.


public class Excellib {
public String getExceldata(String sheetname,int rownum,int cellnum, boolean isString) {
    String retVal=null;
    try {
        FileInputStream fis=new FileInputStream("E:\\Sample-Automation-Workspace\\SampleTestDataDriven\\Registration.xlsx");
        Workbook wb=WorkbookFactory.create(fis);
        Sheet s=wb.getSheet(sheetname);
        Row r=s.getRow(rownum);
        Cell c=r.getCell(cellnum);
        if(c.getCellType() == Cell.CELL_TYPE_STRING)
        retVal=c.getStringCellValue();
        else {
            retVal = String.valueOf(c.getNumericCellValue());
        }

I Tried This and It worked For me


I would much rather go the route of the wil's answer or Vinayak Dornala, unfortunately they effected my performance far to much. I went for a HACKY solution of implicit casting:

for (Row row : sheet){
String strValue = (row.getCell(numericColumn)+""); // hack
...

I don't suggest you do this, for my situation it worked because of the nature of how the system worked and I had a reliable file source.

Footnote: numericColumn Is an int which is generated from reading the header of the file processed.


I would recommend the following approach when modifying cell's type is undesirable:

if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
    String str = NumberToTextConverter.toText(cell.getNumericCellValue())
}

NumberToTextConverter can correctly convert double value to a text using Excel's rules without precision loss.


Many of these answers reference old POI documentation and classes. In the newest POI 3.16, Cell with the int types has been deprecated

Cell.CELL_TYPE_STRING

enter image description here

Instead the CellType enum can be used.

CellType.STRING 

Just be sure to update your pom with the poi dependency as well as the poi-ooxml dependency to the new 3.16 version otherwise you will continue to get exceptions. One advantage with this version is that you can specify the cell type at the time the cell is created eliminating all the extra steps described in previous answers:

titleRowCell = currentReportRow.createCell(currentReportColumnIndex, CellType.STRING);

Do you control the excel worksheet in anyway? Is there a template the users have for giving you the input? If so, you can have code format the input cells for you.


Try:

new java.text.DecimalFormat("0").format( cell.getNumericCellValue() )

Should format the number correctly.


I don't think we had this class back when you asked the question, but today there is an easy answer.

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.

Also, note that lots of people suggest doing cell.setCellType(Cell.CELL_TYPE_STRING), but the Apache POI JavaDocs quite clearly state that you shouldn't do this! Doing the setCellType call will loose formatting, as the javadocs explain the only way to convert to a String with formatting remaining is to use the DataFormatter class.


cell.setCellType(Cell.CELL_TYPE_STRING); is working fine for me


As long as the cell is in text format before the user types in the number, POI will allow you to obtain the value as a string. One key is that if there is a small green triangle in the upper left-hand corner of cell that is formatted as Text, you will be able to retrieve its value as a string (the green triangle appears whenever something that appears to be a number is coerced into a text format). If you have Text formatted cells that contain numbers, but POI will not let you fetch those values as strings, there are a few things you can do to the Spreadsheet data to allow that:

  • Double click on the cell so that the editing cursor is present inside the cell, then click on Enter (which can be done only one cell at a time).
  • Use the Excel 2007 text conversion function (which can be done on multiple cells at once).
  • Cut out the offending values to another location, reformat the spreadsheet cells as text, then repaste the previously cut out values as Unformatted Values back into the proper area.

One final thing that you can do is that if you are using POI to obtain data from an Excel 2007 spreadsheet, you can the Cell class 'getRawValue()' method. This does not care what the format is. It will simply return a string with the raw data.


This worked perfect for me.

Double legacyRow = row.getCell(col).getNumericCellValue();
String legacyRowStr = legacyRow.toString();
if(legacyRowStr.contains(".0")){
    legacyRowStr = legacyRowStr.substring(0, legacyRowStr.length()-2);
}

Yes, this works perfectly

recommended:

        DataFormatter dataFormatter = new DataFormatter();
        String value = dataFormatter.formatCellValue(cell);

old:

cell.setCellType(Cell.CELL_TYPE_STRING);

even if you have a problem with retrieving a value from cell having formula, still this works.


As already mentioned in the Poi's JavaDocs (https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/Cell.html#setCellType%28int%29) don't use:

cell.setCellType(Cell.CELL_TYPE_STRING);

but use:

DataFormatter df = new DataFormatter();
String value = df.formatCellValue(cell);

More examples on http://massapi.com/class/da/DataFormatter.html


It looks like this can't be done in the current version of POI, based on the fact that this bug:

https://issues.apache.org/bugzilla/show_bug.cgi?id=46136

is still outstanding.


Examples related to java

Under what circumstances can I call findViewById with an Options Menu / Action Bar item? How much should a function trust another function How to implement a simple scenario the OO way Two constructors How do I get some variable from another class in Java? this in equals method How to split a string in two and store it in a field How to do perspective fixing? String index out of range: 4 My eclipse won't open, i download the bundle pack it keeps saying error log

Examples related to excel

Python: Pandas pd.read_excel giving ImportError: Install xlrd >= 0.9.0 for Excel support Converting unix time into date-time via excel How to increment a letter N times per iteration and store in an array? 'Microsoft.ACE.OLEDB.16.0' provider is not registered on the local machine. (System.Data) How to import an Excel file into SQL Server? Copy filtered data to another sheet using VBA Better way to find last used row Could pandas use column as index? Check if a value is in an array or not with Excel VBA How to sort dates from Oldest to Newest in Excel?

Examples related to apache-poi

Alternative to deprecated getCellType Apache POI error loading XSSFWorkbook class Cannot get a text value from a numeric cell “Poi” org.apache.poi.POIXMLException: org.apache.poi.openxml4j.exceptions.InvalidFormatException: Merging cells in Excel using Apache POI get number of columns of a particular row in given excel using Java How to get row count in an Excel file using POI library? How to check if an excel cell is empty using Apache POI? What is the better API to Reading Excel sheets in java - JXL or Apache POI Using Apache POI how to read a specific excel column