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="US-ASCII"
Content-Transfer-Encoding: quoted-printable
Subject: RE: [poi] Column --> Autofit selection
Date: Tue, 24 Jan 2006 17:16:14 -0000
Message-ID: 
 <2DF66B61F300C247ACF5F407088D80B10DD8C5@LDNPCMEU302VEUA.INTRANET.BARCAPINT.COM>
Thread-Topic: [poi] Column --> Autofit selection
Thread-Index: AcYdAdb8BqfYyht3T7e25awgWTckKQEBTbegAACrAnA=
From: <Iain.Shepherd@barclayscapital.com>
To: <poi-user@jakarta.apache.org>

The attachment got swallowed somewhere. I have pasted it at the end of
this mail.


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


import java.awt.Font;
import java.awt.FontMetrics;
import java.awt.Graphics2D;
import java.awt.image.BufferedImage;
import java.util.HashMap;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFFont;

/**
 * Calculates the width of a column, based on the values within it.
 * <p>
 * For each new value added to the column, call {@link
#isNotificationRequired}.
 * If the result is true, call {@link #notifyCellValue}.
 */
public class AutoColumnSizer
{
    private static final short WIDTH_MIN =3D 40;
    private static final short WIDTH_MAX =3D 250;
  =20
    private static final short WIDTH_PADDING =3D 5;

    private static final int[] ROW_BAND =3D {2, 100, 1000, 10000, 65536};
    private static final int[] ROW_BAND_SAMPLE_FREQUENCY =3D {1, 10, 100,
1000};

    /** Graphics context used for obtaining FontMetrics objects */
    private Graphics2D graphics =3D null;
    /** Maps a Short (HSSF font index) to a FontMetrics object */
    private Map fontMetrics =3D new HashMap();
  =20
    private short currentWidth =3D WIDTH_MIN;
  =20
  =20
    public AutoColumnSizer()
    {
        // Nothing to do
    }
  =20
  =20
    private FontMetrics getFontMetrics(HSSFFont hf)
    {
        FontMetrics fm;
        Short pFont =3D new Short(hf.getIndex());
      =20
        fm =3D (FontMetrics) fontMetrics.get(pFont);
        if(fm =3D=3D null) {
            // Lazy initialization of FontMetrics
            int style;
            if((hf.getBoldweight() =3D=3D HSSFFont.BOLDWEIGHT_BOLD) ||
hf.getItalic()) {
                style =3D 0;
                if(hf.getBoldweight() =3D=3D HSSFFont.BOLDWEIGHT_BOLD)
                    style ^=3D Font.BOLD;
                if(hf.getItalic())
                    style ^=3D Font.ITALIC;
            }
            else {
                style =3D Font.PLAIN;
            }
            // TODO - HSSFFonts can also be Subscript or Superscript
            Font f =3D new java.awt.Font(hf.getFontName(), style,
hf.getFontHeightInPoints());
          =20
          =20
            if(graphics =3D=3D null) {
                // Lazy initialization of Graphics2D
                // Graphics & FontMetrics is not specified anywhere as
threadsafe,
                // so each AutoColumnSizer creates its own
                // It would be faster to use one per thread. But overall
performance
                // is already totally acceptable so I haven't bothered.
                // (It takes awhile the first time you run this code in
a given VM,
                // but further runs are quick).
                BufferedImage i =3D new BufferedImage(1, 1,
BufferedImage.TYPE_BYTE_GRAY);
                graphics =3D i.createGraphics();
            }
          =20
            fm =3D graphics.getFontMetrics(f);=20
            fontMetrics.put(pFont, fm);
        }
      =20
        return fm;
    }
  =20

    /**
     * When you add a new value to a column, call this method to ask
whether
     * the AutoColumnSizer is interested in it.
     */
    public boolean isNotificationRequired(int row)
    {
        if(row < 0) throw new IllegalArgumentException("illegal row: " +
row);
      =20
        /* To improve performance, we calculate column widths based on
         * a SAMPLE of all rows. */
      =20
        // Find which band the row falls into...
        int rowBand =3D -1;      =20
        for (int band=3D0; band < ROW_BAND.length; band++) {
            if(row < ROW_BAND[band]) {
                rowBand =3D band - 1;
                break;
            }
        }
      =20
        if(rowBand =3D=3D -1) {
            // Row doesn't fall into any band
            return false;
        }      =20
        else if((row % ROW_BAND_SAMPLE_FREQUENCY[rowBand]) !=3D 0) {
            // Row isn't selected for our sample
            return false;
        }
        else {
            return true;
        }
    }

  =20
    public void notifyCellValue(String val, HSSFFont font)
    {
        if(val =3D=3D null || val.length() =3D=3D 0) return;
        if(font =3D=3D null) throw new IllegalArgumentException("font is
null");
      =20
        short width;
        {
            FontMetrics fm =3D getFontMetrics(font);
            int w =3D fm.stringWidth(val);
            width =3D (w > Short.MAX_VALUE) ? Short.MAX_VALUE : (short) w;
            // TODO - this gives an underestimate with large font-sizes.
            // TODO - What we *should* be considering is the 'display
width'.
            // This means we'd have to take into account cell type &
format.
        }
      =20
        if(width > currentWidth) {
            currentWidth =3D width;
        }
    }


    public short getWidth()
    {
        if((currentWidth + WIDTH_PADDING) <=3D WIDTH_MAX)
            return (short)(currentWidth + WIDTH_PADDING);
        else
            return WIDTH_MAX;
    }


    public void dispose()
    {
        if(graphics !=3D null) {
            graphics.finalize();
            graphics =3D null;
        }
        fontMetrics =3D null;
    }
}=20


------------------------------------------------------------------------
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/