org.apache.poi.hssf.record.FormulaRecord Java Examples

The following examples show how to use org.apache.poi.hssf.record.FormulaRecord. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.

Example #1

Source File: RowBlock.java From dremio-oss with Apache License 2.0

7 votes

public void process(Record r) {
  switch (r.getSid()) {
    case MergeCellsRecord.sid:
      mergeCellRecords.add((MergeCellsRecord) r);
      break;
    case SharedFormulaRecord.sid:
      shFrmRecords.add((SharedFormulaRecord) r);
      if (!(prevRec instanceof FormulaRecord)) {
        throw new RuntimeException("Shared formula record should follow a FormulaRecord");
      }
      FormulaRecord fr = (FormulaRecord) prevRec;
      firstCellRefs.add(new CellReference(fr.getRow(), fr.getColumn()));
      break;
    case ArrayRecord.sid:
      arrayRecords.add((ArrayRecord) r);
      break;
    case TableRecord.sid:
      tableRecords.add((TableRecord) r);
      break;
    default:
      plainRecords.add(r);
      break;
  }
  prevRec = r;
}

Example #2

Source File: RowBlock.java From dremio-oss with Apache License 2.0

6 votes

static boolean isRowBlockRecord(int sid) {
  switch (sid) {
    case RowRecord.sid:

    case BlankRecord.sid:
    case BoolErrRecord.sid:
    case FormulaRecord.sid:
    case LabelRecord.sid:
    case LabelSSTRecord.sid:
    case NumberRecord.sid:
    case RKRecord.sid:

    case ArrayRecord.sid:
    case SharedFormulaRecord.sid:
    case TableRecord.sid:
      return true;
  }
  return false;
}

Example #3

Source File: HSSFCell.java From lams with GNU General Public License v2.0

6 votes

/**
 * Get the value of the cell as a number.
 * For strings we throw an exception.
 * For blank cells we return a 0.
 * See {@link HSSFDataFormatter} for turning this
 *  number into a string similar to that which
 *  Excel would render this number as.
 */
public double getNumericCellValue() {

    switch(_cellType) {
        case BLANK:
            return 0.0;
        case NUMERIC:
            return ((NumberRecord)_record).getValue();
        default:
            throw typeMismatch(CellType.NUMERIC, _cellType, false);
        case FORMULA:
            break;
    }
    FormulaRecord fr = ((FormulaRecordAggregate)_record).getFormulaRecord();
    checkFormulaCachedValueType(CellType.NUMERIC, fr);
    return fr.getValue();
}

Example #4

Source File: RecordOrderer.java From lams with GNU General Public License v2.0

6 votes

/**
 * @return <code>true</code> if the specified record id normally appears in the row blocks section
 * of the sheet records
 */
public static boolean isRowBlockRecord(int sid) {
	switch (sid) {
		case RowRecord.sid:

		case BlankRecord.sid:
		case BoolErrRecord.sid:
		case FormulaRecord.sid:
		case LabelRecord.sid:
		case LabelSSTRecord.sid:
		case NumberRecord.sid:
		case RKRecord.sid:

		case ArrayRecord.sid:
		case SharedFormulaRecord.sid:
		case TableRecord.sid:
			return true;
	}
	return false;
}

Example #5

Source File: HSSFCell.java From lams with GNU General Public License v2.0

6 votes

/**
 * get the value of the cell as a boolean.  For strings, numbers, and errors, we throw an exception.
 * For blank cells we return a false.
 */
@Override
public boolean getBooleanCellValue() {

    switch(_cellType) {
        case BLANK:
            return false;
        case BOOLEAN:
            return (( BoolErrRecord ) _record).getBooleanValue();
        case FORMULA:
            break;
        default:
            throw typeMismatch(CellType.BOOLEAN, _cellType, false);
    }
    FormulaRecord fr = ((FormulaRecordAggregate)_record).getFormulaRecord();
    checkFormulaCachedValueType(CellType.BOOLEAN, fr);
    return fr.getCachedBooleanValue();
}

Example #6

Source File: FormatTrackingHSSFListener.java From lams with GNU General Public License v2.0

