more refactoring of common ss tests

git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@759714 13f79535-47bb-0310-9956-ffa450edef68

1 files changed, 198 insertions, 5 deletions

diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFName.java b/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFName.java
index 56bd25b346..291698ec8f 100755
--- a/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFName.java
+++ b/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFName.java
@@ -19,24 +19,34 @@ package org.apache.poi.hssf.usermodel;
 
 import java.util.Date;
 import java.util.GregorianCalendar;
+import java.io.File;
+import java.io.FileOutputStream;
+import java.io.IOException;
 
 import junit.framework.AssertionFailedError;
 import org.apache.poi.hssf.HSSFITestDataProvider;
+import org.apache.poi.hssf.HSSFTestDataSamples;
 import org.apache.poi.hssf.model.Sheet;
+import org.apache.poi.hssf.model.HSSFFormulaParser;
 import org.apache.poi.hssf.record.DBCellRecord;
 import org.apache.poi.hssf.record.FormulaRecord;
 import org.apache.poi.hssf.record.Record;
 import org.apache.poi.hssf.record.StringRecord;
+import org.apache.poi.hssf.record.formula.Ptg;
 import org.apache.poi.ss.usermodel.ErrorConstants;
 import org.apache.poi.ss.usermodel.BaseTestCell;
 import org.apache.poi.ss.usermodel.BaseTestNamedRange;
+import org.apache.poi.ss.util.AreaReference;
+import org.apache.poi.ss.util.CellReference;
+import org.apache.poi.ss.formula.FormulaType;
 
 /**
- * Tests various functionality having to do with {@link org.apache.poi.hssf.usermodel.HSSFCell}.  For instance support for
- * particular datatypes, etc.
- * @author Andrew C. Oliver (andy at superlinksoftware dot com)
- * @author  Dan Sherman (dsherman at isisph.com)
- * @author Alex Jacoby (ajacoby at gmail.com)
+ * Tests various functionality having to do with {@link org.apache.poi.ss.usermodel.Name}.
+ *
+ * @author Andrew C. Oliver (acoliver at apache dot org)
+ * @author ROMANL
+ * @author Danny Mui (danny at muibros.com)
+ * @author Amol S. Deshmukh < amol at ap ache dot org >
  */
 public final class TestHSSFName extends BaseTestNamedRange {
 
@@ -45,4 +55,187 @@ public final class TestHSSFName extends BaseTestNamedRange {
         return HSSFITestDataProvider.getInstance();
     }
 
+    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.getRefersToFormula());
+         } else {
+             assertEquals("FirstSheet!A:A,FirstSheet!$A$1:$IV$3", nr1.getRefersToFormula());
+         }
+
+         // 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.getRefersToFormula());
+
+         // 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.getRefersToFormula());
+
+         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);
+             }
+         }
+     }
+
+    /** Test of TestCase method, of class test.RangeTest. */
+    public void testNamedRange() {
+        HSSFWorkbook wb = getTestDataProvider().openSampleWorkbook("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.setRefersToFormula(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.getRefersToFormula());
+    }
+
+    /**
+     * 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 = getTestDataProvider().openSampleWorkbook("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.getRefersToFormula();
+
+        assertEquals(sheetName+"!$A$1:$D$10", reference);
+
+        HSSFName namedRange2 = wb.getNameAt(1);
+
+        assertEquals(sheetName+"!$D$17:$G$27", namedRange2.getRefersToFormula());
+        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 = getTestDataProvider().openSampleWorkbook("namedinput.xls");
+
+        HSSFName name = wb.getNameAt(0);
+        String sheetName = wb.getSheetName(0);
+
+        assertEquals(sheetName+"!$A$1:$D$10", name.getRefersToFormula());
+
+        name = wb.getNameAt(1);
+        String newReference = sheetName +"!$A$1:$C$36";
+
+        name.setRefersToFormula(newReference);
+        assertEquals(newReference, name.getRefersToFormula());
+    }
+
+     /**
+      * Test to see if the print area can be retrieved from an excel created file
+      */
+     public void testPrintAreaFileRead() {
+         HSSFWorkbook workbook = getTestDataProvider().openSampleWorkbook("SimpleWithPrintArea.xls");
+
+        String sheetName = workbook.getSheetName(0);
+        String reference = sheetName+"!$A$1:$C$5";
+
+        assertEquals(reference, workbook.getPrintArea(0));
+    }
+
+
+    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.getRefersToFormula());
+        new AreaReference(name1.getRefersToFormula());
+        assertTrue("Successfully constructed first reference", true);
+
+        HSSFName name2 = wb.getNameAt(1);
+        assertEquals("b", name2.getNameName());
+        assertEquals("Sheet1!#REF!", name2.getRefersToFormula());
+        assertTrue(name2.isDeleted());
+        try {
+            new AreaReference(name2.getRefersToFormula());
+            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
+        }
+    }
+
+    /**
+     * 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, 0);
+        for (int i = 0; i < ptgs.length; i++) {
+            assertEquals('R', ptgs[i].getRVAType());
+        }
+
+    }
 }
\ No newline at end of file