poi-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Ruchi Agrawal <ruchiagraw...@yahoo.com>
Subject Re: Automatic sizing of cells in POI
Date Mon, 15 Sep 2008 17:50:14 GMT
Thanks a ton for all your help and suggestions. The format index does return >0. I will
open a bugzilla ticket and attach some code. I found on the net that that there was a bug
in POI3.1 final which is being fixed. I dont think it should be related to my case since my
width does not exceed Short.MAX_VALUE, but just wanted to bring up.

45645 - Fix for HSSFSheet.autoSizeColumn() for widths exceeding Short.MAX_VALUE(POI-DEVELOPERS)
.
http://poi.apache.org/changes.html



----- Original Message ----
From: Mhall <mhall@polk.net>
To: Ruchi Agrawal <ruchiagrawal2@yahoo.com>
Cc: dev@poi.apache.org
Sent: Monday, September 15, 2008 12:41:10 PM
Subject: Re: Automatic sizing of cells in POI

Get the format index before setting it, and check that it returns > 0.  I don't recall
what happens if it can't create a format definition for your string, but it may be returning
an invalid index.

If that doesn't work it may be a bug, open a bugzilla ticket and attach some code that will
reproduce the error.


-----Original message-----
From: Ruchi Agrawal ruchiagrawal2@yahoo.com
Date: Mon, 15 Sep 2008 12:35:21 -0400
To: mhall@lakeland.net
Subject: Re: Automatic sizing of cells in POI

> Yes i am using a format string on A2.
> 
> HSSFDataFormat formt = wb.createDataFormat(); 
> cellStyle.setDataFormat(formt.getFormat("m/d/yy h:mm:ss AM/PM"));
> cell.setCellValue(cellValue);//where cell value is a java.util.Date
>  When i click on A2 in excel i see the data in correct format. In the below code the
cell A2 is not even returned from the row.cellIterator(); and hence its not part of the while
loop where the column is being suto sized. That is my very first part of yje problem as why
that cell is not being returned and why System.out.println(row.getCell(0)); is printing "null".
> 
> 
> 
> 
> 
> ----- Original Message ----
> From: Mhall <mhall@polk.net>
> To: Ruchi Agrawal <ruchiagrawal2@yahoo.com>; mhall@lakeland.net
> Cc: dev@poi.apache.org
> Sent: Monday, September 15, 2008 12:22:03 PM
> Subject: Re: Automatic sizing of cells in POI
> 
> Are you setting a format string on A2?  calling HSSFCell.setCellValue(Date) converts
the date into a double, and uses a numeric cell style instead of a string cell type, but it
doesn't apply a date format to the cell.  HSSFSheet.autoSizeColumn(short) then takes the
value of the cell, and if you haven't given it a date format, will use the length of the double
(as text) for column width.
> 
> -----Original message-----
> From: Ruchi Agrawal ruchiagrawal2@yahoo.com
> Date: Mon, 15 Sep 2008 12:10:42 -0400
> To: mhall@lakeland.net
> Subject: Re: Automatic sizing of cells in POI
> 
> > cell A1 and B1 (i.e the column headers) are set using string. The data on A2 is
set using Date and B2 as double.. Excel sheet attached. I have another related problem in
the code below. The print statements on line 3 and 4 return me null on line 3 and only line
4 returns a value. row.cellIterator() also returns only one cell i.e the cell B2.
> > 
> > 1) private static void autoSizeColumns(HSSFSheet sheet, TableModel table ) throws
Exception {
> >  2) HSSFRow row = sheet.getRow(1);
> >  3) System.out.println(row.getCell(0));
> >  4) System.out.println(row.getCell(1));
> >  5) java.util.Iterator<HSSFCell> it = row.cellIterator(); 
> >  6) while(it.hasNext()){
> >   7) HSSFCell cell = it.next();
> >   8) System.out.println("value " + cell.toString());
> >   9) short colNum = (short)cell.getCellNum(); 
> >   10) sheet.autoSizeColumn(colNum,true); 
> >   11) sheet.setColumnWidth(colNum, (short)(sheet.getColumnWidth(colNum)+256));

