You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by ye...@apache.org on 2008/12/04 19:38:01 UTC
svn commit: r723392 - in /poi/trunk/src: documentation/content/xdocs/
java/org/apache/poi/hssf/usermodel/ java/org/apache/poi/ss/formula/
ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/
ooxml/java/org/apache/poi/xssf/usermodel/ ooxml/testcases/org/...
Author: yegor
Date: Thu Dec 4 10:38:00 2008
New Revision: 723392
URL: http://svn.apache.org/viewvc?rev=723392&view=rev
Log:
1. Support sheet-level names2. Fixed XSSFCell to properly handle cell references with column numbers up to XFD3. when pasring formula, HSSFName.setRefersToFormula must set type of operands to Ptg.CLASS_REF, otherwise created named don't appear in the dropdown to the left of formula bar in Excel
Modified:
poi/trunk/src/documentation/content/xdocs/changes.xml
poi/trunk/src/documentation/content/xdocs/status.xml
poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java
poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFName.java
poi/trunk/src/java/org/apache/poi/ss/formula/OperandClassTransformer.java
poi/trunk/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/Cell.java
poi/trunk/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/Name.java
poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java
poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFName.java
poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java
poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java
poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFCell.java
poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFName.java
poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestNamedRange.java
Modified: poi/trunk/src/documentation/content/xdocs/changes.xml
URL: http://svn.apache.org/viewvc/poi/trunk/src/documentation/content/xdocs/changes.xml?rev=723392&r1=723391&r2=723392&view=diff
==============================================================================
--- poi/trunk/src/documentation/content/xdocs/changes.xml (original)
+++ poi/trunk/src/documentation/content/xdocs/changes.xml Thu Dec 4 10:38:00 2008
@@ -37,6 +37,8 @@
<!-- Don't forget to update status.xml too! -->
<release version="3.5-beta5" date="2008-??-??">
+ <action dev="POI-DEVELOPERS" type="add">Support sheet-level names</action>
+ <action dev="POI-DEVELOPERS" type="fix">Fixed XSSFCell to properly handle cell references with column numbers up to XFD</action>
<action dev="POI-DEVELOPERS" type="fix">44914 - Fixed warning message "WARN. Unread n bytes of record 0xNN"</action>
<action dev="POI-DEVELOPERS" type="add">46156 - Improved number to text conversion to be closer to that of Excel</action>
<action dev="POI-DEVELOPERS" type="fix">46312 - Fixed ValueRecordsAggregate to handle removal of new empty row</action>
Modified: poi/trunk/src/documentation/content/xdocs/status.xml
URL: http://svn.apache.org/viewvc/poi/trunk/src/documentation/content/xdocs/status.xml?rev=723392&r1=723391&r2=723392&view=diff
==============================================================================
--- poi/trunk/src/documentation/content/xdocs/status.xml (original)
+++ poi/trunk/src/documentation/content/xdocs/status.xml Thu Dec 4 10:38:00 2008
@@ -34,6 +34,8 @@
<!-- Don't forget to update changes.xml too! -->
<changes>
<release version="3.5-beta5" date="2008-??-??">
+ <action dev="POI-DEVELOPERS" type="add">Support sheet-level names</action>
+ <action dev="POI-DEVELOPERS" type="fix">Fixed XSSFCell to properly handle cell references with column numbers up to XFD</action>
<action dev="POI-DEVELOPERS" type="fix">44914 - Fixed warning message "WARN. Unread n bytes of record 0xNN"</action>
<action dev="POI-DEVELOPERS" type="add">46156 - Improved number to text conversion to be closer to that of Excel</action>
<action dev="POI-DEVELOPERS" type="fix">46312 - Fixed ValueRecordsAggregate to handle removal of new empty row</action>
Modified: poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java?rev=723392&r1=723391&r2=723392&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java Thu Dec 4 10:38:00 2008
@@ -557,6 +557,17 @@
stringValue.setUnicodeString(book.getWorkbook().getSSTString(index));
}
+ /**
+ * Sets formula for this cell.
+ * <p>
+ * Note, this method only sets the formula string and does not calculate the formula value.
+ * To set the precalculated value use {@link #setCellValue(double)} or {@link #setCellValue(String)}
+ * </p>
+ *
+ * @param formula the formula to set, e.g. <code>SUM(C4:E4)</code>.
+ * If the argument is <code>null</code> then the current formula is removed.
+ * @throws IllegalArgumentException if the formula is unparsable
+ */
public void setCellFormula(String formula) {
int row=record.getRow();
short col=record.getColumn();
Modified: poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFName.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFName.java?rev=723392&r1=723391&r2=723392&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFName.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFName.java Thu Dec 4 10:38:00 2008
@@ -22,6 +22,7 @@
import org.apache.poi.hssf.record.NameRecord;
import org.apache.poi.hssf.record.formula.Ptg;
import org.apache.poi.ss.usermodel.Name;
+import org.apache.poi.ss.formula.FormulaType;
/**
* High Level Representation of a 'defined name' which could be a 'built-in' name,
@@ -115,7 +116,7 @@
* @deprecated (Nov 2008) Misleading name. Use {@link #setRefersToFormula(String)} instead.
*/
public void setReference(String ref){
- setRefersToFormula(ref);
+ setRefersToFormula(ref);
}
/**
@@ -133,9 +134,9 @@
* @throws IllegalArgumentException if the specified reference is unparsable
*/
public void setRefersToFormula(String formulaText) {
- Ptg[] ptgs = HSSFFormulaParser.parse(formulaText, _book);
- _definedNameRec.setNameDefinition(ptgs);
- }
+ Ptg[] ptgs = HSSFFormulaParser.parse(formulaText, _book, FormulaType.NAMEDRANGE);
+ _definedNameRec.setNameDefinition(ptgs);
+ }
/**
* Returns the formula that the name is defined to refer to. The following are representative examples:
@@ -147,7 +148,7 @@
if (_definedNameRec.isFunctionName()) {
throw new IllegalStateException("Only applicable to named ranges");
}
- return HSSFFormulaParser.toFormulaString(_book, _definedNameRec.getNameDefinition());
+ return HSSFFormulaParser.toFormulaString(_book, _definedNameRec.getNameDefinition());
}
/**
@@ -176,4 +177,49 @@
sb.append("]");
return sb.toString();
}
+
+ /**
+ * Specifies if the defined name is a local name, and if so, which sheet it is on.
+ *
+ * @param index if greater than 0, the defined name is a local name and the value MUST be a 0-based index
+ * to the collection of sheets as they appear in the workbook.
+ * @throws IllegalArgumentException if the sheet index is invalid.
+ */
+ public void setSheetIndex(int index){
+ int lastSheetIx = _book.getNumberOfSheets() - 1;
+ if (index < -1 || index > lastSheetIx) {
+ throw new IllegalArgumentException("Sheet index (" + index +") is out of range" +
+ (lastSheetIx == -1 ? "" : (" (0.." + lastSheetIx + ")")));
+ }
+
+ _definedNameRec.setSheetNumber(index + 1);
+ }
+
+ /**
+ * Returns the sheet index this name applies to.
+ *
+ * @return the sheet index this name applies to, -1 if this name applies to the entire workbook
+ */
+ public int getSheetIndex(){
+ return _definedNameRec.getSheetNumber() - 1;
+ }
+
+ /**
+ * Returns the comment the user provided when the name was created.
+ *
+ * @return the user comment for this named range
+ */
+ public String getComment(){
+ return _definedNameRec.getDescriptionText();
+ }
+
+ /**
+ * Sets the comment the user provided when the name was created.
+ *
+ * @param comment the user comment for this named range
+ */
+ public void setComment(String comment){
+ _definedNameRec.setDescriptionText(comment);
+ }
+
}
Modified: poi/trunk/src/java/org/apache/poi/ss/formula/OperandClassTransformer.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/OperandClassTransformer.java?rev=723392&r1=723391&r2=723392&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/OperandClassTransformer.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/OperandClassTransformer.java Thu Dec 4 10:38:00 2008
@@ -69,6 +69,7 @@
case FormulaType.CELL:
rootNodeOperandClass = Ptg.CLASS_VALUE;
break;
+ case FormulaType.NAMEDRANGE:
case FormulaType.DATAVALIDATION_LIST:
rootNodeOperandClass = Ptg.CLASS_REF;
break;
Modified: poi/trunk/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/Cell.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/Cell.java?rev=723392&r1=723391&r2=723392&view=diff
==============================================================================
--- poi/trunk/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/Cell.java (original)
+++ poi/trunk/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/Cell.java Thu Dec 4 10:38:00 2008
@@ -189,6 +189,7 @@
*
* @param formula the formula to set, e.g. <code>SUM(C4:E4)</code>.
* If the argument is <code>null</code> then the current formula is removed.
+ * @throws IllegalArgumentException if the formula is unparsable
*/
void setCellFormula(String formula);
Modified: poi/trunk/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/Name.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/Name.java?rev=723392&r1=723391&r2=723392&view=diff
==============================================================================
--- poi/trunk/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/Name.java (original)
+++ poi/trunk/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/Name.java Thu Dec 4 10:38:00 2008
@@ -93,7 +93,7 @@
void setNameName(String name);
/**
- * Returns the formula that the name is defined to refer to. The following are representative examples:
+ * Returns the formula that the name is defined to refer to.
*
* @return the reference for this name
* @see #setRefersToFormula(String)
@@ -129,4 +129,33 @@
* @return true if the name refers to a deleted cell, false otherwise
*/
boolean isDeleted();
+
+ /**
+ * Tell Excel that this name applies to the worksheet with the specified index instead of the entire workbook.
+ *
+ * @param sheetId the sheet index this name applies to, -1 unsets this property making the name workbook-global
+ * @throws IllegalArgumentException if the sheet index is invalid.
+ */
+ public void setSheetIndex(int sheetId);
+
+ /**
+ * Returns the sheet index this name applies to.
+ *
+ * @return the sheet index this name applies to, -1 if this name applies to the entire workbook
+ */
+ public int getSheetIndex();
+
+ /**
+ * Returns the comment the user provided when the name was created.
+ *
+ * @return the user comment for this named range
+ */
+ public String getComment();
+
+ /**
+ * Sets the comment the user provided when the name was created.
+ *
+ * @param comment the user comment for this named range
+ */
+ public void setComment(String comment);
}
Modified: poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java?rev=723392&r1=723391&r2=723392&view=diff
==============================================================================
--- poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java (original)
+++ poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java Thu Dec 4 10:38:00 2008
@@ -23,8 +23,11 @@
import java.util.Date;
import org.apache.poi.hssf.record.formula.eval.ErrorEval;
+import org.apache.poi.hssf.record.formula.Ptg;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellReference;
+import org.apache.poi.ss.formula.FormulaParser;
+import org.apache.poi.ss.formula.FormulaType;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.POIXMLException;
@@ -51,7 +54,7 @@
/**
* The maximum number of columns in SpreadsheetML
*/
- private static final int MAX_COLUMN_NUMBER = 16384;
+ public static final int MAX_COLUMN_NUMBER = 16384; //2^14
private static final String FALSE_AS_STRING = "0";
private static final String TRUE_AS_STRING = "1";
@@ -330,6 +333,7 @@
*
* @param formula the formula to set, e.g. <code>SUM(C4:E4)</code>.
* If the argument is <code>null</code> then the current formula is removed.
+ * @throws IllegalArgumentException if the formula is invalid
*/
public void setCellFormula(String formula) {
if (formula == null && cell.isSetF()) {
@@ -337,6 +341,16 @@
return;
}
+ XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(row.getSheet().getWorkbook());
+ try {
+ Ptg[] ptgs = FormulaParser.parse(formula, fpb, FormulaType.CELL);
+ } catch (RuntimeException e) {
+ if (e.getClass().getName().startsWith(FormulaParser.class.getName())) {
+ throw new IllegalArgumentException("Unparsable formula '" + formula + "'", e);
+ }
+ throw e;
+ }
+
CTCellFormula f = CTCellFormula.Factory.newInstance();
f.setStringValue(formula);
cell.setF(f);
@@ -580,7 +594,8 @@
protected void setCellNum(int num) {
checkBounds(num);
cellNum = num;
- cell.setR(formatPosition());
+ String ref = new CellReference(getRowIndex(), getColumnIndex()).formatAsString();
+ cell.setR(ref);
}
/**
@@ -599,22 +614,6 @@
}
/**
- * Builds an A1 style reference from internal represenetation
- *
- * @return an A1 style reference to the location of this cell
- */
- protected String formatPosition() {
- int col = this.getColumnIndex();
- String result = Character.valueOf((char) (col % 26 + 'A')).toString();
- if (col >= 26){
- col = col / 26;
- result = Character.valueOf((char) (col + '@')) + result;
- }
- result = result + String.valueOf(row.getRowNum() + 1);
- return result;
- }
-
- /**
* Set the cells type (numeric, formula or string)
*
* @throws IllegalArgumentException if the specified cell type is invalid
@@ -733,10 +732,10 @@
*/
private static void checkBounds(int cellNum) {
if (cellNum > MAX_COLUMN_NUMBER) {
- throw new POIXMLException("You cannot have more than "+MAX_COLUMN_NUMBER+" columns " +
+ throw new IllegalArgumentException("You cannot have more than "+MAX_COLUMN_NUMBER+" columns " +
"in a given row because Excel can't handle it");
} else if (cellNum < 0) {
- throw new POIXMLException("You cannot reference columns with an index of less then 0.");
+ throw new IllegalArgumentException("You cannot reference columns with an index of less then 0.");
}
}
Modified: poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFName.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFName.java?rev=723392&r1=723391&r2=723392&view=diff
==============================================================================
--- poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFName.java (original)
+++ poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFName.java Thu Dec 4 10:38:00 2008
@@ -170,9 +170,8 @@
*/
public void setRefersToFormula(String formulaText) {
XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(workbook);
- Ptg[] ptgs;
try {
- ptgs = FormulaParser.parse(formulaText, fpb, FormulaType.CELL); // TODO - use type NAMEDRANGE
+ Ptg[] ptgs = FormulaParser.parse(formulaText, fpb, FormulaType.NAMEDRANGE);
} catch (RuntimeException e) {
if (e.getClass().getName().startsWith(FormulaParser.class.getName())) {
throw new IllegalArgumentException("Unparsable formula '" + formulaText + "'", e);
@@ -195,11 +194,20 @@
/**
* Tell Excel that this name applies to the worksheet with the specified index instead of the entire workbook.
*
- * @param sheetId the sheet index this name applies to, -1 unsets this property making the name workbook-global
+ * @param index the sheet index this name applies to, -1 unsets this property making the name workbook-global
*/
- public void setLocalSheetId(int sheetId) {
- if(sheetId == -1) ctName.unsetLocalSheetId();
- else ctName.setLocalSheetId(sheetId);
+ public void setSheetIndex(int index) {
+ int lastSheetIx = workbook.getNumberOfSheets() - 1;
+ if (index < -1 || index > lastSheetIx) {
+ throw new IllegalArgumentException("Sheet index (" + index +") is out of range" +
+ (lastSheetIx == -1 ? "" : (" (0.." + lastSheetIx + ")")));
+ }
+
+ if(index == -1) {
+ if(ctName.isSetLocalSheetId()) ctName.unsetLocalSheetId();
+ } else {
+ ctName.setLocalSheetId(index);
+ }
}
/**
@@ -207,7 +215,7 @@
*
* @return the sheet index this name applies to, -1 if this name applies to the entire workbook
*/
- public int getLocalSheetId() {
+ public int getSheetIndex() {
return ctName.isSetLocalSheetId() ? (int) ctName.getLocalSheetId() : -1;
}
Modified: poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java?rev=723392&r1=723391&r2=723392&view=diff
==============================================================================
--- poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java (original)
+++ poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java Thu Dec 4 10:38:00 2008
@@ -21,6 +21,7 @@
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
+import org.apache.poi.POIXMLException;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCell;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTRow;
@@ -30,6 +31,11 @@
public class XSSFRow implements Row, Comparable<XSSFRow> {
/**
+ * The maximum number of rows in SpreadsheetML
+ */
+ public static final int MAX_ROW_NUMBER = 1048576; //2 ^ 20
+
+ /**
* the xml bean containing all cell definitions for this row
*/
private final CTRow row;
@@ -309,10 +315,12 @@
* Set the row number of this row.
*
* @param rowNum the row number (0-based)
- * @throws IllegalArgumentException if rowNum < 0
+ * @throws IllegalArgumentException if rowNum < 0 or greater than {@link #MAX_ROW_NUMBER}
*/
public void setRowNum(int rowNum) {
if(rowNum < 0) throw new IllegalArgumentException("Row number must be >= 0");
+ if (rowNum > MAX_ROW_NUMBER)
+ throw new IllegalArgumentException("You cannot have more than "+MAX_ROW_NUMBER+" rows ");
this.row.setR(rowNum + 1);
}
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=723392&r1=723391&r2=723392&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 Dec 4 10:38:00 2008
@@ -745,7 +745,7 @@
public void removePrintArea(int sheetIndex) {
int cont = 0;
for (XSSFName name : namedRanges) {
- if (name.getNameName().equals(XSSFName.BUILTIN_PRINT_AREA) && name.getLocalSheetId() == sheetIndex) {
+ if (name.getNameName().equals(XSSFName.BUILTIN_PRINT_AREA) && name.getSheetIndex() == sheetIndex) {
namedRanges.remove(cont);
break;
}
@@ -967,7 +967,7 @@
private XSSFName getBuiltInName(String builtInCode, int sheetNumber) {
for (XSSFName name : namedRanges) {
- if (name.getNameName().equalsIgnoreCase(builtInCode) && name.getLocalSheetId() == sheetNumber) {
+ if (name.getNameName().equalsIgnoreCase(builtInCode) && name.getSheetIndex() == sheetNumber) {
return name;
}
}
Modified: poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFCell.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFCell.java?rev=723392&r1=723391&r2=723392&view=diff
==============================================================================
--- poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFCell.java (original)
+++ poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFCell.java Thu Dec 4 10:38:00 2008
@@ -305,22 +305,41 @@
assertEquals(255, XSSFCell.parseCellNum("IV32768"));
}
- public void testFormatPosition() {
- XSSFRow row = createParentObjects();
- row.setRowNum(0);
+ public void testSetCellReference() {
+ XSSFWorkbook wb = new XSSFWorkbook();
+ XSSFSheet sheet = wb.createSheet();
+ XSSFRow row = sheet.createRow(0);
XSSFCell cell = row.createCell(0);
- cell.setCellNum((short) 0);
- assertEquals("A1", cell.formatPosition());
- cell.setCellNum((short) 25);
- assertEquals("Z1", cell.formatPosition());
- cell.setCellNum((short) 26);
- assertEquals("AA1", cell.formatPosition());
- cell.setCellNum((short) 255);
- assertEquals("IV1", cell.formatPosition());
- row.setRowNum(32767);
- assertEquals("IV32768", cell.formatPosition());
+ assertEquals("A1", cell.getCTCell().getR());
+
+ row = sheet.createRow(100);
+ cell = row.createCell(100);
+ assertEquals("CW101", cell.getCTCell().getR());
+
+ row = sheet.createRow(XSSFRow.MAX_ROW_NUMBER);
+ cell = row.createCell(100);
+ assertEquals("CW1048577", cell.getCTCell().getR());
+
+ row = sheet.createRow(XSSFRow.MAX_ROW_NUMBER);
+ cell = row.createCell(XSSFCell.MAX_COLUMN_NUMBER);
+ assertEquals("XFE1048577", cell.getCTCell().getR());
+
+ try {
+ sheet.createRow(XSSFRow.MAX_ROW_NUMBER + 1);
+ fail("expecting exception when rownum > XSSFRow.MAX_ROW_NUMBER");
+ } catch(IllegalArgumentException e){
+ ;
+ }
+
+ try {
+ row = sheet.createRow(100);
+ row.createCell(XSSFCell.MAX_COLUMN_NUMBER + 1);
+ fail("expecting exception when columnIndex > XSSFCell.MAX_COLUMN_NUMBER");
+ } catch(IllegalArgumentException e){
+ ;
+ }
}
-
+
public void testGetCellComment() {
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet();
Modified: poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFName.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFName.java?rev=723392&r1=723391&r2=723392&view=diff
==============================================================================
--- poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFName.java (original)
+++ poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFName.java Thu Dec 4 10:38:00 2008
@@ -52,9 +52,25 @@
assertEquals("'Testing Named Ranges'!$A$1:$B$1", name1.getRefersToFormula());
assertEquals("Testing Named Ranges", name1.getSheetName());
- assertEquals(-1, name1.getLocalSheetId());
- name1.setLocalSheetId(1);
- assertEquals(1, name1.getLocalSheetId());
+ assertEquals(-1, name1.getSheetIndex());
+ name1.setSheetIndex(-1);
+ assertEquals(-1, name1.getSheetIndex());
+ try {
+ name1.setSheetIndex(1);
+ fail("should throw IllegalArgumentException");
+ } catch(IllegalArgumentException e){
+ assertEquals("Sheet index (1) is out of range", e.getMessage());
+ }
+ wb.createSheet();
+ try {
+ name1.setSheetIndex(1);
+ fail("should throw IllegalArgumentException");
+ } catch(IllegalArgumentException e){
+ assertEquals("Sheet index (1) is out of range (0..0)", e.getMessage());
+ }
+ wb.createSheet();
+ name1.setSheetIndex(1);
+ assertEquals(1, name1.getSheetIndex());
}
public void testUnicodeNamedRange() {
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=723392&r1=723391&r2=723392&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 Thu Dec 4 10:38:00 2008
@@ -24,8 +24,11 @@
import junit.framework.TestCase;
import org.apache.poi.hssf.HSSFTestDataSamples;
+import org.apache.poi.hssf.model.HSSFFormulaParser;
+import org.apache.poi.hssf.record.formula.Ptg;
import org.apache.poi.ss.util.AreaReference;
import org.apache.poi.ss.util.CellReference;
+import org.apache.poi.ss.formula.FormulaType;
/**
*
@@ -36,498 +39,514 @@
*/
public final class TestNamedRange extends TestCase {
- private static HSSFWorkbook openSample(String sampleFileName) {
- return HSSFTestDataSamples.openSampleWorkbook(sampleFileName);
- }
-
- /** Test of TestCase method, of class test.RangeTest. */
- public void testNamedRange() {
- HSSFWorkbook wb = openSample("Simple.xls");
-
- //Creating new Named Range
- HSSFName newNamedRange = wb.createName();
-
- //Getting Sheet Name for the reference
- String sheetName = wb.getSheetName(0);
-
- //Setting its name
- newNamedRange.setNameName("RangeTest");
- //Setting its reference
- newNamedRange.setReference(sheetName + "!$D$4:$E$8");
-
- //Getting NAmed Range
- HSSFName namedRange1 = wb.getNameAt(0);
- //Getting it sheet name
- sheetName = namedRange1.getSheetName();
-
- // sanity check
- SanityChecker c = new SanityChecker();
- c.checkHSSFWorkbook(wb);
-
- wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
- HSSFName nm =wb.getNameAt(wb.getNameIndex("RangeTest"));
- assertTrue("Name is "+nm.getNameName(),"RangeTest".equals(nm.getNameName()));
- assertEquals(wb.getSheetName(0)+"!$D$4:$E$8", nm.getReference());
- }
-
- /**
- * Reads an excel file already containing a named range.
- * <p>
- * Addresses Bug <a href="http://issues.apache.org/bugzilla/show_bug.cgi?id=9632" target="_bug">#9632</a>
- */
- public void testNamedRead() {
- HSSFWorkbook wb = openSample("namedinput.xls");
-
- //Get index of the namedrange with the name = "NamedRangeName" , which was defined in input.xls as A1:D10
- int NamedRangeIndex = wb.getNameIndex("NamedRangeName");
-
- //Getting NAmed Range
- HSSFName namedRange1 = wb.getNameAt(NamedRangeIndex);
- String sheetName = wb.getSheetName(0);
-
- //Getting its reference
- String reference = namedRange1.getReference();
-
- assertEquals(sheetName+"!$A$1:$D$10", reference);
-
- HSSFName namedRange2 = wb.getNameAt(1);
-
- assertEquals(sheetName+"!$D$17:$G$27", namedRange2.getReference());
- assertEquals("SecondNamedRange", namedRange2.getNameName());
- }
-
- /**
- * Reads an excel file already containing a named range and updates it
- * <p>
- * Addresses Bug <a href="http://issues.apache.org/bugzilla/show_bug.cgi?id=16411" target="_bug">#16411</a>
- */
- public void testNamedReadModify() {
- HSSFWorkbook wb = openSample("namedinput.xls");
-
- HSSFName name = wb.getNameAt(0);
- String sheetName = wb.getSheetName(0);
-
- assertEquals(sheetName+"!$A$1:$D$10", name.getReference());
-
- name = wb.getNameAt(1);
- String newReference = sheetName +"!$A$1:$C$36";
-
- name.setReference(newReference);
- assertEquals(newReference, name.getReference());
- }
-
- /**
- * Test that multiple named ranges can be added written and read
- */
- public void testMultipleNamedWrite() {
- HSSFWorkbook wb = new HSSFWorkbook();
-
-
- wb.createSheet("testSheet1");
- String sheetName = wb.getSheetName(0);
-
- assertEquals("testSheet1", sheetName);
-
- //Creating new Named Range
- HSSFName newNamedRange = wb.createName();
-
- newNamedRange.setNameName("RangeTest");
- newNamedRange.setReference(sheetName + "!$D$4:$E$8");
-
- //Creating another new Named Range
- HSSFName newNamedRange2 = wb.createName();
-
- newNamedRange2.setNameName("AnotherTest");
- newNamedRange2.setReference(sheetName + "!$F$1:$G$6");
-
- wb.getNameAt(0);
-
- wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
- HSSFName nm =wb.getNameAt(wb.getNameIndex("RangeTest"));
- assertTrue("Name is "+nm.getNameName(),"RangeTest".equals(nm.getNameName()));
- assertTrue("Reference is "+nm.getReference(),(wb.getSheetName(0)+"!$D$4:$E$8").equals(nm.getReference()));
-
- nm = wb.getNameAt(wb.getNameIndex("AnotherTest"));
- assertTrue("Name is "+nm.getNameName(),"AnotherTest".equals(nm.getNameName()));
- assertTrue("Reference is "+nm.getReference(),newNamedRange2.getReference().equals(nm.getReference()));
- }
-
- /**
- * Test case provided by czhang@cambian.com (Chun Zhang)
- * <p>
- * Addresses Bug <a href="http://issues.apache.org/bugzilla/show_bug.cgi?id=13775" target="_bug">#13775</a>
- */
- public void testMultiNamedRange() {
-
- // Create a new workbook
- HSSFWorkbook wb = new HSSFWorkbook ();
-
-
- // Create a worksheet 'sheet1' in the new workbook
- wb.createSheet ();
- wb.setSheetName (0, "sheet1");
-
- // Create another worksheet 'sheet2' in the new workbook
- wb.createSheet ();
- wb.setSheetName (1, "sheet2");
-
- // Create a new named range for worksheet 'sheet1'
- HSSFName namedRange1 = wb.createName();
-
- // Set the name for the named range for worksheet 'sheet1'
- namedRange1.setNameName("RangeTest1");
-
- // Set the reference for the named range for worksheet 'sheet1'
- namedRange1.setReference("sheet1" + "!$A$1:$L$41");
-
- // Create a new named range for worksheet 'sheet2'
- HSSFName namedRange2 = wb.createName();
-
- // Set the name for the named range for worksheet 'sheet2'
- namedRange2.setNameName("RangeTest2");
-
- // Set the reference for the named range for worksheet 'sheet2'
- namedRange2.setReference("sheet2" + "!$A$1:$O$21");
-
- // Write the workbook to a file
- // Read the Excel file and verify its content
- wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
- HSSFName nm1 =wb.getNameAt(wb.getNameIndex("RangeTest1"));
- assertTrue("Name is "+nm1.getNameName(),"RangeTest1".equals(nm1.getNameName()));
- assertTrue("Reference is "+nm1.getReference(),(wb.getSheetName(0)+"!$A$1:$L$41").equals(nm1.getReference()));
-
- HSSFName nm2 =wb.getNameAt(wb.getNameIndex("RangeTest2"));
- assertTrue("Name is "+nm2.getNameName(),"RangeTest2".equals(nm2.getNameName()));
- assertTrue("Reference is "+nm2.getReference(),(wb.getSheetName(1)+"!$A$1:$O$21").equals(nm2.getReference()));
- }
-
- public void testUnicodeNamedRange() {
- HSSFWorkbook workBook = new HSSFWorkbook();
- workBook.createSheet("Test");
- HSSFName name = workBook.createName();
- name.setNameName("\u03B1");
- name.setReference("Test!$D$3:$E$8");
-
-
- HSSFWorkbook workBook2 = HSSFTestDataSamples.writeOutAndReadBack(workBook);
- HSSFName name2 = workBook2.getNameAt(0);
-
- assertEquals("\u03B1", name2.getNameName());
- assertEquals("Test!$D$3:$E$8", name2.getReference());
- }
-
- /**
- * Test to see if the print areas can be retrieved/created in memory
- */
- public void testSinglePrintArea() {
- HSSFWorkbook workbook = new HSSFWorkbook();
- workbook.createSheet("Test Print Area");
- String sheetName = workbook.getSheetName(0);
-
- String reference = "$A$1:$B$1";
- workbook.setPrintArea(0, reference);
-
- String retrievedPrintArea = workbook.getPrintArea(0);
-
- assertNotNull("Print Area not defined for first sheet", retrievedPrintArea);
- assertEquals("'" + sheetName + "'!$A$1:$B$1", retrievedPrintArea);
- }
-
- /**
- * For Convenience, don't force sheet names to be used
- */
- public void testSinglePrintAreaWOSheet()
- {
- HSSFWorkbook workbook = new HSSFWorkbook();
- workbook.createSheet("Test Print Area");
- String sheetName = workbook.getSheetName(0);
-
- String reference = "$A$1:$B$1";
- workbook.setPrintArea(0, reference);
-
- String retrievedPrintArea = workbook.getPrintArea(0);
-
- assertNotNull("Print Area not defined for first sheet", retrievedPrintArea);
- assertEquals("'" + sheetName + "'!" + reference, retrievedPrintArea);
- }
-
- /**
- * Test to see if the print area can be retrieved from an excel created file
- */
- public void testPrintAreaFileRead() {
- HSSFWorkbook workbook = openSample("SimpleWithPrintArea.xls");
-
- String sheetName = workbook.getSheetName(0);
- String reference = sheetName+"!$A$1:$C$5";
-
- assertEquals(reference, workbook.getPrintArea(0));
- }
-
- /**
- * Test to see if the print area made it to the file
- */
- public void testPrintAreaFile() {
- HSSFWorkbook workbook = new HSSFWorkbook();
- workbook.createSheet("Test Print Area");
- String sheetName = workbook.getSheetName(0);
-
-
- String reference = "$A$1:$B$1";
- workbook.setPrintArea(0, reference);
-
- workbook = HSSFTestDataSamples.writeOutAndReadBack(workbook);
-
- String retrievedPrintArea = workbook.getPrintArea(0);
- assertNotNull("Print Area not defined for first sheet", retrievedPrintArea);
- assertEquals("References Match", "'" + sheetName + "'!$A$1:$B$1", retrievedPrintArea);
- }
-
- /**
- * Test to see if multiple print areas made it to the file
- */
- public void testMultiplePrintAreaFile() {
- HSSFWorkbook workbook = new HSSFWorkbook();
-
- workbook.createSheet("Sheet1");
- workbook.createSheet("Sheet2");
- workbook.createSheet("Sheet3");
- String reference1 = "$A$1:$B$1";
- String reference2 = "$B$2:$D$5";
- String reference3 = "$D$2:$F$5";
-
- workbook.setPrintArea(0, reference1);
- workbook.setPrintArea(1, reference2);
- workbook.setPrintArea(2, reference3);
-
- //Check created print areas
- String retrievedPrintArea;
-
- retrievedPrintArea = workbook.getPrintArea(0);
- assertNotNull("Print Area Not Found (Sheet 1)", retrievedPrintArea);
- assertEquals("Sheet1!" + reference1, retrievedPrintArea);
-
- retrievedPrintArea = workbook.getPrintArea(1);
- assertNotNull("Print Area Not Found (Sheet 2)", retrievedPrintArea);
- assertEquals("Sheet2!" + reference2, retrievedPrintArea);
-
- retrievedPrintArea = workbook.getPrintArea(2);
- assertNotNull("Print Area Not Found (Sheet 3)", retrievedPrintArea);
- assertEquals("Sheet3!" + reference3, retrievedPrintArea);
-
- // Check print areas after re-reading workbook
- workbook = HSSFTestDataSamples.writeOutAndReadBack(workbook);
-
- retrievedPrintArea = workbook.getPrintArea(0);
- assertNotNull("Print Area Not Found (Sheet 1)", retrievedPrintArea);
- assertEquals("Sheet1!" + reference1, retrievedPrintArea);
-
- retrievedPrintArea = workbook.getPrintArea(1);
- assertNotNull("Print Area Not Found (Sheet 2)", retrievedPrintArea);
- assertEquals("Sheet2!" + reference2, retrievedPrintArea);
-
- retrievedPrintArea = workbook.getPrintArea(2);
- assertNotNull("Print Area Not Found (Sheet 3)", retrievedPrintArea);
- assertEquals("Sheet3!" + reference3, retrievedPrintArea);
- }
-
- /**
- * Tests the setting of print areas with coordinates (Row/Column designations)
- *
- */
- public void testPrintAreaCoords(){
- HSSFWorkbook workbook = new HSSFWorkbook();
- workbook.createSheet("Test Print Area");
- String sheetName = workbook.getSheetName(0);
-
- workbook.setPrintArea(0, 0, 1, 0, 0);
-
- String retrievedPrintArea = workbook.getPrintArea(0);
-
- assertNotNull("Print Area not defined for first sheet", 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();
- workbook.createSheet("Test Print Area");
-
- String reference = "$A$1:$B$1,$D$1:$F$2";
- workbook.setPrintArea(0, reference);
- String retrievedPrintArea = workbook.getPrintArea(0);
- assertNotNull("Print Area not defined for first sheet", retrievedPrintArea);
- assertEquals("'Test Print Area'!$A$1:$B$1,'Test Print Area'!$D$1:$F$2", retrievedPrintArea);
- }
-
- /**
- * Verifies an existing print area is deleted
- *
- */
- public void testPrintAreaRemove() {
- HSSFWorkbook workbook = new HSSFWorkbook();
- workbook.createSheet("Test Print Area");
- workbook.getSheetName(0);
-
- workbook.setPrintArea(0, 0, 1, 0, 0);
-
- String retrievedPrintArea = workbook.getPrintArea(0);
-
- assertNotNull("Print Area not defined for first sheet", retrievedPrintArea);
-
- workbook.removePrintArea(0);
- assertNull("PrintArea was not removed", workbook.getPrintArea(0));
- }
-
- /**
- * Verifies correct functioning for "single cell named range" (aka "named cell")
- */
- public void testNamedCell_1() {
-
- // setup for this testcase
- String sheetName = "Test Named Cell";
- String cellName = "A name for a named cell";
- String cellValue = "TEST Value";
- HSSFWorkbook wb = new HSSFWorkbook();
- HSSFSheet sheet = wb.createSheet(sheetName);
- sheet.createRow(0).createCell(0).setCellValue(new HSSFRichTextString(cellValue));
-
- // create named range for a single cell using areareference
- HSSFName namedCell = wb.createName();
- namedCell.setNameName(cellName);
- String reference = "'" + sheetName + "'" + "!A1:A1";
- namedCell.setReference(reference);
-
- // retrieve the newly created named range
- int namedCellIdx = wb.getNameIndex(cellName);
- HSSFName aNamedCell = wb.getNameAt(namedCellIdx);
- assertNotNull(aNamedCell);
-
- // retrieve the cell at the named range and test its contents
- AreaReference aref = new AreaReference(aNamedCell.getReference());
- assertTrue("Should be exactly 1 cell in the named cell :'" +cellName+"'", aref.isSingleCell());
-
- CellReference cref = aref.getFirstCell();
- assertNotNull(cref);
- HSSFSheet s = wb.getSheet(cref.getSheetName());
- assertNotNull(s);
- HSSFRow r = sheet.getRow(cref.getRow());
- HSSFCell c = r.getCell(cref.getCol());
- String contents = c.getRichStringCellValue().getString();
- assertEquals("Contents of cell retrieved by its named reference", contents, cellValue);
- }
-
- /**
- * Verifies correct functioning for "single cell named range" (aka "named cell")
- */
- public void testNamedCell_2() {
-
- // setup for this testcase
- String sname = "TestSheet", cname = "TestName", cvalue = "TestVal";
- HSSFWorkbook wb = new HSSFWorkbook();
- HSSFSheet sheet = wb.createSheet(sname);
- sheet.createRow(0).createCell(0).setCellValue(new HSSFRichTextString(cvalue));
-
- // create named range for a single cell using cellreference
- HSSFName namedCell = wb.createName();
- namedCell.setNameName(cname);
- String reference = sname+"!A1";
- namedCell.setReference(reference);
-
- // retrieve the newly created named range
- int namedCellIdx = wb.getNameIndex(cname);
- HSSFName aNamedCell = wb.getNameAt(namedCellIdx);
- assertNotNull(aNamedCell);
-
- // retrieve the cell at the named range and test its contents
- CellReference cref = new CellReference(aNamedCell.getReference());
- assertNotNull(cref);
- HSSFSheet s = wb.getSheet(cref.getSheetName());
- HSSFRow r = sheet.getRow(cref.getRow());
- HSSFCell c = r.getCell(cref.getCol());
- String contents = c.getRichStringCellValue().getString();
- assertEquals("Contents of cell retrieved by its named reference", contents, cvalue);
- }
-
- public void testDeletedReference() throws Exception {
- HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("24207.xls");
- assertEquals(2, wb.getNumberOfNames());
-
- HSSFName name1 = wb.getNameAt(0);
- assertEquals("a", name1.getNameName());
- assertEquals("Sheet1!$A$1", name1.getReference());
- new AreaReference(name1.getReference());
- assertTrue("Successfully constructed first reference", true);
-
- HSSFName name2 = wb.getNameAt(1);
- assertEquals("b", name2.getNameName());
- assertEquals("Sheet1!#REF!", name2.getReference());
- assertTrue(name2.isDeleted());
- try {
- new AreaReference(name2.getReference());
- fail("attempt to supply an invalid reference to AreaReference constructor results in exception");
- } catch (StringIndexOutOfBoundsException e) { // TODO - use a different exception for this condition
- // expected during successful test
- }
- }
-
- public void testRepeatingRowsAndColumsNames() {
- // First test that setting RR&C for same sheet more than once only creates a
- // single Print_Titles built-in record
- HSSFWorkbook wb = new HSSFWorkbook();
- HSSFSheet sheet = wb.createSheet("FirstSheet");
-
- // set repeating rows and columns twice for the first sheet
- for (int i = 0; i < 2; i++) {
- wb.setRepeatingRowsAndColumns(0, 0, 0, 0, 3-1);
- sheet.createFreezePane(0, 3);
- }
- assertEquals(1, wb.getNumberOfNames());
- HSSFName nr1 = wb.getNameAt(0);
-
- assertEquals("Print_Titles", nr1.getNameName());
- if (false) {
- // TODO - full column references not rendering properly, absolute markers not present either
- assertEquals("FirstSheet!$A:$A,FirstSheet!$1:$3", nr1.getReference());
- } else {
- assertEquals("FirstSheet!A:A,FirstSheet!$A$1:$IV$3", nr1.getReference());
- }
-
- // Save and re-open
- HSSFWorkbook nwb = HSSFTestDataSamples.writeOutAndReadBack(wb);
-
- assertEquals(1, nwb.getNumberOfNames());
- nr1 = nwb.getNameAt(0);
-
- assertEquals("Print_Titles", nr1.getNameName());
- assertEquals("FirstSheet!A:A,FirstSheet!$A$1:$IV$3", nr1.getReference());
-
- // check that setting RR&C on a second sheet causes a new Print_Titles built-in
- // name to be created
- sheet = nwb.createSheet("SecondSheet");
- nwb.setRepeatingRowsAndColumns(1, 1, 2, 0, 0);
-
- assertEquals(2, nwb.getNumberOfNames());
- HSSFName nr2 = nwb.getNameAt(1);
-
- assertEquals("Print_Titles", nr2.getNameName());
- assertEquals("SecondSheet!B:C,SecondSheet!$A$1:$IV$1", nr2.getReference());
-
- if (false) {
- // In case you fancy checking in excel, to ensure it
- // won't complain about the file now
- try {
- File tempFile = File.createTempFile("POI-45126-", ".xls");
- FileOutputStream fout = new FileOutputStream(tempFile);
- nwb.write(fout);
- fout.close();
- System.out.println("check out " + tempFile.getAbsolutePath());
- } catch (IOException e) {
- throw new RuntimeException(e);
- }
- }
- }
+ private static HSSFWorkbook openSample(String sampleFileName) {
+ return HSSFTestDataSamples.openSampleWorkbook(sampleFileName);
+ }
+
+ /** Test of TestCase method, of class test.RangeTest. */
+ public void testNamedRange() {
+ HSSFWorkbook wb = openSample("Simple.xls");
+
+ //Creating new Named Range
+ HSSFName newNamedRange = wb.createName();
+
+ //Getting Sheet Name for the reference
+ String sheetName = wb.getSheetName(0);
+
+ //Setting its name
+ newNamedRange.setNameName("RangeTest");
+ //Setting its reference
+ newNamedRange.setReference(sheetName + "!$D$4:$E$8");
+
+ //Getting NAmed Range
+ HSSFName namedRange1 = wb.getNameAt(0);
+ //Getting it sheet name
+ sheetName = namedRange1.getSheetName();
+
+ // sanity check
+ SanityChecker c = new SanityChecker();
+ c.checkHSSFWorkbook(wb);
+
+ wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
+ HSSFName nm =wb.getNameAt(wb.getNameIndex("RangeTest"));
+ assertTrue("Name is "+nm.getNameName(),"RangeTest".equals(nm.getNameName()));
+ assertEquals(wb.getSheetName(0)+"!$D$4:$E$8", nm.getReference());
+ }
+
+ /**
+ * Reads an excel file already containing a named range.
+ * <p>
+ * Addresses Bug <a href="http://issues.apache.org/bugzilla/show_bug.cgi?id=9632" target="_bug">#9632</a>
+ */
+ public void testNamedRead() {
+ HSSFWorkbook wb = openSample("namedinput.xls");
+
+ //Get index of the namedrange with the name = "NamedRangeName" , which was defined in input.xls as A1:D10
+ int NamedRangeIndex = wb.getNameIndex("NamedRangeName");
+
+ //Getting NAmed Range
+ HSSFName namedRange1 = wb.getNameAt(NamedRangeIndex);
+ String sheetName = wb.getSheetName(0);
+
+ //Getting its reference
+ String reference = namedRange1.getReference();
+
+ assertEquals(sheetName+"!$A$1:$D$10", reference);
+
+ HSSFName namedRange2 = wb.getNameAt(1);
+
+ assertEquals(sheetName+"!$D$17:$G$27", namedRange2.getReference());
+ assertEquals("SecondNamedRange", namedRange2.getNameName());
+ }
+
+ /**
+ * Reads an excel file already containing a named range and updates it
+ * <p>
+ * Addresses Bug <a href="http://issues.apache.org/bugzilla/show_bug.cgi?id=16411" target="_bug">#16411</a>
+ */
+ public void testNamedReadModify() {
+ HSSFWorkbook wb = openSample("namedinput.xls");
+
+ HSSFName name = wb.getNameAt(0);
+ String sheetName = wb.getSheetName(0);
+
+ assertEquals(sheetName+"!$A$1:$D$10", name.getReference());
+
+ name = wb.getNameAt(1);
+ String newReference = sheetName +"!$A$1:$C$36";
+
+ name.setReference(newReference);
+ assertEquals(newReference, name.getReference());
+ }
+
+ /**
+ * Test that multiple named ranges can be added written and read
+ */
+ public void testMultipleNamedWrite() {
+ HSSFWorkbook wb = new HSSFWorkbook();
+
+
+ wb.createSheet("testSheet1");
+ String sheetName = wb.getSheetName(0);
+
+ assertEquals("testSheet1", sheetName);
+
+ //Creating new Named Range
+ HSSFName newNamedRange = wb.createName();
+
+ newNamedRange.setNameName("RangeTest");
+ newNamedRange.setReference(sheetName + "!$D$4:$E$8");
+
+ //Creating another new Named Range
+ HSSFName newNamedRange2 = wb.createName();
+
+ newNamedRange2.setNameName("AnotherTest");
+ newNamedRange2.setReference(sheetName + "!$F$1:$G$6");
+
+ wb.getNameAt(0);
+
+ wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
+ HSSFName nm =wb.getNameAt(wb.getNameIndex("RangeTest"));
+ assertTrue("Name is "+nm.getNameName(),"RangeTest".equals(nm.getNameName()));
+ assertTrue("Reference is "+nm.getReference(),(wb.getSheetName(0)+"!$D$4:$E$8").equals(nm.getReference()));
+
+ nm = wb.getNameAt(wb.getNameIndex("AnotherTest"));
+ assertTrue("Name is "+nm.getNameName(),"AnotherTest".equals(nm.getNameName()));
+ assertTrue("Reference is "+nm.getReference(),newNamedRange2.getReference().equals(nm.getReference()));
+ }
+
+ /**
+ * Test case provided by czhang@cambian.com (Chun Zhang)
+ * <p>
+ * Addresses Bug <a href="http://issues.apache.org/bugzilla/show_bug.cgi?id=13775" target="_bug">#13775</a>
+ */
+ public void testMultiNamedRange() {
+
+ // Create a new workbook
+ HSSFWorkbook wb = new HSSFWorkbook ();
+
+
+ // Create a worksheet 'sheet1' in the new workbook
+ wb.createSheet ();
+ wb.setSheetName (0, "sheet1");
+
+ // Create another worksheet 'sheet2' in the new workbook
+ wb.createSheet ();
+ wb.setSheetName (1, "sheet2");
+
+ // Create a new named range for worksheet 'sheet1'
+ HSSFName namedRange1 = wb.createName();
+
+ // Set the name for the named range for worksheet 'sheet1'
+ namedRange1.setNameName("RangeTest1");
+
+ // Set the reference for the named range for worksheet 'sheet1'
+ namedRange1.setReference("sheet1" + "!$A$1:$L$41");
+
+ // Create a new named range for worksheet 'sheet2'
+ HSSFName namedRange2 = wb.createName();
+
+ // Set the name for the named range for worksheet 'sheet2'
+ namedRange2.setNameName("RangeTest2");
+
+ // Set the reference for the named range for worksheet 'sheet2'
+ namedRange2.setReference("sheet2" + "!$A$1:$O$21");
+
+ // Write the workbook to a file
+ // Read the Excel file and verify its content
+ wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
+ HSSFName nm1 =wb.getNameAt(wb.getNameIndex("RangeTest1"));
+ assertTrue("Name is "+nm1.getNameName(),"RangeTest1".equals(nm1.getNameName()));
+ assertTrue("Reference is "+nm1.getReference(),(wb.getSheetName(0)+"!$A$1:$L$41").equals(nm1.getReference()));
+
+ HSSFName nm2 =wb.getNameAt(wb.getNameIndex("RangeTest2"));
+ assertTrue("Name is "+nm2.getNameName(),"RangeTest2".equals(nm2.getNameName()));
+ assertTrue("Reference is "+nm2.getReference(),(wb.getSheetName(1)+"!$A$1:$O$21").equals(nm2.getReference()));
+ }
+
+ public void testUnicodeNamedRange() {
+ HSSFWorkbook workBook = new HSSFWorkbook();
+ workBook.createSheet("Test");
+ HSSFName name = workBook.createName();
+ name.setNameName("\u03B1");
+ name.setReference("Test!$D$3:$E$8");
+
+
+ HSSFWorkbook workBook2 = HSSFTestDataSamples.writeOutAndReadBack(workBook);
+ HSSFName name2 = workBook2.getNameAt(0);
+
+ assertEquals("\u03B1", name2.getNameName());
+ assertEquals("Test!$D$3:$E$8", name2.getReference());
+ }
+
+ /**
+ * Test to see if the print areas can be retrieved/created in memory
+ */
+ public void testSinglePrintArea() {
+ HSSFWorkbook workbook = new HSSFWorkbook();
+ workbook.createSheet("Test Print Area");
+ String sheetName = workbook.getSheetName(0);
+
+ String reference = "$A$1:$B$1";
+ workbook.setPrintArea(0, reference);
+
+ String retrievedPrintArea = workbook.getPrintArea(0);
+
+ assertNotNull("Print Area not defined for first sheet", retrievedPrintArea);
+ assertEquals("'" + sheetName + "'!$A$1:$B$1", retrievedPrintArea);
+ }
+
+ /**
+ * For Convenience, don't force sheet names to be used
+ */
+ public void testSinglePrintAreaWOSheet()
+ {
+ HSSFWorkbook workbook = new HSSFWorkbook();
+ workbook.createSheet("Test Print Area");
+ String sheetName = workbook.getSheetName(0);
+
+ String reference = "$A$1:$B$1";
+ workbook.setPrintArea(0, reference);
+
+ String retrievedPrintArea = workbook.getPrintArea(0);
+
+ assertNotNull("Print Area not defined for first sheet", retrievedPrintArea);
+ assertEquals("'" + sheetName + "'!" + reference, retrievedPrintArea);
+ }
+
+ /**
+ * Test to see if the print area can be retrieved from an excel created file
+ */
+ public void testPrintAreaFileRead() {
+ HSSFWorkbook workbook = openSample("SimpleWithPrintArea.xls");
+
+ String sheetName = workbook.getSheetName(0);
+ String reference = sheetName+"!$A$1:$C$5";
+
+ assertEquals(reference, workbook.getPrintArea(0));
+ }
+
+ /**
+ * Test to see if the print area made it to the file
+ */
+ public void testPrintAreaFile() {
+ HSSFWorkbook workbook = new HSSFWorkbook();
+ workbook.createSheet("Test Print Area");
+ String sheetName = workbook.getSheetName(0);
+
+
+ String reference = "$A$1:$B$1";
+ workbook.setPrintArea(0, reference);
+
+ workbook = HSSFTestDataSamples.writeOutAndReadBack(workbook);
+
+ String retrievedPrintArea = workbook.getPrintArea(0);
+ assertNotNull("Print Area not defined for first sheet", retrievedPrintArea);
+ assertEquals("References Match", "'" + sheetName + "'!$A$1:$B$1", retrievedPrintArea);
+ }
+
+ /**
+ * Test to see if multiple print areas made it to the file
+ */
+ public void testMultiplePrintAreaFile() {
+ HSSFWorkbook workbook = new HSSFWorkbook();
+
+ workbook.createSheet("Sheet1");
+ workbook.createSheet("Sheet2");
+ workbook.createSheet("Sheet3");
+ String reference1 = "$A$1:$B$1";
+ String reference2 = "$B$2:$D$5";
+ String reference3 = "$D$2:$F$5";
+
+ workbook.setPrintArea(0, reference1);
+ workbook.setPrintArea(1, reference2);
+ workbook.setPrintArea(2, reference3);
+
+ //Check created print areas
+ String retrievedPrintArea;
+
+ retrievedPrintArea = workbook.getPrintArea(0);
+ assertNotNull("Print Area Not Found (Sheet 1)", retrievedPrintArea);
+ assertEquals("Sheet1!" + reference1, retrievedPrintArea);
+
+ retrievedPrintArea = workbook.getPrintArea(1);
+ assertNotNull("Print Area Not Found (Sheet 2)", retrievedPrintArea);
+ assertEquals("Sheet2!" + reference2, retrievedPrintArea);
+
+ retrievedPrintArea = workbook.getPrintArea(2);
+ assertNotNull("Print Area Not Found (Sheet 3)", retrievedPrintArea);
+ assertEquals("Sheet3!" + reference3, retrievedPrintArea);
+
+ // Check print areas after re-reading workbook
+ workbook = HSSFTestDataSamples.writeOutAndReadBack(workbook);
+
+ retrievedPrintArea = workbook.getPrintArea(0);
+ assertNotNull("Print Area Not Found (Sheet 1)", retrievedPrintArea);
+ assertEquals("Sheet1!" + reference1, retrievedPrintArea);
+
+ retrievedPrintArea = workbook.getPrintArea(1);
+ assertNotNull("Print Area Not Found (Sheet 2)", retrievedPrintArea);
+ assertEquals("Sheet2!" + reference2, retrievedPrintArea);
+
+ retrievedPrintArea = workbook.getPrintArea(2);
+ assertNotNull("Print Area Not Found (Sheet 3)", retrievedPrintArea);
+ assertEquals("Sheet3!" + reference3, retrievedPrintArea);
+ }
+
+ /**
+ * Tests the setting of print areas with coordinates (Row/Column designations)
+ *
+ */
+ public void testPrintAreaCoords(){
+ HSSFWorkbook workbook = new HSSFWorkbook();
+ workbook.createSheet("Test Print Area");
+ String sheetName = workbook.getSheetName(0);
+
+ workbook.setPrintArea(0, 0, 1, 0, 0);
+
+ String retrievedPrintArea = workbook.getPrintArea(0);
+
+ assertNotNull("Print Area not defined for first sheet", 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();
+ workbook.createSheet("Test Print Area");
+
+ String reference = "$A$1:$B$1,$D$1:$F$2";
+ workbook.setPrintArea(0, reference);
+ String retrievedPrintArea = workbook.getPrintArea(0);
+ assertNotNull("Print Area not defined for first sheet", retrievedPrintArea);
+ assertEquals("'Test Print Area'!$A$1:$B$1,'Test Print Area'!$D$1:$F$2", retrievedPrintArea);
+ }
+
+ /**
+ * Verifies an existing print area is deleted
+ *
+ */
+ public void testPrintAreaRemove() {
+ HSSFWorkbook workbook = new HSSFWorkbook();
+ workbook.createSheet("Test Print Area");
+ workbook.getSheetName(0);
+
+ workbook.setPrintArea(0, 0, 1, 0, 0);
+
+ String retrievedPrintArea = workbook.getPrintArea(0);
+
+ assertNotNull("Print Area not defined for first sheet", retrievedPrintArea);
+
+ workbook.removePrintArea(0);
+ assertNull("PrintArea was not removed", workbook.getPrintArea(0));
+ }
+
+ /**
+ * Verifies correct functioning for "single cell named range" (aka "named cell")
+ */
+ public void testNamedCell_1() {
+
+ // setup for this testcase
+ String sheetName = "Test Named Cell";
+ String cellName = "A name for a named cell";
+ String cellValue = "TEST Value";
+ HSSFWorkbook wb = new HSSFWorkbook();
+ HSSFSheet sheet = wb.createSheet(sheetName);
+ sheet.createRow(0).createCell(0).setCellValue(new HSSFRichTextString(cellValue));
+
+ // create named range for a single cell using areareference
+ HSSFName namedCell = wb.createName();
+ namedCell.setNameName(cellName);
+ String reference = "'" + sheetName + "'" + "!A1:A1";
+ namedCell.setReference(reference);
+
+ // retrieve the newly created named range
+ int namedCellIdx = wb.getNameIndex(cellName);
+ HSSFName aNamedCell = wb.getNameAt(namedCellIdx);
+ assertNotNull(aNamedCell);
+
+ // retrieve the cell at the named range and test its contents
+ AreaReference aref = new AreaReference(aNamedCell.getReference());
+ assertTrue("Should be exactly 1 cell in the named cell :'" +cellName+"'", aref.isSingleCell());
+
+ CellReference cref = aref.getFirstCell();
+ assertNotNull(cref);
+ HSSFSheet s = wb.getSheet(cref.getSheetName());
+ assertNotNull(s);
+ HSSFRow r = sheet.getRow(cref.getRow());
+ HSSFCell c = r.getCell(cref.getCol());
+ String contents = c.getRichStringCellValue().getString();
+ assertEquals("Contents of cell retrieved by its named reference", contents, cellValue);
+ }
+
+ /**
+ * Verifies correct functioning for "single cell named range" (aka "named cell")
+ */
+ public void testNamedCell_2() {
+
+ // setup for this testcase
+ String sname = "TestSheet", cname = "TestName", cvalue = "TestVal";
+ HSSFWorkbook wb = new HSSFWorkbook();
+ HSSFSheet sheet = wb.createSheet(sname);
+ sheet.createRow(0).createCell(0).setCellValue(new HSSFRichTextString(cvalue));
+
+ // create named range for a single cell using cellreference
+ HSSFName namedCell = wb.createName();
+ namedCell.setNameName(cname);
+ String reference = sname+"!A1";
+ namedCell.setReference(reference);
+
+ // retrieve the newly created named range
+ int namedCellIdx = wb.getNameIndex(cname);
+ HSSFName aNamedCell = wb.getNameAt(namedCellIdx);
+ assertNotNull(aNamedCell);
+
+ // retrieve the cell at the named range and test its contents
+ CellReference cref = new CellReference(aNamedCell.getReference());
+ assertNotNull(cref);
+ HSSFSheet s = wb.getSheet(cref.getSheetName());
+ HSSFRow r = sheet.getRow(cref.getRow());
+ HSSFCell c = r.getCell(cref.getCol());
+ String contents = c.getRichStringCellValue().getString();
+ assertEquals("Contents of cell retrieved by its named reference", contents, cvalue);
+ }
+
+ public void testDeletedReference() throws Exception {
+ HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("24207.xls");
+ assertEquals(2, wb.getNumberOfNames());
+
+ HSSFName name1 = wb.getNameAt(0);
+ assertEquals("a", name1.getNameName());
+ assertEquals("Sheet1!$A$1", name1.getReference());
+ new AreaReference(name1.getReference());
+ assertTrue("Successfully constructed first reference", true);
+
+ HSSFName name2 = wb.getNameAt(1);
+ assertEquals("b", name2.getNameName());
+ assertEquals("Sheet1!#REF!", name2.getReference());
+ assertTrue(name2.isDeleted());
+ try {
+ new AreaReference(name2.getReference());
+ fail("attempt to supply an invalid reference to AreaReference constructor results in exception");
+ } catch (StringIndexOutOfBoundsException e) { // TODO - use a different exception for this condition
+ // expected during successful test
+ }
+ }
+
+ public void testRepeatingRowsAndColumsNames() {
+ // First test that setting RR&C for same sheet more than once only creates a
+ // single Print_Titles built-in record
+ HSSFWorkbook wb = new HSSFWorkbook();
+ HSSFSheet sheet = wb.createSheet("FirstSheet");
+
+ // set repeating rows and columns twice for the first sheet
+ for (int i = 0; i < 2; i++) {
+ wb.setRepeatingRowsAndColumns(0, 0, 0, 0, 3-1);
+ sheet.createFreezePane(0, 3);
+ }
+ assertEquals(1, wb.getNumberOfNames());
+ HSSFName nr1 = wb.getNameAt(0);
+
+ assertEquals("Print_Titles", nr1.getNameName());
+ if (false) {
+ // TODO - full column references not rendering properly, absolute markers not present either
+ assertEquals("FirstSheet!$A:$A,FirstSheet!$1:$3", nr1.getReference());
+ } else {
+ assertEquals("FirstSheet!A:A,FirstSheet!$A$1:$IV$3", nr1.getReference());
+ }
+
+ // Save and re-open
+ HSSFWorkbook nwb = HSSFTestDataSamples.writeOutAndReadBack(wb);
+
+ assertEquals(1, nwb.getNumberOfNames());
+ nr1 = nwb.getNameAt(0);
+
+ assertEquals("Print_Titles", nr1.getNameName());
+ assertEquals("FirstSheet!A:A,FirstSheet!$A$1:$IV$3", nr1.getReference());
+
+ // check that setting RR&C on a second sheet causes a new Print_Titles built-in
+ // name to be created
+ sheet = nwb.createSheet("SecondSheet");
+ nwb.setRepeatingRowsAndColumns(1, 1, 2, 0, 0);
+
+ assertEquals(2, nwb.getNumberOfNames());
+ HSSFName nr2 = nwb.getNameAt(1);
+
+ assertEquals("Print_Titles", nr2.getNameName());
+ assertEquals("SecondSheet!B:C,SecondSheet!$A$1:$IV$1", nr2.getReference());
+
+ if (false) {
+ // In case you fancy checking in excel, to ensure it
+ // won't complain about the file now
+ try {
+ File tempFile = File.createTempFile("POI-45126-", ".xls");
+ FileOutputStream fout = new FileOutputStream(tempFile);
+ nwb.write(fout);
+ fout.close();
+ System.out.println("check out " + tempFile.getAbsolutePath());
+ } catch (IOException e) {
+ throw new RuntimeException(e);
+ }
+ }
+ }
+
+ /**
+ * When setting A1 type of referencese HSSFName.setRefersToFormula
+ * must set the type of operands to Ptg.CLASS_REF,
+ * otherwise created named don't appear in the dropdown to the left opf formula bar in Excel
+ */
+ public void testTypeOfRootPtg(){
+ HSSFWorkbook wb = new HSSFWorkbook();
+ wb.createSheet("CSCO");
+
+ Ptg[] ptgs = HSSFFormulaParser.parse("CSCO!$E$71", wb, FormulaType.NAMEDRANGE);
+ for (int i = 0; i < ptgs.length; i++) {
+ assertEquals('R', ptgs[i].getRVAType());
+ }
+
+ }
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org