Examples of org.apache.poi.ss.usermodel.Name

org.apache.poi.ss.usermodel.Name

Represents a defined name for a range of cells.

A name is a meaningful shorthand that makes it easier to understand the purpose of a cell reference, constant or a formula.

Examples:

 Sheet sheet = workbook.createSheet("Loan Calculator"); Name name; name = workbook.createName(); name.setNameName("Interest_Rate"); name.setRefersToFormula("'Loan Calculator'!$E$5"); name = wb.createName(); name.setNameName("Loan_Amount"); name.setRefersToFormula("'Loan Calculator'!$E$4"); name = wb.createName(); name.setNameName("Number_of_Payments"); name.setRefersToFormula("'Loan Calculator'!$E$10"); name = wb.createName(); name.setNameName("Monthly_Payment"); name.setRefersToFormula("-PMT(Interest_Rate/12,Number_of_Payments,Loan_Amount)"); name = wb.createName(); name.setNameName("Values_Entered"); name.setRefersToFormula("IF(Loan_Amount*Interest_Rate>0,1,0)");

     */
    public void test48923() throws Exception {
       XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("48923.xlsx");
       assertEquals(4, wb.getNumberOfNames());
       
       Name b1 = wb.getName("NameB1");
       Name b2 = wb.getName("NameB2");
       Name sheet2 = wb.getName("NameSheet2");
       Name test = wb.getName("Test");
       
       assertNotNull(b1);
       assertEquals("NameB1", b1.getNameName());
       assertEquals("Sheet1", b1.getSheetName());
       assertEquals(-1, b1.getSheetIndex());
       
       assertNotNull(b2);
       assertEquals("NameB2", b2.getNameName());
       assertEquals("Sheet1", b2.getSheetName());
       assertEquals(-1, b2.getSheetIndex());
       
       assertNotNull(sheet2);
       assertEquals("NameSheet2", sheet2.getNameName());
       assertEquals("Sheet2", sheet2.getSheetName());
       assertEquals(-1, sheet2.getSheetIndex());
       
       assertNotNull(test);
       assertEquals("Test", test.getNameName());
       assertEquals("Sheet1", test.getSheetName());
       assertEquals(-1, test.getSheetIndex());
    }

View Full Code Here

        cell = sheet.getRow(0).getCell(0);
        assertEquals("#REF!*#REF!", cell.getCellFormula());
        assertEquals(Cell.CELL_TYPE_ERROR, evaluator.evaluateInCell(cell).getCellType());
        assertEquals("#REF!", FormulaError.forInt(cell.getErrorCellValue()).getString());


        Name nm1 = wb.getName("sale_1");
        assertNotNull("name sale_1 should be present", nm1);
        assertEquals("Sheet1!#REF!", nm1.getRefersToFormula());
        Name nm2 = wb.getName("sale_2");
        assertNotNull("name sale_2 should be present", nm2);
        assertEquals("Sheet1!#REF!", nm2.getRefersToFormula());


        cell = sheet.getRow(1).getCell(0);
        assertEquals("sale_1*sale_2", cell.getCellFormula());
        assertEquals(Cell.CELL_TYPE_ERROR, evaluator.evaluateInCell(cell).getCellType());
        assertEquals("#REF!", FormulaError.forInt(cell.getErrorCellValue()).getString());

View Full Code Here

      throw new AssertionFailedError("Identified bug 47312c - '"
          + leadingZeroCellRef + "' should parse as 'B1'.");
    }


    // create a defined name called 'B0' and try again
    Name n = wb.createName();
    n.setNameName("B0");
    n.setRefersToFormula("1+1");
    ptgs = HSSFFormulaParser.parse("B0", wb);
    confirmTokenClasses(ptgs, NamePtg.class);
  }

