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 [EMAIL PROTECTED]
#isNotificationRequired}.
* If the result is true, call [EMAIL PROTECTED] #notifyCellValue}.
*/
public class AutoColumnSizer
{
private static final short WIDTH_MIN = 40;
private static final short WIDTH_MAX = 250;
private static final short WIDTH_PADDING = 5;
private static final int[] ROW_BAND = {2, 100, 1000, 10000, 65536};
private static final int[] ROW_BAND_SAMPLE_FREQUENCY = {1, 10, 100,
1000};
/** Graphics context used for obtaining FontMetrics objects */
private Graphics2D graphics = null;
/** Maps a Short (HSSF font index) to a FontMetrics object */
private Map fontMetrics = new HashMap();
private short currentWidth = WIDTH_MIN;
public AutoColumnSizer()
{
// Nothing to do
}
private FontMetrics getFontMetrics(HSSFFont hf)
{
FontMetrics fm;
Short pFont = new Short(hf.getIndex());
fm = (FontMetrics) fontMetrics.get(pFont);
if(fm == null) {
// Lazy initialization of FontMetrics
int style;
if((hf.getBoldweight() == HSSFFont.BOLDWEIGHT_BOLD) ||
hf.getItalic()) {
style = 0;
if(hf.getBoldweight() == HSSFFont.BOLDWEIGHT_BOLD)
style ^= Font.BOLD;
if(hf.getItalic())
style ^= Font.ITALIC;
}
else {
style = Font.PLAIN;
}
// TODO - HSSFFonts can also be Subscript or Superscript
Font f = new java.awt.Font(hf.getFontName(), style,
hf.getFontHeightInPoints());
if(graphics == 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 = new BufferedImage(1, 1,
BufferedImage.TYPE_BYTE_GRAY);
graphics = i.createGraphics();
}
fm = graphics.getFontMetrics(f);
fontMetrics.put(pFont, fm);
}
return fm;
}
/**
* 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);
/* To improve performance, we calculate column widths based on
* a SAMPLE of all rows. */
// Find which band the row falls into...
int rowBand = -1;
for (int band=0; band < ROW_BAND.length; band++) {
if(row < ROW_BAND[band]) {
rowBand = band - 1;
break;
}
}
if(rowBand == -1) {
// Row doesn't fall into any band
return false;
}
else if((row % ROW_BAND_SAMPLE_FREQUENCY[rowBand]) != 0) {
// Row isn't selected for our sample
return false;
}
else {
return true;
}
}
public void notifyCellValue(String val, HSSFFont font)
{
if(val == null || val.length() == 0) return;
if(font == null) throw new IllegalArgumentException("font is
null");
short width;
{
FontMetrics fm = getFontMetrics(font);
int w = fm.stringWidth(val);
width = (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.
}
if(width > currentWidth) {
currentWidth = width;
}
}
public short getWidth()
{
if((currentWidth + WIDTH_PADDING) <= WIDTH_MAX)
return (short)(currentWidth + WIDTH_PADDING);
else
return WIDTH_MAX;
}
public void dispose()
{
if(graphics != null) {
graphics.finalize();
graphics = null;
}
fontMetrics = null;
}
}
------------------------------------------------------------------------
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/
