You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Shuli Zhou/schedule <sh...@Barra.COM> on 2005/09/22 01:50:07 UTC
date cell as date type instead of custom type
Hi,
We use the package to generate Excel files. When loading the generated files
in Excel, all date cells have custom format not date format. Even if they
look exactly the same in Excel, we have problems later using another
software to read those date cells. That software expects them to be of date
type not custom type. Is there a way to make date cell as date type using
POI?
Here is the how the cell style is set:
short formatIx = this.dataFormat.getFormat(desc.format);
s.setDataFormat(formatIx);
this.dataFormat is of HSSFDataFormat
s is of HSSFCellStyle.
Thanks.
Shirley
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
RE: date cell as date type instead of custom type
Posted by "Peter Dow (DSS)" <pd...@dowsoftware.com>.
Hi Shirley,
Dates in Excel are just double-precision floating point numbers. The
integer portion (to the left of the decimal point) is the date -- actually
the number of days from 12/31/1899 -- and the fractional portion (to the
right of the decimal point) is the time (fraction of 86400 seconds in a
day).
If you put the number 1.25 into a cell and format it as a date, it will show
01/01/1900. If you format it as a time, it will show 6:00:00 AM (one-fourth
of a day is 6 hours, so starting at midnight, that's 6am).
Here's the code (RPG) I use to set a date format (courtesy Scott Klement):
// Create a cell style for dates. Dates in Excel
// are numbers that are formatted in a particular
// way.
//
Dates = HSSFWorkbook_createCellStyle(book);
DataFmt = HSSFWorkbook_createDataFormat(book);
TempStr = new_String('m/d/yy');
DateFmt = HSSFDataFormat_getFormat(DataFmt: TempStr);
HSSFCellStyle_setDataFormat(Dates: DateFmt);
The trickier part is converting a string date, e.g. "09/21/2005", to a
serial date. In RPG, there are ways to convert a string to a date, then
perform date arithmetic to get the Excel date number; basically subtract
"12/31/1899" from "09/21/2005" to determine the number of days between them,
and that's the Excel serial date number. I assume there's something similar
in java or C or whatever you're using. There is one trick -- Excel considers
1900 a leap year (it wasn't), so if the date you're converting is after
2/28/1900, you need to add 1.
hth,
Peter Dow
Dow Software Services, Inc.
www.dowsoftware.com
909 793-9050 voice
909 793-4480 fax
> -----Original Message-----
> From: Shuli Zhou/schedule [mailto:shuli.zhou@Barra.COM]
> Sent: Wednesday, September 21, 2005 4:50 PM
> To: 'poi-user@jakarta.apache.org'
> Subject: date cell as date type instead of custom type
>
>
> Hi,
>
> We use the package to generate Excel files. When loading the
> generated files
> in Excel, all date cells have custom format not date format. Even if they
> look exactly the same in Excel, we have problems later using another
> software to read those date cells. That software expects them to
> be of date
> type not custom type. Is there a way to make date cell as date type using
> POI?
>
> Here is the how the cell style is set:
> short formatIx = this.dataFormat.getFormat(desc.format);
> s.setDataFormat(formatIx);
>
> this.dataFormat is of HSSFDataFormat
> s is of HSSFCellStyle.
>
> Thanks.
> Shirley
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
> Mailing List: http://jakarta.apache.org/site/mail2.html#poi
> The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
>
>
> --
> No virus found in this incoming message.
> Checked by AVG Anti-Virus.
> Version: 7.0.344 / Virus Database: 267.11.3/107 - Release Date: 9/20/2005
>
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.11.4/109 - Release Date: 9/21/2005
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/