poi-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ye...@apache.org
Subject svn commit: r1138819 - in /poi/trunk/src: documentation/content/xdocs/ documentation/content/xdocs/spreadsheet/ java/org/apache/poi/hssf/model/ java/org/apache/poi/hssf/usermodel/ java/org/apache/poi/ss/usermodel/ java/org/apache/poi/ss/util/ ooxml/jav...
Date Thu, 23 Jun 2011 10:49:11 GMT
Author: yegor
Date: Thu Jun 23 10:49:11 2011
New Revision: 1138819

URL: http://svn.apache.org/viewvc?rev=1138819&view=rev
Log:
Bug 51415 - Fixed Workbook.createSheet(sheetName) to truncate names longer than 31 characters

Modified:
    poi/trunk/src/documentation/content/xdocs/spreadsheet/quick-guide.xml
    poi/trunk/src/documentation/content/xdocs/status.xml
    poi/trunk/src/java/org/apache/poi/hssf/model/InternalWorkbook.java
    poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java
    poi/trunk/src/java/org/apache/poi/ss/usermodel/Workbook.java
    poi/trunk/src/java/org/apache/poi/ss/util/WorkbookUtil.java
    poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java
    poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestWorkbook.java

Modified: poi/trunk/src/documentation/content/xdocs/spreadsheet/quick-guide.xml
URL: http://svn.apache.org/viewvc/poi/trunk/src/documentation/content/xdocs/spreadsheet/quick-guide.xml?rev=1138819&r1=1138818&r2=1138819&view=diff
==============================================================================
--- poi/trunk/src/documentation/content/xdocs/spreadsheet/quick-guide.xml (original)
+++ poi/trunk/src/documentation/content/xdocs/spreadsheet/quick-guide.xml Thu Jun 23 10:49:11
2011
@@ -92,14 +92,31 @@
                 <anchor id="NewSheet"/>
                 <section><title>New Sheet</title>
                     <source>
-    Workbook wb = new HSSFWorkbook();
-    //Workbook wb = new XSSFWorkbook();
+    Workbook wb = new HSSFWorkbook();  // or new XSSFWorkbook();
     Sheet sheet1 = wb.createSheet("new sheet");
     Sheet sheet2 = wb.createSheet("second sheet");
+
+    // Note that sheet name is Excel must not exceed 31 characters
+    // and must not contain any of the any of the following characters:
+    // 0x0000
+    // 0x0003
+    // colon (:)
+    // backslash (\)
+    // asterisk (*)
+    // question mark (?)
+    // forward slash (/)
+    // opening square bracket ([)
+    // closing square bracket (])
+
+    // You can use org.apache.poi.ss.util.WorkbookUtil#createSafeSheetName(String nameProposal)}
+    // for a safe way to create valid names, this utility replaces invalid characters with
a space (' ')
+    String safeName = WorkbookUtil.createSafeSheetName("[O'Brien's sales*?]"); // returns
" O'Brien's sales   "
+    Sheet sheet3 = wb.createSheet(safeName);
+
     FileOutputStream fileOut = new FileOutputStream("workbook.xls");
     wb.write(fileOut);
     fileOut.close();
-                    </source>
+                     </source>
                 </section>
                 <anchor id="CreateCells"/>
                 <section><title>Creating Cells</title>
@@ -271,15 +288,15 @@
                 so using Java 1.5 you can simply take advantage
                 of the built in "foreach" support - see below.</p>
 				<source>
-	Sheet sheet = wb.getSheetAt(0);
-	for (Iterator&lt;Row&gt; rit = sheet.rowIterator(); rit.hasNext(); ) {
-		Row row = rit.next();
-		for (Iterator&lt;Cell&gt; cit = row.cellIterator(); cit.hasNext(); ) {
-			Cell cell = cit.next();
-			// Do something here
-		}
-	}
-				</source>
+    sheet sheet = wb.getsheetat(0);
+    for (iterator&lt;row&gt; rit = sheet.rowiterator(); rit.hasnext(); ) {
+      row row = rit.next();
+      for (iterator&lt;cell&gt; cit = row.celliterator(); cit.hasnext(); ) {
+        cell cell = cit.next();
+        // do something here
+      }
+    }
+        </source>
                 </section>
                 <section><title>Iterate over rows and cells using Java 1.5 foreach
