poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From <Iain.Sheph...@barclayscapital.com>
Subject RE: [poi] Column --> Autofit selection
Date Thu, 19 Jan 2006 13:09:17 GMT
Thomas,

I have actually written the code to do it using FontMetrics as Antony suggested.

The performance hit is around 1 second on our hardware, I have a simple sampling algorithm
so I only check cell widths on a sample of the rows (I check around 400 rows for a 65,536
row spreadsheet).

However the FontMetrics widths don't exactly match with Excel widths, there is a certain difference.
A multiplication factor that makes the Excel columns look perfect for Arial 10pt, is wrong
for Arial 18pt bold. I haven't bothered trying to solve this problem as we only ever use Arial
10.

Let me know if you want this code, I will try and extract it.

Iain



-----Original Message-----
From: Anthony Andrews [mailto:pythonaddict@yahoo.com] 
Sent: 19 January 2006 09:29
To: POI Users List
Subject: Re: [poi] Column --> Autofit selection


Hello Thomas,
  
  Sorry to say there is not an easier way than this. It would be nice to  have a method that
replicates what Excel does when you work  interactively with it or one that mimics the applications
response when  you click on one of the row/column dividers but there is nothing like  this
as far as I know.
  
  Thomas.Laengle@sealedair.com wrote:  Hello Christian, Hi Anthony,

many thanks for your help.
Actually I was hoping there will be a "cheaper" way to do it ;-) So I will go the hard way,
if necessary.

Thanks,
Thomas




There is another way if you are wiling to go to  the trouble. It is possible using the FontMetrics
class to discover the  length of a String with some accuracy. This technique will also  compensate
for different fonts/font sizes and could overcome the  problem Christian notes under point
a) in his reply. Be prepared for a  performance hit if you use this technique as you will
be creating and  destroying quite a number of different objects.

  Aside from this, I would do EXACTLY what Christian advises.

Christian Gosch  wrote:  We do the hard work way:

We just track every value we enter resp. its length as a String and hold an array/list with
an entry for the max. length (that is char count) of every column. After filling of the sheet
is complete, we go through the columns and set every column width according to its matching
measure. For this we use a factor based on advice by POI, currently 256. This cuts longer
texts (hides the last chars), but works for usual cases.

In fact this is a bit ugly, since...
(a) the calculated length does not really handle cases like "iiiii" vs. "WWWWW" with proportional
fonts, witch would in turn require to calculate the "real" (optical) width of the textual
content based on font metrics data :-( But Excel is not very good in doing this job also :-)
(b) *of course* it would be fine to have just an option associated with the column that says
"auto-fit". But as far as I can see, Excel does simply not provide that: In fact it seems
that Excel calculates the optimal width just on (dialogue user) demand and sets the actual
result of this computation as new column width. :-(

hth,
--cg

On Wednesday, January 18, 2006 4:14 PM [GMT+1=CET], Thomas.Laengle@sealedair.com  wrote:

> Hello,
>
> I try to define the layout of columns in POI.
> Is there a way in POI to set the column to the optimal width? In Excel 
> you can achieve this by hand: Format-->Column-->Autofit Selection. 
> Does POI provide a  method doing the same?
>
> What I know is how to set a default width:
> ###
> HSSFSheet sheet = wb.createSheet("Test");
> ....
> sheet.setDefaultColumnWidth((short)20);
> ###
>
> Is there a smarter way?
> Many thanks in advanced!
> Thomas
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
> Mailing List:     http://jakarta.apache.org/site/mail2.html#poi
> The Apache Jakarta Poi Project:  http://jakarta.apache.org/poi/

Regards,
--
Dipl.-Inform. Christian Gosch
Systems Development
inovex GmbH
Karlsruher Strasse 71
D-75179 Pforzheim
Tel.: +49 (0)72 31 - 31 91 - 85
Fax: +49 (0)72 31 - 31 91 - 91
mailto:c.gosch@inovex.de
http://www.inovex.de


---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List:     http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project:  http://jakarta.apache.org/poi/





---------------------------------
Yahoo! Photos – Showcase holiday pictures in hardcover
 Photo Books. You design it and we’ll bind it!





		
---------------------------------
Yahoo! Photos
 Ring in the New Year with Photo Calendars. Add photos, events, holidays, whatever.


------------------------------------------------------------------------
For more information about Barclays Capital, please
visit our web site at http://www.barcap.com.


Internet communications are not secure and therefore the Barclays 
Group does not accept legal responsibility for the contents of this 
message.  Although the Barclays Group operates anti-virus programmes, 
it does not accept responsibility for any damage whatsoever that is 
caused by viruses being passed.  Any views or opinions presented are 
solely those of the author and do not necessarily represent those of the 
Barclays Group.  Replies to this email may be monitored by the Barclays 
Group for operational or business reasons.

------------------------------------------------------------------------


---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List:     http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project:  http://jakarta.apache.org/poi/


Mime
View raw message