poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Anthony Andrews <pythonadd...@yahoo.com>
Subject RE: [poi] Column --> Autofit selection
Date Thu, 19 Jan 2006 14:07:47 GMT
This is well off topic so I apologise but I was  very interested to hear that Ian. In the past,
I have done loads - far  too much really - printing using Java; that is where I first ran
into  the FontMetrics class. Never noticed that discrepancy you mentioned  with regard to
different fonts. Must be that checking line lengths is  far less critical than column widths.
Must go back and have another  look when I get the chance.
  
  Is it OK if I have a look at your code as well if you post it for Thomas?

Iain.Shepherd@barclayscapital.com wrote:  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/




		
---------------------------------
Yahoo! Photos – Showcase holiday pictures in hardcover
 Photo Books. You design it and we’ll bind it!
Mime
  • Unnamed multipart/alternative (inline, 8-Bit, 0 bytes)
View raw message