Search in sources :

Example 6 with CellValueRecordInterface

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

the class MissingRecordAwareHSSFListener method processRecord.

public void processRecord(Record record) {
    int thisRow;
    int thisColumn;
    CellValueRecordInterface[] expandedRecords = null;
    if (record instanceof CellValueRecordInterface) {
        CellValueRecordInterface valueRec = (CellValueRecordInterface) record;
        thisRow = valueRec.getRow();
        thisColumn = valueRec.getColumn();
    } else {
        if (record instanceof StringRecord) {
            //it contains only cashed result of the previous FormulaRecord evaluation
            childListener.processRecord(record);
            return;
        }
        thisRow = -1;
        thisColumn = -1;
        switch(record.getSid()) {
            // the workbook
            case BOFRecord.sid:
                BOFRecord bof = (BOFRecord) record;
                if (bof.getType() == BOFRecord.TYPE_WORKBOOK || bof.getType() == BOFRecord.TYPE_WORKSHEET) {
                    // Reset the row and column counts - new workbook / worksheet
                    resetCounts();
                }
                break;
            case RowRecord.sid:
                RowRecord rowrec = (RowRecord) record;
                // If there's a jump in rows, fire off missing row records
                if (lastRowRow + 1 < rowrec.getRowNumber()) {
                    for (int i = (lastRowRow + 1); i < rowrec.getRowNumber(); i++) {
                        MissingRowDummyRecord dr = new MissingRowDummyRecord(i);
                        childListener.processRecord(dr);
                    }
                }
                // Record this as the last row we saw
                lastRowRow = rowrec.getRowNumber();
                lastCellColumn = -1;
                break;
            case SharedFormulaRecord.sid:
                // SharedFormulaRecord occurs after the first FormulaRecord of the cell range.
                // There are probably (but not always) more cell records after this
                // - so don't fire off the LastCellOfRowDummyRecord yet
                childListener.processRecord(record);
                return;
            case MulBlankRecord.sid:
                // These appear in the middle of the cell records, to
                //  specify that the next bunch are empty but styled
                // Expand this out into multiple blank cells
                MulBlankRecord mbr = (MulBlankRecord) record;
                expandedRecords = RecordFactory.convertBlankRecords(mbr);
                break;
            case MulRKRecord.sid:
                // This is multiple consecutive number cells in one record
                // Exand this out into multiple regular number cells
                MulRKRecord mrk = (MulRKRecord) record;
                expandedRecords = RecordFactory.convertRKRecords(mrk);
                break;
            case NoteRecord.sid:
                NoteRecord nrec = (NoteRecord) record;
                thisRow = nrec.getRow();
                thisColumn = nrec.getColumn();
                break;
            default:
                break;
        }
    }
    // First part of expanded record handling
    if (expandedRecords != null && expandedRecords.length > 0) {
        thisRow = expandedRecords[0].getRow();
        thisColumn = expandedRecords[0].getColumn();
    }
    //  dummy end-of-row records
    if (thisRow != lastCellRow && thisRow > 0) {
        if (lastCellRow == -1)
            lastCellRow = 0;
        for (int i = lastCellRow; i < thisRow; i++) {
            int cols = -1;
            if (i == lastCellRow) {
                cols = lastCellColumn;
            }
            childListener.processRecord(new LastCellOfRowDummyRecord(i, cols));
        }
    }
    // final dummy end-of-row record
    if (lastCellRow != -1 && lastCellColumn != -1 && thisRow == -1) {
        childListener.processRecord(new LastCellOfRowDummyRecord(lastCellRow, lastCellColumn));
        lastCellRow = -1;
        lastCellColumn = -1;
    }
    //  the column counter
    if (thisRow != lastCellRow) {
        lastCellColumn = -1;
    }
    //  the dummy cell records
    if (lastCellColumn != thisColumn - 1) {
        for (int i = lastCellColumn + 1; i < thisColumn; i++) {
            childListener.processRecord(new MissingCellDummyRecord(thisRow, i));
        }
    }
    // Next part of expanded record handling
    if (expandedRecords != null && expandedRecords.length > 0) {
        thisColumn = expandedRecords[expandedRecords.length - 1].getColumn();
    }
    // Update cell and row counts as needed
    if (thisColumn != -1) {
        lastCellColumn = thisColumn;
        lastCellRow = thisRow;
    }
    // Pass along the record(s)
    if (expandedRecords != null && expandedRecords.length > 0) {
        for (CellValueRecordInterface r : expandedRecords) {
            childListener.processRecord((Record) r);
        }
    } else {
        childListener.processRecord(record);
    }
}
Also used : MissingRowDummyRecord(org.apache.poi.hssf.eventusermodel.dummyrecord.MissingRowDummyRecord) MulBlankRecord(org.apache.poi.hssf.record.MulBlankRecord) CellValueRecordInterface(org.apache.poi.hssf.record.CellValueRecordInterface) RowRecord(org.apache.poi.hssf.record.RowRecord) LastCellOfRowDummyRecord(org.apache.poi.hssf.eventusermodel.dummyrecord.LastCellOfRowDummyRecord) MulRKRecord(org.apache.poi.hssf.record.MulRKRecord) NoteRecord(org.apache.poi.hssf.record.NoteRecord) BOFRecord(org.apache.poi.hssf.record.BOFRecord) MissingCellDummyRecord(org.apache.poi.hssf.eventusermodel.dummyrecord.MissingCellDummyRecord) StringRecord(org.apache.poi.hssf.record.StringRecord)

