Examples with InternalSheet org.apache.poi.hssf.model.InternalSheet used on opensource projects

Example 6 with InternalSheet

use of org.apache.poi.hssf.model.InternalSheet in project poi by apache.

the class TestHSSFSheet method autoFilter.

@Test
public void autoFilter() throws IOException {
    HSSFWorkbook wb1 = new HSSFWorkbook();
    HSSFSheet sh = wb1.createSheet();
    InternalWorkbook iwb = wb1.getWorkbook();
    InternalSheet ish = sh.getSheet();
    assertNull(iwb.getSpecificBuiltinRecord(NameRecord.BUILTIN_FILTER_DB, 1));
    assertNull(ish.findFirstRecordBySid(AutoFilterInfoRecord.sid));
    CellRangeAddress range = CellRangeAddress.valueOf("A1:B10");
    sh.setAutoFilter(range);
    NameRecord name = iwb.getSpecificBuiltinRecord(NameRecord.BUILTIN_FILTER_DB, 1);
    assertNotNull(name);
    // The built-in name for auto-filter must consist of a single Area3d Ptg.
    Ptg[] ptg = name.getNameDefinition();
    assertEquals("The built-in name for auto-filter must consist of a single Area3d Ptg", 1, ptg.length);
    assertTrue("The built-in name for auto-filter must consist of a single Area3d Ptg", ptg[0] instanceof Area3DPtg);
    Area3DPtg aref = (Area3DPtg) ptg[0];
    assertEquals(range.getFirstColumn(), aref.getFirstColumn());
    assertEquals(range.getFirstRow(), aref.getFirstRow());
    assertEquals(range.getLastColumn(), aref.getLastColumn());
    assertEquals(range.getLastRow(), aref.getLastRow());
    // verify  AutoFilterInfoRecord
    AutoFilterInfoRecord afilter = (AutoFilterInfoRecord) ish.findFirstRecordBySid(AutoFilterInfoRecord.sid);
    assertNotNull(afilter);
    //filter covers two columns
    assertEquals(2, afilter.getNumEntries());
    HSSFPatriarch dr = sh.getDrawingPatriarch();
    assertNotNull(dr);
    HSSFSimpleShape comboBoxShape = (HSSFSimpleShape) dr.getChildren().get(0);
    assertEquals(comboBoxShape.getShapeType(), HSSFSimpleShape.OBJECT_TYPE_COMBO_BOX);
    // ObjRecord will appear after serializetion
    assertNull(ish.findFirstRecordBySid(ObjRecord.sid));
    HSSFWorkbook wb2 = HSSFTestDataSamples.writeOutAndReadBack(wb1);
    wb1.close();
    sh = wb2.getSheetAt(0);
    ish = sh.getSheet();
    ObjRecord objRecord = (ObjRecord) ish.findFirstRecordBySid(ObjRecord.sid);
    List<SubRecord> subRecords = objRecord.getSubRecords();
    assertEquals(3, subRecords.size());
    assertTrue(subRecords.get(0) instanceof CommonObjectDataSubRecord);
    // must be present, see Bug 51481
    assertTrue(subRecords.get(1) instanceof FtCblsSubRecord);
    assertTrue(subRecords.get(2) instanceof LbsDataSubRecord);
    wb2.close();
}

Example 7 with InternalSheet

use of org.apache.poi.hssf.model.InternalSheet in project poi by apache.

the class TestBugs method bug51675.

@Test
public void bug51675() throws Exception {
    final List<Short> list = new ArrayList<Short>();
    HSSFWorkbook wb = openSample("51675.xls");
    HSSFSheet sh = wb.getSheetAt(0);
    InternalSheet ish = HSSFTestHelper.getSheetForTest(sh);
    PageSettingsBlock psb = (PageSettingsBlock) ish.getRecords().get(13);
    psb.visitContainedRecords(new RecordAggregate.RecordVisitor() {

        @Override
        public void visitRecord(Record r) {
            list.add(r.getSid());
        }
    });
    assertEquals(UnknownRecord.BITMAP_00E9, list.get(list.size() - 1).intValue());
    assertEquals(UnknownRecord.HEADER_FOOTER_089C, list.get(list.size() - 2).intValue());
    wb.close();
}

Example 8 with InternalSheet

use of org.apache.poi.hssf.model.InternalSheet in project poi by apache.

the class TestHSSFWorkbook method sheetSerializeSizeMismatch_bug45066.

/**
     * If Sheet.getSize() returns a different result to Sheet.serialize(), this will cause the BOF
     * records to be written with invalid offset indexes.  Excel does not like this, and such
     * errors are particularly hard to track down.  This test ensures that HSSFWorkbook throws
     * a specific exception as soon as the situation is detected. See bugzilla 45066
     * @throws IOException 
     */
