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