You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Christian Gosch <c....@inovex.de> on 2005/11/15 18:21:22 UTC
Re: [poi] RE: POI 2.5.1 final: Date value formatting to 'DD.MM.YYYY'
OK, the first step I have got now, and to be honest, I did not tell the
whole truth about my code, because I did not think of the missing part being
of special meaning :-(
In fact I "reused" _one_ HSSFCellStyle object and tried to give it another
DataFormat any time I use it. But there does not seem to be any change any
more after the first setting...
Now I prepare 3 HSSFCellStyle objects, one for String, Number and Date each.
And oops, it works -- at least I get something like "d-mmm-yy" like you say
(or "10 Jun. 44" in the example).
But now the desired format "DD.MM.YYYY" (for example: "10.04.1944") is still
open, since this is not predefined.
A sample sheet is attached (I hope it is passed through the listserv). It
was generated using MS Excel2000 deDE on W2KSP4 deDE.
Regards,
Christian
On Tuesday, November 15, 2005 5:45 PM [GMT+1=CET],
Laubach Shawn Contr 327 CSSG/GFSL <Sh...@tinker.af.mil> wrote:
> That's add as the data format 15 DEC is really "d-mmm-yy". Would it
> be possible to attach an example sheet along with a list of a cell or
> two that has that particular format.
>
> Shawn
>
> -----Original Message-----
> From: Christian Gosch [mailto:c.gosch@inovex.de]
> Sent: Tuesday, November 15, 2005 10:36 AM
> To: POI Users List
> Subject: POI 2.5.1 final: Date value formatting to 'DD.MM.YYYY'
>
> Hi,
>
> I cannot successfully format a cell with "DD.MM.YYYY" nor with any
> other date format. All I get is "#.##0" number format for the cells.
> How can I achieve "DD.MM.YYYY" formatting?
>
> POI version: 2.5.1 final (this is fairly old, but there does not seem
> to be any newer "final" version :-( )
>
>
> Code sample:
>
> // font for rows:
> HSSFCellStyle borderdFormat = workbook.createCellStyle();
> // borderdFormat.setBorderTop( HSSFCellStyle.BORDER_HAIR); //
> should be...
> // borderdFormat.setBorderBottom( HSSFCellStyle.BORDER_HAIR);
> // should be...
> borderdFormat.setBorderTop( HSSFCellStyle.BORDER_DOTTED); //
> ...but is not (!)
> borderdFormat.setBorderBottom( HSSFCellStyle.BORDER_DOTTED);
> // ...but is not (!)
> [...]
> else if (data instanceof Date) {
> // data is of type Date
> log.debug("XLS-DL: Date: xlsZeile = " + xlsZeile + "
> spalte = " + spalte + " value = " + data + " class = " +
> data.getClass());
> borderdFormat.setDataFormat((short)15);
> label.setCellValue( ((Date)data));
> label.setCellStyle(borderdFormat); sdata =
> data.toString(); }
>
> The log.debug(...) output is like:
> XLS-DL: Date: xlsZeile = 1 spalte = 10 value = 1944-04-10 01:00:00.0
> class = class java.sql.Timestamp
>
> The resulting cell value display in Excel2000 (deDE in W2KSP4 deDE)
> is:
> 16.172
>
>
> which would be the result of formatting "10.4.1944" or "Apr. 10,
> 1944" with "#.##0".
>
>
> What am I doing wrong?
>
> regards,
Gruesse,
--
Dipl.-Inform. Christian Gosch
Systems Development
inovex GmbH
Karlsruher Strasse 71
D-75179 Pforzheim
Tel.: +49 (0)72 31 - 31 91 - 85
Fax: +49 (0)72 31 - 31 91 - 91
mailto:c.gosch@inovex.de
http://www.inovex.de
Re: [poi] RE: POI 2.5.1 final: Date value formatting to 'DD.MM.YYYY'
Posted by Karl-Heinz Zengerle <ka...@sawag.com>.
Hi Christian.
Be careful.
If you've a lot of date cells and do workbook.createDataFormat() for any
such cell it'll result in too many data formats. Do it just once and
then refer to it when using a date format.
Regards, Karl-Heinz.
Am Di 15.11.2005 18:32 schrieb Christian Gosch <c....@inovex.de>:
> OK, I got this by myself too.
>
> Although not being very obvious, I found by decompilation that
> "HSSFDataFormat myFormat = workbook.createDataFormat()" and a
> successive
> call to "myFormat.getFormat("DD.MM.YYYY")" creates an appropriate
> format, if
> there is no such one in the workbook. That does the job.
>
> Regards,
> Christian
>
>
> On Tuesday, November 15, 2005 6:21 PM [GMT+1=CET],
> Christian Gosch <c....@inovex.de> wrote:
>
> >> OK, the first step I have got now, and to be honest, I did not tell
> >> the whole truth about my code, because I did not think of the
> >> missing part being of special meaning :-(
> >>
> >> In fact I "reused" _one_ HSSFCellStyle object and tried to give it
> >> another DataFormat any time I use it. But there does not seem to be
> >> any change any more after the first setting...
> >>
> >> Now I prepare 3 HSSFCellStyle objects, one for String, Number and
> >> Date each. And oops, it works -- at least I get something like
> >> "d-mmm-yy" like you say (or "10 Jun. 44" in the example).
> >>
> >>
> >> But now the desired format "DD.MM.YYYY" (for example: "10.04.1944")
> >> is still open, since this is not predefined.
> >>
> >> A sample sheet is attached (I hope it is passed through the
> >> listserv). It was generated using MS Excel2000 deDE on W2KSP4 deDE.
> >>
> >> Regards,
> >> Christian
> >>
> >>
> >> On Tuesday, November 15, 2005 5:45 PM [GMT+1=CET],
> >> Laubach Shawn Contr 327 CSSG/GFSL <Sh...@tinker.af.mil>
> >> wrote:
> >>
> >>> That's add as the data format 15 DEC is really "d-mmm-yy". Would
> >>> it
> >>> be possible to attach an example sheet along with a list of a cell
> >>> or two that has that particular format.
> >>>
> >>> Shawn
> >>>
> >>> -----Original Message-----
> >>> From: Christian Gosch [mailto:c.gosch@inovex.de]
> >>> Sent: Tuesday, November 15, 2005 10:36 AM
> >>> To: POI Users List
> >>> Subject: POI 2.5.1 final: Date value formatting to 'DD.MM.YYYY'
> >>>
> >>> Hi,
> >>>
> >>> I cannot successfully format a cell with "DD.MM.YYYY" nor with any
> >>> other date format. All I get is "#.##0" number format for the
> >>> cells.
> >>> How can I achieve "DD.MM.YYYY" formatting?
> >>>
> >>> POI version: 2.5.1 final (this is fairly old, but there does not
> >>> seem to be any newer "final" version :-( )
> >>>
> >>>
> >>> Code sample:
> >>>
> >>> // font for rows:
> >>> HSSFCellStyle borderdFormat = workbook.createCellStyle();
> >>> // borderdFormat.setBorderTop( HSSFCellStyle.BORDER_HAIR);
> >>> // should be...
> >>> // borderdFormat.setBorderBottom(
> >>> HSSFCellStyle.BORDER_HAIR); // should be...
> >>> borderdFormat.setBorderTop( HSSFCellStyle.BORDER_DOTTED); //
> >>> ...but is not (!)
> >>> borderdFormat.setBorderBottom( HSSFCellStyle.BORDER_DOTTED);
> >>> // ...but is not (!)
> >>> [...]
> >>> else if (data instanceof Date) {
> >>> // data is of type Date
> >>> log.debug("XLS-DL: Date: xlsZeile = " + xlsZeile + "
> >>> spalte = " + spalte + " value = " + data + " class = " +
> >>> data.getClass());
> >>> borderdFormat.setDataFormat((short)15);
> >>> label.setCellValue( ((Date)data));
> >>> label.setCellStyle(borderdFormat); sdata =
> >>> data.toString(); }
> >>>
> >>> The log.debug(...) output is like:
> >>> XLS-DL: Date: xlsZeile = 1 spalte = 10 value = 1944-04-10
> >>> 01:00:00.0
> >>> class = class java.sql.Timestamp
> >>>
> >>> The resulting cell value display in Excel2000 (deDE in W2KSP4
> >>> deDE)
> >>> is:
> >>> 16.172
> >>>
> >>>
> >>> which would be the result of formatting "10.4.1944" or "Apr. 10,
> >>> 1944" with "#.##0".
> >>>
> >>>
> >>> What am I doing wrong?
> >>>
> >>> regards,
> >>
> >> Gruesse,
> >> --
> >> Dipl.-Inform. Christian Gosch
> >> Systems Development
> >> inovex GmbH
> >> Karlsruher Strasse 71
> >> D-75179 Pforzheim
> >> Tel.: +49 (0)72 31 - 31 91 - 85
> >> Fax: +49 (0)72 31 - 31 91 - 91
> >> mailto:c.gosch@inovex.de
> >> http://www.inovex.de
> >>
> >>
> >
> >
> >
> >>
> >> ---------------------------------------------------------------------
> >> 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/
>
> Gruesse,
> --
> Dipl.-Inform. Christian Gosch
> Systems Development
> inovex GmbH
> Karlsruher Strasse 71
> D-75179 Pforzheim
> Tel.: +49 (0)72 31 - 31 91 - 85
> Fax: +49 (0)72 31 - 31 91 - 91
> mailto:c.gosch@inovex.de
> http://www.inovex.de
>
>
> ---------------------------------------------------------------------
> 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/
>
>
---------------------------------------------------------------------
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: [poi] RE: POI 2.5.1 final: Date value formatting to 'DD.MM.YYYY'
Posted by Christian Gosch <c....@inovex.de>.
OK, I got this by myself too.
Although not being very obvious, I found by decompilation that
"HSSFDataFormat myFormat = workbook.createDataFormat()" and a successive
call to "myFormat.getFormat("DD.MM.YYYY")" creates an appropriate format, if
there is no such one in the workbook. That does the job.
Regards,
Christian
On Tuesday, November 15, 2005 6:21 PM [GMT+1=CET],
Christian Gosch <c....@inovex.de> wrote:
>> OK, the first step I have got now, and to be honest, I did not tell
>> the whole truth about my code, because I did not think of the
>> missing part being of special meaning :-(
>>
>> In fact I "reused" _one_ HSSFCellStyle object and tried to give it
>> another DataFormat any time I use it. But there does not seem to be
>> any change any more after the first setting...
>>
>> Now I prepare 3 HSSFCellStyle objects, one for String, Number and
>> Date each. And oops, it works -- at least I get something like
>> "d-mmm-yy" like you say (or "10 Jun. 44" in the example).
>>
>>
>> But now the desired format "DD.MM.YYYY" (for example: "10.04.1944")
>> is still open, since this is not predefined.
>>
>> A sample sheet is attached (I hope it is passed through the
>> listserv). It was generated using MS Excel2000 deDE on W2KSP4 deDE.
>>
>> Regards,
>> Christian
>>
>>
>> On Tuesday, November 15, 2005 5:45 PM [GMT+1=CET],
>> Laubach Shawn Contr 327 CSSG/GFSL <Sh...@tinker.af.mil>
>> wrote:
>>
>>> That's add as the data format 15 DEC is really "d-mmm-yy". Would it
>>> be possible to attach an example sheet along with a list of a cell
>>> or two that has that particular format.
>>>
>>> Shawn
>>>
>>> -----Original Message-----
>>> From: Christian Gosch [mailto:c.gosch@inovex.de]
>>> Sent: Tuesday, November 15, 2005 10:36 AM
>>> To: POI Users List
>>> Subject: POI 2.5.1 final: Date value formatting to 'DD.MM.YYYY'
>>>
>>> Hi,
>>>
>>> I cannot successfully format a cell with "DD.MM.YYYY" nor with any
>>> other date format. All I get is "#.##0" number format for the cells.
>>> How can I achieve "DD.MM.YYYY" formatting?
>>>
>>> POI version: 2.5.1 final (this is fairly old, but there does not
>>> seem to be any newer "final" version :-( )
>>>
>>>
>>> Code sample:
>>>
>>> // font for rows:
>>> HSSFCellStyle borderdFormat = workbook.createCellStyle();
>>> // borderdFormat.setBorderTop( HSSFCellStyle.BORDER_HAIR);
>>> // should be...
>>> // borderdFormat.setBorderBottom(
>>> HSSFCellStyle.BORDER_HAIR); // should be...
>>> borderdFormat.setBorderTop( HSSFCellStyle.BORDER_DOTTED); //
>>> ...but is not (!)
>>> borderdFormat.setBorderBottom( HSSFCellStyle.BORDER_DOTTED);
>>> // ...but is not (!)
>>> [...]
>>> else if (data instanceof Date) {
>>> // data is of type Date
>>> log.debug("XLS-DL: Date: xlsZeile = " + xlsZeile + "
>>> spalte = " + spalte + " value = " + data + " class = " +
>>> data.getClass());
>>> borderdFormat.setDataFormat((short)15);
>>> label.setCellValue( ((Date)data));
>>> label.setCellStyle(borderdFormat); sdata =
>>> data.toString(); }
>>>
>>> The log.debug(...) output is like:
>>> XLS-DL: Date: xlsZeile = 1 spalte = 10 value = 1944-04-10 01:00:00.0
>>> class = class java.sql.Timestamp
>>>
>>> The resulting cell value display in Excel2000 (deDE in W2KSP4 deDE)
>>> is:
>>> 16.172
>>>
>>>
>>> which would be the result of formatting "10.4.1944" or "Apr. 10,
>>> 1944" with "#.##0".
>>>
>>>
>>> What am I doing wrong?
>>>
>>> regards,
>>
>> Gruesse,
>> --
>> Dipl.-Inform. Christian Gosch
>> Systems Development
>> inovex GmbH
>> Karlsruher Strasse 71
>> D-75179 Pforzheim
>> Tel.: +49 (0)72 31 - 31 91 - 85
>> Fax: +49 (0)72 31 - 31 91 - 91
>> mailto:c.gosch@inovex.de
>> http://www.inovex.de
>>
>>
>
>
>
>> ---------------------------------------------------------------------
>> 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/
Gruesse,
--
Dipl.-Inform. Christian Gosch
Systems Development
inovex GmbH
Karlsruher Strasse 71
D-75179 Pforzheim
Tel.: +49 (0)72 31 - 31 91 - 85
Fax: +49 (0)72 31 - 31 91 - 91
mailto:c.gosch@inovex.de
http://www.inovex.de
---------------------------------------------------------------------
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/