Example 7 with CellValueRecordInterface

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

the class HSSFSheet method setPropertiesFromSheet.

/**
     * used internally to set the properties given a Sheet object
     */
private void setPropertiesFromSheet(InternalSheet sheet) {
    RowRecord row = sheet.getNextRow();
    while (row != null) {
        createRowFromRecord(row);
        row = sheet.getNextRow();
    }
    Iterator<CellValueRecordInterface> iter = sheet.getCellValueIterator();
    long timestart = System.currentTimeMillis();
    if (log.check(POILogger.DEBUG)) {
        log.log(DEBUG, "Time at start of cell creating in HSSF sheet = ", Long.valueOf(timestart));
    }
    HSSFRow lastrow = null;
    // Add every cell to its row
    while (iter.hasNext()) {
        CellValueRecordInterface cval = iter.next();
        long cellstart = System.currentTimeMillis();
        HSSFRow hrow = lastrow;
        if (hrow == null || hrow.getRowNum() != cval.getRow()) {
            hrow = getRow(cval.getRow());
            lastrow = hrow;
            if (hrow == null) {
                /* we removed this check, see bug 47245 for the discussion around this
                    // Some tools (like Perl module Spreadsheet::WriteExcel - bug 41187) skip the RowRecords
                    // Excel, OpenOffice.org and GoogleDocs are all OK with this, so POI should be too.
                    if (rowRecordsAlreadyPresent) {
                        // if at least one row record is present, all should be present.
                        throw new RuntimeException("Unexpected missing row when some rows already present");
                    }*/
                // create the row record on the fly now.
                RowRecord rowRec = new RowRecord(cval.getRow());
                sheet.addRow(rowRec);
                hrow = createRowFromRecord(rowRec);
            }
        }
        if (log.check(POILogger.DEBUG)) {
            if (cval instanceof Record) {
                log.log(DEBUG, "record id = " + Integer.toHexString(((Record) cval).getSid()));
            } else {
                log.log(DEBUG, "record = " + cval);
            }
        }
        hrow.createCellFromRecord(cval);
        if (log.check(POILogger.DEBUG)) {
            log.log(DEBUG, "record took ", Long.valueOf(System.currentTimeMillis() - cellstart));
        }
    }
    if (log.check(POILogger.DEBUG)) {
        log.log(DEBUG, "total sheet cell creation took ", Long.valueOf(System.currentTimeMillis() - timestart));
    }
}
Also used : CellValueRecordInterface(org.apache.poi.hssf.record.CellValueRecordInterface) RowRecord(org.apache.poi.hssf.record.RowRecord) SCLRecord(org.apache.poi.hssf.record.SCLRecord) DrawingRecord(org.apache.poi.hssf.record.DrawingRecord) Record(org.apache.poi.hssf.record.Record) HyperlinkRecord(org.apache.poi.hssf.record.HyperlinkRecord) RowRecord(org.apache.poi.hssf.record.RowRecord) WindowTwoRecord(org.apache.poi.hssf.record.WindowTwoRecord) EscherRecord(org.apache.poi.ddf.EscherRecord) NameRecord(org.apache.poi.hssf.record.NameRecord) WSBoolRecord(org.apache.poi.hssf.record.WSBoolRecord) ExtendedFormatRecord(org.apache.poi.hssf.record.ExtendedFormatRecord) DimensionsRecord(org.apache.poi.hssf.record.DimensionsRecord) AutoFilterInfoRecord(org.apache.poi.hssf.record.AutoFilterInfoRecord) DVRecord(org.apache.poi.hssf.record.DVRecord)

