Bug 63509: Allow to format a CellReference without sheet-name for ignoredErrors.
git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1861817 13f79535-47bb-0310-9956-ffa450edef68
diff --git a/src/java/org/apache/poi/ss/util/CellReference.java b/src/java/org/apache/poi/ss/util/CellReference.java
index 067cdeb..6e6dbe6 100644
--- a/src/java/org/apache/poi/ss/util/CellReference.java
+++ b/src/java/org/apache/poi/ss/util/CellReference.java
@@ -487,8 +487,28 @@
* @return the text representation of this cell reference as it would appear in a formula.
*/
public String formatAsString() {
+ return formatAsString(true);
+ }
+
+ /**
+ * Returns a text representation of this cell reference and allows to control
+ * if the sheetname is included in the reference.
+ *
+ * <p>
+ * Example return values:
+ * <table border="0" cellpadding="1" cellspacing="0" summary="Example return values">
+ * <tr><th align='left'>Result</th><th align='left'>Comment</th></tr>
+ * <tr><td>A1</td><td>Cell reference without sheet</td></tr>
+ * <tr><td>Sheet1!A1</td><td>Standard sheet name</td></tr>
+ * <tr><td>'O''Brien''s Sales'!A1' </td><td>Sheet name with special characters</td></tr>
+ * </table>
+ * @param includeSheetName If true and there is a sheet name set for this cell reference,
+ * the reference is prefixed with the sheet name and '!'
+ * @return the text representation of this cell reference as it would appear in a formula.
+ */
+ public String formatAsString(boolean includeSheetName) {
StringBuilder sb = new StringBuilder(32);
- if(_sheetName != null) {
+ if(includeSheetName && _sheetName != null) {
SheetNameFormatter.appendFormat(sb, _sheetName);
sb.append(SHEET_NAME_DELIMITER);
}
diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java
index d0a43af..f897d80 100644
--- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java
+++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java
@@ -4555,7 +4555,7 @@
* @param ignoredErrorTypes Types of error to ignore there.
*/
public void addIgnoredErrors(CellReference cell, IgnoredErrorType... ignoredErrorTypes) {
- addIgnoredErrors(cell.formatAsString(), ignoredErrorTypes);
+ addIgnoredErrors(cell.formatAsString(false), ignoredErrorTypes);
}
/**
diff --git a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFBugs.java b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFBugs.java
index 65227c0..e0bf0f4 100644
--- a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFBugs.java
+++ b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFBugs.java
@@ -101,6 +101,7 @@
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.FormulaError;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
+import org.apache.poi.ss.usermodel.IgnoredErrorType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Name;
import org.apache.poi.ss.usermodel.PrintSetup;
@@ -3462,4 +3463,30 @@
assertEquals(1, ctMergeCellCount);
assertEquals(1, ctMergeCellListSize);
}
+
+ @Test
+ public void testBug63509() throws IOException {
+ XSSFWorkbook workbook = new XSSFWorkbook();
+
+ XSSFSheet sheet = workbook.createSheet("sheet1");
+
+ Row row = sheet.createRow(0);
+
+ Cell cell = row.createCell(0);
+ cell.setCellValue("1000");
+
+ // This causes the error
+ sheet.addIgnoredErrors(new CellReference(cell), IgnoredErrorType.NUMBER_STORED_AS_TEXT);
+
+ // Workaround
+ // sheet.addIgnoredErrors(new CellReference(cell.getRowIndex(), cell.getColumnIndex(), false, false),
+ // IgnoredErrorType.NUMBER_STORED_AS_TEXT);
+
+ /*File file = new File("/tmp/63509.xlsx");
+ try(FileOutputStream outputStream = new FileOutputStream(file)) {
+ workbook.write(outputStream);
+ }*/
+
+ workbook.close();
+ }
}
diff --git a/src/testcases/org/apache/poi/ss/util/TestCellReference.java b/src/testcases/org/apache/poi/ss/util/TestCellReference.java
index 71e2218..095eb70 100644
--- a/src/testcases/org/apache/poi/ss/util/TestCellReference.java
+++ b/src/testcases/org/apache/poi/ss/util/TestCellReference.java
@@ -54,18 +54,28 @@
cellReference = new CellReference(row, col);
assertEquals("A1", cellReference.formatAsString());
+ assertEquals("A1", cellReference.formatAsString(true));
+ assertEquals("A1", cellReference.formatAsString(false));
cellReference = new CellReference(row, col, absRow, absCol);
assertEquals("A$1", cellReference.formatAsString());
+ assertEquals("A$1", cellReference.formatAsString(true));
+ assertEquals("A$1", cellReference.formatAsString(false));
cellReference = new CellReference(row, (short)col);
assertEquals("A1", cellReference.formatAsString());
+ assertEquals("A1", cellReference.formatAsString(true));
+ assertEquals("A1", cellReference.formatAsString(false));
cellReference = new CellReference(cellRef);
assertEquals("A1", cellReference.formatAsString());
+ assertEquals("A1", cellReference.formatAsString(true));
+ assertEquals("A1", cellReference.formatAsString(false));
cellReference = new CellReference(sheet, row, col, absRow, absCol);
assertEquals("Sheet1!A$1", cellReference.formatAsString());
+ assertEquals("Sheet1!A$1", cellReference.formatAsString(true));
+ assertEquals("A$1", cellReference.formatAsString(false));
cellReference = new CellReference(sheet+"!$A1");
assertFalse(cellReference.isRowAbsolute());
@@ -95,18 +105,26 @@
cellReference = new CellReference(null, 0, 0, false, false);
assertEquals("A1", cellReference.formatAsString());
+ assertEquals("A1", cellReference.formatAsString(true));
+ assertEquals("A1", cellReference.formatAsString(false));
//absolute references
cellReference = new CellReference(null, 0, 0, true, false);
assertEquals("A$1", cellReference.formatAsString());
+ assertEquals("A$1", cellReference.formatAsString(true));
+ assertEquals("A$1", cellReference.formatAsString(false));
//sheet name with no spaces
cellReference = new CellReference("Sheet1", 0, 0, true, false);
assertEquals("Sheet1!A$1", cellReference.formatAsString());
+ assertEquals("Sheet1!A$1", cellReference.formatAsString(true));
+ assertEquals("A$1", cellReference.formatAsString(false));
//sheet name with spaces
cellReference = new CellReference("Sheet 1", 0, 0, true, false);
assertEquals("'Sheet 1'!A$1", cellReference.formatAsString());
+ assertEquals("'Sheet 1'!A$1", cellReference.formatAsString(true));
+ assertEquals("A$1", cellReference.formatAsString(false));
}
@Test
@@ -474,6 +492,11 @@
final CellReference badCellRef = new CellReference(cell);
assertEquals("'Ctor test'!$A$1", goodCellRef.formatAsString());
+ assertEquals("'Ctor test'!$A$1", goodCellRef.formatAsString(true));
+ assertEquals("$A$1", goodCellRef.formatAsString(false));
+
assertEquals("'Ctor test'!A1", badCellRef.formatAsString());
+ assertEquals("'Ctor test'!A1", badCellRef.formatAsString(true));
+ assertEquals("A1", badCellRef.formatAsString(false));
}
}
