Examples with Name org.apache.poi.ss.usermodel.Name used on opensource projects

Example 1 with Name

use of org.apache.poi.ss.usermodel.Name in project poi by apache.

the class FormulaParser method addName.

/**
     * Adds a name (named range or user defined function) to underlying workbook's names table
     * @param functionName
     */
private void addName(String functionName) {
    final Name name = _book.createName();
    name.setFunction(true);
    name.setNameName(functionName);
    name.setSheetIndex(_sheetIndex);
}

Example 2 with Name

use of org.apache.poi.ss.usermodel.Name in project poi by apache.

the class LinkedDropDownLists method buildDataSheet.

/**
     * Called to populate the named areas/regions. The contents of the cells on
     * row one will be used to populate the first drop down list. The contents of
     * the cells on rows two, three and four will be used to populate the second
     * drop down list, just which row will be determined by the choice the user
     * makes in the first drop down list.
     * 
     * In all cases, the approach is to create a row, create and populate cells
     * with data and then specify a name that identifies those cells. With the
     * exception of the first range, the names that are chosen for each range
     * of cells are quite important. In short, each of the options the user 
     * could select in the first drop down list is used as the name for another
     * range of cells. Thus, in this example, the user can select either 
     * 'Animal', 'Vegetable' or 'Mineral' in the first drop down and so the
     * sheet contains ranges named 'ANIMAL', 'VEGETABLE' and 'MINERAL'.
     * 
     * @param dataSheet An instance of a class that implements the Sheet Sheet
     *        interface (HSSFSheet or XSSFSheet).
     */
private static final void buildDataSheet(Sheet dataSheet) {
    Row row = null;
    Cell cell = null;
    Name name = null;
    // The first row will hold the data for the first validation.
    row = dataSheet.createRow(10);
    cell = row.createCell(0);
    cell.setCellValue("Animal");
    cell = row.createCell(1);
    cell.setCellValue("Vegetable");
    cell = row.createCell(2);
    cell.setCellValue("Mineral");
    name = dataSheet.getWorkbook().createName();
    name.setRefersToFormula("$A$11:$C$11");
    name.setNameName("CHOICES");
    // The next three rows will hold the data that will be used to
    // populate the second, or linked, drop down list.
    row = dataSheet.createRow(11);
    cell = row.createCell(0);
    cell.setCellValue("Lion");
    cell = row.createCell(1);
    cell.setCellValue("Tiger");
    cell = row.createCell(2);
    cell.setCellValue("Leopard");
    cell = row.createCell(3);
    cell.setCellValue("Elephant");
    cell = row.createCell(4);
    cell.setCellValue("Eagle");
    cell = row.createCell(5);
    cell.setCellValue("Horse");
    cell = row.createCell(6);
    cell.setCellValue("Zebra");
    name = dataSheet.getWorkbook().createName();
    name.setRefersToFormula("$A$12:$G$12");
    name.setNameName("ANIMAL");
    row = dataSheet.createRow(12);
    cell = row.createCell(0);
    cell.setCellValue("Cabbage");
    cell = row.createCell(1);
    cell.setCellValue("Cauliflower");
    cell = row.createCell(2);
    cell.setCellValue("Potato");
    cell = row.createCell(3);
    cell.setCellValue("Onion");
    cell = row.createCell(4);
    cell.setCellValue("Beetroot");
    cell = row.createCell(5);
    cell.setCellValue("Asparagus");
    cell = row.createCell(6);
    cell.setCellValue("Spinach");
    cell = row.createCell(7);
    cell.setCellValue("Chard");
    name = dataSheet.getWorkbook().createName();
    name.setRefersToFormula("$A$13:$H$13");
    name.setNameName("VEGETABLE");
    row = dataSheet.createRow(13);
    cell = row.createCell(0);
    cell.setCellValue("Bauxite");
    cell = row.createCell(1);
    cell.setCellValue("Quartz");
    cell = row.createCell(2);
    cell.setCellValue("Feldspar");
    cell = row.createCell(3);
    cell.setCellValue("Shist");
    cell = row.createCell(4);
    cell.setCellValue("Shale");
    cell = row.createCell(5);
    cell.setCellValue("Mica");
    name = dataSheet.getWorkbook().createName();
    name.setRefersToFormula("$A$14:$F$14");
    name.setNameName("MINERAL");
}

