Bug 16767 - string "12345" becomes the number 12345
Summary: string "12345" becomes the number 12345
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.0-dev
Hardware: Macintosh All
: P3 minor (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2003-02-04 17:26 UTC by Andy Lee
Modified: 2005-03-20 17:06 UTC (History)
1 user (show)

Spreadsheet generated by the code I submitted. (5.50 KB, application/octet-stream)
2003-02-04 17:31 UTC, Andy Lee
How I would have created the cell by hand. (6.00 KB, application/octet-stream)
2003-02-04 17:35 UTC, Andy Lee

Note You need to log in before you can comment on or make changes to this bug.
Description Andy Lee 2003-02-04 17:26:45 UTC
The code below generates a 1-cell spreadsheet.  When I open it, the "12345" is
left-justified, like I would expect a string value to be.  But if I double-click
the cell and then click out of it without making any changes, it becomes the
integer 12345.

If I were creating the spreadsheet by hand I would have entered '12345 to force
the value to be a string, and the apostrophe would be there when I double-click
the cell.


import org.apache.poi.hssf.usermodel.*;
import java.io.*;

public class Test
    public static void main(String[] args)
        catch (Exception e)

    private static void doMain() throws Exception
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("test");

        HSSFRow row = sheet.createRow((short)0);
        HSSFCell cell = row.createCell((short)0);


        FileOutputStream fos = new FileOutputStream("/tmp/test.xls");
Comment 1 Andy Lee 2003-02-04 17:31:54 UTC
Created attachment 4724 [details]
Spreadsheet generated by the code I submitted.
Comment 2 Andy Lee 2003-02-04 17:35:01 UTC
Created attachment 4725 [details]
How I would have created the cell by hand.
Comment 3 Andy Lee 2003-02-04 21:30:58 UTC
On a related note, if I do setCellValue("'abc'") and double-click the cell, I
lose the first apostrophe, so instead of staying


it becomes


Comment 4 Jason Height 2003-02-04 21:44:31 UTC
This looks like a reasonably simple patch for you to submit to us...

To start look at the HSSFCell.setCellValue(String). My guess is that you will 
need to add a ' to the string entered into the cell if the string you supply 
starts with a '

I dont believe that the first thing that you mention is a bug, it is normal 
Excel behaviour. If you want a String cell that cant be changed, then firstly 
fix the issue mentioned above and then write cell.setCellValue("'12345") in 
your code.

Comment 5 Andy Lee 2003-02-04 22:10:54 UTC
The problem with prepending an extra ' is that it will show up in Excel.  For
example, setCellValue("''abc'") causes this to appear in Excel:


When I double-click the cell and then exit it without changing it, the display
changes to...


...and thereafter behaves the way I want.  But of course I shouldn't have to
double-click every cell to make it appear correctly.  As for the original
problem, if I use '12345 then it starts out displayed as


(which is not the intended effect) and only becomes


after the double-click.

It seems to me we need to know how *Excel* decides to prepend the ', which it
should only do when I double-click the cell for editing.

Comment 6 Jason Height 2003-02-04 22:21:55 UTC
Ok Andy,

Seems like you have a good understanding of the combinations. 

Do you feel up to creating a patch? If so can I suggest that you use the 
biffviewer utility to display the file from POI before opening in excel. Then 
open the file in excel and produce the desired behaviour and then compare the 
outputs. It is likely that the difference you are interested in will be in 
either in the SST record or the LABELSST.

OR, if you are not confident in producing a patch then let me know and i may 
have a look at it.

Comment 7 Andy Lee 2003-02-04 23:54:21 UTC
I'd be glad to take a shot at it -- checking out the source tree now.  I've
never submitted a patch before, but the instructions look quite straightforward.
 I assume there is no great hurry.

Comment 8 Andy Oliver 2003-07-24 15:39:06 UTC
I wonder if the thing has a dataformat of NOT-general if this still ahppens. 
This is too minor to hold up 2.0
Comment 9 Shawn Laubach 2003-07-24 16:10:12 UTC
Andy O. is right.  This is a little gotcha with excel.  The default data format 
is general which looks at the value and tries to determine what it is.  I 
doesn't default to what the cell type is (part of the reason is there is not 
direct way through the interface to do that).  The solution is to set the data 
format for that particular cell to text which then makes it come out properly.  

Setting the format to text also fixes the quoting problem.  This comes about 
because you put a single ' in front of a number to tell excel that it is text 
and not to treat it as a number.

This really isn't a bug as much as a feature of excel.