6 votes

/**
 * Formats the given numeric of date cells contents as a String, in as
 * close as we can to the way that Excel would do so. Uses the various
 * format records to manage this.
 *
 * TODO - move this to a central class in such a way that hssf.usermodel can
 * make use of it too
 * 
 * @param cell the cell
 * 
 * @return the given numeric of date cells contents as a String
 */
public String formatNumberDateCell(CellValueRecordInterface cell) {
	double value;
	if (cell instanceof NumberRecord) {
		value = ((NumberRecord) cell).getValue();
	} else if (cell instanceof FormulaRecord) {
		value = ((FormulaRecord) cell).getValue();
	} else {
		throw new IllegalArgumentException("Unsupported CellValue Record passed in " + cell);
	}

	// Get the built in format, if there is one
	int formatIndex = getFormatIndex(cell);
	String formatString = getFormatString(cell);

	if (formatString == null) {
		return _defaultFormat.format(value);
	}
	// Format, using the nice new
	// HSSFDataFormatter to do the work for us
	return _formatter.formatRawCellContents(value, formatIndex, formatString);
}

Example #7

Source File: FormulaViewer.java From lams with GNU General Public License v2.0

6 votes

/**
 * Method run
 * 
 * @throws IOException if the file contained errors 
 */
public void run() throws IOException {
    NPOIFSFileSystem fs  = new NPOIFSFileSystem(new File(file), true);
    try {
        InputStream is = BiffViewer.getPOIFSInputStream(fs);
        try {
            List<Record> records = RecordFactory.createRecords(is);

            for (Record record : records) {
                if (record.getSid() == FormulaRecord.sid) {
                    if (list) {
                        listFormula((FormulaRecord) record);
                    } else {
                        parseFormulaRecord((FormulaRecord) record);
                    }
                }
            }
        } finally {
            is.close();
        }
    } finally {
        fs.close();
    }
}

Example #8

Source File: FormulaViewer.java From lams with GNU General Public License v2.0

6 votes

private String formulaString(FormulaRecord record) {

        StringBuilder buf = new StringBuilder();
		Ptg[] tokens = record.getParsedExpression();
		for (Ptg token : tokens) {
			buf.append( token.toFormulaString());
            switch (token.getPtgClass()) {
                case Ptg.CLASS_REF :
                    buf.append("(R)");
                    break;
                case Ptg.CLASS_VALUE :
                    buf.append("(V)");
                    break;
                case Ptg.CLASS_ARRAY :
                    buf.append("(A)");
                    break;
                default:
                    throwInvalidRVAToken(token);
            }
            buf.append(' ');
        } 
        return buf.toString();
    }

Example #9

Source File: HSSFCell.java From lams with GNU General Public License v2.0

5 votes

private String convertCellValueToString() {

        switch (_cellType) {
            case BLANK:
                return "";
            case BOOLEAN:
                return ((BoolErrRecord) _record).getBooleanValue() ? "TRUE" : "FALSE";
            case STRING:
                int sstIndex = ((LabelSSTRecord)_record).getSSTIndex();
                return _book.getWorkbook().getSSTString(sstIndex).getString();
            case NUMERIC:
                return NumberToTextConverter.toText(((NumberRecord)_record).getValue());
            case ERROR:
                   return FormulaError.forInt(((BoolErrRecord)_record).getErrorValue()).getString();
            case FORMULA:
                // should really evaluate, but HSSFCell can't call HSSFFormulaEvaluator
                // just use cached formula result instead
                break;
            default:
                throw new IllegalStateException("Unexpected cell type (" + _cellType + ")");
        }
        FormulaRecordAggregate fra = ((FormulaRecordAggregate)_record);
        FormulaRecord fr = fra.getFormulaRecord();
        switch (CellType.forInt(fr.getCachedResultType())) {
            case BOOLEAN:
                return fr.getCachedBooleanValue() ? "TRUE" : "FALSE";
            case STRING:
                return fra.getStringValue();
            case NUMERIC:
                return NumberToTextConverter.toText(fr.getValue());
            case ERROR:
                return FormulaError.forInt(fr.getCachedErrorValue()).getString();
            default:
                throw new IllegalStateException("Unexpected formula result type (" + _cellType + ")");
        }
        
    }

