Java Code Examples for org.apache.poi.hssf.model.HSSFFormulaParser#toFormulaString()

The following examples show how to use org.apache.poi.hssf.model.HSSFFormulaParser#toFormulaString() . 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: XLSFormatter.java    From yarg with Apache License 2.0 6 votes vote down vote up 
protected void updateFormulas(Area templateArea, Area dependentResultArea) {
    HSSFSheet templateSheet = getTemplateSheetForRangeName(templateWorkbook, templateArea.getName());
    HSSFSheet resultSheet = templateToResultSheetsMapping.get(templateSheet);

    AreaReference area = dependentResultArea.toAreaReference();
    for (CellReference cell : area.getAllReferencedCells()) {
        HSSFCell resultCell = getCellFromReference(cell, resultSheet);

        if (resultCell.getCellType() == CellType.FORMULA) {
            Ptg[] ptgs = HSSFFormulaParser.parse(resultCell.getCellFormula(), resultWorkbook);

            for (Ptg ptg : ptgs) {
                if (ptg instanceof AreaPtg) {
                    areaDependencyManager.updateAreaPtg(templateArea, dependentResultArea, (AreaPtg) ptg);
                } else if (ptg instanceof RefPtg) {
                    areaDependencyManager.updateRefPtg(templateArea, dependentResultArea, (RefPtg) ptg);
                }
            }

            String calculatedFormula = HSSFFormulaParser.toFormulaString(templateWorkbook, ptgs);
            resultCell.setCellFormula(calculatedFormula);
        }
    }
}
Example 2
Source File: HSSFName.java    From lams with GNU General Public License v2.0 5 votes vote down vote up 
public String getRefersToFormula() {
    if (_definedNameRec.isFunctionName()) {
        throw new IllegalStateException("Only applicable to named ranges");
    }
    Ptg[] ptgs = _definedNameRec.getNameDefinition();
    if (ptgs.length < 1) {
        // 'refersToFormula' has not been set yet
        return null;
    }
    return HSSFFormulaParser.toFormulaString(_book, ptgs);
}
Example 3
Source File: HSSFWorkbook.java    From lams with GNU General Public License v2.0 5 votes vote down vote up 
/**
 * Retrieves the reference for the printarea of the specified sheet, the sheet name is appended to the reference even if it was not specified.
 * @param sheetIndex Zero-based sheet index (0 Represents the first sheet to keep consistent with java)
 * @return String Null if no print area has been defined
 */
@Override
public String getPrintArea(int sheetIndex) {
    NameRecord name = workbook.getSpecificBuiltinRecord(NameRecord.BUILTIN_PRINT_AREA, sheetIndex+1);
    //adding one here because 0 indicates a global named region; doesn't make sense for print areas
    if (name == null) {
        return null;
    }

    return HSSFFormulaParser.toFormulaString(this, name.getNameDefinition());
}
Example 4
Source File: FormulaRecordHandler.java    From easyexcel with Apache License 2.0 4 votes vote down vote up 
@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 5
Source File: FormulaViewer.java    From lams with GNU General Public License v2.0 4 votes vote down vote up 
private static String composeFormula(FormulaRecord record)
{
   return  HSSFFormulaParser.toFormulaString((HSSFWorkbook)null, record.getParsedExpression());
}
Example 6
Source File: HSSFConditionalFormattingRule.java    From lams with GNU General Public License v2.0 4 votes vote down vote up 
protected static String toFormulaString(Ptg[] parsedExpression, HSSFWorkbook workbook) {
    if(parsedExpression == null || parsedExpression.length == 0) {
        return null;
    }
    return HSSFFormulaParser.toFormulaString(workbook, parsedExpression);
}
Example 7
Source File: HSSFCell.java    From lams with GNU General Public License v2.0 4 votes vote down vote up 
public String getCellFormula() {
    if (!(_record instanceof FormulaRecordAggregate)) {
        throw typeMismatch(CellType.FORMULA, _cellType, true);
    }
    return HSSFFormulaParser.toFormulaString(_book, ((FormulaRecordAggregate)_record).getFormulaTokens());
}
Example 8
Source File: EventBasedExcelExtractor.java    From lams with GNU General Public License v2.0 4 votes vote down vote up 
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);
    }
}