Sep 6 2017

How can I read numeric strings in Excel cells as string (not numbers) with Apache POI? Stack Overflow #prepend #cell #phone


Gagravarr s warning against doing this is right! From the docs: If what you want to do is get a String value for your numeric cell, stop. This is not the way to do it. Instead, for fetching the string value of a numeric or boolean or date cell, use DataFormatter instead. I was using this technique myself until I wound up accidentally changing data I didn t intend to change. (Set type to String, read value, set type back to numeric, read again and get a different numeric value!) cfinley Jul 31 ’15 at 13:19

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 .

answered Aug 17 ’11 at 23:26

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.

answered Jul 30 ’09 at 10:24

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

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

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.

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

Written by admin

Leave a Reply

Your email address will not be published. Required fields are marked *