Example #10

Source File: HSSFCell.java From lams with GNU General Public License v2.0

5 votes

/**
 * Chooses a new boolean value for the cell when its type is changing.<p>
 *
 * Usually the caller is calling setCellType() with the intention of calling
 * setCellValue(boolean) straight afterwards.  This method only exists to give
 * the cell a somewhat reasonable value until the setCellValue() call (if at all).
 * TODO - perhaps a method like setCellTypeAndValue(int, Object) should be introduced to avoid this
 */
private boolean convertCellValueToBoolean() {

    switch (_cellType) {
        case BOOLEAN:
            return (( BoolErrRecord ) _record).getBooleanValue();
        case STRING:
            int sstIndex = ((LabelSSTRecord)_record).getSSTIndex();
            String text = _book.getWorkbook().getSSTString(sstIndex).getString();
            return Boolean.valueOf(text).booleanValue();
        case NUMERIC:
            return ((NumberRecord)_record).getValue() != 0;

        case FORMULA:
            // use cached formula result if it's the right type:
            FormulaRecord fr = ((FormulaRecordAggregate)_record).getFormulaRecord();
            checkFormulaCachedValueType(CellType.BOOLEAN, fr);
            return fr.getCachedBooleanValue();
        // Other cases convert to false
        // These choices are not well justified.
        case ERROR:
        case BLANK:
            return false;
    }
    throw new RuntimeException("Unexpected cell type (" + _cellType + ")");
}

Example #11

Source File: HSSFCell.java From lams with GNU General Public License v2.0

5 votes

public void setCellFormula(String formula) {
    if(isPartOfArrayFormulaGroup()){
        notifyArrayFormulaChanging();
    }

    int row=_record.getRow();
    short col=_record.getColumn();
    short styleIndex=_record.getXFIndex();

    if (formula==null) {
        notifyFormulaChanging();
        setCellType(CellType.BLANK, false, row, col, styleIndex);
        return;
    }
    int sheetIndex = _book.getSheetIndex(_sheet);
    Ptg[] ptgs = HSSFFormulaParser.parse(formula, _book, FormulaType.CELL, sheetIndex);
    setCellType(CellType.FORMULA, false, row, col, styleIndex);
    FormulaRecordAggregate agg = (FormulaRecordAggregate) _record;
    FormulaRecord frec = agg.getFormulaRecord();
    frec.setOptions((short) 2);
    frec.setValue(0);

    //only set to default if there is no extended format index already set
    if (agg.getXFIndex() == (short)0) {
        agg.setXFIndex((short) 0x0f);
    }
    agg.setParsedExpression(ptgs);
}

Example #12

Source File: HSSFCell.java From lams with GNU General Public License v2.0

5 votes

/**
 * get the value of the cell as an error code.  For strings, numbers, and booleans, we throw an exception.
 * For blank cells we return a 0.
 */
@Override
public byte getErrorCellValue() {
    switch(_cellType) {
        case ERROR:
            return (( BoolErrRecord ) _record).getErrorValue();
        case FORMULA:
            FormulaRecord fr = ((FormulaRecordAggregate)_record).getFormulaRecord();
            checkFormulaCachedValueType(CellType.ERROR, fr);
            return (byte) fr.getCachedErrorValue();
        default:
            throw typeMismatch(CellType.ERROR, _cellType, false);
    }
}

Example #13

Source File: FormulaViewer.java From lams with GNU General Public License v2.0

5 votes

/**
 * Method parseFormulaRecord
 *
 * @param record the record to be parsed
 */
public void parseFormulaRecord(FormulaRecord record)
{
    System.out.println("==============================");
    System.out.print("row = " + record.getRow());
    System.out.println(", col = " + record.getColumn());
    System.out.println("value = " + record.getValue());
    System.out.print("xf = " + record.getXFIndex());
    System.out.print(", number of ptgs = "
                       + record.getParsedExpression().length);
    System.out.println(", options = " + record.getOptions());
    System.out.println("RPN List = "+formulaString(record));
    System.out.println("Formula text = "+ composeFormula(record));
}

