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

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. "!"

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

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.

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

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

You can read numerical cells as String using java.

int type = cell.getCellType();
if(type == 0){
   String value = NumberToTextConverter.toText(cell.getNumericCellValue());
}
else{
   value = String.valueOf(cell.getStringCellValue());
}

Here,

0 => numeric cell

getCellType() => this method use to get type of excel cell.

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.