Mailing-List: contact poi-user-help@jakarta.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "POI Users List" <poi-user@jakarta.apache.org>
Received-SPF: pass (asf.osuosl.org: local policy)
Reply-To: <pdow@dowsoftware.com>
From: "Peter Dow \(DSS\)" <pdow@dowsoftware.com>
To: "POI Users List" <poi-user@jakarta.apache.org>
Subject: RE: date cell as date type instead of custom type
Date: Thu, 22 Sep 2005 11:17:36 -0700
Message-ID: <ADEHILFGFGECDGEHGKGJGEKMEAAA.pdow@dowsoftware.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
In-Reply-To: <AA1E1F7FC9E1124F999E37BFA1DD34470D34B807@iris.win.barra.com>
Importance: Normal

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/