View Full Code Here

    /**
     * Test for a file with NameRecord with NameCommentRecord comments
     */
    public void test49185() throws Exception {
      HSSFWorkbook wb = openSample("49185.xls");
      Name name = wb.getName("foobarName");
      assertEquals("This is a comment", name.getComment());
      
      // Rename the name, comment comes with it
      name.setNameName("ChangedName");
      assertEquals("This is a comment", name.getComment());
      
      // Save and re-check
      wb = writeOutAndReadBack(wb);
      name = wb.getName("ChangedName");
      assertEquals("This is a comment", name.getComment());
      
      // Now try to change it
      name.setComment("Changed Comment");
      assertEquals("Changed Comment", name.getComment());
      
      // Save and re-check
      wb = writeOutAndReadBack(wb);
      name = wb.getName("ChangedName");
      assertEquals("Changed Comment", name.getComment());
    }

View Full Code Here

      throw new AssertionFailedError("Identified bug 47312c - '"
          + leadingZeroCellRef + "' should parse as 'B1'.");
    }


    // create a defined name called 'B0' and try again
    Name n = wb.createName();
    n.setNameName("B0");
    n.setRefersToFormula("1+1");
    ptgs = HSSFFormulaParser.parse("B0", wb);
    confirmTokenClasses(ptgs, NamePtg.class);
  }

View Full Code Here

    
    wb.createSheet(sheetName);


    assertSheetOrder(wb, "Sheet1", "Sheet2", "Sheet3", "ASheet");


    Name n = wb.createName();
    n.setNameName(nameName);
    n.setSheetIndex(3);
    n.setRefersToFormula(sheetName + "!A1");


    assertSheetOrder(wb, "Sheet1", "Sheet2", "Sheet3", "ASheet");
    assertEquals("ASheet!A1", wb.getName(nameName).getRefersToFormula());
    
    ByteArrayOutputStream stream = new ByteArrayOutputStream();

View Full Code Here

    
    @Test
    public void basicRead() {
        XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("ref-56737.xlsx");
        assertNotNull(wb.getExternalLinksTable());
        Name name = null;


        assertEquals(1, wb.getExternalLinksTable().size());


        ExternalLinksTable links = wb.getExternalLinksTable().get(0);
        assertEquals(3, links.getSheetNames().size());
        assertEquals(2, links.getDefinedNames().size());
        
        assertEquals("Uses",    links.getSheetNames().get(0));
        assertEquals("Defines", links.getSheetNames().get(1));
        assertEquals("56737",   links.getSheetNames().get(2));
        
        name = links.getDefinedNames().get(0);
        assertEquals("NR_Global_B2", name.getNameName());
        assertEquals(-1, name.getSheetIndex());
        assertEquals(null, name.getSheetName());
        assertEquals("'Defines'!$B$2", name.getRefersToFormula());
        
        name = links.getDefinedNames().get(1);
        assertEquals("NR_To_A1", name.getNameName());
        assertEquals(1, name.getSheetIndex());
        assertEquals("Defines", name.getSheetName());
        assertEquals("'Defines'!$A$1", name.getRefersToFormula());
        
        assertEquals("56737.xlsx", links.getLinkedFileName());
    }

View Full Code Here

    }
    
    @Test
    public void basicReadWriteRead() {
        XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("ref-56737.xlsx");
        Name name = wb.getExternalLinksTable().get(0).getDefinedNames().get(1);
        name.setNameName("Testing");
        name.setRefersToFormula("$A$1");
        
        wb = XSSFTestDataSamples.writeOutAndReadBack(wb);
        assertEquals(1, wb.getExternalLinksTable().size());
        ExternalLinksTable links = wb.getExternalLinksTable().get(0);
        
        name = links.getDefinedNames().get(0);
        assertEquals("NR_Global_B2", name.getNameName());
        assertEquals(-1, name.getSheetIndex());
        assertEquals(null, name.getSheetName());
        assertEquals("'Defines'!$B$2", name.getRefersToFormula());
        
        name = links.getDefinedNames().get(1);
        assertEquals("Testing", name.getNameName());
        assertEquals(1, name.getSheetIndex());
        assertEquals("Defines", name.getSheetName());
        assertEquals("$A$1", name.getRefersToFormula());
    }

