Cool functionality, thanks Yegor! Two quick questions..
1. Is this expected to compile with jdk 1.4 or 1.3? 2. for formula cells, it shouldnt probably be sized on the text of the formula, but on the formula result. Of course, for POI created formula's we dont have the result... thoughts?? Regards - Avik On Tuesday 13 February 2007 21:55, [EMAIL PROTECTED] wrote: > Author: yegor > Date: Tue Feb 13 08:25:55 2007 > New Revision: 507076 > > URL: http://svn.apache.org/viewvc?view=rev&rev=507076 > Log: > support for auto-sizing worksheet columns > > Modified: > jakarta/poi/trunk/src/documentation/content/xdocs/hssf/quick-guide.xml > jakarta/poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java > > Modified: > jakarta/poi/trunk/src/documentation/content/xdocs/hssf/quick-guide.xml URL: > http://svn.apache.org/viewvc/jakarta/poi/trunk/src/documentation/content/xd >ocs/hssf/quick-guide.xml?view=diff&rev=507076&r1=507075&r2=507076 > =========================================================================== >=== --- > jakarta/poi/trunk/src/documentation/content/xdocs/hssf/quick-guide.xml > (original) +++ > jakarta/poi/trunk/src/documentation/content/xdocs/hssf/quick-guide.xml Tue > Feb 13 08:25:55 2007 @@ -65,6 +65,7 @@ > <li><link href="#Images">Images</link></li> > <li><link href="#NamedRanges">Named Ranges and Named > Cells</link></li> <li><link href="#CellComments">How to set cell > comments</link></li> + <li><link href="#Autofit">How to > adjust column width to fit the contents</link></li> </ul> > </section> > <section><title>Features</title> > @@ -451,9 +452,9 @@ > HSSFWorkbook wb = new HSSFWorkbook(); > HSSFSheet sheet = wb.createSheet("format sheet"); > HSSFPrintSetup ps = sheet.getPrintSetup(); > - > + > sheet.setAutobreaks(true); > - > + > ps.setFitHeight((short)1); > ps.setFitWidth((short)1); > > @@ -473,15 +474,15 @@ > wb.setPrintArea(0, "$A$1:$C$2"); > //sets the print area for the first sheet > //Alternatively: > - //wb.setPrintArea(0, 0, 1, 0, 0) is equivalent to using the name > reference (See the JavaDocs for more details) + //wb.setPrintArea(0, 0, > 1, 0, 0) is equivalent to using the name reference (See the JavaDocs for > more details) > > // Create various cells and rows for spreadsheet. > > FileOutputStream fileOut = new FileOutputStream("workbook.xls"); > wb.write(fileOut); > fileOut.close(); > - > - > + > + > </source> > </section> > > @@ -491,9 +492,9 @@ > HSSFWorkbook wb = new HSSFWorkbook(); > HSSFSheet sheet = wb.createSheet("format sheet"); > HSSFFooter footer = sheet.getFooter() > - > + > footer.setRight( "Page " + HSSFFooter.page() + " of " + > HSSFFooter.numPages() ); - > + > > > // Create various cells and rows for spreadsheet. > @@ -502,8 +503,8 @@ > wb.write(fileOut); > fileOut.close(); > </source> > - </section> > - > + </section> > + > <anchor id="ConvenienceFunctions"/> > <section><title>Using the Convenience Functions</title> > <p> > @@ -568,7 +569,7 @@ > wb.write(fileOut); > fileOut.close(); > </source> > - </section> > + </section> > > <anchor id="SelectSheet"/> > <section><title>Set a sheet as selected</title> > @@ -583,7 +584,7 @@ > wb.write(fileOut); > fileOut.close(); > </source> > - </section> > + </section> > > <anchor id="Zoom"/> > <section><title>Set the zoom magnification</title> > @@ -709,7 +710,7 @@ > HSSFHeader header = sheet.getHeader(); > header.setCenter("Center Header"); > header.setLeft("Left Header"); > - header.setRight(HSSFHeader.font("Stencil-Normal", "Italic") + > + header.setRight(HSSFHeader.font("Stencil-Normal", "Italic") + > HSSFHeader.fontSize((short) 16) + "Right w/ > Stencil-Normal Italic font and size 16"); > > FileOutputStream fileOut = new FileOutputStream("workbook.xls"); > @@ -993,10 +994,10 @@ > <section> > <title>Named Ranges and Named Cells</title> > <p> > - Named Range is a way to refer to a group of cells by a > name. Named Cell is a + Named Range is a way to refer to a > group of cells by a name. Named Cell is a degenerate case of Named Range in > that the 'group of cells' contains exactly one cell. You can create as well > as refer to cells in a workbook by their named range. - When > working with Named Ranges, the classes: > org.apache.poi.hssf.util.CellReference and + When working > with Named Ranges, the classes: org.apache.poi.hssf.util.CellReference and > & org.apache.poi.hssf.util.AreaReference are used. </p> > <p> > @@ -1008,25 +1009,25 @@ > HSSFWorkbook wb = new HSSFWorkbook(); > HSSFSheet sheet = wb.createSheet(sname); > sheet.createRow(0).createCell((short) 0).setCellValue(cvalue); > - > + > // 1. create named range for a single cell using areareference > HSSFName namedCell = wb.createName(); > namedCell.setNameName(cname); > String reference = sname+"!A1:A1"; // area reference > namedCell.setReference(reference); > - > + > // 2. create named range for a single cell using cellreference > HSSFName namedCell = wb.createName(); > namedCell.setNameName(cname); > String reference = sname+"!A1"; // cell reference > namedCell.setReference(reference); > - > + > // 3. create named range for an area using AreaReference > HSSFName namedCell = wb.createName(); > namedCell.setNameName(cname); > String reference = sname+"!A1:C5"; // area reference > namedCell.setReference(reference); > - > + > </source> > <p> > Reading from Named Range / Named Cell > @@ -1039,7 +1040,7 @@ > // retrieve the named range > int namedCellIdx = wb.getNameIndex(cellName); > HSSFName aNamedCell = wb.getNameAt(namedCellIdx); > - > + > // retrieve the cell at the named range and test its contents > AreaReference aref = new AreaReference(aNamedCell.getReference()); > CellReference[] crefs = aref.getCells(); > @@ -1050,7 +1051,7 @@ > // extract the cell contents based on cell type etc. > } > </source> > - > + > </section> > <anchor id="CellComments"/> > <section><title>Cell Comments</title> > @@ -1103,7 +1104,7 @@ > > comment2.setString(string); > //by default comments are hidden. This one is always visible. > - comment2.setVisible(true); > + comment2.setVisible(true); > > comment2.setAuthor("Bill Gates"); > > @@ -1124,12 +1125,21 @@ > </p> > <source> > HSSFCell cell = sheet.get(3).getColumn((short)1); > - HSSFComment comment = cell.getCellComment(); > + HSSFComment comment = cell.getCellComment(); > if (comment != null) { > HSSFRichTextString str = comment.getString(); > String author = comment.getAuthor(); > } > </source> > + </section> > + <anchor id="Autofit"/> > + <section><title>Adjust column width to fit the contents</title> > + <source> > + HSSFSheet sheet = workbook.getSheetAt(0); > + sheet.autoSizeColumn((short)0); //adjust width of the first column > + sheet.autoSizeColumn((short)1); //adjust width of the second column > + </source> > + > </section> > > </body> > > Modified: > jakarta/poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java > URL: > http://svn.apache.org/viewvc/jakarta/poi/trunk/src/java/org/apache/poi/hssf >/usermodel/HSSFSheet.java?view=diff&rev=507076&r1=507075&r2=507076 > =========================================================================== >=== --- > jakarta/poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java > (original) +++ > jakarta/poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java Tue > Feb 13 08:25:55 2007 @@ -36,6 +36,12 @@ > import java.util.Iterator; > import java.util.List; > import java.util.TreeMap; > +import java.text.AttributedString; > +import java.text.NumberFormat; > +import java.text.DecimalFormat; > +import java.awt.font.TextLayout; > +import java.awt.font.FontRenderContext; > +import java.awt.font.TextAttribute; > > /** > * High level representation of a worksheet. > @@ -44,6 +50,7 @@ > * @author Libin Roman (romal at vistaportal.com) > * @author Shawn Laubach (slaubach at apache dot org) (Just a little) > * @author Jean-Pierre Paris (jean-pierre.paris at m4x dot org) (Just a > little, too) + * @author Yegor Kozlov (yegor at apache.org) (Autosizing > columns) */ > > public class HSSFSheet > @@ -1381,4 +1388,96 @@ > public void setDefaultColumnStyle(short column, HSSFCellStyle style) { > sheet.setColumn(column, new Short(style.getIndex()), null, null, null, > null); } > + > + /** > + * Adjusts the column width to fit the contents. > + * > + * @param column the column index > + */ > + public void autoSizeColumn(short column) { > + AttributedString str; > + TextLayout layout; > + /** > + * Excel measures columns in units of 1/256th of a character width > + * but the docs say nothing about what particular character is > used. + * '0' looks a good choice. > + */ > + char defaultChar = '0'; > + > + FontRenderContext frc = new FontRenderContext(null, true, true); > + > + HSSFWorkbook wb = new HSSFWorkbook(book); > + HSSFFont defaultFont = wb.getFontAt((short) 0); > + > + str = new AttributedString("" + defaultChar); > + str.addAttribute(TextAttribute.FAMILY, defaultFont.getFontName()); > + str.addAttribute(TextAttribute.SIZE, new > Float(defaultFont.getFontHeightInPoints())); + layout = new > TextLayout(str.getIterator(), frc); > + int defaultCharWidth = (int)layout.getAdvance(); > + > + double width = -1; > + for (Iterator it = rowIterator(); it.hasNext();) { > + HSSFRow row = (HSSFRow) it.next(); > + HSSFCell cell = row.getCell(column); > + if (cell == null) continue; > + > + HSSFCellStyle style = cell.getCellStyle(); > + HSSFFont font = wb.getFontAt(style.getFontIndex()); > + if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { > + HSSFRichTextString rt = cell.getRichStringCellValue(); > + String[] lines = rt.getString().split("\\n"); > + for (int i = 0; i < lines.length; i++) { > + str = new AttributedString(lines[i] + defaultChar); > + str.addAttribute(TextAttribute.FAMILY, > font.getFontName()); + > str.addAttribute(TextAttribute.SIZE, new > Float(font.getFontHeightInPoints())); + if > (font.getBoldweight() == HSSFFont.BOLDWEIGHT_BOLD) > str.addAttribute(TextAttribute.WEIGHT, TextAttribute.WEIGHT_BOLD); + > if (rt.numFormattingRuns() > 0) { > + for (int j = 0; j < lines[i].length(); j++) { > + int idx = rt.getFontAtIndex(j); > + if (idx != 0) { > + HSSFFont fnt = wb.getFontAt((short) idx); > + str.addAttribute(TextAttribute.FAMILY, > fnt.getFontName(), j, j + 1); + > str.addAttribute(TextAttribute.SIZE, new > Float(fnt.getFontHeightInPoints()), j, j + 1); + > } > + } > + } > + layout = new TextLayout(str.getIterator(), frc); > + width = Math.max(width, layout.getAdvance() / > defaultCharWidth); + } > + } else { > + String sval = null; > + if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { > + HSSFDataFormat dataformat = wb.createDataFormat(); > + short idx = style.getDataFormat(); > + String format = > dataformat.getFormat(idx).replaceAll("\"", ""); + double > value = cell.getNumericCellValue(); > + try { > + NumberFormat fmt; > + if ("General".equals(format)) > + fmt = new DecimalFormat(); > + else > + fmt = new DecimalFormat(format); > + sval = fmt.format(value); > + } catch (Exception e) { > + sval = "" + value; > + } > + } else if (cell.getCellType() == > HSSFCell.CELL_TYPE_FORMULA) { + sval = > cell.getCellFormula(); > + } else if (cell.getCellType() == > HSSFCell.CELL_TYPE_BOOLEAN) { + sval = > String.valueOf(cell.getBooleanCellValue()); + } > + > + str = new AttributedString(sval + defaultChar); > + str.addAttribute(TextAttribute.FAMILY, > font.getFontName()); + str.addAttribute(TextAttribute.SIZE, > new Float(font.getFontHeightInPoints())); + layout = new > TextLayout(str.getIterator(), frc); > + width = Math.max(width, layout.getAdvance() / > defaultCharWidth); + } > + > + if (width != -1) { > + sheet.setColumnWidth(column, (short) (width * 256)); > + } > + } > + } > + > } > > > > --------------------------------------------------------------------- > 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/ --------------------------------------------------------------------- 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/