Example 8 with CellValueRecordInterface

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

the class HSSFSheet method removeArrayFormula.

@Override
public CellRange<HSSFCell> removeArrayFormula(Cell cell) {
    if (cell.getSheet() != this) {
        throw new IllegalArgumentException("Specified cell does not belong to this sheet.");
    }
    CellValueRecordInterface rec = ((HSSFCell) cell).getCellValueRecord();
    if (!(rec instanceof FormulaRecordAggregate)) {
        String ref = new CellReference(cell).formatAsString();
        throw new IllegalArgumentException("Cell " + ref + " is not part of an array formula.");
    }
    FormulaRecordAggregate fra = (FormulaRecordAggregate) rec;
    CellRangeAddress range = fra.removeArrayFormula(cell.getRowIndex(), cell.getColumnIndex());
    CellRange<HSSFCell> result = getCellRange(range);
    // clear all cells in the range
    for (Cell c : result) {
        c.setCellType(CellType.BLANK);
    }
    return result;
}
Also used : CellValueRecordInterface(org.apache.poi.hssf.record.CellValueRecordInterface) FormulaRecordAggregate(org.apache.poi.hssf.record.aggregates.FormulaRecordAggregate) CellRangeAddress(org.apache.poi.ss.util.CellRangeAddress) CellReference(org.apache.poi.ss.util.CellReference) Cell(org.apache.poi.ss.usermodel.Cell)

Example 9 with CellValueRecordInterface

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

the class TestFormatTrackingHSSFListener method testTurnToString.

/**
	 * Ensure that all number and formula records can be
	 *  turned into strings without problems.
	 * For now, we're just looking to get text back, no
	 *  exceptions thrown, but in future we might also
	 *  want to check the exact strings!
	 */
@Test
public void testTurnToString() throws Exception {
    String[] files = new String[] { "45365.xls", "45365-2.xls", "MissingBits.xls" };
    for (String file : files) {
        processFile(file);
        // Check we found our formats
        assertTrue(listener.getNumberOfCustomFormats() > 5);
        assertTrue(listener.getNumberOfExtendedFormats() > 5);
        //  cells into strings without error
        for (Record r : mockListen._records) {
            CellValueRecordInterface cvr = null;
            if (r instanceof NumberRecord) {
                cvr = (CellValueRecordInterface) r;
            }
            if (r instanceof FormulaRecord) {
                cvr = (CellValueRecordInterface) r;
            }
            if (cvr != null) {
                // Should always give us a string 
                String s = listener.formatNumberDateCell(cvr);
                assertNotNull(s);
                assertTrue(s.length() > 0);
            }
        }
    // TODO - test some specific format strings
    }
}
Also used : FormulaRecord(org.apache.poi.hssf.record.FormulaRecord) CellValueRecordInterface(org.apache.poi.hssf.record.CellValueRecordInterface) Record(org.apache.poi.hssf.record.Record) NumberRecord(org.apache.poi.hssf.record.NumberRecord) FormulaRecord(org.apache.poi.hssf.record.FormulaRecord) NumberRecord(org.apache.poi.hssf.record.NumberRecord) Test(org.junit.Test)

Example 10 with CellValueRecordInterface

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

the class TestBugs method bugZipCodeFormulas.

/**
     * From the mailing list - ensure we can handle a formula
     *  containing a zip code, eg ="70164"
     */
