Examples with NameRecord org.apache.poi.hssf.record.NameRecord used on opensource projects

Example 16 with NameRecord

use of org.apache.poi.hssf.record.NameRecord in project poi by apache.

the class InternalWorkbook method removeSheet.

/**
     * @param sheetIndex zero based sheet index
     */
public void removeSheet(int sheetIndex) {
    if (boundsheets.size() > sheetIndex) {
        records.remove(records.getBspos() - (boundsheets.size() - 1) + sheetIndex);
        boundsheets.remove(sheetIndex);
        fixTabIdRecord();
    }
    // Within NameRecords, it's ok to have the formula
    //  part point at deleted sheets. It's also ok to
    //  have the ExternSheetNumber point at deleted
    //  sheets.
    // However, the sheet index must be adjusted, or
    //  excel will break. (Sheet index is either 0 for
    //  global, or 1 based index to sheet)
    int sheetNum1Based = sheetIndex + 1;
    for (int i = 0; i < getNumNames(); i++) {
        NameRecord nr = getNameRecord(i);
        if (nr.getSheetNumber() == sheetNum1Based) {
            // Excel re-writes these to point to no sheet
            nr.setSheetNumber(0);
        } else if (nr.getSheetNumber() > sheetNum1Based) {
            // Bump down by one, so still points
            //  at the same sheet
            nr.setSheetNumber(nr.getSheetNumber() - 1);
        }
    }
    if (linkTable != null) {
        // also tell the LinkTable about the removed sheet
        //index hasn't change in the linktable
        linkTable.removeSheet(sheetIndex);
    }
}

Example 17 with NameRecord

use of org.apache.poi.hssf.record.NameRecord in project poi by apache.

the class HSSFSheet method setAutoFilter.

@Override
public HSSFAutoFilter setAutoFilter(CellRangeAddress range) {
    InternalWorkbook workbook = _workbook.getWorkbook();
    int sheetIndex = _workbook.getSheetIndex(this);
    NameRecord name = workbook.getSpecificBuiltinRecord(NameRecord.BUILTIN_FILTER_DB, sheetIndex + 1);
    if (name == null) {
        name = workbook.createBuiltInName(NameRecord.BUILTIN_FILTER_DB, sheetIndex + 1);
    }
    int firstRow = range.getFirstRow();
    // if row was not given when constructing the range...
    if (firstRow == -1) {
        firstRow = 0;
    }
    // The built-in name must consist of a single Area3d Ptg.
    Area3DPtg ptg = new Area3DPtg(firstRow, range.getLastRow(), range.getFirstColumn(), range.getLastColumn(), false, false, false, false, sheetIndex);
    name.setNameDefinition(new Ptg[] { ptg });
    AutoFilterInfoRecord r = new AutoFilterInfoRecord();
    // the number of columns that have AutoFilter enabled.
    int numcols = 1 + range.getLastColumn() - range.getFirstColumn();
    r.setNumEntries((short) numcols);
    int idx = _sheet.findFirstRecordLocBySid(DimensionsRecord.sid);
    _sheet.getRecords().add(idx, r);
    //create a combobox control for each column
    HSSFPatriarch p = createDrawingPatriarch();
    final int firstColumn = range.getFirstColumn();
    final int lastColumn = range.getLastColumn();
    for (int col = firstColumn; col <= lastColumn; col++) {
        p.createComboBox(new HSSFClientAnchor(0, 0, 0, 0, (short) col, firstRow, (short) (col + 1), firstRow + 1));
    }
    return new HSSFAutoFilter(this);
}

Example 18 with NameRecord

use of org.apache.poi.hssf.record.NameRecord in project poi by apache.

the class TestBugs method bug46664.

/**
     * Newly created sheets need to get a
     *  proper TabID, otherwise print setup
     *  gets confused on them.
     * Also ensure that print setup refs are
     *  by reference not value
     */
@Test
public void bug46664() throws Exception {
    HSSFWorkbook wb1 = new HSSFWorkbook();
    HSSFSheet sheet = wb1.createSheet("new_sheet");
    HSSFRow row = sheet.createRow((short) 0);
    row.createCell(0).setCellValue(new HSSFRichTextString("Column A"));
    row.createCell(1).setCellValue(new HSSFRichTextString("Column B"));
    row.createCell(2).setCellValue(new HSSFRichTextString("Column C"));
    row.createCell(3).setCellValue(new HSSFRichTextString("Column D"));
    row.createCell(4).setCellValue(new HSSFRichTextString("Column E"));
    row.createCell(5).setCellValue(new HSSFRichTextString("Column F"));
    //set print area from column a to column c (on first row)
    wb1.setPrintArea(//sheet index
    0, //start column
    0, //end column
    2, //start row
    0, //end row
    0);
    HSSFWorkbook wb2 = writeOutAndReadBack(wb1);
    wb1.close();
    // Ensure the tab index
    TabIdRecord tr = null;
    for (Record r : wb2.getWorkbook().getRecords()) {
        if (r instanceof TabIdRecord) {
            tr = (TabIdRecord) r;
        }
    }
    assertNotNull(tr);
    assertEquals(1, tr._tabids.length);
    assertEquals(0, tr._tabids[0]);
    // Ensure the print setup
    assertEquals("new_sheet!$A$1:$C$1", wb2.getPrintArea(0));
    HSSFName printArea = wb2.getName("Print_Area");
    assertNotNull(printArea);
    assertEquals("new_sheet!$A$1:$C$1", printArea.getRefersToFormula());
    // Needs reference not value
    NameRecord nr = wb2.getWorkbook().getNameRecord(wb2.getNameIndex("Print_Area"));
    assertEquals("Print_Area", nr.getNameText());
    assertEquals(1, nr.getNameDefinition().length);
    assertEquals("new_sheet!$A$1:$C$1", ((Area3DPtg) nr.getNameDefinition()[0]).toFormulaString(HSSFEvaluationWorkbook.create(wb2)));
    assertEquals('R', nr.getNameDefinition()[0].getRVAType());
    wb2.close();
}

