poi-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Ruchi2 <ruchiagraw...@yahoo.com>
Subject Re: Automatic sizing of cells in POI
Date Mon, 15 Sep 2008 15:13:10 GMT

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
View raw message