Example #14

Source File: FormulaRecordAggregate.java From lams with GNU General Public License v2.0

5 votes

/**
 * @param stringRec may be <code>null</code> if this formula does not have a cached text
 * value.
 * @param svm the {@link SharedValueManager} for the current sheet
 */
public FormulaRecordAggregate(FormulaRecord formulaRec, StringRecord stringRec, SharedValueManager svm) {
	if (svm == null) {
		throw new IllegalArgumentException("sfm must not be null");
	}
	if (formulaRec.hasCachedResultString()) {
		if (stringRec == null) {
			throw new RecordFormatException("Formula record flag is set but String record was not found");
		}
		_stringRecord = stringRec;
	} else {
		// Usually stringRec is null here (in agreement with what the formula rec says).
		// In the case where an extra StringRecord is erroneously present, Excel (2007)
		// ignores it (see bug 46213).
		_stringRecord = null;
	}

	_formulaRecord = formulaRec;
	_sharedValueManager = svm;
	if (formulaRec.isSharedFormula()) {
		CellReference firstCell = formulaRec.getFormula().getExpReference();
		if (firstCell == null) {
			handleMissingSharedFormulaRecord(formulaRec);
		} else {
			_sharedFormulaRecord = svm.linkSharedFormulaRecord(firstCell, this);
		}
	}
}

Example #15

Source File: EventBasedExcelExtractor.java From lams with GNU General Public License v2.0

4 votes

public void processRecord(Record record) {
    String thisText = null;
    int thisRow = -1;

    switch(record.getSid()) {
    case BoundSheetRecord.sid:
        BoundSheetRecord sr = (BoundSheetRecord)record;
        sheetNames.add(sr.getSheetname());
        break;
    case BOFRecord.sid:
        BOFRecord bof = (BOFRecord)record;
        if(bof.getType() == BOFRecord.TYPE_WORKSHEET) {
            sheetNum++;
            rowNum = -1;

            if(_includeSheetNames) {
                if(_text.length() > 0) _text.append("\n");
                _text.append(sheetNames.get(sheetNum));
            }
        }
        break;
    case SSTRecord.sid:
        sstRecord = (SSTRecord)record;
        break;

    case FormulaRecord.sid:
        FormulaRecord frec = (FormulaRecord) record;
        thisRow = frec.getRow();

        if(_formulasNotResults) {
            thisText = HSSFFormulaParser.toFormulaString((HSSFWorkbook)null, frec.getParsedExpression());
        } else {
            if(frec.hasCachedResultString()) {
                // Formula result is a string
                // This is stored in the next record
                outputNextStringValue = true;
                nextRow = frec.getRow();
            } else {
                thisText = _ft.formatNumberDateCell(frec);
            }
        }
        break;
    case StringRecord.sid:
        if(outputNextStringValue) {
            // String for formula
            StringRecord srec = (StringRecord)record;
            thisText = srec.getString();
            thisRow = nextRow;
            outputNextStringValue = false;
        }
        break;
    case LabelRecord.sid:
        LabelRecord lrec = (LabelRecord) record;
        thisRow = lrec.getRow();
        thisText = lrec.getValue();
        break;
    case LabelSSTRecord.sid:
        LabelSSTRecord lsrec = (LabelSSTRecord) record;
        thisRow = lsrec.getRow();
        if(sstRecord == null) {
            throw new IllegalStateException("No SST record found");
        }
        thisText = sstRecord.getString(lsrec.getSSTIndex()).toString();
        break;
    case NoteRecord.sid:
        NoteRecord nrec = (NoteRecord) record;
        thisRow = nrec.getRow();
        // TODO: Find object to match nrec.getShapeId()
        break;
    case NumberRecord.sid:
        NumberRecord numrec = (NumberRecord) record;
        thisRow = numrec.getRow();
        thisText = _ft.formatNumberDateCell(numrec);
        break;
    default:
        break;
    }

    if(thisText != null) {
        if(thisRow != rowNum) {
            rowNum = thisRow;
            if(_text.length() > 0)
                _text.append("\n");
        } else {
            _text.append("\t");
        }
        _text.append(thisText);
    }
}