Example 3 with Name

use of org.apache.poi.ss.usermodel.Name in project poi by apache.

the class XSSFPivotCacheDefinition method getPivotArea.

/**
     * Find the 2D base data area for the pivot table, either from its direct reference or named table/range.
     * @return AreaReference representing the current area defined by the pivot table
     * @throws IllegalArgumentException if the ref attribute is not contiguous or the name attribute is not found.
     */
@Beta
public AreaReference getPivotArea(Workbook wb) throws IllegalArgumentException {
    final CTWorksheetSource wsSource = ctPivotCacheDefinition.getCacheSource().getWorksheetSource();
    final String ref = wsSource.getRef();
    final String name = wsSource.getName();
    if (ref == null && name == null) {
        throw new IllegalArgumentException("Pivot cache must reference an area, named range, or table.");
    }
    // this is the XML format, so tell the reference that.
    if (ref != null) {
        return new AreaReference(ref, SpreadsheetVersion.EXCEL2007);
    }
    assert (name != null);
    // named range or table?
    final Name range = wb.getName(name);
    if (range != null) {
        return new AreaReference(range.getRefersToFormula(), SpreadsheetVersion.EXCEL2007);
    }
    // not a named range, check for a table.
    // do this second, as tables are sheet-specific, but named ranges are not, and may not have a sheet name given.
    final XSSFSheet sheet = (XSSFSheet) wb.getSheet(wsSource.getSheet());
    for (XSSFTable table : sheet.getTables()) {
        // TODO: case-sensitive?
        if (name.equals(table.getName())) {
            return new AreaReference(table.getStartCellReference(), table.getEndCellReference());
        }
    }
    throw new IllegalArgumentException("Name '" + name + "' was not found.");
}

Example 4 with Name

use of org.apache.poi.ss.usermodel.Name in project poi by apache.

the class TestFormulaParser method testZeroRowRefs.

/**
     * Zero is not a valid row number so cell references like 'A0' are not valid.
     * Actually, they should be treated like defined names.
     * <br/>
     * In addition, leading zeros (on the row component) should be removed from cell
     * references during parsing.
     */
@Test
public void testZeroRowRefs() throws IOException {
    // bad because zero is not a valid row number
    String badCellRef = "B0";
    // this should get parsed as "B1"
    String leadingZeroCellRef = "B000001";
    HSSFWorkbook wb = new HSSFWorkbook();
    try {
        HSSFFormulaParser.parse(badCellRef, wb);
        fail("Identified bug 47312b - Shouldn't be able to parse cell ref '" + badCellRef + "'.");
    } catch (FormulaParseException e) {
        // expected during successful test
        confirmParseException(e, "Specified named range '" + badCellRef + "' does not exist in the current workbook.");
    }
    Ptg[] ptgs;
    try {
        ptgs = HSSFFormulaParser.parse(leadingZeroCellRef, wb);
        assertEquals("B1", ((RefPtg) ptgs[0]).toFormulaString());
    } catch (FormulaParseException e) {
        confirmParseException(e, "Specified named range '" + leadingZeroCellRef + "' does not exist in the current workbook.");
        // close but no cigar
        fail("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);
    wb.close();
}

Example 5 with Name

use of org.apache.poi.ss.usermodel.Name in project poi by apache.

the class TestFormulas method testFormulasWithUnderscore.

/**
     * Verify that FormulaParser handles defined names beginning with underscores,
     * see Bug #49640
     */
@Test
public void testFormulasWithUnderscore() throws IOException {
    HSSFWorkbook wb = new HSSFWorkbook();
    try {
        Name nm1 = wb.createName();
        nm1.setNameName("_score1");
        nm1.setRefersToFormula("A1");
        Name nm2 = wb.createName();
        nm2.setNameName("_score2");
        nm2.setRefersToFormula("A2");
        Sheet sheet = wb.createSheet();
        Cell cell = sheet.createRow(0).createCell(2);
        cell.setCellFormula("_score1*SUM(_score1+_score2)");
        assertEquals("_score1*SUM(_score1+_score2)", cell.getCellFormula());
    } finally {
        wb.close();
    }
}