loops</title>
 				<p>Sometimes, you'd like to just iterate over all the rows in
@@ -293,12 +310,12 @@
 				the cells, and for Sheet gives the 
 				<em>rowIterator()</em> to iterator over all the rows.</p>
 				<source>
-	Sheet sheet = wb.getSheetAt(0);
-	for (Row row : sheet) {
-		for (Cell cell : row) {
-			// Do something here
-		}
-	}
+    Sheet sheet = wb.getSheetAt(0);
+    for (Row row : sheet) {
+      for (Cell cell : row) {
+        // Do something here
+      }
+    }
 				</source>
                 </section>
 
@@ -314,37 +331,37 @@
                     in one sheet, print out the cell's reference
                     (eg A3), and then the cell's contents.</p>
 				<source>
-// import org.apache.poi.ss.usermodel.*;
+    // import org.apache.poi.ss.usermodel.*;
 
-Sheet sheet1 = wb.getSheetAt(0);
-for (Row row : sheet1) {
-	for (Cell cell : row) {
-		CellReference cellRef = new CellReference(row.getRowNum(), cell.getCellNum());
-		System.out.print(cellRef.formatAsString());
-		System.out.print(" - ");
-		
-		switch(cell.getCellType()) {
-      case Cell.CELL_TYPE_STRING:
-        System.out.println(cell.getRichStringCellValue().getString());
-        break;
-      case Cell.CELL_TYPE_NUMERIC:
-        if(DateUtil.isCellDateFormatted(cell)) {
-          System.out.println(cell.getDateCellValue());
-        } else {
-          System.out.println(cell.getNumericCellValue());
+    Sheet sheet1 = wb.getSheetAt(0);
+    for (Row row : sheet1) {
+        for (Cell cell : row) {
+            CellReference cellRef = new CellReference(row.getRowNum(), cell.getCellNum());
+            System.out.print(cellRef.formatAsString());
+            System.out.print(" - ");
+
+            switch (cell.getCellType()) {
+                case Cell.CELL_TYPE_STRING:
+                    System.out.println(cell.getRichStringCellValue().getString());
+                    break;
+                case Cell.CELL_TYPE_NUMERIC:
+                    if (DateUtil.isCellDateFormatted(cell)) {
+                        System.out.println(cell.getDateCellValue());
+                    } else {
+                        System.out.println(cell.getNumericCellValue());
+                    }
+                    break;
+                case Cell.CELL_TYPE_BOOLEAN:
+                    System.out.println(cell.getBooleanCellValue());
+                    break;
+                case Cell.CELL_TYPE_FORMULA:
+                    System.out.println(cell.getCellFormula());
+                    break;
+                default:
+                    System.out.println();
+            }
         }
-        break;
-      case Cell.CELL_TYPE_BOOLEAN:
-        System.out.println(cell.getBooleanCellValue());
-        break;
-      case Cell.CELL_TYPE_FORMULA:
-        System.out.println(cell.getCellFormula());
-        break;
-      default:
-        System.out.println();
-		}
-	}
-}
+    }
 				</source>
                 </section>
 
@@ -353,13 +370,13 @@ for (Row row : sheet1) {
 					<p>For most text extraction requirements, the standard
 					ExcelExtractor class should provide all you need.</p>
 					<source>
-	InputStream inp = new FileInputStream("workbook.xls");
-	HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(inp));
-	ExcelExtractor extractor = new ExcelExtractor(wb);
-
-	extractor.setFormulasNotResults(true);
-	extractor.setIncludeSheetNames(false);
-	String text = extractor.getText();
+    InputStream inp = new FileInputStream("workbook.xls");
+    HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(inp));
+    ExcelExtractor extractor = new ExcelExtractor(wb);
+
+    extractor.setFormulasNotResults(true);
+    extractor.setIncludeSheetNames(false);
+    String text = extractor.getText();
 					</source>
 					<p>For very fancy text extraction, XLS to CSV etc, 
 					take a look at
@@ -458,29 +475,29 @@ Examples:
 </p>
 <p><strong>Wrong:</strong></p>
 <source>
