poi-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Michael <mh...@lakeland.net>
Subject Re: Automatic sizing of cells in POI
Date Sat, 13 Sep 2008 14:38:55 GMT
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


Mime
View raw message