From c48613da0a67f364a08ce93716d771b24d608860 Mon Sep 17 00:00:00 2001 From: Yegor Kozlov Date: Tue, 13 Feb 2007 16:25:55 +0000 Subject: [PATCH] support for auto-sizing worksheet columns git-svn-id: https://svn.apache.org/repos/asf/jakarta/poi/trunk@507076 13f79535-47bb-0310-9956-ffa450edef68 --- .../content/xdocs/hssf/quick-guide.xml | 54 +++++----- .../apache/poi/hssf/usermodel/HSSFSheet.java | 99 +++++++++++++++++++ 2 files changed, 131 insertions(+), 22 deletions(-) diff --git a/src/documentation/content/xdocs/hssf/quick-guide.xml b/src/documentation/content/xdocs/hssf/quick-guide.xml index 6c3d964d0c..470dcb4589 100644 --- a/src/documentation/content/xdocs/hssf/quick-guide.xml +++ b/src/documentation/content/xdocs/hssf/quick-guide.xml @@ -65,6 +65,7 @@
  • Images
  • Named Ranges and Named Cells
  • How to set cell comments
    • +
  • How to adjust column width to fit the contents
    • Features @@ -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(); - - + +
    @@ -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(); - - + +
    Using the Convenience Functions

    @@ -568,7 +569,7 @@ wb.write(fileOut); fileOut.close(); -

    +
    Set a sheet as selected @@ -583,7 +584,7 @@ wb.write(fileOut); fileOut.close(); -
    +
    Set the zoom magnification @@ -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 @@
    Named Ranges and Named Cells

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

    @@ -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); - +

    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. } - +

    Cell Comments @@ -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,13 +1125,22 @@

    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(); }
    + +
    Adjust column width to fit the contents + + 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 + + +
    diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java b/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java index 574535c70e..9c3c147a68 100644 --- a/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java +++ b/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java @@ -36,6 +36,12 @@ import java.util.ArrayList; 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 @@ import java.util.TreeMap; * @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 class HSSFSheet 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)); + } + } + } + } -- 2.39.5