org.apache.poi.hssf.model.HSSFFormulaParser Java Examples

@SuppressWarnings("resource")
   private Ptg[] createListFormula(HSSFSheet sheet) {

	if (_explicitListValues == null) {
           HSSFWorkbook wb = sheet.getWorkbook();
           // formula is parsed with slightly different RVA rules: (root node type must be 'reference')
		return HSSFFormulaParser.parse(_formula1, wb, FormulaType.DATAVALIDATION_LIST, wb.getSheetIndex(sheet));
		// To do: Excel places restrictions on the available operations within a list formula.
		// Some things like union and intersection are not allowed.
	}
	// explicit list was provided
	StringBuffer sb = new StringBuffer(_explicitListValues.length * 16);
	for (int i = 0; i < _explicitListValues.length; i++) {
		if (i > 0) {
			sb.append('\0'); // list delimiter is the nul char
		}
		sb.append(_explicitListValues[i]);
	
	}
	return new Ptg[] { new StringPtg(sb.toString()), };
}
 

/**
 * Sets the printarea for the sheet provided
 * <p>
 * i.e. Reference = $A$1:$B$2
 * @param sheetIndex Zero-based sheet index (0 Represents the first sheet to keep consistent with java)
 * @param reference Valid name Reference for the Print Area
 */
@Override
public void setPrintArea(int sheetIndex, String reference)
{
    NameRecord name = workbook.getSpecificBuiltinRecord(NameRecord.BUILTIN_PRINT_AREA, sheetIndex+1);


    if (name == null) {
        name = workbook.createBuiltInName(NameRecord.BUILTIN_PRINT_AREA, sheetIndex+1);
        // adding one here because 0 indicates a global named region; doesn't make sense for print areas
    }
    String[] parts = COMMA_PATTERN.split(reference);
    StringBuffer sb = new StringBuffer(32);
    for (int i = 0; i < parts.length; i++) {
        if(i>0) {
            sb.append(",");
        }
        SheetNameFormatter.appendFormat(sb, getSheetName(sheetIndex));
        sb.append("!");
        sb.append(parts[i]);
    }
    name.setNameDefinition(HSSFFormulaParser.parse(sb.toString(), this, FormulaType.NAMEDRANGE, sheetIndex));
}
 

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

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

/**
 * @return The parsed token array representing the formula or value specified. 
 * Empty array if both formula and value are <code>null</code>
 */
   @SuppressWarnings("resource")
private static Ptg[] convertDoubleFormula(String formula, Double value, HSSFSheet sheet) {
	if (formula == null) {
		if (value == null) {
			return Ptg.EMPTY_PTG_ARRAY;
		}
		return new Ptg[] { new NumberPtg(value.doubleValue()), };
	}
	if (value != null) {
		throw new IllegalStateException("Both formula and value cannot be present");
	}
       HSSFWorkbook wb = sheet.getWorkbook();
	return HSSFFormulaParser.parse(formula, wb, FormulaType.CELL, wb.getSheetIndex(sheet));
}
 

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

@Override
public CellRange<HSSFCell> setArrayFormula(String formula, CellRangeAddress range) {
    // make sure the formula parses OK first
    int sheetIndex = _workbook.getSheetIndex(this);
    Ptg[] ptgs = HSSFFormulaParser.parse(formula, _workbook, FormulaType.ARRAY, sheetIndex);
    CellRange<HSSFCell> cells = getCellRange(range);

    for (HSSFCell c : cells) {
        c.setCellArrayFormula(range);
    }
    HSSFCell mainArrayFormulaCell = cells.getTopLeftCell();
    FormulaRecordAggregate agg = (FormulaRecordAggregate) mainArrayFormulaCell.getCellValueRecord();
    agg.setArrayFormula(range, ptgs);
    return cells;
}
 

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

@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;
    }
}
 

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

public void setRefersToFormula(String formulaText) {
    Ptg[] ptgs = HSSFFormulaParser.parse(formulaText, _book, FormulaType.NAMEDRANGE, getSheetIndex());
    _definedNameRec.setNameDefinition(ptgs);
}
 

protected static String toFormulaString(Ptg[] parsedExpression, HSSFWorkbook workbook) {
    if(parsedExpression == null || parsedExpression.length == 0) {
        return null;
    }
    return HSSFFormulaParser.toFormulaString(workbook, parsedExpression);
}
 

public String getCellFormula() {
    if (!(_record instanceof FormulaRecordAggregate)) {
        throw typeMismatch(CellType.FORMULA, _cellType, true);
    }
    return HSSFFormulaParser.toFormulaString(_book, ((FormulaRecordAggregate)_record).getFormulaTokens());
}
 

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

/**
 * TODO - parse conditional format formulas properly i.e. produce tRefN and tAreaN instead of tRef and tArea
 * this call will produce the wrong results if the formula contains any cell references
 * One approach might be to apply the inverse of SharedFormulaRecord.convertSharedFormulas(Stack, int, int)
 * Note - two extra parameters (rowIx & colIx) will be required. They probably come from one of the Region objects.
 *
 * @param formula  The formula to parse, excluding the leading equals sign.
 * @param sheet  The sheet that the formula is on.
 * @return <code>null</code> if <tt>formula</tt> was null.
 */
public static Ptg[] parseFormula(String formula, HSSFSheet sheet) {
    if(formula == null) {
        return null;
    }
    int sheetIndex = sheet.getWorkbook().getSheetIndex(sheet);
    return HSSFFormulaParser.parse(formula, sheet.getWorkbook(), FormulaType.CELL, sheetIndex);
}