Apache POI › POI - User

Problem on custom format on existing worksheet

Classic

List

Threaded

7 messages Options

Sancho Chiang

Problem on custom format on existing worksheet

Hi,

I am currently writing an API for writing list of objects into excel worksheet.
POI works great but when it comes to appending data to an existing worksheet,
existing custom data format cannot be applied to the cells. I found that the
indexes of the existing custom format ain't properly mapped.

I drilled into the source code and I got to the line which I guess it's the
source of the problem.

at HSSFDataFormat.getFormat(String)line:235
--> formats.add( ind, i.next() );

I am new to POI and not familiar with the source code. I guess what the above
code does is to populate the builtin data format into "formats", where custom
formats are already in there. Since it is using add(), the existing custom
formats are pushed to the right of the list if the given index is smaller than
those existing custom formats. That messes up the mapping between index and
data format. I don't know whether it's the problem of my code or a known bug. I
hope some guys who are nice enough to clarify the problem. Thx.

This is the code i used:
public static void testDataFormat()throws Exception{
InputStream input = new FileInputStream("players.xls");
POIFSFileSystem fs = new POIFSFileSystem(input);
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheet("Sheet1");
HSSFRow row = sheet.createRow(sheet.getLastRowNum()+1);
HSSFCell cell = row.createCell((short)0);
HSSFCellStyle cellStyle= wb.createCellStyle();
HSSFDataFormat dataFormat = wb.createDataFormat();
short format = dataFormat.getFormat("dd/mm/yyyy");
System.out.println("excelFormat::"+format);
cellStyle.setDataFormat(format);
cell.setCellStyle(cellStyle);
cell.setCellValue(new Date());

FileOutputStream fileOut = new FileOutputStream("players.xls");
wb.write(fileOut);
fileOut.close();
}

Regards,
Sancho Chiang

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/

Lakshmi Prabha

Re: Problem on custom format on existing worksheet

Try using,
HSSFDataFormat.getFormat("dd/MM/yyyy");

mm --> denotes minutes in time format. So, for months
we need to use "MM".

Hope it works.
Regards,
Lakshmi
--- Sancho Chiang <[hidden email]> wrote:

> Hi,
>
> I am currently writing an API for writing list of
> objects into excel worksheet.
> POI works great but when it comes to appending data
> to an existing worksheet,
> existing custom data format cannot be applied to the
> cells. I found that the
> indexes of the existing custom format ain't properly
> mapped.
>
> I drilled into the source code and I got to the line
> which I guess it's the
> source of the problem.
>
> at HSSFDataFormat.getFormat(String)line:235
> --> formats.add( ind, i.next() );
>
> I am new to POI and not familiar with the source
> code. I guess what the above
> code does is to populate the builtin data format
> into "formats", where custom
> formats are already in there. Since it is using
> add(), the existing custom
> formats are pushed to the right of the list if the
> given index is smaller than
> those existing custom formats. That messes up the
> mapping between index and
> data format. I don't know whether it's the problem
> of my code or a known bug. I
> hope some guys who are nice enough to clarify the
> problem. Thx.
>
> This is the code i used:
> public static void testDataFormat()throws
> Exception{
> InputStream input = new
> FileInputStream("players.xls");
> POIFSFileSystem fs = new POIFSFileSystem(input);
> HSSFWorkbook wb = new HSSFWorkbook(fs);
> HSSFSheet sheet = wb.getSheet("Sheet1");
> HSSFRow row =
> sheet.createRow(sheet.getLastRowNum()+1);
> HSSFCell cell = row.createCell((short)0);
> HSSFCellStyle cellStyle= wb.createCellStyle();
> HSSFDataFormat dataFormat = wb.createDataFormat();
> short format = dataFormat.getFormat("dd/mm/yyyy");
> System.out.println("excelFormat::"+format);
> cellStyle.setDataFormat(format);
> cell.setCellStyle(cellStyle);
> cell.setCellValue(new Date());
>
> FileOutputStream fileOut = new
> FileOutputStream("players.xls");
> wb.write(fileOut);
> fileOut.close();
> }
>
> Regards,
> Sancho Chiang
>
>
>

---------------------------------------------------------------------
> To unsubscribe, e-mail:
> [hidden email]
> Mailing List:
> http://jakarta.apache.org/site/mail2.html#poi
> The Apache Jakarta Poi Project:
> http://jakarta.apache.org/poi/
>
>

__________________________________
Yahoo! Mail Mobile
Take Yahoo! Mail with you! Check email on your mobile phone.
http://mobile.yahoo.com/learn/mail

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/

Lakshmi Prabha

Re: Problem on custom format on existing worksheet

In reply to this post by Sancho Chiang

Hi,
sorry, I don't see any issue in your code given.
Anyhow, if you could give us the exact scenerio in
which the problem is coming, (where this is coded?
formats.add( ind, i.next() ); )
and some sample data that you are trying to populate
in the date field, may be we get a solution.
Regards,
Lakshmi
--- Lakshmi Prabha <[hidden email]> wrote:

