From ba5f6a5cf3c995d0896b1d2c8a4937f6cd9128aa Mon Sep 17 00:00:00 2001 From: Nick Burch Date: Wed, 4 Aug 2010 14:13:42 +0000 Subject: [PATCH] Add patch from bug #49690 - Add WorkbookUtil, which provies a way of generating valid sheet names git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@982260 13f79535-47bb-0310-9956-ffa450edef68 --- src/documentation/content/xdocs/status.xml | 1 + .../poi/hssf/usermodel/examples/NewSheet.java | 4 +- .../poi/hssf/record/BoundSheetRecord.java | 2 + .../poi/hssf/usermodel/HSSFWorkbook.java | 3 +- .../org/apache/poi/ss/util/WorkbookUtil.java | 77 +++++++++++++++++ .../apache/poi/ss/util/TestWorkbookUtil.java | 84 +++++++++++++++++++ 6 files changed, 169 insertions(+), 2 deletions(-) create mode 100644 src/java/org/apache/poi/ss/util/WorkbookUtil.java create mode 100644 src/testcases/org/apache/poi/ss/util/TestWorkbookUtil.java diff --git a/src/documentation/content/xdocs/status.xml b/src/documentation/content/xdocs/status.xml index b554d8cea5..effa0239f0 100644 --- a/src/documentation/content/xdocs/status.xml +++ b/src/documentation/content/xdocs/status.xml @@ -34,6 +34,7 @@ + 49690 - Add WorkbookUtil, which provies a way of generating valid sheet names 49694 - Use DataFormatter when autosizing columns, to better match the real display width of formatted cells 49441 - Allow overriding and guessing of HSMF non-unicode string encodings 49689 - Allow the setting of user style names on newly created HSSF cell styles diff --git a/src/examples/src/org/apache/poi/hssf/usermodel/examples/NewSheet.java b/src/examples/src/org/apache/poi/hssf/usermodel/examples/NewSheet.java index 5fdf2dd30d..40754feec4 100644 --- a/src/examples/src/org/apache/poi/hssf/usermodel/examples/NewSheet.java +++ b/src/examples/src/org/apache/poi/hssf/usermodel/examples/NewSheet.java @@ -19,6 +19,7 @@ package org.apache.poi.hssf.usermodel.examples; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.usermodel.HSSFSheet; +import org.apache.poi.ss.util.WorkbookUtil; import java.io.IOException; import java.io.FileOutputStream; @@ -33,7 +34,8 @@ public class NewSheet { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet1 = wb.createSheet("new sheet"); HSSFSheet sheet2 = wb.createSheet(); // create with default name - wb.setSheetName(1, "second sheet"); // setting sheet name later + final String name = "second sheet"; + wb.setSheetName(1, WorkbookUtil.createSafeSheetName(name)); // setting sheet name later FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut); fileOut.close(); diff --git a/src/java/org/apache/poi/hssf/record/BoundSheetRecord.java b/src/java/org/apache/poi/hssf/record/BoundSheetRecord.java index b8b6d72ca3..7f73a1dd18 100644 --- a/src/java/org/apache/poi/hssf/record/BoundSheetRecord.java +++ b/src/java/org/apache/poi/hssf/record/BoundSheetRecord.java @@ -84,6 +84,8 @@ public final class BoundSheetRecord extends StandardRecord { /** * Set the sheetname for this sheet. (this appears in the tabs at the bottom) * @param sheetName the name of the sheet + * @see {@link org.apache.poi.ss.util.WorkbookUtil#createSafeSheetName(String nameProposal)} + * for a safe way to create valid names * @throws IllegalArgumentException if sheet name will cause excel to crash. */ public void setSheetname(String sheetName) { diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java b/src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java index de1cb50550..4d5e752ad3 100644 --- a/src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java +++ b/src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java @@ -725,7 +725,8 @@ public final class HSSFWorkbook extends POIDocument implements org.apache.poi.ss * * @param sheetname the name for the new sheet. Note - certain length limits * apply. See {@link #setSheetName(int, String)}. - * + * @see {@link 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 diff --git a/src/java/org/apache/poi/ss/util/WorkbookUtil.java b/src/java/org/apache/poi/ss/util/WorkbookUtil.java new file mode 100644 index 0000000000..11e01a04b1 --- /dev/null +++ b/src/java/org/apache/poi/ss/util/WorkbookUtil.java @@ -0,0 +1,77 @@ +/* ==================================================================== + Licensed to the Apache Software Foundation (ASF) under one or more + contributor license agreements. See the NOTICE file distributed with + this work for additional information regarding copyright ownership. + The ASF licenses this file to You under the Apache License, Version 2.0 + (the "License"); you may not use this file except in compliance with + the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. +==================================================================== */ + +package org.apache.poi.ss.util; + + +/** + * Helper methods for when working with Usermodel Workbooks + */ +public class WorkbookUtil { + + /** + * Creates a valid sheet name, which is conform to the rules. + * In any case, the result safely can be used for + * {@link org.apache.poi.hssf.usermodel.HSSFWorkbook#setSheetName(int, String)}. + *
+ * Rules: + *
    + *
  • never null
    • + *
  • minimum length is 1
    • + *
  • maximum length is 31
    • + *
  • doesn't contain special chars: / \ ? * ] [
    • + *
  • Sheet names must not begin or end with ' (apostrophe)
    • + *
+ * Invalid characters are replaced by one space character ' '. + * + * @param nameProposal can be any string, will be truncated if necessary, + * allowed to be null + * @return a valid string, "empty" if to short, "null" if null + */ + public final static String createSafeSheetName(final String nameProposal) { + if (nameProposal == null) { + return "null"; + } + if (nameProposal.length() < 1) { + return "empty"; + } + final int length = Math.min(31, nameProposal.length()); + final String shortenname = nameProposal.substring(0, length); + final StringBuilder result = new StringBuilder(shortenname); + for (int i=0; i