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

Question

I have excel file with such contents     A1  SomeString A2  2   All fields are set to String format  When I read the file in java using POI  it tells that A2 is in numeric cell format  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

User · Answer

Try   new java text DecimalFormat  0   format  cell getNumericCellValue       Should format the number correctly

User · Answer

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

User · Answer

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

User · Answer

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 amp 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

User · Answer

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

User · Answer

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 lt Row gt  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

cell setCellType Cell CELL TYPE STRING   is working fine for me

User · Answer

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     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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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    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  Now if you read the cell using POI  it will read the formula instead of calculated value  Now do following  Add another column Select complete column created in step 1  and choose Edit- COPY Go to top cell of column created in step 3  and Select Edit- Paste Special In the opened window  Select  Values  radio button Select  OK  Now read using POI API     after reading in Java     just remove the first character i e

User · Answer

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

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

Examples related to java

Examples related to excel

Examples related to apache-poi