You are viewing a plain text version of this content. The canonical link for it is here.

Posted to user@poi.apache.org by Modha Khammammettu <MK...@calcas.com> on 2007/10/14 07:58:13 UTC

Cell format question

Hi All

I am using POI to create excel spreadsheet.

Every cell I create for this spreadsheet, I create like

HSSFCell cell = row.createCell((short)cellNumber);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);

Now excel file gets created. The problem is when I open this excel file
to manually change lets say date value is a cell

1. Initially when the date is written I believe stored as text. (Ex:
12/4/2007)
2. When I manually open this spreadsheet and change the cell (12/5/2007)
the cell format is changed. It is no longer text. Excel does its own
thing (converts to date). I want all the cells to contain text only.

Is there some way I can say, In the spreadsheet I create using POI all
cells are text cells and even if it is opened using Excel and data is
changed the cell contents should still be text.

Please let me know.

Thanks

RE: Cell format question

Posted by Anthony Andrews <py...@yahoo.com>.

Well, if you want the word TRUE to appear in the cell, you can use the same technique - place the single inverted comma in front of the string you are writing to the cell. If you are writing data that you want to be represented only as Strings - text - then place that inverted comma in front of everything by default.

In my opinion, this is not a problem with HSSF but a 'feature' of Excel. Microsoft probably assumed that trying to guess the type of the data you were writing into a cell would be an advantage to the user.

Modha Khammammettu <MK...@calcas.com> wrote: Thanks.
However the case is same when I try to change a cell's contents to TRUE,
making a change to a cell that contains number.

It will be nice if we can say this cell is TEXT and no matter what we
put in treat it as TEXT only. 

-----Original Message-----
From: Anthony Andrews [mailto:pythonaddict@yahoo.com] 
Sent: Sunday, October 14, 2007 12:39 AM
To: POI Users List
Subject: Re: Cell format question

The easiest trick that I know of is to simply prefix the date string
with a single inverted comma. Instead of writing this string to the
cell;

10/10/2007

write this to the cell;

'10/10/2007

That informs Excel that it is to treat the cells contents as a sting.

Modha Khammammettu  wrote: Hi All

I am using POI to create excel spreadsheet.

Every cell I create for this spreadsheet, I create like

HSSFCell cell = row.createCell((short)cellNumber);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);

Now excel file gets created. The problem is when I open this excel file
to manually change lets say date value is a cell

1. Initially when the date is written I believe stored as text. (Ex:
12/4/2007)
2. When I manually open this spreadsheet and change the cell (12/5/2007)
the cell format is changed. It is no longer text. Excel does its own
thing (converts to date). I want all the cells to contain text only.

Is there some way I can say, In the spreadsheet I create using POI all
cells are text cells and even if it is opened using Excel and data is
changed the cell contents should still be text.

Please let me know.

Thanks


       
---------------------------------
Looking for a deal? Find great prices on flights and hotels with Yahoo!
FareChase.

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org



       
---------------------------------
Building a website is a piece of cake. 
Yahoo! Small Business gives you all the tools to get online.

RE: Cell format question

Posted by Modha Khammammettu <MK...@calcas.com>.

Thanks.
However the case is same when I try to change a cell's contents to TRUE,
making a change to a cell that contains number.

It will be nice if we can say this cell is TEXT and no matter what we
put in treat it as TEXT only. 

-----Original Message-----
From: Anthony Andrews [mailto:pythonaddict@yahoo.com] 
Sent: Sunday, October 14, 2007 12:39 AM
To: POI Users List
Subject: Re: Cell format question

The easiest trick that I know of is to simply prefix the date string
with a single inverted comma. Instead of writing this string to the
cell;

10/10/2007

write this to the cell;

'10/10/2007

That informs Excel that it is to treat the cells contents as a sting.

Modha Khammammettu <MK...@calcas.com> wrote: Hi All

I am using POI to create excel spreadsheet.

Every cell I create for this spreadsheet, I create like

HSSFCell cell = row.createCell((short)cellNumber);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);

Now excel file gets created. The problem is when I open this excel file
to manually change lets say date value is a cell

1. Initially when the date is written I believe stored as text. (Ex:
12/4/2007)
2. When I manually open this spreadsheet and change the cell (12/5/2007)
the cell format is changed. It is no longer text. Excel does its own
thing (converts to date). I want all the cells to contain text only.

Is there some way I can say, In the spreadsheet I create using POI all
cells are text cells and even if it is opened using Excel and data is
changed the cell contents should still be text.

Please let me know.

Thanks


       
---------------------------------
Looking for a deal? Find great prices on flights and hotels with Yahoo!
FareChase.

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org

Re: Cell format question

Posted by Anthony Andrews <py...@yahoo.com>.

The easiest trick that I know of is to simply prefix the date string with a single inverted comma. Instead of writing this string to the cell;

10/10/2007

write this to the cell;

'10/10/2007

That informs Excel that it is to treat the cells contents as a sting.

Modha Khammammettu <MK...@calcas.com> wrote: Hi All

I am using POI to create excel spreadsheet.

Every cell I create for this spreadsheet, I create like

HSSFCell cell = row.createCell((short)cellNumber);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);

Now excel file gets created. The problem is when I open this excel file
to manually change lets say date value is a cell

1. Initially when the date is written I believe stored as text. (Ex:
12/4/2007)
2. When I manually open this spreadsheet and change the cell (12/5/2007)
the cell format is changed. It is no longer text. Excel does its own
thing (converts to date). I want all the cells to contain text only.

Is there some way I can say, In the spreadsheet I create using POI all
cells are text cells and even if it is opened using Excel and data is
changed the cell contents should still be text.

Please let me know.

Thanks


       
---------------------------------
Looking for a deal? Find great prices on flights and hotels with Yahoo! FareChase.