@Test
public void sheetSerializeSizeMismatch_bug45066() throws IOException {
    HSSFWorkbook wb = new HSSFWorkbook();
    InternalSheet sheet = wb.createSheet("Sheet1").getSheet();
    List<RecordBase> sheetRecords = sheet.getRecords();
    // one way (of many) to cause the discrepancy is with a badly behaved record:
    sheetRecords.add(new BadlyBehavedRecord());
    // There is also much logic inside Sheet that (if buggy) might also cause the discrepancy
    try {
        wb.getBytes();
        fail("Identified bug 45066 a");
    } catch (IllegalStateException e) {
        // Expected badly behaved sheet record to cause exception
        assertTrue(e.getMessage().startsWith("Actual serialized sheet size"));
    }
    wb.close();
}

Example 9 with InternalSheet

use of org.apache.poi.hssf.model.InternalSheet in project poi by apache.

the class HSSFSheet method shiftRows.

/**
     * Shifts rows between startRow and endRow n number of rows.
     * If you use a negative number, it will shift rows up.
     * Code ensures that rows don't wrap around<p>
     * 
     * Additionally shifts merged regions that are completely defined in these
     * rows (ie. merged 2 cells on a row to be shifted).<p>
     * 
     * TODO Might want to add bounds checking here
     *
     * @param startRow               the row to start shifting
     * @param endRow                 the row to end shifting
     * @param n                      the number of rows to shift
     * @param copyRowHeight          whether to copy the row height during the shift
     * @param resetOriginalRowHeight whether to set the original row's height to the default
     * @param moveComments           whether to move comments at the same time as the cells they are attached to
     */
public void shiftRows(int startRow, int endRow, int n, boolean copyRowHeight, boolean resetOriginalRowHeight, boolean moveComments) {
    int s, inc;
    if (endRow < startRow) {
        throw new IllegalArgumentException("startRow must be less than or equal to endRow. To shift rows up, use n<0.");
    }
    if (n < 0) {
        s = startRow;
        inc = 1;
    } else if (n > 0) {
        s = endRow;
        inc = -1;
    } else {
        // Nothing to do
        return;
    }
    final RowShifter rowShifter = new HSSFRowShifter(this);
    // bounds or deleting them
    if (moveComments) {
        final HSSFPatriarch patriarch = createDrawingPatriarch();
        for (final HSSFShape shape : patriarch.getChildren()) {
            if (!(shape instanceof HSSFComment)) {
                continue;
            }
            final HSSFComment comment = (HSSFComment) shape;
            final int r = comment.getRow();
            if (startRow <= r && r <= endRow) {
                comment.setRow(clip(r + n));
            }
        }
    }
    // Shift Merged Regions
    rowShifter.shiftMergedRegions(startRow, endRow, n);
    // Shift Row Breaks
    _sheet.getPageSettings().shiftRowBreaks(startRow, endRow, n);
    // Delete overwritten hyperlinks
    final int firstOverwrittenRow = startRow + n;
    final int lastOverwrittenRow = endRow + n;
    for (HSSFHyperlink link : getHyperlinkList()) {
        // If hyperlink is fully contained in the rows that will be overwritten, delete the hyperlink
        final int firstRow = link.getFirstRow();
        final int lastRow = link.getLastRow();
        if (firstOverwrittenRow <= firstRow && firstRow <= lastOverwrittenRow && lastOverwrittenRow <= lastRow && lastRow <= lastOverwrittenRow) {
            removeHyperlink(link);
        }
    }
    for (int rowNum = s; rowNum >= startRow && rowNum <= endRow && rowNum >= 0 && rowNum < 65536; rowNum += inc) {
        HSSFRow row = getRow(rowNum);
        // if the row contains cells included in a multi-cell array formula
        if (row != null)
            notifyRowShifting(row);
        HSSFRow row2Replace = getRow(rowNum + n);
        if (row2Replace == null)
            row2Replace = createRow(rowNum + n);
        // Remove all the old cells from the row we'll
        //  be writing to, before we start overwriting
        //  any cells. This avoids issues with cells
        //  changing type, and records not being correctly
        //  overwritten
        row2Replace.removeAllCells();
        // Nothing to do for this row
        if (row == null)
            continue;
        // Fix up row heights if required
        if (copyRowHeight) {
            row2Replace.setHeight(row.getHeight());
        }
        if (resetOriginalRowHeight) {
            row.setHeight((short) 0xff);
        }
        //  the destination row
        for (Iterator<Cell> cells = row.cellIterator(); cells.hasNext(); ) {
            HSSFCell cell = (HSSFCell) cells.next();
            HSSFHyperlink link = cell.getHyperlink();
            row.removeCell(cell);
            CellValueRecordInterface cellRecord = cell.getCellValueRecord();
            cellRecord.setRow(rowNum + n);
            row2Replace.createCellFromRecord(cellRecord);
            _sheet.addValueRecord(rowNum + n, cellRecord);
            if (link != null) {
                link.setFirstRow(link.getFirstRow() + n);
                link.setLastRow(link.getLastRow() + n);
            }
        }
        // Now zap all the cells in the source row
        row.removeAllCells();
    }
    // Re-compute the first and last rows of the sheet as needed
    if (n > 0) {
        // Rows are moving down
        if (startRow == _firstrow) {
            // Need to walk forward to find the first non-blank row
            _firstrow = Math.max(startRow + n, 0);
            for (int i = startRow + 1; i < startRow + n; i++) {
                if (getRow(i) != null) {
                    _firstrow = i;
                    break;
                }
            }
        }
        if (endRow + n > _lastrow) {
            _lastrow = Math.min(endRow + n, SpreadsheetVersion.EXCEL97.getLastRowIndex());
        }
    } else {
        // Rows are moving up
        if (startRow + n < _firstrow) {
            _firstrow = Math.max(startRow + n, 0);
        }
        if (endRow == _lastrow) {
            // Need to walk backward to find the last non-blank row
            // NOTE: n is always negative here
            _lastrow = Math.min(endRow + n, SpreadsheetVersion.EXCEL97.getLastRowIndex());
            for (int i = endRow - 1; i > endRow + n; i--) {
                if (getRow(i) != null) {
                    _lastrow = i;
                    break;
                }
            }
        }
    }
    // Update any formulas on this sheet that point to
    //  rows which have been moved
    int sheetIndex = _workbook.getSheetIndex(this);
    String sheetName = _workbook.getSheetName(sheetIndex);
    short externSheetIndex = _book.checkExternSheet(sheetIndex);
    FormulaShifter shifter = FormulaShifter.createForRowShift(externSheetIndex, sheetName, startRow, endRow, n, SpreadsheetVersion.EXCEL97);
    _sheet.updateFormulasAfterCellShift(shifter, externSheetIndex);
    int nSheets = _workbook.getNumberOfSheets();
    for (int i = 0; i < nSheets; i++) {
        InternalSheet otherSheet = _workbook.getSheetAt(i).getSheet();
        if (otherSheet == this._sheet) {
            continue;
        }
        short otherExtSheetIx = _book.checkExternSheet(i);
        otherSheet.updateFormulasAfterCellShift(shifter, otherExtSheetIx);
    }
    _workbook.getWorkbook().updateNamesAfterCellShift(shifter);
}