@Test
public void bugZipCodeFormulas() throws Exception {
    HSSFWorkbook wb1 = new HSSFWorkbook();
    HSSFSheet s = wb1.createSheet();
    s.createRow(0);
    HSSFCell c1 = s.getRow(0).createCell(0);
    HSSFCell c2 = s.getRow(0).createCell(1);
    HSSFCell c3 = s.getRow(0).createCell(2);
    // As number and string
    c1.setCellFormula("70164");
    c2.setCellFormula("\"70164\"");
    c3.setCellFormula("\"90210\"");
    // Check the formulas
    assertEquals("70164", c1.getCellFormula());
    assertEquals("\"70164\"", c2.getCellFormula());
    // And check the values - blank
    confirmCachedValue(0.0, c1);
    confirmCachedValue(0.0, c2);
    confirmCachedValue(0.0, c3);
    // Try changing the cached value on one of the string
    //  formula cells, so we can see it updates properly
    c3.setCellValue(new HSSFRichTextString("test"));
    confirmCachedValue("test", c3);
    try {
        c3.getNumericCellValue();
        fail("exception should have been thrown");
    } catch (IllegalStateException e) {
        assertEquals("Cannot get a NUMERIC value from a STRING formula cell", e.getMessage());
    }
    // Now evaluate, they should all be changed
    HSSFFormulaEvaluator eval = new HSSFFormulaEvaluator(wb1);
    eval.evaluateFormulaCellEnum(c1);
    eval.evaluateFormulaCellEnum(c2);
    eval.evaluateFormulaCellEnum(c3);
    // Check that the cells now contain
    //  the correct values
    confirmCachedValue(70164.0, c1);
    confirmCachedValue("70164", c2);
    confirmCachedValue("90210", c3);
    // Write and read
    HSSFWorkbook wb2 = writeOutAndReadBack(wb1);
    wb1.close();
    HSSFSheet ns = wb2.getSheetAt(0);
    HSSFCell nc1 = ns.getRow(0).getCell(0);
    HSSFCell nc2 = ns.getRow(0).getCell(1);
    HSSFCell nc3 = ns.getRow(0).getCell(2);
    // Re-check
    confirmCachedValue(70164.0, nc1);
    confirmCachedValue("70164", nc2);
    confirmCachedValue("90210", nc3);
    int i = 0;
    for (Iterator<CellValueRecordInterface> it = ns.getSheet().getCellValueIterator(); it.hasNext(); i++) {
        CellValueRecordInterface cvr = it.next();
        if (cvr instanceof FormulaRecordAggregate) {
            FormulaRecordAggregate fr = (FormulaRecordAggregate) cvr;
            if (i == 0) {
                assertEquals(70164.0, fr.getFormulaRecord().getValue(), 0.0001);
                assertNull(fr.getStringRecord());
            } else if (i == 1) {
                assertEquals(0.0, fr.getFormulaRecord().getValue(), 0.0001);
                assertNotNull(fr.getStringRecord());
                assertEquals("70164", fr.getStringRecord().getString());
            } else {
                assertEquals(0.0, fr.getFormulaRecord().getValue(), 0.0001);
                assertNotNull(fr.getStringRecord());
                assertEquals("90210", fr.getStringRecord().getString());
            }
        }
    }
    assertEquals(3, i);
    wb2.close();
}
Also used : CellValueRecordInterface(org.apache.poi.hssf.record.CellValueRecordInterface) FormulaRecordAggregate(org.apache.poi.hssf.record.aggregates.FormulaRecordAggregate) Test(org.junit.Test)

Aggregations

CellValueRecordInterface (org.apache.poi.hssf.record.CellValueRecordInterface)14 MulBlankRecord (org.apache.poi.hssf.record.MulBlankRecord)6 BlankRecord (org.apache.poi.hssf.record.BlankRecord)5 Test (org.junit.Test)5 Record (org.apache.poi.hssf.record.Record)4 RowRecord (org.apache.poi.hssf.record.RowRecord)4 FormulaRecord (org.apache.poi.hssf.record.FormulaRecord)3 FormulaRecordAggregate (org.apache.poi.hssf.record.aggregates.FormulaRecordAggregate)3 DimensionsRecord (org.apache.poi.hssf.record.DimensionsRecord)2 StringRecord (org.apache.poi.hssf.record.StringRecord)2 WindowTwoRecord (org.apache.poi.hssf.record.WindowTwoRecord)2 Cell (org.apache.poi.ss.usermodel.Cell)2 ArrayList (java.util.ArrayList)1 AssertionFailedError (junit.framework.AssertionFailedError)1 EscherRecord (org.apache.poi.ddf.EscherRecord)1 LastCellOfRowDummyRecord (org.apache.poi.hssf.eventusermodel.dummyrecord.LastCellOfRowDummyRecord)1 MissingCellDummyRecord (org.apache.poi.hssf.eventusermodel.dummyrecord.MissingCellDummyRecord)1 MissingRowDummyRecord (org.apache.poi.hssf.eventusermodel.dummyrecord.MissingRowDummyRecord)1 InternalSheet (org.apache.poi.hssf.model.InternalSheet)1 AutoFilterInfoRecord (org.apache.poi.hssf.record.AutoFilterInfoRecord)1