> Try using,
> HSSFDataFormat.getFormat("dd/MM/yyyy");
>
> mm --> denotes minutes in time format. So, for
> months
> we need to use "MM".
>
> Hope it works.
> Regards,
> Lakshmi
> --- Sancho Chiang <[hidden email]> wrote:
> > Hi,
> >
> > I am currently writing an API for writing list of
> > objects into excel worksheet.
> > POI works great but when it comes to appending
> data
> > to an existing worksheet,
> > existing custom data format cannot be applied to
> the
> > cells. I found that the
> > indexes of the existing custom format ain't
> properly
> > mapped.
> >
> > I drilled into the source code and I got to the
> line
> > which I guess it's the
> > source of the problem.
> >
> > at HSSFDataFormat.getFormat(String)line:235
> > --> formats.add( ind, i.next() );
> >
> > I am new to POI and not familiar with the source
> > code. I guess what the above
> > code does is to populate the builtin data format
> > into "formats", where custom
> > formats are already in there. Since it is using
> > add(), the existing custom
> > formats are pushed to the right of the list if the
> > given index is smaller than
> > those existing custom formats. That messes up the
> > mapping between index and
> > data format. I don't know whether it's the problem
> > of my code or a known bug. I
> > hope some guys who are nice enough to clarify the
> > problem. Thx.
> >
> > This is the code i used:
> > public static void testDataFormat()throws
> > Exception{
> > InputStream input = new
> > FileInputStream("players.xls");
> > POIFSFileSystem fs = new POIFSFileSystem(input);
> > HSSFWorkbook wb = new HSSFWorkbook(fs);
> > HSSFSheet sheet = wb.getSheet("Sheet1");
> > HSSFRow row =
> > sheet.createRow(sheet.getLastRowNum()+1);
> > HSSFCell cell = row.createCell((short)0);
> > HSSFCellStyle cellStyle= wb.createCellStyle();
> > HSSFDataFormat dataFormat =
> wb.createDataFormat();
> > short format =
> dataFormat.getFormat("dd/mm/yyyy");
> > System.out.println("excelFormat::"+format);
> > cellStyle.setDataFormat(format);
> > cell.setCellStyle(cellStyle);
> > cell.setCellValue(new Date());
> >
> > FileOutputStream fileOut = new
> > FileOutputStream("players.xls");
> > wb.write(fileOut);
> > fileOut.close();
> > }
> >
> > Regards,
> > Sancho Chiang
> >
> >
> >
>

---------------------------------------------------------------------

> > To unsubscribe, e-mail:
> > [hidden email]
> > Mailing List:
> > http://jakarta.apache.org/site/mail2.html#poi
> > The Apache Jakarta Poi Project:
> > http://jakarta.apache.org/poi/
> >
> >
>
>
>
> __________________________________
> Yahoo! Mail Mobile
> Take Yahoo! Mail with you! Check email on your
> mobile phone.
> http://mobile.yahoo.com/learn/mail
>
>

Sancho Chiang

Re: Problem on custom format on existing worksheet

Lakshmi Prabha <lakshmipct <at> yahoo.com> writes:

>
> Hi,
> sorry, I don't see any issue in your code given.
> Anyhow, if you could give us the exact scenerio in
> which the problem is coming, (where this is coded?
> formats.add( ind, i.next() ); )
> and some sample data that you are trying to populate
> in the date field, may be we get a solution.
> Regards,
> Lakshmi

Hi,
sorry for my ambiguity. Let me try to clarify it.
The problem I have is that if you try to run the program I posted more than
once, you may find that ONLY the cell in the first row will be formatted. If
you take a look at the "format index" printed out in the console, the "format
index" printed out at the first trial is different from the latter ones.

The code below is the source code of the class HSSFDataFormat which I think it
may be the cause of my problem.
at HSSFDataFormat.getFormat(String)line:235
formats.add( ind, i.next() );

Some folks had similar problem but no solution is provided.
<a href="http://mail-archives.apache.org/mod_mbox/jakarta-poi-user/200403.mbox/%">http://mail-archives.apache.org/mod_mbox/jakarta-poi-user/200403.mbox/%
[hidden email]%3E

Regards,
Sancho Chiang

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/

Shawn Laubach

Re: Problem on custom format on existing worksheet

In reply to this post by Sancho Chiang

If you could create a bug and give a test piece of code and a sample spread
sheet, it would help us track all of this down.

Shawn
----- Original Message -----
From: "Sancho Chiang" <[hidden email]>
To: <[hidden email]>
Sent: Friday, May 06, 2005 5:25 AM
Subject: Problem on custom format on existing worksheet

