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

Example 6 with Name

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

the class TestBugs method bug49185.

/**
     * Test for a file with NameRecord with NameCommentRecord comments
     */
@Test
public void bug49185() throws Exception {
    HSSFWorkbook wb1 = openSample("49185.xls");
    Name name = wb1.getName("foobarName");
    assertNotNull(name);
    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
    HSSFWorkbook wb2 = writeOutAndReadBack(wb1);
    wb1.close();
    name = wb2.getName("ChangedName");
    assertNotNull(name);
    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
    HSSFWorkbook wb3 = writeOutAndReadBack(wb2);
    wb2.close();
    name = wb3.getName("ChangedName");
    assertNotNull(name);
    assertEquals("Changed Comment", name.getComment());
    wb3.close();
}

Example 7 with Name

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

the class TestWorkbook method testBug58085RemoveSheetWithNames.

@Test
public void testBug58085RemoveSheetWithNames() throws Exception {
    HSSFWorkbook wb1 = new HSSFWorkbook();
    Sheet sheet1 = wb1.createSheet("sheet1");
    Sheet sheet2 = wb1.createSheet("sheet2");
    Sheet sheet3 = wb1.createSheet("sheet3");
    sheet1.createRow(0).createCell((short) 0).setCellValue("val1");
    sheet2.createRow(0).createCell((short) 0).setCellValue("val2");
    sheet3.createRow(0).createCell((short) 0).setCellValue("val3");
    Name namedCell1 = wb1.createName();
    namedCell1.setNameName("name1");
    String reference1 = "sheet1!$A$1";
    namedCell1.setRefersToFormula(reference1);
    Name namedCell2 = wb1.createName();
    namedCell2.setNameName("name2");
    String reference2 = "sheet2!$A$1";
    namedCell2.setRefersToFormula(reference2);
    Name namedCell3 = wb1.createName();
    namedCell3.setNameName("name3");
    String reference3 = "sheet3!$A$1";
    namedCell3.setRefersToFormula(reference3);
    Workbook wb2 = HSSFTestDataSamples.writeOutAndReadBack(wb1);
    wb1.close();
    Name nameCell = wb2.getName("name1");
    assertEquals("sheet1!$A$1", nameCell.getRefersToFormula());
    nameCell = wb2.getName("name2");
    assertEquals("sheet2!$A$1", nameCell.getRefersToFormula());
    nameCell = wb2.getName("name3");
    assertEquals("sheet3!$A$1", nameCell.getRefersToFormula());
    wb2.removeSheetAt(wb2.getSheetIndex("sheet1"));
    nameCell = wb2.getName("name1");
    assertEquals("#REF!$A$1", nameCell.getRefersToFormula());
    nameCell = wb2.getName("name2");
    assertEquals("sheet2!$A$1", nameCell.getRefersToFormula());
    nameCell = wb2.getName("name3");
    assertEquals("sheet3!$A$1", nameCell.getRefersToFormula());
    wb2.close();
}

Example 8 with Name

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

the class TestHSSFWorkbook method bug54500.

@Test
public void bug54500() throws Exception {
    String nameName = "AName";
    String sheetName = "ASheet";
    HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("54500.xls");
    assertSheetOrder(wb, "Sheet1", "Sheet2", "Sheet3");
    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");
    final HSSFName name = wb.getName(nameName);
    assertNotNull(name);
    assertEquals("ASheet!A1", name.getRefersToFormula());
    ByteArrayOutputStream stream = new ByteArrayOutputStream();
    wb.write(stream);
    assertSheetOrder(wb, "Sheet1", "Sheet2", "Sheet3", "ASheet");
    assertEquals("ASheet!A1", name.getRefersToFormula());
    wb.removeSheetAt(1);
    assertSheetOrder(wb, "Sheet1", "Sheet3", "ASheet");
    assertEquals("ASheet!A1", name.getRefersToFormula());
    ByteArrayOutputStream stream2 = new ByteArrayOutputStream();
    wb.write(stream2);
    assertSheetOrder(wb, "Sheet1", "Sheet3", "ASheet");
    assertEquals("ASheet!A1", name.getRefersToFormula());
    HSSFWorkbook wb2 = new HSSFWorkbook(new ByteArrayInputStream(stream.toByteArray()));
    expectName(wb2, nameName, "ASheet!A1");
    HSSFWorkbook wb3 = new HSSFWorkbook(new ByteArrayInputStream(stream2.toByteArray()));
    expectName(wb3, nameName, "ASheet!A1");
    wb3.close();
    wb2.close();
    wb.close();
}

Example 9 with Name

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

the class XSSFRowShifter method updateNamedRanges.

/**
     * Updated named ranges
     */
public void updateNamedRanges(FormulaShifter shifter) {
    Workbook wb = sheet.getWorkbook();
    XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create((XSSFWorkbook) wb);
    for (Name name : wb.getAllNames()) {
        String formula = name.getRefersToFormula();
        int sheetIndex = name.getSheetIndex();
        //don't care, named ranges are not allowed to include structured references
        final int rowIndex = -1;
        Ptg[] ptgs = FormulaParser.parse(formula, fpb, FormulaType.NAMEDRANGE, sheetIndex, rowIndex);
        if (shifter.adjustFormula(ptgs, sheetIndex)) {
            String shiftedFmla = FormulaRenderer.toFormulaString(fpb, ptgs);
            name.setRefersToFormula(shiftedFmla);
        }
    }
}

Example 10 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();
    }
}