Anthony As you requested. Hopefully it is clear enough...
Usage 1. Create a separate AutoColumnSizer for each column on each sheet. 2. For each cell you add, call AutoColumnSizer.isNotificationRequired. 3. If that returns true, call AutoColumnSizer.notifyCellValue. ... 4. After all cells are added, call AutoColumnSizer.getWidth. 5. Multiply the result by a magic number (I use 48) and pass that to HSSFSheet.setColumnWidth. You can see from reading the TODOs there are unresolved issues with this approach. Take a float cell with value 100000. At the moment I check the width of "100000". But depending on the style, the *displayed* value might be considerably wider, e.g. "100,000.00". So you basically ought to replicate Excel style handling too... So I don't think this will ever be a good general purpose solution. But it's good enough for us. Cheers Iain -----Original Message----- From: Anthony Andrews [mailto:[EMAIL PROTECTED] Sent: 19 January 2006 14:08 To: POI Users List Subject: RE: [poi] Column --> Autofit selection 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? [EMAIL PROTECTED] 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:[EMAIL PROTECTED] 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. [EMAIL PROTECTED] 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], [EMAIL PROTECTED] 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: [EMAIL PROTECTED] > 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:[EMAIL PROTECTED] http://www.inovex.de --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] 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!
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] Mailing List: http://jakarta.apache.org/site/mail2.html#poi The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
