View | Details | Raw Unified | Return to bug 49690
Collapse All | Expand All

(-)src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java (-1 / +2 lines)
Lines 725-731 Link Here
725
     *
725
     *
726
     * @param sheetname the name for the new sheet. Note - certain length limits
726
     * @param sheetname the name for the new sheet. Note - certain length limits
727
     * apply. See {@link #setSheetName(int, String)}.
727
     * apply. See {@link #setSheetName(int, String)}.
728
     *
728
     * @see {@link org.apache.poi.hssf.util.WorkbookUtil#createSafeSheetName(String nameProposal)}
729
	 *      for a safe way to create valid names
729
     * @return HSSFSheet representing the new sheet.
730
     * @return HSSFSheet representing the new sheet.
730
     * @throws IllegalArgumentException
731
     * @throws IllegalArgumentException
731
     *             if there is already a sheet present with a case-insensitive
732
     *             if there is already a sheet present with a case-insensitive
(-)src/examples/src/org/apache/poi/hssf/usermodel/examples/NewSheet.java (-1 / +3 lines)
Lines 19-24 Link Here
19
19
20
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
20
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
21
import org.apache.poi.hssf.usermodel.HSSFSheet;
21
import org.apache.poi.hssf.usermodel.HSSFSheet;
22
import org.apache.poi.hssf.util.WorkbookUtil;
22
23
23
import java.io.IOException;
24
import java.io.IOException;
24
import java.io.FileOutputStream;
25
import java.io.FileOutputStream;
Lines 33-39 Link Here
33
        HSSFWorkbook wb = new HSSFWorkbook();
34
        HSSFWorkbook wb = new HSSFWorkbook();
34
        HSSFSheet sheet1 = wb.createSheet("new sheet");
35
        HSSFSheet sheet1 = wb.createSheet("new sheet");
35
        HSSFSheet sheet2 = wb.createSheet(); // create with default name
36
        HSSFSheet sheet2 = wb.createSheet(); // create with default name
36
        wb.setSheetName(1, "second sheet"); // setting sheet name later
37
        final String name = "second sheet";
38
		wb.setSheetName(1, WorkbookUtil.createSafeSheetName(name)); // setting sheet name later
37
        FileOutputStream fileOut = new FileOutputStream("workbook.xls");
39
        FileOutputStream fileOut = new FileOutputStream("workbook.xls");
38
        wb.write(fileOut);
40
        wb.write(fileOut);
39
        fileOut.close();
41
        fileOut.close();
(-)src/java/org/apache/poi/hssf/util/WorkbookUtil.java (+78 lines)
Line 0 Link Here
1
/* ====================================================================
2
   Licensed to the Apache Software Foundation (ASF) under one or more
3
   contributor license agreements.  See the NOTICE file distributed with
4
   this work for additional information regarding copyright ownership.
5
   The ASF licenses this file to You under the Apache License, Version 2.0
6
   (the "License"); you may not use this file except in compliance with
7
   the License.  You may obtain a copy of the License at
8
9
       http://www.apache.org/licenses/LICENSE-2.0
10
11
   Unless required by applicable law or agreed to in writing, software
12
   distributed under the License is distributed on an "AS IS" BASIS,
13
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
14
   See the License for the specific language governing permissions and
15
   limitations under the License.
16
==================================================================== */
17
18
package org.apache.poi.hssf.util;
19
20
21
/**
22
 * @author Martin W. Kirst (maki at bitkings de)
23
 */
24
public class WorkbookUtil {
25
	
26
	/**
27
	 * Creates a valid sheet name, which is conform to the rules.
28
	 * In any case, the result safely can be used for 
29
	 * {@link org.apache.poi.hssf.usermodel.HSSFWorkbook#setSheetName(int, String)}.
30
	 * <br>
31
	 * Rules:
32
	 * <ul>
33
	 * <li>never null</li>
34
	 * <li>minimum length is 1</li>
35
	 * <li>maximum length is 31</li>
36
	 * <li>doesn't contain special chars: / \ ? * ] [ </li>
37
	 * <li>Sheet names must not begin or end with ' (apostrophe)</li>
38
	 * </ul>
39
	 * Invalid characters are replaced by one space character ' '.
40
	 * 
41
	 * @param nameProposal can be any string, will be truncated if necessary,
42
	 *        allowed to be null
43
	 * @return a valid string, "empty" if to short, "null" if null         
44
	 */
45
	public final static String createSafeSheetName(final String nameProposal) {
46
		if (nameProposal == null) {
47
			return "null";
48
		}
49
		if (nameProposal.length() < 1) {
50
			return "empty";
51
		}
52
		final int length = Math.min(31, nameProposal.length());
53
		final String shortenname = nameProposal.substring(0, length);
54
		final StringBuilder result = new StringBuilder(shortenname);
55
		for (int i=0; i<length; i++) {
56
			char ch = result.charAt(i);
57
			switch (ch) {
58
				case '/':
59
				case '\\':
60
				case '?':
61
				case '*':
62
				case ']':
63
				case '[':
64
					result.setCharAt(i, ' ');
65
					break;
66
				case '\'':
67
					if (i==0 || i==length-1) {
68
						result.setCharAt(i, ' ');
69
					}
70
					break;
71
				default:
72
					// all other chars OK
73
			}
74
		}
75
		return result.toString();
76
	}
77
78
}
(-)src/testcases/org/apache/poi/hssf/util/TestWorkbookUtil.java (+88 lines)
Line 0 Link Here
1
/* ====================================================================
2
   Licensed to the Apache Software Foundation (ASF) under one or more
3
   contributor license agreements.  See the NOTICE file distributed with
4
   this work for additional information regarding copyright ownership.
5
   The ASF licenses this file to You under the Apache License, Version 2.0
6
   (the "License"); you may not use this file except in compliance with
7
   the License.  You may obtain a copy of the License at
8
9
       http://www.apache.org/licenses/LICENSE-2.0
10
11
   Unless required by applicable law or agreed to in writing, software
12
   distributed under the License is distributed on an "AS IS" BASIS,
13
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
14
   See the License for the specific language governing permissions and
15
   limitations under the License.
16
==================================================================== */
17
18
package org.apache.poi.hssf.util;
19
20
import junit.framework.TestCase;
21
22
/**
23
 * Tests WorkbookUtil.
24
 *
25
 * @see org.apache.poi.hssf.util.WorkbookUtil
26
 *
27
 * @author Martin W. Kirst (maki at bitkings de)
28
 */
29
public final class TestWorkbookUtil extends TestCase {
30
31
32
	/**
33
	 * borrowed test cases from 
34
	 * {@link org.apache.poi.hssf.record.TestBoundSheetRecord#testValidNames()}
35
	 */
36
	public void testCreateSafeNames() {
37
		
38
		String p = "Sheet1";
39
		String actual = WorkbookUtil.createSafeSheetName(p);
40
		assertEquals(p, actual);
41
		
42
		p = "O'Brien's sales";
43
		actual = WorkbookUtil.createSafeSheetName(p);
44
		assertEquals(p, actual);
45
		
46
		p = " data # ";
47
		actual = WorkbookUtil.createSafeSheetName(p);
48
		assertEquals(p, actual);
49
		
50
		p = "data $1.00";
51
		actual = WorkbookUtil.createSafeSheetName(p);
52
		assertEquals(p, actual);
53
		
54
		// now the replaced versions ...
55
		actual = WorkbookUtil.createSafeSheetName("data?");
56
		assertEquals("data ", actual);
57
		
58
		actual = WorkbookUtil.createSafeSheetName("abc/def");
59
		assertEquals("abc def", actual);
60
		
61
		actual = WorkbookUtil.createSafeSheetName("data[0]");
62
		assertEquals("data 0 ", actual);
63
		
64
		actual = WorkbookUtil.createSafeSheetName("data*");
65
		assertEquals("data ", actual);
66
		
67
		actual = WorkbookUtil.createSafeSheetName("abc\\def");
68
		assertEquals("abc def", actual);
69
		
70
		actual = WorkbookUtil.createSafeSheetName("'data");
71
		assertEquals(" data", actual);
72
		
73
		actual = WorkbookUtil.createSafeSheetName("data'");
74
		assertEquals("data ", actual);
75
		
76
		actual = WorkbookUtil.createSafeSheetName("d'at'a");
77
		assertEquals("d'at'a", actual);
78
		
79
		actual = WorkbookUtil.createSafeSheetName(null);
80
		assertEquals("null", actual);
81
		
82
		actual = WorkbookUtil.createSafeSheetName("");
83
		assertEquals("empty", actual);
84
		
85
		actual = WorkbookUtil.createSafeSheetName("1234567890123456789012345678901TOOLONG");
86
		assertEquals("1234567890123456789012345678901", actual);
87
	}
88
}
(-)src/java/org/apache/poi/hssf/record/BoundSheetRecord.java (+2 lines)
Lines 84-89 Link Here
84
	/**
84
	/**
85
	 * Set the sheetname for this sheet.  (this appears in the tabs at the bottom)
85
	 * Set the sheetname for this sheet.  (this appears in the tabs at the bottom)
86
	 * @param sheetName the name of the sheet
86
	 * @param sheetName the name of the sheet
87
	 * @see {@link org.apache.poi.hssf.util.WorkbookUtil#createSafeSheetName(String nameProposal)}
88
	 *      for a safe way to create valid names
87
	 * @throws IllegalArgumentException if sheet name will cause excel to crash.
89
	 * @throws IllegalArgumentException if sheet name will cause excel to crash.
88
	 */
90
	 */
89
	public void setSheetname(String sheetName) {
91
	public void setSheetname(String sheetName) {

Return to bug 49690