Adding dates as text with a leading apostrophe.

[Paal Larsen <pa...@hotmail.no>]

In Excel adding a leading apostrophe changes for instance dates to text and the apostrophe is not shown, except for in the formula line. (For example adding '29.11.2010 is shown as 29.10.2010 .)
 
I want to do this when using POI. When setting the cell value I add a apostrophe in front of a string which represents a date. But the apostrophe is shown when opening the resulting file in Excel. If I add a date manually in another cell in the same file the apostrophe is not shown.
 
Does the apostrophe trick not work with POI?
 
(Adding the dates as text by using a string or changing format of the cell to text isn't an option. By doing this the resulting file shows the dates as left aligned text, but I am importing the file to a third party software and in the resulting spreadsheet such cells are changed to the "Excel integer" the date represents. And manually changing the cell formats afterwards is not an option.) 
 
-Paal Larsen. 		 	   		  

Re: Adding dates as text with a leading apostrophe.

[Mark Beardsley <ma...@tiscali.co.uk>]

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