Bug 48408: Improved documentation for Sheet.setColumnWidth

git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1137656 13f79535-47bb-0310-9956-ffa450edef68

4 files changed, 110 insertions, 5 deletions

diff --git a/src/documentation/content/xdocs/status.xml b/src/documentation/content/xdocs/status.xml
index 77ba26574c..00bf441446 100644
--- a/src/documentation/content/xdocs/status.xml
+++ b/src/documentation/content/xdocs/status.xml
@@ -34,6 +34,7 @@
 
     <changes>
         <release version="3.8-beta4" date="2011-??-??">
+           <action dev="poi-developers" type="fix">48408 - Improved documentation for Sheet.setColumnWidth </action>
            <action dev="poi-developers" type="add">51390 - Added handling of additional properties to HWPF ParagraphSprmCompressor</action>
            <action dev="poi-developers" type="add">51389 - Support for sprmPJc paragraph SPRM in HWPF</action>
            <action dev="poi-developers" type="fix">48469 - New Case Study for PI web site  </action>
diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java b/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java
index e9bd4cf4d5..92b5220356 100644
--- a/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java
+++ b/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java
@@ -429,15 +429,47 @@ public final class HSSFSheet implements org.apache.poi.ss.usermodel.Sheet {
 
     /**
      * Set the width (in units of 1/256th of a character width)
+     *
      * <p>
      * The maximum column width for an individual cell is 255 characters.
      * This value represents the number of characters that can be displayed
-     * in a cell that is formatted with the standard font.
+     * in a cell that is formatted with the standard font (first font in the workbook).
+     * </p>
+     *
+     * <p>
+     * Character width is defined as the maximum digit width
+     * of the numbers <code>0, 1, 2, … 9</code> as rendered
+     * using the default font (first font in the workbook).
+     * <br/>
+     * Unless you are using a very special font, the default character is '0' (zero),
+     * this is true for Arial (default font font in HSSF) and Calibri (default font in XSSF)
+     * </p>
+     *
+     * <p>
+     * Please note, that the width set by this method includes 4 pixels of margin padding (two on each side),
+     * plus 1 pixel padding for the gridlines (Section 3.3.1.12 of the OOXML spec).
+     * This results is a slightly less value of visible characters than passed to this method (approx. 1/2 of a character).
      * </p>
+     * <p>
+     * To compute the actual number of visible characters,
+     *  Excel uses the following formula (Section 3.3.1.12 of the OOXML spec):
+     * </p>
+     * <code>
+     *     width = Truncate([{Number of Visible Characters} *
+     *      {Maximum Digit Width} + {5 pixel padding}]/{Maximum Digit Width}*256)/256
+     * </code>
+     * <p>Using the Calibri font as an example, the maximum digit width of 11 point font size is 7 pixels (at 96 dpi).
+     *  If you set a column width to be eight characters wide, e.g. <code>setColumnWidth(columnIndex, 8*256)</code>,
+     *  then the actual value of visible characters (the value shown in Excel) is derived from the following equation:
+     *  <code>
+            Truncate([numChars*7+5]/7*256)/256 = 8;
+     *  </code>
+     *
+     *  which gives <code>7.29</code>.
      *
      * @param columnIndex - the column to set (0-based)
      * @param width - the width in units of 1/256th of a character width
-     * @throws IllegalArgumentException if width > 65280 (the maximum column width in Excel)
+     * @throws IllegalArgumentException if width > 255*256 (the maximum column width in Excel is 255 characters)
      */
     public void setColumnWidth(int columnIndex, int width) {
         _sheet.setColumnWidth(columnIndex, width);
diff --git a/src/java/org/apache/poi/ss/usermodel/Sheet.java b/src/java/org/apache/poi/ss/usermodel/Sheet.java
index 349c42cbdf..090c25150d 100644
--- a/src/java/org/apache/poi/ss/usermodel/Sheet.java
+++ b/src/java/org/apache/poi/ss/usermodel/Sheet.java
@@ -118,19 +118,59 @@ public interface Sheet extends Iterable<Row> {
 
     /**
      * Set the width (in units of 1/256th of a character width)
+     *
      * <p>
      * The maximum column width for an individual cell is 255 characters.
      * This value represents the number of characters that can be displayed
-     * in a cell that is formatted with the standard font.
+     * in a cell that is formatted with the standard font (first font in the workbook).
+     * </p>
+     *
+     * <p>
+     * Character width is defined as the maximum digit width
+     * of the numbers <code>0, 1, 2, … 9</code> as rendered
+     * using the default font (first font in the workbook).
+     * <br/>
+     * Unless you are using a very special font, the default character is '0' (zero),
+     * this is true for Arial (default font font in HSSF) and Calibri (default font in XSSF)
      * </p>
      *
+     * <p>
+     * Please note, that the width set by this method includes 4 pixels of margin padding (two on each side),
+     * plus 1 pixel padding for the gridlines (Section 3.3.1.12 of the OOXML spec).
+     * This results is a slightly less value of visible characters than passed to this method (approx. 1/2 of a character).
+     * </p>
+     * <p>
+     * To compute the actual number of visible characters,
+     *  Excel uses the following formula (Section 3.3.1.12 of the OOXML spec):
+     * </p>
+     * <code>
+     *     width = Truncate([{Number of Visible Characters} *
+     *      {Maximum Digit Width} + {5 pixel padding}]/{Maximum Digit Width}*256)/256
+     * </code>
+     * <p>Using the Calibri font as an example, the maximum digit width of 11 point font size is 7 pixels (at 96 dpi).
+     *  If you set a column width to be eight characters wide, e.g. <code>setColumnWidth(columnIndex, 8*256)</code>,
+     *  then the actual value of visible characters (the value shown in Excel) is derived from the following equation:
+     *  <code>
+            Truncate([numChars*7+5]/7*256)/256 = 8;
+     *  </code>
+     *
+     *  which gives <code>7.29</code>.
+     *
      * @param columnIndex - the column to set (0-based)
      * @param width - the width in units of 1/256th of a character width
+     * @throws IllegalArgumentException if width > 255*256 (the maximum column width in Excel is 255 characters)
      */
     void setColumnWidth(int columnIndex, int width);
 
     /**
      * get the width (in units of 1/256th of a character width )
+     *
+     * <p>
+     * Character width is defined as the maximum digit width
+     * of the numbers <code>0, 1, 2, … 9</code> as rendered
+     * using the default font (first font in the workbook)
+     * </p>
+     *
      * @param columnIndex - the column to set (0-based)
      * @return width - the width in units of 1/256th of a character width
      */
diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java
index 204b4492fb..6ebd49432e 100644
--- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java
+++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java
@@ -1899,15 +1899,47 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet {
 
     /**
      * Set the width (in units of 1/256th of a character width)
+     *
      * <p>
      * The maximum column width for an individual cell is 255 characters.
      * This value represents the number of characters that can be displayed
-     * in a cell that is formatted with the standard font.
+     * in a cell that is formatted with the standard font (first font in the workbook).
+     * </p>
+     *
+     * <p>
+     * Character width is defined as the maximum digit width
+     * of the numbers <code>0, 1, 2, … 9</code> as rendered
+     * using the default font (first font in the workbook).
+     * <br/>
+     * Unless you are using a very special font, the default character is '0' (zero),
+     * this is true for Arial (default font font in HSSF) and Calibri (default font in XSSF)
+     * </p>
+     *
+     * <p>
+     * Please note, that the width set by this method includes 4 pixels of margin padding (two on each side),
+     * plus 1 pixel padding for the gridlines (Section 3.3.1.12 of the OOXML spec).
+     * This results is a slightly less value of visible characters than passed to this method (approx. 1/2 of a character).
+     * </p>
+     * <p>
+     * To compute the actual number of visible characters,
+     *  Excel uses the following formula (Section 3.3.1.12 of the OOXML spec):
      * </p>
+     * <code>
+     *     width = Truncate([{Number of Visible Characters} *
+     *      {Maximum Digit Width} + {5 pixel padding}]/{Maximum Digit Width}*256)/256
+     * </code>
+     * <p>Using the Calibri font as an example, the maximum digit width of 11 point font size is 7 pixels (at 96 dpi).
+     *  If you set a column width to be eight characters wide, e.g. <code>setColumnWidth(columnIndex, 8*256)</code>,
+     *  then the actual value of visible characters (the value shown in Excel) is derived from the following equation:
+     *  <code>
+            Truncate([numChars*7+5]/7*256)/256 = 8;
+     *  </code>
+     *
+     *  which gives <code>7.29</code>.
      *
      * @param columnIndex - the column to set (0-based)
      * @param width - the width in units of 1/256th of a character width
-     * @throws IllegalArgumentException if width > 65280 (the maximum column width in Excel)
+     * @throws IllegalArgumentException if width > 255*256 (the maximum column width in Excel is 255 characters)
      */
     public void setColumnWidth(int columnIndex, int width) {
         if(width > 255*256) throw new IllegalArgumentException("The maximum column width for an individual cell is 255 characters.");