-        for (int i = 0; i &lt; 10000; i++) {
-            Row row = sheet.createRow(i);
-            Cell cell = row.createCell((short) 0);
-
-            CellStyle style = workbook.createCellStyle();
-            Font font = workbook.createFont();
-            font.setBoldweight(Font.BOLDWEIGHT_BOLD);
-            style.setFont(font);
-            cell.setCellStyle(style);
-        }
-</source>
-<p><strong>Correct:</strong></p>
-<source>
+    for (int i = 0; i &lt; 10000; i++) {
+        Row row = sheet.createRow(i);
+        Cell cell = row.createCell((short) 0);
 
         CellStyle style = workbook.createCellStyle();
         Font font = workbook.createFont();
         font.setBoldweight(Font.BOLDWEIGHT_BOLD);
         style.setFont(font);
-        for (int i = 0; i &lt; 10000; i++) {
-            Row row = sheet.createRow(i);
-            Cell cell = row.createCell((short) 0);
-            cell.setCellStyle(style);
-        }
+        cell.setCellStyle(style);
+    }
+</source>
+<p><strong>Correct:</strong></p>
+<source>
+
+    CellStyle style = workbook.createCellStyle();
+    Font font = workbook.createFont();
+    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
+    style.setFont(font);
+    for (int i = 0; i &lt; 10000; i++) {
+        Row row = sheet.createRow(i);
+        Cell cell = row.createCell((short) 0);
+        cell.setCellStyle(style);
+    }
 </source>
   
                 </section>
@@ -1390,7 +1407,7 @@ Examples:
       If your workbook has many formulas then it is a good idea to evaluate them before auto-sizing.
     </p>
         <warning>
-    To calculate column width HSSFSheet.autoSizeColumn uses Java2D classes
+    To calculate column width Sheet.autoSizeColumn uses Java2D classes
     that throw exception if graphical environment is not available. In case if graphical
environment
     is not available, you must tell Java that you are running in headless mode and
     set the following system property: <code> java.awt.headless=true </code>.
@@ -1692,7 +1709,6 @@ Examples:
     };
 
     sheetCF.addConditionalFormatting(regions, cfRules);
-    sheetCF.addConditionalFormatting(regions, cfRules);
        </source>
      </section>  
     </body>

Modified: poi/trunk/src/documentation/content/xdocs/status.xml
URL: http://svn.apache.org/viewvc/poi/trunk/src/documentation/content/xdocs/status.xml?rev=1138819&r1=1138818&r2=1138819&view=diff
==============================================================================
--- poi/trunk/src/documentation/content/xdocs/status.xml (original)
+++ poi/trunk/src/documentation/content/xdocs/status.xml Thu Jun 23 10:49:11 2011
@@ -34,6 +34,7 @@
 
     <changes>
         <release version="3.8-beta4" date="2011-??-??">
+           <action dev="poi-developers" type="fix">51415 - Fixed Workbook.createSheet(sheetName)
to truncate names longer than 31 characters</action>
            <action dev="poi-developers" type="fix">51332 - Fixed internal IDs of shapes
generated by HSSFPatriarch when there are more than 1023 drawing objects </action>
            <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>

Modified: poi/trunk/src/java/org/apache/poi/hssf/model/InternalWorkbook.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/model/InternalWorkbook.java?rev=1138819&r1=1138818&r2=1138819&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/model/InternalWorkbook.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/model/InternalWorkbook.java Thu Jun 23 10:49:11
2011
@@ -89,6 +89,7 @@ import org.apache.poi.hssf.util.HSSFColo
 import org.apache.poi.ss.formula.EvaluationWorkbook.ExternalName;
 import org.apache.poi.ss.formula.EvaluationWorkbook.ExternalSheet;
 import org.apache.poi.ss.usermodel.BuiltinFormats;
+import org.apache.poi.ss.util.WorkbookUtil;
 import org.apache.poi.util.Internal;
 import org.apache.poi.util.POILogFactory;
 import org.apache.poi.util.POILogger;