Example #16

Source File: RowBlocksReader.java From lams with GNU General Public License v2.0

4 votes

/**
 * Also collects any loose MergeCellRecords and puts them in the supplied
 * mergedCellsTable
 * 
 * @param  rs the record stream
 */
public RowBlocksReader(RecordStream rs) {
	List<Record> plainRecords = new ArrayList<Record>();
	List<Record> shFrmRecords = new ArrayList<Record>();
	List<CellReference> firstCellRefs = new ArrayList<CellReference>();
	List<Record> arrayRecords = new ArrayList<Record>();
	List<Record> tableRecords = new ArrayList<Record>();
	List<Record> mergeCellRecords = new ArrayList<Record>();

	Record prevRec = null;
	while(!RecordOrderer.isEndOfRowBlock(rs.peekNextSid())) {
		// End of row/cell records for the current sheet
		// Note - It is important that this code does not inadvertently add any sheet
		// records from a subsequent sheet.  For example, if SharedFormulaRecords
		// are taken from the wrong sheet, this could cause bug 44449.
		if (!rs.hasNext()) {
			throw new RuntimeException("Failed to find end of row/cell records");

		}
		Record rec = rs.getNext();
		List<Record> dest;
		switch (rec.getSid()) {
			case MergeCellsRecord.sid:    dest = mergeCellRecords; break;
			case SharedFormulaRecord.sid: dest = shFrmRecords;
				if (!(prevRec instanceof FormulaRecord)) {
					throw new RuntimeException("Shared formula record should follow a FormulaRecord");
				}
				FormulaRecord fr = (FormulaRecord)prevRec;
				firstCellRefs.add(new CellReference(fr.getRow(), fr.getColumn()));
				break;
			case ArrayRecord.sid:         dest = arrayRecords;     break;
			case TableRecord.sid:         dest = tableRecords;     break;
			default:                      dest = plainRecords;
		}
		dest.add(rec);
		prevRec = rec;
	}
	SharedFormulaRecord[] sharedFormulaRecs = new SharedFormulaRecord[shFrmRecords.size()];
	CellReference[] firstCells = new CellReference[firstCellRefs.size()];
	ArrayRecord[] arrayRecs = new ArrayRecord[arrayRecords.size()];
	TableRecord[] tableRecs = new TableRecord[tableRecords.size()];
	shFrmRecords.toArray(sharedFormulaRecs);
	firstCellRefs.toArray(firstCells);
	arrayRecords.toArray(arrayRecs);
	tableRecords.toArray(tableRecs);

	_plainRecords = plainRecords;
	_sfm = SharedValueManager.create(sharedFormulaRecs, firstCells, arrayRecs, tableRecs);
	_mergedCellsRecords = new MergeCellsRecord[mergeCellRecords.size()];
	mergeCellRecords.toArray(_mergedCellsRecords);
}

Example #17

Source File: FormulaRecordAggregate.java From lams with GNU General Public License v2.0

4 votes

public FormulaRecord getFormulaRecord() {
	return _formulaRecord;
}

Example #18

Source File: HSSFCell.java From lams with GNU General Public License v2.0

4 votes

private static void checkFormulaCachedValueType(CellType expectedTypeCode, FormulaRecord fr) {
    CellType cachedValueType = CellType.forInt(fr.getCachedResultType());
    if (cachedValueType != expectedTypeCode) {
        throw typeMismatch(expectedTypeCode, cachedValueType, true);
    }
}

Example #19

Source File: FormulaRecordHandler.java From easyexcel with Apache License 2.0

4 votes

