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:[EMAIL PROTECTED]
> Sent: Wednesday, September 21, 2005 4:50 PM
> To: '[email protected]'
> 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: [EMAIL PROTECTED]
> 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: [EMAIL PROTECTED]
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