Example 19 with NameRecord

use of org.apache.poi.hssf.record.NameRecord in project poi by apache.

the class HSSFWorkbook method cloneSheet.

/**
     * create an HSSFSheet from an existing sheet in the HSSFWorkbook.
     *
     * @return HSSFSheet representing the cloned sheet.
     */
@Override
public HSSFSheet cloneSheet(int sheetIndex) {
    validateSheetIndex(sheetIndex);
    HSSFSheet srcSheet = _sheets.get(sheetIndex);
    String srcName = workbook.getSheetName(sheetIndex);
    HSSFSheet clonedSheet = srcSheet.cloneSheet(this);
    clonedSheet.setSelected(false);
    clonedSheet.setActive(false);
    String name = getUniqueSheetName(srcName);
    int newSheetIndex = _sheets.size();
    _sheets.add(clonedSheet);
    workbook.setSheetName(newSheetIndex, name);
    // Check this sheet has an autofilter, (which has a built-in NameRecord at workbook level)
    int filterDbNameIndex = findExistingBuiltinNameRecordIdx(sheetIndex, NameRecord.BUILTIN_FILTER_DB);
    if (filterDbNameIndex != -1) {
        NameRecord newNameRecord = workbook.cloneFilter(filterDbNameIndex, newSheetIndex);
        HSSFName newName = new HSSFName(this, newNameRecord);
        names.add(newName);
    }
    return clonedSheet;
}

Example 20 with NameRecord

use of org.apache.poi.hssf.record.NameRecord in project poi by apache.

the class TestHSSFWorkbook method namesToDeleteSheets.

/**
     * Checks that us and HSSFName play nicely with named ranges
     *  that point to deleted sheets
     */
@Test
public void namesToDeleteSheets() throws IOException {
    HSSFWorkbook b = HSSFTestDataSamples.openSampleWorkbook("30978-deleted.xls");
    assertEquals(3, b.getNumberOfNames());
    // Sheet 2 is deleted
    assertEquals("Sheet1", b.getSheetName(0));
    assertEquals("Sheet3", b.getSheetName(1));
    Area3DPtg ptg;
    NameRecord nr;
    HSSFName n;
    /* ======= Name pointing to deleted sheet ====== */
    // First at low level
    nr = b.getWorkbook().getNameRecord(0);
    assertEquals("On2", nr.getNameText());
    assertEquals(0, nr.getSheetNumber());
    assertEquals(1, nr.getExternSheetNumber());
    assertEquals(1, nr.getNameDefinition().length);
    ptg = (Area3DPtg) nr.getNameDefinition()[0];
    assertEquals(1, ptg.getExternSheetIndex());
    assertEquals(0, ptg.getFirstColumn());
    assertEquals(0, ptg.getFirstRow());
    assertEquals(0, ptg.getLastColumn());
    assertEquals(2, ptg.getLastRow());
    // Now at high level
    n = b.getNameAt(0);
    assertEquals("On2", n.getNameName());
    assertEquals("", n.getSheetName());
    assertEquals("#REF!$A$1:$A$3", n.getRefersToFormula());
    /* ======= Name pointing to 1st sheet ====== */
    // First at low level
    nr = b.getWorkbook().getNameRecord(1);
    assertEquals("OnOne", nr.getNameText());
    assertEquals(0, nr.getSheetNumber());
    assertEquals(0, nr.getExternSheetNumber());
    assertEquals(1, nr.getNameDefinition().length);
    ptg = (Area3DPtg) nr.getNameDefinition()[0];
    assertEquals(0, ptg.getExternSheetIndex());
    assertEquals(0, ptg.getFirstColumn());
    assertEquals(2, ptg.getFirstRow());
    assertEquals(0, ptg.getLastColumn());
    assertEquals(3, ptg.getLastRow());
    // Now at high level
    n = b.getNameAt(1);
    assertEquals("OnOne", n.getNameName());
    assertEquals("Sheet1", n.getSheetName());
    assertEquals("Sheet1!$A$3:$A$4", n.getRefersToFormula());
    /* ======= Name pointing to 3rd sheet ====== */
    // First at low level
    nr = b.getWorkbook().getNameRecord(2);
    assertEquals("OnSheet3", nr.getNameText());
    assertEquals(0, nr.getSheetNumber());
    assertEquals(2, nr.getExternSheetNumber());
    assertEquals(1, nr.getNameDefinition().length);
    ptg = (Area3DPtg) nr.getNameDefinition()[0];
    assertEquals(2, ptg.getExternSheetIndex());
    assertEquals(0, ptg.getFirstColumn());
    assertEquals(0, ptg.getFirstRow());
    assertEquals(0, ptg.getLastColumn());
    assertEquals(1, ptg.getLastRow());
    // Now at high level
    n = b.getNameAt(2);
    assertEquals("OnSheet3", n.getNameName());
    assertEquals("Sheet3", n.getSheetName());
    assertEquals("Sheet3!$A$1:$A$2", n.getRefersToFormula());
    b.close();
}