> Hi,
>
> I am currently writing an API for writing list of objects into excel
> worksheet.
> POI works great but when it comes to appending data to an existing
> worksheet,
> existing custom data format cannot be applied to the cells. I found that
> the
> indexes of the existing custom format ain't properly mapped.
>
> I drilled into the source code and I got to the line which I guess it's
> the
> source of the problem.
>
> at HSSFDataFormat.getFormat(String)line:235
> --> formats.add( ind, i.next() );
>
> I am new to POI and not familiar with the source code. I guess what the
> above
> code does is to populate the builtin data format into "formats", where
> custom
> formats are already in there. Since it is using add(), the existing custom
> formats are pushed to the right of the list if the given index is smaller
> than
> those existing custom formats. That messes up the mapping between index
> and
> data format. I don't know whether it's the problem of my code or a known
> bug. I
> hope some guys who are nice enough to clarify the problem. Thx.
>
> This is the code i used:
> public static void testDataFormat()throws Exception{
> InputStream input = new FileInputStream("players.xls");
> POIFSFileSystem fs = new POIFSFileSystem(input);
> HSSFWorkbook wb = new HSSFWorkbook(fs);
> HSSFSheet sheet = wb.getSheet("Sheet1");
> HSSFRow row = sheet.createRow(sheet.getLastRowNum()+1);
> HSSFCell cell = row.createCell((short)0);
> HSSFCellStyle cellStyle= wb.createCellStyle();
> HSSFDataFormat dataFormat = wb.createDataFormat();
> short format = dataFormat.getFormat("dd/mm/yyyy");
> System.out.println("excelFormat::"+format);
> cellStyle.setDataFormat(format);
> cell.setCellStyle(cellStyle);
> cell.setCellValue(new Date());
>
> FileOutputStream fileOut = new FileOutputStream("players.xls");
> wb.write(fileOut);
> fileOut.close();
> }
>
> Regards,
> Sancho Chiang
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> Mailing List: http://jakarta.apache.org/site/mail2.html#poi
> The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
>

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/

Sancho Chiang

Re: Problem on custom format on existing worksheet

Shawn Laubach <shawnlaubach <at> cox.net> writes:

>
> If you could create a bug and give a test piece of code and a sample spread
> sheet, it would help us track all of this down.
>
> Shawn

public static void main(String[] args) throws Exception{
InputStream input = new FileInputStream("players.xls");
POIFSFileSystem fs = new POIFSFileSystem(input);
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheet("Sheet1");
HSSFRow row = sheet.createRow(sheet.getLastRowNum()+1);
HSSFCell cell = row.createCell((short)0);
HSSFCellStyle cellStyle= wb.createCellStyle();
HSSFDataFormat dataFormat = wb.createDataFormat();
short format = dataFormat.getFormat("dd/mm/yyyy");
System.out.println("excelFormat::"+format);
cellStyle.setDataFormat(format);
cell.setCellStyle(cellStyle);
cell.setCellValue(new Date());

FileOutputStream fileOut = new FileOutputStream("players.xls");
wb.write(fileOut);
fileOut.close();
}

This is the code I used. What the code is trying to do is to append the date to
the first cell in the next avaiable row.
At the begining, the "players.xls" is just an empty spreadsheet. It works
perfectly fine at the very first time(i.e for an empty sheet). That means the
content in cell "A1" is a formatted date. However, after I ran the code for the
second time, the newly added content in the cell "B1" is a numeric value
instead of a fomatted date. Is there anything wrong with my code? Thx a lot.

Regards,
Sancho Chiang

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/

Laubach Shawn Contr 327 CSSG/GFSL

RE: Problem on custom format on existing worksheet

In reply to this post by Sancho Chiang

I've just committed a fix. You can get if from CVS.

Shawn

-----Original Message-----
From: news [mailto:[hidden email]] On Behalf Of Sancho Chiang
Sent: Sunday, May 08, 2005 8:13 PM
To: [hidden email]
Subject: Re: Problem on custom format on existing worksheet

Shawn Laubach <shawnlaubach <at> cox.net> writes:

>
> If you could create a bug and give a test piece of code and a sample
spread
> sheet, it would help us track all of this down.
>
> Shawn

public static void main(String[] args) throws Exception{
InputStream input = new FileInputStream("players.xls");
POIFSFileSystem fs = new POIFSFileSystem(input);
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheet("Sheet1");
HSSFRow row = sheet.createRow(sheet.getLastRowNum()+1);
HSSFCell cell = row.createCell((short)0);
HSSFCellStyle cellStyle= wb.createCellStyle();
HSSFDataFormat dataFormat = wb.createDataFormat();
short format = dataFormat.getFormat("dd/mm/yyyy");
System.out.println("excelFormat::"+format);
cellStyle.setDataFormat(format);
cell.setCellStyle(cellStyle);
cell.setCellValue(new Date());

FileOutputStream fileOut = new FileOutputStream("players.xls");
wb.write(fileOut);
fileOut.close();
}

This is the code I used. What the code is trying to do is to append the date
to
the first cell in the next avaiable row.
At the begining, the "players.xls" is just an empty spreadsheet. It works
perfectly fine at the very first time(i.e for an empty sheet). That means
the
content in cell "A1" is a formatted date. However, after I ran the code for
the
second time, the newly added content in the cell "B1" is a numeric value
instead of a fomatted date. Is there anything wrong with my code? Thx a lot.

Regards,
Sancho Chiang

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/