Mailing-List: contact poi-user-help@jakarta.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "POI Users List" <poi-user@jakarta.apache.org>
Received-SPF: pass (asf.osuosl.org: local policy)
Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain; charset="windows-1256"
Content-Transfer-Encoding: quoted-printable
Subject: RE: [poi] Column --> Autofit selection
Date: Thu, 19 Jan 2006 13:09:17 -0000
Message-ID: 
 <2DF66B61F300C247ACF5F407088D80B10DD891@LDNPCMEU302VEUA.INTRANET.BARCAPINT.COM>
Thread-Topic: [poi] Column --> Autofit selection
Thread-Index: AcYc2tQBZ8c2ik+2SHGyp5p+ssgdoQAHWdrQ
From: <Iain.Shepherd@barclayscapital.com>
To: <poi-user@jakarta.apache.org>

Thomas,

I have actually written the code to do it using FontMetrics as Antony sugge=
sted.

The performance hit is around 1 second on our hardware, I have a simple sam=
pling algorithm so I only check cell widths on a sample of the rows (I chec=
k 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 colu=
mns look perfect for Arial 10pt, is wrong for Arial 18pt bold. I haven't bo=
thered 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]=20
Sent: 19 January 2006 09:29
To: POI Users List
Subject: Re: [poi] Column --> Autofit selection


Hello Thomas,
 =20
  Sorry to say there is not an easier way than this. It would be nice to  h=
ave a method that replicates what Excel does when you work  interactively w=
ith 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.
 =20
  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 possibl=
e using the FontMetrics class to discover the  length of a String with some=
 accuracy. This technique will also  compensate for different fonts/font si=
zes and could overcome the  problem Christian notes under point a) in his r=
eply. Be prepared for a  performance hit if you use this technique as you w=
ill 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 u=
se 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. "WW=
WWW" 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 no=
t provide that: In fact it seems that Excel calculates the optimal width ju=
st 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=3DCET], Thomas.Laengle@sealed=
air.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=20
> you can achieve this by hand: Format-->Column-->Autofit Selection.=20
> Does POI provide a  method doing the same?
>
> What I know is how to set a default width:
> ###
> HSSFSheet sheet =3D 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 =E2=80=93 Showcase holiday pictures in hardcover
 Photo Books. You design it and we=E2=80=99ll bind it!


	=09
---------------------------------
Yahoo! Photos
 Ring in the New Year with Photo Calendars. Add photos, events, holidays, w=
hatever.


------------------------------------------------------------------------
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=20
Group does not accept legal responsibility for the contents of this=20
message.  Although the Barclays Group operates anti-virus programmes,=20
it does not accept responsibility for any damage whatsoever that is=20
caused by viruses being passed.  Any views or opinions presented are=20
solely those of the author and do not necessarily represent those of the=20
Barclays Group.  Replies to this email may be monitored by the Barclays=20
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/