Example 10 with InternalSheet

use of org.apache.poi.hssf.model.InternalSheet in project poi by apache.

the class TestPageSettingsBlock method testHeaderFooter_bug46840.

/**
	 * Bug 46840 occurred because POI failed to recognise HEADERFOOTER as part of the
	 * {@link PageSettingsBlock}.
	 */
public void testHeaderFooter_bug46840() {
    int rowIx = 5;
    int colIx = 6;
    NumberRecord nr = new NumberRecord();
    nr.setRow(rowIx);
    nr.setColumn((short) colIx);
    nr.setValue(3.0);
    Record[] recs = { BOFRecord.createSheetBOF(), new HeaderRecord("&LSales Figures"), new FooterRecord("&LJanuary"), new HeaderFooterRecord(HexRead.readFromString("9C 08 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 C4 60 00 00 00 00 00 00 00 00")), new DimensionsRecord(), new WindowTwoRecord(), new UserSViewBegin(HexRead.readFromString("ED 77 3B 86 BC 3F 37 4C A9 58 60 23 43 68 54 4B 01 00 00 00 64 00 00 00 40 00 00 00 02 00 00 00 3D 80 04 00 00 00 00 00 00 00 0C 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 F0 3F FF FF 01 00")), new HeaderRecord("&LSales Figures"), new FooterRecord("&LJanuary"), new HeaderFooterRecord(HexRead.readFromString("9C 08 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 C4 60 00 00 00 00 00 00 00 00")), new UserSViewEnd(HexRead.readFromString("01, 00")), EOFRecord.instance };
    RecordStream rs = new RecordStream(Arrays.asList(recs), 0);
    InternalSheet sheet;
    try {
        sheet = InternalSheet.createSheet(rs);
    } catch (RuntimeException e) {
        if (e.getMessage().equals("two Page Settings Blocks found in the same sheet")) {
            throw new AssertionFailedError("Identified bug 46480");
        }
        throw e;
    }
    RecordCollector rv = new RecordCollector();
    sheet.visitContainedRecords(rv, rowIx);
    Record[] outRecs = rv.getRecords();
    assertEquals(13, outRecs.length);
}