@@ -579,6 +580,10 @@ public final class InternalWorkbook {
      */
     public void setSheetName(int sheetnum, String sheetname) {
         checkSheets(sheetnum);
+
+        // YK: Mimic Excel and silently truncate sheet names longer than 31 characters
+        if(sheetname.length() > 31) sheetname = sheetname.substring(0, 31);
+
         BoundSheetRecord sheet = boundsheets.get(sheetnum);
         sheet.setSheetname(sheetname);
     }

Modified: poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java?rev=1138819&r1=1138818&r2=1138819&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java Thu Jun 23 10:49:11
2011
@@ -543,15 +543,20 @@ public final class HSSFWorkbook extends 
     }
 
     /**
-     * Sets the sheet name.
-     * Will throw IllegalArgumentException if the name is duplicated or contains /\?*[]
-     * Note - Excel allows sheet names up to 31 chars in length but other applications allow
more.
-     * Excel does not crash with names longer than 31 chars, but silently truncates such
names to
-     * 31 chars.  POI enforces uniqueness on the first 31 chars.
+     * Set the sheet name.
      *
-     * @param sheetIx number (0 based)
+     * @param sheet number (0 based)
+     * @throws IllegalArgumentException if the name is null or invalid
+     *  or workbook already contains a sheet with this name
+     * @see {@link #createSheet(String)}
+     * @see {@link org.apache.poi.ss.util.WorkbookUtil#createSafeSheetName(String nameProposal)}
+     *      for a safe way to create valid names
      */
     public void setSheetName(int sheetIx, String name) {
+        if (name == null) {
+            throw new IllegalArgumentException("sheetName must not be null");
+        }
+
         if (workbook.doesContainsSheetName(name, sheetIx)) {
             throw new IllegalArgumentException("The workbook already contains a sheet with
this name");
         }
@@ -757,20 +762,55 @@ public final class HSSFWorkbook extends 
     }
 
     /**
-     * create an HSSFSheet for this HSSFWorkbook, adds it to the sheets and
-     * returns the high level representation. Use this to create new sheets.
+     * Create a new sheet for this Workbook and return the high level representation.
+     * Use this to create new sheets.
      *
-     * @param sheetname the name for the new sheet. Note - certain length limits
-     * apply. See {@link #setSheetName(int, String)}.
-     * @see org.apache.poi.ss.util.WorkbookUtil#createSafeSheetName(String nameProposal)
-     *  for a safe way to create valid names
-     * @return HSSFSheet representing the new sheet.
-     * @throws IllegalArgumentException
-     *             if there is already a sheet present with a case-insensitive
-     *             match for the specified name.
+     * <p>
+     *     Note that Excel allows sheet names up to 31 chars in length but other applications
+     *     (such as OpenOffice) allow more. Some versions of Excel crash with names longer
than 31 chars,
+     *     others - truncate such names to 31 character.
+     * </p>
+     * <p>
+     *     POI's SpreadsheetAPI silently truncates the input argument to 31 characters.
+     *     Example:
+     *
+     *     <pre><code>
+     *     Sheet sheet = workbook.createSheet("My very long sheet name which is longer than
31 chars"); // will be truncated
+     *     assert 31 == sheet.getSheetName().length();
+     *     assert "My very long sheet name which i" == sheet.getSheetName();
+     *     </code></pre>
+     * </p>
+     *
+     * Except the 31-character constraint, Excel applies some other rules:
+     * <p>
+     * Sheet name MUST be unique in the workbook and MUST NOT contain the any of the following
characters:
+     * <ul>
+     * <li> 0x0000 </li>
+     * <li> 0x0003 </li>
+     * <li> colon (:) </li>
+     * <li> backslash (\) </li>
+     * <li> asterisk (*) </li>
+     * <li> question mark (?) </li>
+     * <li> forward slash (/) </li>
+     * <li> opening square bracket ([) </li>
+     * <li> closing square bracket (]) </li>
+     * </ul>
+     * The string MUST NOT begin or end with the single quote (') character.
+     * </p>
+     *
+     * @param sheetname  sheetname to set for the sheet.
+     * @return Sheet representing the new sheet.
+     * @throws IllegalArgumentException if the name is null or invalid
+     *  or workbook already contains a sheet with this name
+     * @see {@link org.apache.poi.ss.util.WorkbookUtil#createSafeSheetName(String nameProposal)}
+     *      for a safe way to create valid names
      */
     public HSSFSheet createSheet(String sheetname)
     {
+        if (sheetname == null) {
+            throw new IllegalArgumentException("sheetName must not be null");
+        }
+
         if (workbook.doesContainsSheetName( sheetname, _sheets.size() ))
             throw new IllegalArgumentException( "The workbook already contains a sheet of
this name" );
 

Modified: poi/trunk/src/java/org/apache/poi/ss/usermodel/Workbook.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/usermodel/Workbook.java?rev=1138819&r1=1138818&r2=1138819&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/usermodel/Workbook.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/usermodel/Workbook.java Thu Jun 23 10:49:11 2011
@@ -131,12 +131,16 @@ public interface Workbook {
      * Set the sheet name.
      *
      * @param sheet number (0 based)
-     * @throws IllegalArgumentException if the name is greater than 31 chars or contains
<code>/\?*[]</code>
+     * @throws IllegalArgumentException if the name is null or invalid
+     *  or workbook already contains a sheet with this name
+     * @see {@link #createSheet(String)}
+     * @see {@link org.apache.poi.ss.util.WorkbookUtil#createSafeSheetName(String nameProposal)}
+     *      for a safe way to create valid names
      */
     void setSheetName(int sheet, String name);
 
     /**
-     * Set the sheet name
+     * Get the sheet name
      *
      * @param sheet sheet number (0 based)
      * @return Sheet name
@@ -168,12 +172,48 @@ public interface Workbook {
     Sheet createSheet();
 
     /**
-     * Create an Sheet for this Workbook, adds it to the sheets and returns
-     * the high level representation.  Use this to create new sheets.
+     * Create a new sheet for this Workbook and return the high level representation.
+     * Use this to create new sheets.
+     *
+     * <p>
+     *     Note that Excel allows sheet names up to 31 chars in length but other applications
+     *     (such as OpenOffice) allow more. Some versions of Excel crash with names longer
than 31 chars,
+     *     others - truncate such names to 31 character.
+     * </p>
+     * <p>
+     *     POI's SpreadsheetAPI silently truncates the input argument to 31 characters.
+     *     Example:
+     *
+     *     <pre><code>
+     *     Sheet sheet = workbook.createSheet("My very long sheet name which is longer than
31 chars"); // will be truncated
+     *     assert 31 == sheet.getSheetName().length();
+     *     assert "My very long sheet name which i" == sheet.getSheetName();
+     *     </code></pre>
+     * </p>
+     *
+     * Except the 31-character constraint, Excel applies some other rules:
+     * <p>
+     * Sheet name MUST be unique in the workbook and MUST NOT contain the any of the following
characters:
+     * <ul>
+     * <li> 0x0000 </li>
+     * <li> 0x0003 </li>
+     * <li> colon (:) </li>
+     * <li> backslash (\) </li>
+     * <li> asterisk (*) </li>
+     * <li> question mark (?) </li>
+     * <li> forward slash (/) </li>
+     * <li> opening square bracket ([) </li>
+     * <li> closing square bracket (]) </li>
+     * </ul>
+     * The string MUST NOT begin or end with the single quote (') character.
+     * </p>
      *
      * @param sheetname  sheetname to set for the sheet.
      * @return Sheet representing the new sheet.
-     * @throws IllegalArgumentException if the name is greater than 31 chars or contains
<code>/\?*[]</code>
+     * @throws IllegalArgumentException if the name is null or invalid
+     *  or workbook already contains a sheet with this name
+     * @see {@link org.apache.poi.ss.util.WorkbookUtil#createSafeSheetName(String nameProposal)}
+     *      for a safe way to create valid names
      */
     Sheet createSheet(String sheetname);
 

Modified: poi/trunk/src/java/org/apache/poi/ss/util/WorkbookUtil.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/util/WorkbookUtil.java?rev=1138819&r1=1138818&r2=1138819&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/util/WorkbookUtil.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/util/WorkbookUtil.java Thu Jun 23 10:49:11 2011
@@ -104,9 +104,11 @@ public class WorkbookUtil {
             throw new IllegalArgumentException("sheetName must not be null");
         }
         int len = sheetName.length();
-        if (len < 1) {
-            throw new IllegalArgumentException("sheetName must not be empty string");
+        if (len < 1 || len > 31) {
+            throw new IllegalArgumentException("sheetName '" + sheetName
+                    + "' is invalid - character count MUST be greater than or equal to 1
and less than or equal to 31");
         }
+
         for (int i=0; i<len; i++) {
             char ch = sheetName.charAt(i);
             switch (ch) {

Modified: poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java?rev=1138819&r1=1138818&r2=1138819&view=diff
==============================================================================
--- poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java (original)
+++ poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java Thu Jun 23 10:49:11
2011
@@ -494,17 +494,61 @@ public class XSSFWorkbook extends POIXML
     }
 
     /**
-     * Create an XSSFSheet for this workbook, adds it to the sheets and returns
-     * the high level representation.  Use this to create new sheets.
+     * Create a new sheet for this Workbook and return the high level representation.
+     * Use this to create new sheets.
      *
-     * @param sheetname  sheetname to set for the sheet, can't be duplicate, greater than
31 chars or contain /\?*[]
-     * @return XSSFSheet representing the new sheet.
-     * @throws IllegalArgumentException if the sheetname is invalid or the workbook already
contains a sheet of this name
+     * <p>
+     *     Note that Excel allows sheet names up to 31 chars in length but other applications
+     *     (such as OpenOffice) allow more. Some versions of Excel crash with names longer
than 31 chars,
+     *     others - truncate such names to 31 character.
+     * </p>
+     * <p>
+     *     POI's SpreadsheetAPI silently truncates the input argument to 31 characters.
+     *     Example:
+     *
+     *     <pre><code>
+     *     Sheet sheet = workbook.createSheet("My very long sheet name which is longer than
31 chars"); // will be truncated
+     *     assert 31 == sheet.getSheetName().length();
+     *     assert "My very long sheet name which i" == sheet.getSheetName();
+     *     </code></pre>
+     * </p>
+     *
+     * Except the 31-character constraint, Excel applies some other rules:
+     * <p>
+     * Sheet name MUST be unique in the workbook and MUST NOT contain the any of the following
characters:
+     * <ul>
+     * <li> 0x0000 </li>
+     * <li> 0x0003 </li>
+     * <li> colon (:) </li>
+     * <li> backslash (\) </li>
+     * <li> asterisk (*) </li>
+     * <li> question mark (?) </li>
+     * <li> forward slash (/) </li>
+     * <li> opening square bracket ([) </li>
+     * <li> closing square bracket (]) </li>
+     * </ul>
+     * The string MUST NOT begin or end with the single quote (') character.
+     * </p>
+     *
+     * @param sheetname  sheetname to set for the sheet.
+     * @return Sheet representing the new sheet.
+     * @throws IllegalArgumentException if the name is null or invalid
+     *  or workbook already contains a sheet with this name
+     * @see {@link org.apache.poi.ss.util.WorkbookUtil#createSafeSheetName(String nameProposal)}
+     *      for a safe way to create valid names
      */
     public XSSFSheet createSheet(String sheetname) {
+        if (sheetname == null) {
+            throw new IllegalArgumentException("sheetName must not be null");
+        }
+
         if (containsSheet( sheetname, sheets.size() ))
                throw new IllegalArgumentException( "The workbook already contains a sheet
of this name");
 
+        // YK: Mimic Excel and silently truncate sheet names longer than 31 characters
+        if(sheetname.length() > 31) sheetname = sheetname.substring(0, 31);
+        WorkbookUtil.validateSheetName(sheetname);
+
         CTSheet sheet = addSheet(sheetname);
 
         int sheetNumber = 1;
@@ -525,8 +569,6 @@ public class XSSFWorkbook extends POIXML
     }
 
     private CTSheet addSheet(String sheetname) {
-        WorkbookUtil.validateSheetName(sheetname);
-
         CTSheet sheet = workbook.getSheets().addNewSheet();
         sheet.setName(sheetname);
         return sheet;
@@ -1115,21 +1157,29 @@ public class XSSFWorkbook extends POIXML
 
     /**
      * Set the sheet name.
-     * Will throw IllegalArgumentException if the name is greater than 31 chars
-     * or contains /\?*[]
      *
-     * @param sheetIndex number (0 based)
+     * @param sheetIndex sheet number (0 based)
+     * @param sheetname  the new sheet name
+     * @throws IllegalArgumentException if the name is null or invalid
+     *  or workbook already contains a sheet with this name
+     * @see {@link #createSheet(String)}
+     * @see {@link org.apache.poi.ss.util.WorkbookUtil#createSafeSheetName(String nameProposal)}
+     *      for a safe way to create valid names
      */
-    public void setSheetName(int sheetIndex, String name) {
+    public void setSheetName(int sheetIndex, String sheetname) {
         validateSheetIndex(sheetIndex);
-        WorkbookUtil.validateSheetName(name);
-        if (containsSheet(name, sheetIndex ))
+
+        // YK: Mimic Excel and silently truncate sheet names longer than 31 characters
+        if(sheetname != null && sheetname.length() > 31) sheetname = sheetname.substring(0,
31);
+        WorkbookUtil.validateSheetName(sheetname);
+
+        if (containsSheet(sheetname, sheetIndex ))
             throw new IllegalArgumentException( "The workbook already contains a sheet of
this name" );
 
         XSSFFormulaUtils utils = new XSSFFormulaUtils(this);
-        utils.updateSheetName(sheetIndex, name);
+        utils.updateSheetName(sheetIndex, sheetname);
 
-        workbook.getSheets().getSheetArray(sheetIndex).setName(name);
+        workbook.getSheets().getSheetArray(sheetIndex).setName(sheetname);
     }
 
     /**

Modified: poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestWorkbook.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestWorkbook.java?rev=1138819&r1=1138818&r2=1138819&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestWorkbook.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestWorkbook.java Thu Jun 23 10:49:11
2011
@@ -99,6 +99,22 @@ public abstract class BaseTestWorkbook e
             // expected during successful test
         }
 
+        //try to assign an invalid name to the 2nd sheet
+        try {
+            wb.createSheet(null);
+            fail("should have thrown exceptiuon due to invalid sheet name");
+        } catch (IllegalArgumentException e) {
+            // expected during successful test
+        }
+
+        try {
+            wb.setSheetName(2, null);
+
+            fail("should have thrown exceptiuon due to invalid sheet name");
+        } catch (IllegalArgumentException e) {
+            // expected during successful test
+        }
+
         //check
         assertEquals(0, wb.getSheetIndex("sheet0"));
         assertEquals(1, wb.getSheetIndex("sheet1"));
@@ -129,9 +145,14 @@ public abstract class BaseTestWorkbook e
         Workbook wb = _testDataProvider.createWorkbook();
 
         String sheetName1 = "My very long sheet name which is longer than 31 chars";
+        String truncatedSheetName1 = sheetName1.substring(0, 31);
         Sheet sh1 = wb.createSheet(sheetName1);
-        assertEquals(sheetName1, sh1.getSheetName());
-        assertSame(sh1, wb.getSheet(sheetName1));
+        assertEquals(truncatedSheetName1, sh1.getSheetName());
+        assertSame(sh1, wb.getSheet(truncatedSheetName1));
+        // now via wb.setSheetName
+        wb.setSheetName(0, sheetName1);
+        assertEquals(truncatedSheetName1, sh1.getSheetName());
+        assertSame(sh1, wb.getSheet(truncatedSheetName1));
 
         String sheetName2 = "My very long sheet name which is longer than 31 chars " +
                 "and sheetName2.substring(0, 31) == sheetName1.substring(0, 31)";
@@ -144,15 +165,16 @@ public abstract class BaseTestWorkbook e
         }
 
         String sheetName3 = "POI allows creating sheets with names longer than 31 characters";
+        String truncatedSheetName3 = sheetName3.substring(0, 31);
         Sheet sh3 = wb.createSheet(sheetName3);
-        assertEquals(sheetName3, sh3.getSheetName());
-        assertSame(sh3, wb.getSheet(sheetName3));
+        assertEquals(truncatedSheetName3, sh3.getSheetName());
+        assertSame(sh3, wb.getSheet(truncatedSheetName3));
 
         //serialize and read again
         wb = _testDataProvider.writeOutAndReadBack(wb);
         assertEquals(2, wb.getNumberOfSheets());
-        assertEquals(0, wb.getSheetIndex(sheetName1));
-        assertEquals(1, wb.getSheetIndex(sheetName3));
+        assertEquals(0, wb.getSheetIndex(truncatedSheetName1));
+        assertEquals(1, wb.getSheetIndex(truncatedSheetName3));
     }
 
     public void testRemoveSheetAt() {



---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org


Mime
View raw message