poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mark Beardsley <markbrd...@tiscali.co.uk>
Subject Re: Adding dates as text with a leading apostrophe.
Date Mon, 29 Nov 2010 07:40:23 GMT

No Paal, this will not work with POI at all. Excel perfroms lots of little
tricks like this that are built in to the user interface - thinlk parsing
HTML if you copy and paste it into a cell for instance.

Another thread recently cropped up asking something similar and wanting to
force the cell to be set as text irrespective of the value that was entered
- it was actually to prevent the cell reverting to the numeric type if the
user edited it's contents in Excel. The trick that list member (Sanjeev
Verma) found can be best explained by copying their message here;

"Nick, Mark and Gabriel:

thank you guys for replying, very appreciated.

After making the post, I was browsing through the API when I saw the
DataFormat class and its hierarchy, the BuiltinFormats class, and the
setDataFormat method of the CellStyle class. So did some experimentation,
and the code below seems to work!

                XSSFCellStyle textFormatStyle = book.createCellStyle();
               
textFormatStyle.setDataFormat((short)BuiltinFormats.getBuiltinFormat("text"));
                XSSFCell cell = row.createCell(columnIndex++);
                cell.setCellStyle(textFormatStyle);

now, once spreadsheet is created, I can edit a cell, and when I tab out, the
format remains "text". I can still change it if I want to by doing right
click --> "Format Cells...", but at least it will not change on its own.

Thanks again guys for looking at this and the quick response, very
appreciated!

Regards
Sanjeev "

It seems as though forcing the format for the cell to be the built-in text
format worked in that case and it may in yours but of course I cannot
promise that this is the case. If you do test this and find that it does
work, could I ask you to confirm this on the list please as there have been
others asking a similar question in the past?

Yours

Mark B
-- 
View this message in context: http://apache-poi.1045710.n5.nabble.com/Adding-dates-as-text-with-a-leading-apostrophe-tp3283872p3284005.html
Sent from the POI - User mailing list archive at Nabble.com.

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


Mime
View raw message