> >  12)}
> > 13) }
> > 
> > 
> > 
> > ----- Original Message ----
> > From: Mhall <mhall@polk.net>
> > To: POI Developers List <dev@poi.apache.org>; Ruchi2 <ruchiagrawal2@yahoo.com>
> > Sent: Monday, September 15, 2008 11:59:47 AM
> > Subject: Re: Automatic sizing of cells in POI
> > 
> > Okay, I think I was wrong in saying that column index starts at 1.
> > 
> > Are you setting the cell value of A1 using a string, or a number?
> > 
> > -----Original message-----
> > From: Ruchi2 ruchiagrawal2@yahoo.com
> > Date: Mon, 15 Sep 2008 10:13:42 -0400
> > To: dev@poi.apache.org
> > Subject: Re: Automatic sizing of cells in POI
> > 
> > > 
> > > Thank you for your reply. The set of column width does not seem to work
> > > either. My sheet looks like below.
> > > A1 and B1 has column headers : col1 and col2.. A2 and B2 have the data A2 =>
> > > 8/29/08 2:36:00 PM and  B2 => (265.3457). Basically cell A2 is that date
> > > cell when i open excel the data on A2 shows as "#####" but when clicked over
> > > it does show the correct value on top. My first problem is that
> > > cell.iterator() on row(1) in POI returns me only one cell B2 i.e the
> > > (265.3457). It does not even return me the cell A2 even tho it has the value
> > > and shows the correct value when i open the excel sheet.
> > > 
> > > Please suggest if you have any more ideas. Thanks.
> > >  
> > > 
> > > mhall119 wrote:
> > > > 
> > > > Is the date cell in question in row #1?  An important thing to remember
> > > > is that in Excel, you can have a non-existant cell between 2 existing
> > > > ones.  So, for example, if your spreadsheet has data in cells A1 and
C1,
> > > > but not B1, and your date cell is in B2, row.cellIterator() will only
> > > > give you A1 and C1, so you won't be calling autoSizeColumn() on B.
> > > > 
> > > > Another important thing is that autoSizeColumn is a bit of a hack, there
> > > > isn't an Excel file property that says "autosize this column", instead
> > > > the method attempts to calculate the necessary width by rendering the
> > > > text using the cell's font format, then it has to divide the number of
> > > > pixels into 1/256 of a "character", which is what Excel uses, though the
> > > > spec doesn't actually specify how many pixels a "character" has.  It
> > > > works in most cases, but may not work for all of them, yours might be
a
> > > > corner case.  You can always add a "character" width to the auto sized
> > > > value for safe measure:
> > > > 
> > > > HSSFRow row = sheet.getRow(1); 
> > > > java.util.Iterator<HSSFCell> it = row.cellIterator(); 
> > > > while(it.hasNext()){ 
> > > >     HSSFCell cell = it.next(); 
> > > >     short colNum = (short)cell.getCellNum();
> > > >     sheet.autoSizeColumn(colNum); 
> > > >     sheet.setColumnWidth(colNum, sheet.getColumnWidth(colNum)+256);
> > > > }
> > > > 
> > > > Also, if you have merged regions that you want to be included in the
> > > > auto size calculation, be sure to use sheet.autoSizeColumn(colNum,
> > > > true), otherwise they will be ignored.
> > > > 
> > > > On Fri, 2008-09-12 at 14:36 -0700, Ruchi2 wrote:
> > > >> I am trying to work on automatic sizing of columns in POI. I am trying
to
> > > >> do
> > > >> this per column once all my data is generated. The data in one of
the
> > > >> columns is date time and since it is larger than the cell width it
shows
> > > >> as
> > > >> "#######" when i open excel. The below code des not work at all int
his
> > > >> case. Can some please provide any help. 
> > > >> 
> > > >> HSSFRow row  = sheet.getRow(1); 
> > > >> java.util.Iterator<HSSFCell> it = row.cellIterator(); 
> > > >> while(it.hasNext()){ 
> > > >>        HSSFCell cell = it.next(); 
> > > >>        sheet.autoSizeColumn((short)cell.getCellNum()); 
> > > >> }
> > > > -- 
> > > > Michael <mhall@lakeland.net>
> > > > 
> > > > 
> > > > ---------------------------------------------------------------------
> > > > To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
> > > > For additional commands, e-mail: dev-help@poi.apache.org
> > > > 
> > > > 
> > > > 
> > > 
> > > -- 
> > > View this message in context: http://www.nabble.com/Automatic-sizing-of-cells-in-POI-tp19464564p19495026.html
> > > Sent from the POI - Dev mailing list archive at Nabble.com.
> > > 
> > > 
> > > ---------------------------------------------------------------------
> > > To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
> > > For additional commands, e-mail: dev-help@poi.apache.org
> > > 
> > > 
> > 
> > 
> > 
> > 
> > 
> 
> 
> 
> 
> 



      
Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message