You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by ni...@apache.org on 2008/02/01 14:02:15 UTC
svn commit: r617491 - in /poi/trunk: ./ src/documentation/content/xdocs/
src/java/org/apache/poi/hssf/record/formula/
src/scratchpad/testcases/org/apache/poi/hssf/usermodel/
src/testcases/org/apache/poi/hssf/
src/testcases/org/apache/poi/hssf/record/fo...
Author: nick
Date: Fri Feb 1 05:02:06 2008
New Revision: 617491
URL: http://svn.apache.org/viewvc?rev=617491&view=rev
Log:
Patch from bug #44336 - correctly escape sheet names in formula references, including tests for this, and fixes to old tests that were expecting the un-escaped sheet names
Added:
poi/trunk/src/java/org/apache/poi/hssf/record/formula/SheetNameFormatter.java (with props)
poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/AllFormulaTests.java (with props)
poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/TestArea3DPtg.java (with props)
poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/TestRef3DPtg.java (with props)
poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/TestSheetNameFormatter.java (with props)
Modified:
poi/trunk/build.xml
poi/trunk/src/documentation/content/xdocs/changes.xml
poi/trunk/src/documentation/content/xdocs/status.xml
poi/trunk/src/java/org/apache/poi/hssf/record/formula/Area3DPtg.java
poi/trunk/src/java/org/apache/poi/hssf/record/formula/Ref3DPtg.java
poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/usermodel/TestFormulaEvaluatorDocs.java
poi/trunk/src/testcases/org/apache/poi/hssf/HSSFTests.java
poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/AbstractPtgTestCase.java
poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/TestFuncPtg.java (props changed)
poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestNamedRange.java
Modified: poi/trunk/build.xml
URL: http://svn.apache.org/viewvc/poi/trunk/build.xml?rev=617491&r1=617490&r2=617491&view=diff
==============================================================================
--- poi/trunk/build.xml (original)
+++ poi/trunk/build.xml Fri Feb 1 05:02:06 2008
@@ -259,6 +259,7 @@
<antcall target="with.clover"/>
<mkdir dir="build"/>
+ <mkdir dir="build/non-ant-classes"/>
<mkdir dir="${main.output.dir}"/>
<mkdir dir="${scratchpad.output.dir}"/>
<mkdir dir="${contrib.output.dir}"/>
Modified: poi/trunk/src/documentation/content/xdocs/changes.xml
URL: http://svn.apache.org/viewvc/poi/trunk/src/documentation/content/xdocs/changes.xml?rev=617491&r1=617490&r2=617491&view=diff
==============================================================================
--- poi/trunk/src/documentation/content/xdocs/changes.xml (original)
+++ poi/trunk/src/documentation/content/xdocs/changes.xml Fri Feb 1 05:02:06 2008
@@ -36,6 +36,7 @@
<!-- Don't forget to update status.xml too! -->
<release version="3.1-beta1" date="2008-??-??">
+ <action dev="POI-DEVELOPERS" type="fix">44336 - Properly escape sheet names as required when figuring out the text of formulas</action>
<action dev="POI-DEVELOPERS" type="add">44326 - Improvements to how SystemOutLogger and CommonsLogger log messages with exceptions, and avoid an infinite loop with certain log messages with exceptions</action>
<action dev="POI-DEVELOPERS" type="add">Support for a completed Record based "pull" stream, via org.apache.poi.hssf.eventusermodel.HSSFRecordStream, to complement the existing "push" Event User Model listener stuff</action>
</release>
Modified: poi/trunk/src/documentation/content/xdocs/status.xml
URL: http://svn.apache.org/viewvc/poi/trunk/src/documentation/content/xdocs/status.xml?rev=617491&r1=617490&r2=617491&view=diff
==============================================================================
--- poi/trunk/src/documentation/content/xdocs/status.xml (original)
+++ poi/trunk/src/documentation/content/xdocs/status.xml Fri Feb 1 05:02:06 2008
@@ -33,6 +33,7 @@
<!-- Don't forget to update changes.xml too! -->
<changes>
<release version="3.1-beta1" date="2008-??-??">
+ <action dev="POI-DEVELOPERS" type="fix">44336 - Properly escape sheet names as required when figuring out the text of formulas</action>
<action dev="POI-DEVELOPERS" type="add">44326 - Improvements to how SystemOutLogger and CommonsLogger log messages with exceptions, and avoid an infinite loop with certain log messages with exceptions</action>
<action dev="POI-DEVELOPERS" type="add">Support for a completed Record based "pull" stream, via org.apache.poi.hssf.eventusermodel.HSSFRecordStream, to complement the existing "push" Event User Model listener stuff</action>
</release>
Modified: poi/trunk/src/java/org/apache/poi/hssf/record/formula/Area3DPtg.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/record/formula/Area3DPtg.java?rev=617491&r1=617490&r2=617491&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/record/formula/Area3DPtg.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/record/formula/Area3DPtg.java Fri Feb 1 05:02:06 2008
@@ -261,13 +261,16 @@
setLastRowRelative( !lastCell.isRowAbsolute() );
}
+ /**
+ * @return text representation of this area reference that can be used in text
+ * formulas. The sheet name will get properly delimited if required.
+ */
public String toFormulaString(Workbook book)
{
- SheetReferences refs = book == null ? null : book.getSheetReferences();
StringBuffer retval = new StringBuffer();
- if ( refs != null )
- {
- retval.append( refs.getSheetName( this.field_1_index_extern_sheet ) );
+ String sheetName = Ref3DPtg.getSheetName(book, field_1_index_extern_sheet);
+ if(sheetName != null) {
+ SheetNameFormatter.appendFormat(retval, sheetName);
retval.append( '!' );
}
retval.append( ( new CellReference( getFirstRow(), getFirstColumn(), !isFirstRowRelative(), !isFirstColRelative() ) ).toString() );
Modified: poi/trunk/src/java/org/apache/poi/hssf/record/formula/Ref3DPtg.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/record/formula/Ref3DPtg.java?rev=617491&r1=617490&r2=617491&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/record/formula/Ref3DPtg.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/record/formula/Ref3DPtg.java Fri Feb 1 05:02:06 2008
@@ -157,18 +157,31 @@
}
- public String toFormulaString(Workbook book) {
+ // TODO - find a home for this method
+ // There is already a method on Workbook called getSheetName but it seems to do something different.
+ static String getSheetName(Workbook book, int externSheetIndex) {
+ // TODO - there are 3 ways this method can return null. Is each valid?
+ if (book == null) {
+ return null;
+ }
+
+ SheetReferences refs = book.getSheetReferences();
+ if (refs == null) {
+ return null;
+ }
+ return refs.getSheetName(externSheetIndex);
+ }
+ /**
+ * @return text representation of this cell reference that can be used in text
+ * formulas. The sheet name will get properly delimited if required.
+ */
+ public String toFormulaString(Workbook book)
+ {
StringBuffer retval = new StringBuffer();
- SheetReferences refs = book == null ? null : book.getSheetReferences();
- if (refs != null) {
- String sheetName =refs.getSheetName((int)this.field_1_index_extern_sheet);
- boolean appendQuotes = sheetName.indexOf(" ") >= 0;
- if (appendQuotes)
- retval.append("'");
- retval.append(sheetName);
- if (appendQuotes)
- retval.append("'");
- retval.append('!');
+ String sheetName = getSheetName(book, field_1_index_extern_sheet);
+ if(sheetName != null) {
+ SheetNameFormatter.appendFormat(retval, sheetName);
+ retval.append( '!' );
}
retval.append((new CellReference(getRow(),getColumn(),!isRowRelative(),!isColRelative())).toString());
return retval.toString();
Added: poi/trunk/src/java/org/apache/poi/hssf/record/formula/SheetNameFormatter.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/record/formula/SheetNameFormatter.java?rev=617491&view=auto
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/record/formula/SheetNameFormatter.java (added)
+++ poi/trunk/src/java/org/apache/poi/hssf/record/formula/SheetNameFormatter.java Fri Feb 1 05:02:06 2008
@@ -0,0 +1,245 @@
+/* ====================================================================
+ 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.hssf.record.formula;
+
+import java.util.regex.Matcher;
+import java.util.regex.Pattern;
+
+/**
+ * Formats sheet names for use in formula expressions.
+ *
+ * @author Josh Micich
+ */
+final class SheetNameFormatter {
+
+ private static final String BIFF8_LAST_COLUMN = "IV";
+ private static final int BIFF8_LAST_COLUMN_TEXT_LEN = BIFF8_LAST_COLUMN.length();
+ private static final String BIFF8_LAST_ROW = String.valueOf(0x10000);
+ private static final int BIFF8_LAST_ROW_TEXT_LEN = BIFF8_LAST_ROW.length();
+
+ private static final char DELIMITER = '\'';
+
+ private static final Pattern CELL_REF_PATTERN = Pattern.compile("([A-Za-z])+[0-9]+");
+
+ private SheetNameFormatter() {
+ // no instances of this class
+ }
+ /**
+ * Used to format sheet names as they would appear in cell formula expressions.
+ * @return the sheet name unchanged if there is no need for delimiting. Otherwise the sheet
+ * name is enclosed in single quotes ('). Any single quotes which were already present in the
+ * sheet name will be converted to double single quotes ('').
+ */
+ public static String format(String rawSheetName) {
+ StringBuffer sb = new StringBuffer(rawSheetName.length() + 2);
+ appendFormat(sb, rawSheetName);
+ return sb.toString();
+ }
+
+ /**
+ * Convenience method for when a StringBuffer is already available
+ *
+ * @param out - sheet name will be appended here possibly with delimiting quotes
+ */
+ public static void appendFormat(StringBuffer out, String rawSheetName) {
+ boolean needsQuotes = needsDelimiting(rawSheetName);
+ if(needsQuotes) {
+ out.append(DELIMITER);
+ appendAndEscape(out, rawSheetName);
+ out.append(DELIMITER);
+ } else {
+ out.append(rawSheetName);
+ }
+ }
+
+ private static void appendAndEscape(StringBuffer sb, String rawSheetName) {
+ int len = rawSheetName.length();
+ for(int i=0; i<len; i++) {
+ char ch = rawSheetName.charAt(i);
+ if(ch == DELIMITER) {
+ // single quotes (') are encoded as ('')
+ sb.append(DELIMITER);
+ }
+ sb.append(ch);
+ }
+ }
+
+ private static boolean needsDelimiting(String rawSheetName) {
+ int len = rawSheetName.length();
+ if(len < 1) {
+ throw new RuntimeException("Zero length string is an invalid sheet name");
+ }
+ if(Character.isDigit(rawSheetName.charAt(0))) {
+ // sheet name with digit in the first position always requires delimiting
+ return true;
+ }
+ for(int i=0; i<len; i++) {
+ char ch = rawSheetName.charAt(i);
+ if(isSpecialChar(ch)) {
+ return true;
+ }
+ }
+ if(Character.isLetter(rawSheetName.charAt(0))
+ && Character.isDigit(rawSheetName.charAt(len-1))) {
+ // note - values like "A$1:$C$20" don't get this far
+ if(nameLooksLikePlainCellReference(rawSheetName)) {
+ return true;
+ }
+ }
+ return false;
+ }
+
+ /**
+ * @return <code>true</code> if the presence of the specified character in a sheet name would
+ * require the sheet name to be delimited in formulas. This includes every non-alphanumeric
+ * character besides underscore '_'.
+ */
+ /* package */ static boolean isSpecialChar(char ch) {
+ // note - Character.isJavaIdentifierPart() would allow dollars '$'
+ if(Character.isLetterOrDigit(ch)) {
+ return false;
+ }
+ switch(ch) {
+ case '_': // underscore is ok
+ return false;
+ case '\n':
+ case '\r':
+ case '\t':
+ throw new RuntimeException("Illegal character (0x"
+ + Integer.toHexString(ch) + ") found in sheet name");
+ }
+ return true;
+ }
+
+
+ /**
+ * Used to decide whether sheet names like 'AB123' need delimiting due to the fact that they
+ * look like cell references.
+ * <p/>
+ * This code is currently being used for translating formulas represented with <code>Ptg</code>
+ * tokens into human readable text form. In formula expressions, a sheet name always has a
+ * trailing '!' so there is little chance for ambiguity. It doesn't matter too much what this
+ * method returns but it is worth noting the likely consumers of these formula text strings:
+ * <ol>
+ * <li>POI's own formula parser</li>
+ * <li>Visual reading by human</li>
+ * <li>VBA automation entry into Excel cell contents e.g. ActiveCell.Formula = "=c64!A1"</li>
+ * <li>Manual entry into Excel cell contents</li>
+ * <li>Some third party formula parser</li>
+ * </ol>
+ *
+ * At the time of writing, POI's formula parser tolerates cell-like sheet names in formulas
+ * with or without delimiters. The same goes for Excel(2007), both manual and automated entry.
+ * <p/>
+ * For better or worse this implementation attempts to replicate Excel's formula renderer.
+ * Excel uses range checking on the apparent 'row' and 'column' components. Note however that
+ * the maximum sheet size varies across versions:
+ * <p/>
+ * <blockquote><table border="0" cellpadding="1" cellspacing="0"
+ * summary="Notable cases.">
+ * <tr><th>Version </th><th>File Format </th>
+ * <th>Last Column </th><th>Last Row</th></tr>
+ * <tr><td>97-2003</td><td>BIFF8</td><td>"IV" (2^8)</td><td>65536 (2^14)</td></tr>
+ * <tr><td>2007</td><td>BIFF12</td><td>"XFD" (2^14)</td><td>1048576 (2^20)</td></tr>
+ * </table></blockquote>
+ * POI currently targets BIFF8 (Excel 97-2003), so the following behaviour can be observed for
+ * this method:
+ * <blockquote><table border="0" cellpadding="1" cellspacing="0"
+ * summary="Notable cases.">
+ * <tr><th>Input </th>
+ * <th>Result </th></tr>
+ * <tr><td>"A1", 1</td><td>true</td></tr>
+ * <tr><td>"a111", 1</td><td>true</td></tr>
+ * <tr><td>"A65536", 1</td><td>true</td></tr>
+ * <tr><td>"A65537", 1</td><td>false</td></tr>
+ * <tr><td>"iv1", 2</td><td>true</td></tr>
+ * <tr><td>"IW1", 2</td><td>false</td></tr>
+ * <tr><td>"AAA1", 3</td><td>false</td></tr>
+ * <tr><td>"a111", 1</td><td>true</td></tr>
+ * <tr><td>"Sheet1", 6</td><td>false</td></tr>
+ * </table></blockquote>
+ */
+ /* package */ static boolean cellReferenceIsWithinRange(String rawSheetName, int numberOfLetters) {
+
+ if(numberOfLetters > BIFF8_LAST_COLUMN_TEXT_LEN) {
+ // "Sheet1" case etc
+ return false; // that was easy
+ }
+ int nDigits = rawSheetName.length() - numberOfLetters;
+ if(nDigits > BIFF8_LAST_ROW_TEXT_LEN) {
+ return false;
+ }
+ if(numberOfLetters == BIFF8_LAST_COLUMN_TEXT_LEN) {
+ String colStr = rawSheetName.substring(0, BIFF8_LAST_COLUMN_TEXT_LEN).toUpperCase();
+ if(colStr.compareTo(BIFF8_LAST_COLUMN) > 0) {
+ return false;
+ }
+ } else {
+ // apparent column name has less chars than max
+ // no need to check range
+ }
+
+ if(nDigits == BIFF8_LAST_ROW_TEXT_LEN) {
+ String colStr = rawSheetName.substring(numberOfLetters);
+ // ASCII comparison is valid if digit count is same
+ if(colStr.compareTo(BIFF8_LAST_ROW) > 0) {
+ return false;
+ }
+ } else {
+ // apparent row has less chars than max
+ // no need to check range
+ }
+
+ return true;
+ }
+
+ /**
+ * Note - this method assumes the specified rawSheetName has only letters and digits. It
+ * cannot be used to match absolute or range references (using the dollar or colon char).
+ * <p/>
+ * Some notable cases:
+ * <blockquote><table border="0" cellpadding="1" cellspacing="0"
+ * summary="Notable cases.">
+ * <tr><th>Input </th><th>Result </th><th>Comments</th></tr>
+ * <tr><td>"A1" </td><td>true</td><td> </td></tr>
+ * <tr><td>"a111" </td><td>true</td><td> </td></tr>
+ * <tr><td>"AA" </td><td>false</td><td> </td></tr>
+ * <tr><td>"aa1" </td><td>true</td><td> </td></tr>
+ * <tr><td>"A1A" </td><td>false</td><td> </td></tr>
+ * <tr><td>"A1A1" </td><td>false</td><td> </td></tr>
+ * <tr><td>"A$1:$C$20" </td><td>false</td><td>Not a plain cell reference</td></tr>
+ * <tr><td>"SALES20080101" </td><td>true</td>
+ * <td>Still needs delimiting even though well out of range</td></tr>
+ * </table></blockquote>
+ *
+ * @return <code>true</code> if there is any possible ambiguity that the specified rawSheetName
+ * could be interpreted as a valid cell name.
+ */
+ /* package */ static boolean nameLooksLikePlainCellReference(String rawSheetName) {
+ Matcher matcher = CELL_REF_PATTERN.matcher(rawSheetName);
+ if(!matcher.matches()) {
+ return false;
+ }
+
+ // rawSheetName == "Sheet1" gets this far.
+ String lettersPrefix = matcher.group(1);
+ return cellReferenceIsWithinRange(rawSheetName, lettersPrefix.length());
+ }
+
+}
Propchange: poi/trunk/src/java/org/apache/poi/hssf/record/formula/SheetNameFormatter.java
------------------------------------------------------------------------------
svn:eol-style = native
Propchange: poi/trunk/src/java/org/apache/poi/hssf/record/formula/SheetNameFormatter.java
------------------------------------------------------------------------------
svn:executable = *
Modified: poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/usermodel/TestFormulaEvaluatorDocs.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/usermodel/TestFormulaEvaluatorDocs.java?rev=617491&r1=617490&r2=617491&view=diff
==============================================================================
--- poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/usermodel/TestFormulaEvaluatorDocs.java (original)
+++ poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/usermodel/TestFormulaEvaluatorDocs.java Fri Feb 1 05:02:06 2008
@@ -82,7 +82,7 @@
assertEquals(HSSFCell.CELL_TYPE_FORMULA, wb.getSheetAt(0).getRow(1).getCell((short)2).getCellType());
assertEquals(22.3, wb.getSheetAt(1).getRow(0).getCell((short)0).getNumericCellValue(), 0);
- assertEquals("S1!A1", wb.getSheetAt(1).getRow(0).getCell((short)0).getCellFormula());
+ assertEquals("'S1'!A1", wb.getSheetAt(1).getRow(0).getCell((short)0).getCellFormula());
assertEquals(HSSFCell.CELL_TYPE_FORMULA, wb.getSheetAt(1).getRow(0).getCell((short)0).getCellType());
Modified: poi/trunk/src/testcases/org/apache/poi/hssf/HSSFTests.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/HSSFTests.java?rev=617491&r1=617490&r2=617491&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/hssf/HSSFTests.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/hssf/HSSFTests.java Fri Feb 1 05:02:06 2008
@@ -75,13 +75,7 @@
import org.apache.poi.hssf.record.TestValueRangeRecord;
import org.apache.poi.hssf.record.aggregates.TestRowRecordsAggregate;
import org.apache.poi.hssf.record.aggregates.TestValueRecordsAggregate;
-import org.apache.poi.hssf.record.formula.TestAreaErrPtg;
-import org.apache.poi.hssf.record.formula.TestErrPtg;
-import org.apache.poi.hssf.record.formula.TestFuncPtg;
-import org.apache.poi.hssf.record.formula.TestIntersectionPtg;
-import org.apache.poi.hssf.record.formula.TestPercentPtg;
-import org.apache.poi.hssf.record.formula.TestRangePtg;
-import org.apache.poi.hssf.record.formula.TestUnionPtg;
+import org.apache.poi.hssf.record.formula.AllFormulaTests;
import org.apache.poi.hssf.usermodel.TestBugs;
import org.apache.poi.hssf.usermodel.TestCellStyle;
import org.apache.poi.hssf.usermodel.TestCloneSheet;
@@ -215,13 +209,7 @@
suite.addTest(new TestSuite(TestSheetReferences.class));
- suite.addTest(new TestSuite(TestAreaErrPtg.class));
- suite.addTest(new TestSuite(TestErrPtg.class));
- suite.addTest(new TestSuite(TestFuncPtg.class));
- suite.addTest(new TestSuite(TestIntersectionPtg.class));
- suite.addTest(new TestSuite(TestPercentPtg.class));
- suite.addTest(new TestSuite(TestRangePtg.class));
- suite.addTest(new TestSuite(TestUnionPtg.class));
+ suite.addTest(AllFormulaTests.suite());
suite.addTest(new TestSuite(TestValueRecordsAggregate.class));
suite.addTest(new TestSuite(TestNameRecord.class));
suite.addTest(new TestSuite(TestEventRecordFactory.class));
Modified: poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/AbstractPtgTestCase.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/AbstractPtgTestCase.java?rev=617491&r1=617490&r2=617491&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/AbstractPtgTestCase.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/AbstractPtgTestCase.java Fri Feb 1 05:02:06 2008
@@ -26,6 +26,7 @@
import junit.framework.TestCase;
+import org.apache.poi.hssf.model.Workbook;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
/**
@@ -34,7 +35,7 @@
*
* @author Daniel Noll (daniel at nuix dot com dot au)
*/
-public class AbstractPtgTestCase extends TestCase
+public abstract class AbstractPtgTestCase extends TestCase
{
/** Directory containing the test data. */
private static String dataDir = System.getProperty("HSSF.testdata.path");
@@ -46,7 +47,7 @@
* @return the loaded workbook.
* @throws IOException if an error occurs loading the workbook.
*/
- protected static HSSFWorkbook loadWorkbook(String filename)
+ protected static final HSSFWorkbook loadWorkbook(String filename)
throws IOException {
File file = new File(dataDir, filename);
InputStream stream = new BufferedInputStream(new FileInputStream(file));
@@ -59,4 +60,18 @@
stream.close();
}
}
+
+ /**
+ * Creates a new Workbook and adds one sheet with the specified name
+ */
+ protected static final Workbook createWorkbookWithSheet(String sheetName) {
+
+ Workbook book = Workbook.createWorkbook();
+ // this creates sheet if it doesn't exist
+ book.checkExternSheet(0);
+ // TODO - this call alone does not create the sheet even though the javadoc says it does
+ book.setSheetName(0, sheetName);
+ return book;
+ }
+
}
Added: poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/AllFormulaTests.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/AllFormulaTests.java?rev=617491&view=auto
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/AllFormulaTests.java (added)
+++ poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/AllFormulaTests.java Fri Feb 1 05:02:06 2008
@@ -0,0 +1,47 @@
+/* ====================================================================
+ 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.hssf.record.formula;
+
+import junit.framework.Test;
+import junit.framework.TestSuite;
+
+/**
+ * Collects all tests for this package.
+ *
+ * @author Josh Micich
+ */
+public class AllFormulaTests {
+
+ public static Test suite() {
+ TestSuite result = new TestSuite("Tests for org.apache.poi.hssf.record.formula");
+ result.addTestSuite(TestArea3DPtg.class);
+ result.addTestSuite(TestAreaErrPtg.class);
+ result.addTestSuite(TestAreaPtg.class);
+ result.addTestSuite(TestErrPtg.class);
+ result.addTestSuite(TestFuncPtg.class);
+ result.addTestSuite(TestIntersectionPtg.class);
+ result.addTestSuite(TestPercentPtg.class);
+ result.addTestSuite(TestRangePtg.class);
+ result.addTestSuite(TestRef3DPtg.class);
+ result.addTestSuite(TestReferencePtg.class);
+ result.addTestSuite(TestSheetNameFormatter.class);
+ result.addTestSuite(TestUnionPtg.class);
+ return result;
+ }
+}
Propchange: poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/AllFormulaTests.java
------------------------------------------------------------------------------
svn:eol-style = native
Added: poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/TestArea3DPtg.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/TestArea3DPtg.java?rev=617491&view=auto
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/TestArea3DPtg.java (added)
+++ poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/TestArea3DPtg.java Fri Feb 1 05:02:06 2008
@@ -0,0 +1,50 @@
+/* ====================================================================
+ 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.hssf.record.formula;
+
+import org.apache.poi.hssf.model.Workbook;
+
+/**
+ * Tests for Area3DPtg
+ *
+ * @author Josh Micich
+ */
+public final class TestArea3DPtg extends AbstractPtgTestCase {
+
+ /**
+ * confirms that sheet names get properly escaped
+ */
+ public void testToFormulaString() {
+
+ Area3DPtg target = new Area3DPtg("A1:B1", (short)0);
+
+ String sheetName = "my sheet";
+ Workbook book = createWorkbookWithSheet(sheetName);
+ assertEquals("'my sheet'!A1:B1", target.toFormulaString(book));
+
+ book.setSheetName(0, "Sheet1");
+ assertEquals("Sheet1!A1:B1", target.toFormulaString(book));
+
+ book.setSheetName(0, "C64");
+ assertEquals("'C64'!A1:B1", target.toFormulaString(book));
+ }
+
+
+
+}
Propchange: poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/TestArea3DPtg.java
------------------------------------------------------------------------------
svn:eol-style = native
Propchange: poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/TestFuncPtg.java
('svn:executable' removed)
Added: poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/TestRef3DPtg.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/TestRef3DPtg.java?rev=617491&view=auto
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/TestRef3DPtg.java (added)
+++ poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/TestRef3DPtg.java Fri Feb 1 05:02:06 2008
@@ -0,0 +1,44 @@
+/* ====================================================================
+ 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.hssf.record.formula;
+
+import org.apache.poi.hssf.model.Workbook;
+
+/**
+ * Tests for Ref3DPtg
+ *
+ * @author Josh Micich
+ */
+public final class TestRef3DPtg extends AbstractPtgTestCase {
+
+ public void testToFormulaString() {
+
+ Ref3DPtg target = new Ref3DPtg("A1", (short)0);
+
+ Workbook book = createWorkbookWithSheet("my sheet");
+
+ assertEquals("'my sheet'!A1", target.toFormulaString(book));
+
+ book.setSheetName(0, "ProfitAndLoss");
+ assertEquals("ProfitAndLoss!A1", target.toFormulaString(book));
+
+ book.setSheetName(0, "profit+loss");
+ assertEquals("'profit+loss'!A1", target.toFormulaString(book));
+ }
+}
Propchange: poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/TestRef3DPtg.java
------------------------------------------------------------------------------
svn:eol-style = native
Added: poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/TestSheetNameFormatter.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/TestSheetNameFormatter.java?rev=617491&view=auto
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/TestSheetNameFormatter.java (added)
+++ poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/TestSheetNameFormatter.java Fri Feb 1 05:02:06 2008
@@ -0,0 +1,99 @@
+/* ====================================================================
+ 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.hssf.record.formula;
+
+import junit.framework.TestCase;
+
+/**
+ * Tests for SheetNameFormatter
+ *
+ * @author Josh Micich
+ */
+public final class TestSheetNameFormatter extends TestCase {
+
+ public TestSheetNameFormatter(String testName) {
+ super(testName);
+ }
+
+ private static void confirmFormat(String rawSheetName, String expectedSheetNameEncoding) {
+ assertEquals(expectedSheetNameEncoding, SheetNameFormatter.format(rawSheetName));
+ }
+
+ /**
+ * Tests main public method 'format'
+ */
+ public void testFormat() {
+
+ confirmFormat("abc", "abc");
+ confirmFormat("123", "'123'");
+
+ confirmFormat("my sheet", "'my sheet'"); // space
+ confirmFormat("A:MEM", "'A:MEM'"); // colon
+
+ confirmFormat("O'Brian", "'O''Brian'"); // single quote gets doubled
+
+
+ confirmFormat("3rdTimeLucky", "'3rdTimeLucky'"); // digit in first pos
+ confirmFormat("_", "_"); // plain underscore OK
+ confirmFormat("my_3rd_sheet", "my_3rd_sheet"); // underscores and digits OK
+ confirmFormat("A12220", "'A12220'");
+ confirmFormat("TAXRETURN19980415", "TAXRETURN19980415");
+ }
+
+ private static void confirmCellNameMatch(String rawSheetName, boolean expected) {
+ assertEquals(expected, SheetNameFormatter.nameLooksLikePlainCellReference(rawSheetName));
+ }
+
+ /**
+ * Tests functionality to determine whether a sheet name containing only letters and digits
+ * would look (to Excel) like a cell name.
+ */
+ public void testLooksLikePlainCellReference() {
+
+ confirmCellNameMatch("A1", true);
+ confirmCellNameMatch("a111", true);
+ confirmCellNameMatch("AA", false);
+ confirmCellNameMatch("aa1", true);
+ confirmCellNameMatch("A1A", false);
+ confirmCellNameMatch("A1A1", false);
+ confirmCellNameMatch("SALES20080101", false); // out of range
+ }
+
+ private static void confirmCellRange(String text, int numberOfPrefixLetters, boolean expected) {
+ assertEquals(expected, SheetNameFormatter.cellReferenceIsWithinRange(text, numberOfPrefixLetters));
+ }
+
+ /**
+ * Tests exact boundaries for names that look very close to cell names (i.e. contain 1 or more
+ * letters followed by one or more digits).
+ */
+ public void testCellRange() {
+ confirmCellRange("A1", 1, true);
+ confirmCellRange("a111", 1, true);
+ confirmCellRange("A65536", 1, true);
+ confirmCellRange("A65537", 1, false);
+ confirmCellRange("iv1", 2, true);
+ confirmCellRange("IW1", 2, false);
+ confirmCellRange("AAA1", 3, false);
+ confirmCellRange("a111", 1, true);
+ confirmCellRange("Sheet1", 6, false);
+ confirmCellRange("iV65536", 2, true); // max cell in Excel 97-2003
+ confirmCellRange("IW65537", 2, false);
+ }
+}
Propchange: poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/TestSheetNameFormatter.java
------------------------------------------------------------------------------
svn:eol-style = native
Modified: poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestNamedRange.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestNamedRange.java?rev=617491&r1=617490&r2=617491&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestNamedRange.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestNamedRange.java Fri Feb 1 05:02:06 2008
@@ -351,7 +351,7 @@
String retrievedPrintArea = workbook.getPrintArea(0);
assertNotNull("Print Area not defined for first sheet", retrievedPrintArea);
- assertEquals(reference, retrievedPrintArea);
+ assertEquals("'" + sheetName + "'!$A$1:$B$1", retrievedPrintArea);
}
@@ -370,7 +370,7 @@
String retrievedPrintArea = workbook.getPrintArea(0);
assertNotNull("Print Area not defined for first sheet", retrievedPrintArea);
- assertEquals(sheetName+"!"+reference, retrievedPrintArea);
+ assertEquals("'" + sheetName + "'!" + reference, retrievedPrintArea);
}
@@ -437,7 +437,7 @@
String retrievedPrintArea = workbook.getPrintArea(0);
assertNotNull("Print Area not defined for first sheet", retrievedPrintArea);
- assertEquals("References Match", reference, retrievedPrintArea);
+ assertEquals("References Match", "'" + sheetName + "'!$A$1:$B$1", retrievedPrintArea);
}
@@ -449,9 +449,9 @@
{
HSSFWorkbook workbook = new HSSFWorkbook();
- HSSFSheet sheet = workbook.createSheet("Sheet 1");
- sheet = workbook.createSheet("Sheet 2");
- sheet = workbook.createSheet("Sheet 3");
+ HSSFSheet sheet = workbook.createSheet("Sheet1");
+ sheet = workbook.createSheet("Sheet2");
+ sheet = workbook.createSheet("Sheet3");
String sheetName = workbook.getSheetName(0);
String reference = null;
@@ -508,9 +508,30 @@
String retrievedPrintArea = workbook.getPrintArea(0);
assertNotNull("Print Area not defined for first sheet", retrievedPrintArea);
- assertEquals(reference, retrievedPrintArea);
+ assertEquals("'" + sheetName + "'!$A$1:$B$1", retrievedPrintArea);
}
-
+
+
+ /**
+ * Tests the parsing of union area expressions, and re-display in the presence of sheet names
+ * with special characters.
+ */
+ public void testPrintAreaUnion(){
+ HSSFWorkbook workbook = new HSSFWorkbook();
+ HSSFSheet sheet = workbook.createSheet("Test Print Area");
+ String sheetName = workbook.getSheetName(0);
+
+
+ String reference = sheetName + "!$A$1:$B$1, " + sheetName + "!$D$1:$F$2";
+ String expResult = "'" + sheetName + "'!$A$1:$B$1,'" + sheetName + "'!$D$1:$F$2";
+ workbook.setPrintArea(0, reference);
+
+ String retrievedPrintArea = workbook.getPrintArea(0);
+
+ assertNotNull("Print Area not defined for first sheet", retrievedPrintArea);
+ assertEquals(expResult, retrievedPrintArea);
+ }
+
/**
* Verifies an existing print area is deleted
*
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org