Date Formats when writing to Excel

[Neil Benn <ne...@gmail.com>]

Hello,

         I'm trying to figure out the date formatting string to use to
write dates into the excel sheet.  My code to create the date format
in the cell is as follws:

<explanation>
col.getColumnDataFormatting() = the date formatting string
this.getScanTime() - a java.util.Date instance
wb = HSSFWorkBook
colNum = an Integer representing the column to work on
row = a HSSFRow
</explanation>
<code>
CreationHelper createHelper = wb.getCreationHelper();
HSSFCell dateCell = row.createCell(colNum);
HSSFCellStyle dateCellStyle = wb.createCellStyle();
dateCellStyle.setDataFormat(createHelper.createDataFormat().getFormat(col.getColumnDataFormatting()));
dateCell.setCellStyle(dateCellStyle);
dateCell.setCellValue(this.getScanTime());
</code>

  I've worked out he following:

m - min in the hour
M - month in the year
y - year
h or H - hour in 24 hour clock format
s - second

  However I'm missing the following and the standard java date
formatting codes don't work:

AM/PM
Hour in 12 hour clock format

  Could anyone help me fill in the blanks please?

  Thanks, in advance for your help.

Cheers,

Neil

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

Re: Date Formats when writing to Excel

[Neil Benn <ne...@gmail.com>]

Ah yes - I can use a date formatting formula in excel - I'll give it a try!

Thanks.

On Fri, Dec 17, 2010 at 3:31 PM, Mark Beardsley
<ma...@tiscali.co.uk> wrote:
>
> Hello Neil
>
> For things like this, I always use Excel itself. Use the application and
> play about with the cell format until you see what you are after. Next
> simply copy the format string and use iot in POI, that almost always does
> the trick.
>
> Yours
>
> Mark B
> --
> View this message in context: http://apache-poi.1045710.n5.nabble.com/Date-Formats-when-writing-to-Excel-tp3306887p3309563.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
>
>

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

Re: Date Formats when writing to Excel

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

Hello Neil

For things like this, I always use Excel itself. Use the application and
play about with the cell format until you see what you are after. Next
simply copy the format string and use iot in POI, that almost always does
the trick.

Yours

Mark B
-- 
View this message in context: http://apache-poi.1045710.n5.nabble.com/Date-Formats-when-writing-to-Excel-tp3306887p3309563.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