@Override
public void processRecord(XlsReadContext xlsReadContext, Record record) {
    FormulaRecord frec = (FormulaRecord)record;
    Map<Integer, Cell> cellMap = xlsReadContext.xlsReadSheetHolder().getCellMap();
    CellData tempCellData = new CellData();
    tempCellData.setRowIndex(frec.getRow());
    tempCellData.setColumnIndex((int)frec.getColumn());
    CellType cellType = CellType.forInt(frec.getCachedResultType());
    String formulaValue = null;
    try {
        formulaValue = HSSFFormulaParser.toFormulaString(xlsReadContext.xlsReadWorkbookHolder().getHssfWorkbook(),
            frec.getParsedExpression());
    } catch (Exception e) {
        LOGGER.debug("Get formula value error.", e);
    }
    tempCellData.setFormula(Boolean.TRUE);
    tempCellData.setFormulaValue(formulaValue);
    xlsReadContext.xlsReadSheetHolder().setTempRowType(RowTypeEnum.DATA);
    switch (cellType) {
        case STRING:
            // Formula result is a string
            // This is stored in the next record
            tempCellData.setType(CellDataTypeEnum.STRING);
            xlsReadContext.xlsReadSheetHolder().setTempCellData(tempCellData);
            break;
        case NUMERIC:
            tempCellData.setType(CellDataTypeEnum.NUMBER);
            tempCellData.setNumberValue(BigDecimal.valueOf(frec.getValue()));
            Integer dataFormat =
                xlsReadContext.xlsReadWorkbookHolder().getFormatTrackingHSSFListener().getFormatIndex(frec);
            tempCellData.setDataFormat(dataFormat);
            tempCellData.setDataFormatString(BuiltinFormats.getBuiltinFormat(dataFormat,
                xlsReadContext.xlsReadWorkbookHolder().getFormatTrackingHSSFListener().getFormatString(frec),
                xlsReadContext.readSheetHolder().getGlobalConfiguration().getLocale()));
            cellMap.put((int)frec.getColumn(), tempCellData);
            break;
        case ERROR:
            tempCellData.setType(CellDataTypeEnum.ERROR);
            tempCellData.setStringValue(ERROR);
            cellMap.put((int)frec.getColumn(), tempCellData);
            break;
        case BOOLEAN:
            tempCellData.setType(CellDataTypeEnum.BOOLEAN);
            tempCellData.setBooleanValue(frec.getCachedBooleanValue());
            cellMap.put((int)frec.getColumn(), tempCellData);
            break;
        default:
            tempCellData.setType(CellDataTypeEnum.EMPTY);
            cellMap.put((int)frec.getColumn(), tempCellData);
            break;
    }
}

Example #20

Source File: FormulaViewer.java From lams with GNU General Public License v2.0

4 votes

private static String composeFormula(FormulaRecord record)
{
   return  HSSFFormulaParser.toFormulaString((HSSFWorkbook)null, record.getParsedExpression());
}

Example #21

Source File: FormulaViewer.java From lams with GNU General Public License v2.0

4 votes

private void listFormula(FormulaRecord record) {
    String sep="~";
    Ptg[] tokens= record.getParsedExpression();
    Ptg token;
    int numptgs = tokens.length;
    String numArg;
        token = tokens[numptgs-1];
        if (token instanceof FuncPtg) {
            numArg = String.valueOf(numptgs-1);
        } else { 
        	numArg = String.valueOf(-1);
        }
        
        StringBuilder buf = new StringBuilder();
        
        if (token instanceof ExpPtg) return;
        buf.append(token.toFormulaString());
        buf.append(sep);
        switch (token.getPtgClass()) {
            case Ptg.CLASS_REF :
                buf.append("REF");
                break;
            case Ptg.CLASS_VALUE :
                buf.append("VALUE");
                break;
            case Ptg.CLASS_ARRAY :
                buf.append("ARRAY");
                break;
            default:
                throwInvalidRVAToken(token);
        }
        
        buf.append(sep);
        if (numptgs>1) {
            token = tokens[numptgs-2];
            switch (token.getPtgClass()) {
                case Ptg.CLASS_REF :
                    buf.append("REF");
                    break;
                case Ptg.CLASS_VALUE :
                    buf.append("VALUE");
                    break;
                case Ptg.CLASS_ARRAY :
                    buf.append("ARRAY");
                    break;
                default:
                    throwInvalidRVAToken(token);
            }
        }else {
            buf.append("VALUE");
        }
        buf.append(sep);
        buf.append(numArg);
        System.out.println(buf);
}

Example #22