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/ |
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/ |
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 > > > 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 <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/ |
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/ |
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/ |
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/ |
Free forum by Nabble | Edit this page |