View Full Code Here

    @Test
    public void readWithReferencesToTwoExternalBooks() {
        XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("ref2-56737.xlsx");
        
        assertNotNull(wb.getExternalLinksTable());
        Name name = null;


        assertEquals(2, wb.getExternalLinksTable().size());


        // Check the first one, links to 56737.xlsx
        ExternalLinksTable links = wb.getExternalLinksTable().get(0);
        assertEquals("56737.xlsx", links.getLinkedFileName());
        assertEquals(3, links.getSheetNames().size());
        assertEquals(2, links.getDefinedNames().size());
        
        assertEquals("Uses",    links.getSheetNames().get(0));
        assertEquals("Defines", links.getSheetNames().get(1));
        assertEquals("56737",   links.getSheetNames().get(2));
        
        name = links.getDefinedNames().get(0);
        assertEquals("NR_Global_B2", name.getNameName());
        assertEquals(-1, name.getSheetIndex());
        assertEquals(null, name.getSheetName());
        assertEquals("'Defines'!$B$2", name.getRefersToFormula());
        
        name = links.getDefinedNames().get(1);
        assertEquals("NR_To_A1", name.getNameName());
        assertEquals(1, name.getSheetIndex());
        assertEquals("Defines", name.getSheetName());
        assertEquals("'Defines'!$A$1", name.getRefersToFormula());


        
        // Check the second one, links to 56737.xls, slightly differently
        links = wb.getExternalLinksTable().get(1);
        assertEquals("56737.xls", links.getLinkedFileName());
        assertEquals(2, links.getSheetNames().size());
        assertEquals(2, links.getDefinedNames().size());
        
        assertEquals("Uses",    links.getSheetNames().get(0));
        assertEquals("Defines", links.getSheetNames().get(1));
        
        name = links.getDefinedNames().get(0);
        assertEquals("NR_Global_B2", name.getNameName());
        assertEquals(-1, name.getSheetIndex());
        assertEquals(null, name.getSheetName());
        assertEquals("'Defines'!$B$2", name.getRefersToFormula());
        
        name = links.getDefinedNames().get(1);
        assertEquals("NR_To_A1", name.getNameName());
        assertEquals(1, name.getSheetIndex());
        assertEquals("Defines", name.getSheetName());
        assertEquals("'Defines'!$A$1", name.getRefersToFormula());
    }

View Full Code Here

    @Test
    public void bug48923() throws Exception {
       XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("48923.xlsx");
       assertEquals(4, wb.getNumberOfNames());
       
       Name b1 = wb.getName("NameB1");
       Name b2 = wb.getName("NameB2");
       Name sheet2 = wb.getName("NameSheet2");
       Name test = wb.getName("Test");
       
       assertNotNull(b1);
       assertEquals("NameB1", b1.getNameName());
       assertEquals("Sheet1", b1.getSheetName());
       assertEquals(-1, b1.getSheetIndex());
       
       assertNotNull(b2);
       assertEquals("NameB2", b2.getNameName());
       assertEquals("Sheet1", b2.getSheetName());
       assertEquals(-1, b2.getSheetIndex());
       
       assertNotNull(sheet2);
       assertEquals("NameSheet2", sheet2.getNameName());
       assertEquals("Sheet2", sheet2.getSheetName());
       assertEquals(-1, sheet2.getSheetIndex());
       
       assertNotNull(test);
       assertEquals("Test", test.getNameName());
       assertEquals("Sheet1", test.getSheetName());
       assertEquals(-1, test.getSheetIndex());
    }

View Full Code Here

0 1 2 3 4 5 6

TOP

Related Classes of org.apache.poi.ss.usermodel.Name

org.apache.poi.hssf.model.TestFormulaParser

org.apache.poi.hssf.record.formula.NamePtg

org.apache.poi.hssf.usermodel.TestBugs

org.apache.poi.hssf.usermodel.TestFormulas

org.apache.poi.hssf.usermodel.TestHSSFWorkbook

org.apache.poi.ss.formula.TestWorkbookEvaluator

org.apache.poi.xssf.model.TestExternalLinksTable

org.apache.poi.xssf.usermodel.TestXSSFBugs

org.apache.poi.xssf.usermodel.TestXSSFWorkbook

All source code are property of their respective owners. Java is a trademark of Sun Microsystems, Inc and owned by ORACLE Inc. Contact coftware#gmail.com.