poi-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mhall <mh...@polk.net>
Subject Re: Automatic sizing of cells in POI
Date Mon, 15 Sep 2008 17:22:03 GMT
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
> > 
> > 
> 
> 
> 
> 
> 

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org


Mime
View raw message