You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@poi.apache.org by Greg Woolsey <gr...@gmail.com> on 2017/06/09 23:42:23 UTC
DataTables and formula evaluation
POI doesn't currently support "data table" formulas (a variation on array
formulas). However, the implementation of XSSFCell.isFormulaCell() still
thinks the "master" cell for a data table is a formula type cell. But the
formula is not a stored one, but implied by the definition of a data table
and it's parameters. Thus getCellFormula() returns an empty string, which
doesn't parse as a valid formula.
I think isFormulaCell() should just not consider these as formula cells
until POI supports data table formulas. Currently saying they are formulas
but not actually figuring out what that formula is doesn't make sense.
Anyone think of a case this would be bad? It should only affect data table
master cells, changing the current expression from:
if (_cell.getF() != null
|| getSheet().isCellInArrayFormulaContext(this))
to
if ( (_cell.isSetF() && _cell.getF().getT() != STCellFormulaType.DATA_TABLE
)
|| getSheet().isCellInArrayFormulaContext(this))
doesn't break anything for me.
Re: DataTables and formula evaluation
Posted by Greg Woolsey <gr...@gmail.com>.
Also, isFormulaCell() is private. the JavaDoc will go on getCellType().
On Fri, Jun 9, 2017 at 5:45 PM Greg Woolsey <gr...@gmail.com> wrote:
> If we want additional methods, the JavaDoc needs to also note that only
> the top left cell has this property, the rest appear as normal cells with
> normal static values. The only way to tell is to use the existing method
> isPartOfArrayFormulaGroup() which asks the sheet if it is in the range of
> an array formula it knows about. that's probably good for now, with
> JavaDoc mentioning POI doesn't handle data tables yet, which includes their
> functionality that overlaps array formulas.
>
> On Fri, Jun 9, 2017 at 5:16 PM Javen O'Neal <on...@apache.org> wrote:
>
>> I think most people using isFormulaCell assume they're working with a cell
>> containing a regular formula, not a cell that belongs to an array formula
>> or table.
>> I'd say it's fair to document this distinction in the JavaDoc and provide
>> a
>> isArrayFormulaCell and isTableFormulaCell if those concepts are useful.
>>
>> On Jun 9, 2017 4:42 PM, "Greg Woolsey" <gr...@gmail.com> wrote:
>>
>> POI doesn't currently support "data table" formulas (a variation on array
>> formulas). However, the implementation of XSSFCell.isFormulaCell() still
>> thinks the "master" cell for a data table is a formula type cell. But the
>> formula is not a stored one, but implied by the definition of a data table
>> and it's parameters. Thus getCellFormula() returns an empty string, which
>> doesn't parse as a valid formula.
>>
>> I think isFormulaCell() should just not consider these as formula cells
>> until POI supports data table formulas. Currently saying they are
>> formulas
>> but not actually figuring out what that formula is doesn't make sense.
>>
>> Anyone think of a case this would be bad? It should only affect data
>> table
>> master cells, changing the current expression from:
>>
>> if (_cell.getF() != null
>> || getSheet().isCellInArrayFormulaContext(this))
>>
>> to
>>
>> if ( (_cell.isSetF() && _cell.getF().getT() !=
>> STCellFormulaType.DATA_TABLE
>> )
>> || getSheet().isCellInArrayFormulaContext(this))
>>
>> doesn't break anything for me.
>>
>
Re: DataTables and formula evaluation
Posted by Greg Woolsey <gr...@gmail.com>.
If we want additional methods, the JavaDoc needs to also note that only the
top left cell has this property, the rest appear as normal cells with
normal static values. The only way to tell is to use the existing method
isPartOfArrayFormulaGroup() which asks the sheet if it is in the range of
an array formula it knows about. that's probably good for now, with
JavaDoc mentioning POI doesn't handle data tables yet, which includes their
functionality that overlaps array formulas.
On Fri, Jun 9, 2017 at 5:16 PM Javen O'Neal <on...@apache.org> wrote:
> I think most people using isFormulaCell assume they're working with a cell
> containing a regular formula, not a cell that belongs to an array formula
> or table.
> I'd say it's fair to document this distinction in the JavaDoc and provide a
> isArrayFormulaCell and isTableFormulaCell if those concepts are useful.
>
> On Jun 9, 2017 4:42 PM, "Greg Woolsey" <gr...@gmail.com> wrote:
>
> POI doesn't currently support "data table" formulas (a variation on array
> formulas). However, the implementation of XSSFCell.isFormulaCell() still
> thinks the "master" cell for a data table is a formula type cell. But the
> formula is not a stored one, but implied by the definition of a data table
> and it's parameters. Thus getCellFormula() returns an empty string, which
> doesn't parse as a valid formula.
>
> I think isFormulaCell() should just not consider these as formula cells
> until POI supports data table formulas. Currently saying they are formulas
> but not actually figuring out what that formula is doesn't make sense.
>
> Anyone think of a case this would be bad? It should only affect data table
> master cells, changing the current expression from:
>
> if (_cell.getF() != null
> || getSheet().isCellInArrayFormulaContext(this))
>
> to
>
> if ( (_cell.isSetF() && _cell.getF().getT() != STCellFormulaType.DATA_TABLE
> )
> || getSheet().isCellInArrayFormulaContext(this))
>
> doesn't break anything for me.
>
Re: DataTables and formula evaluation
Posted by Javen O'Neal <on...@apache.org>.
I think most people using isFormulaCell assume they're working with a cell
containing a regular formula, not a cell that belongs to an array formula
or table.
I'd say it's fair to document this distinction in the JavaDoc and provide a
isArrayFormulaCell and isTableFormulaCell if those concepts are useful.
On Jun 9, 2017 4:42 PM, "Greg Woolsey" <gr...@gmail.com> wrote:
POI doesn't currently support "data table" formulas (a variation on array
formulas). However, the implementation of XSSFCell.isFormulaCell() still
thinks the "master" cell for a data table is a formula type cell. But the
formula is not a stored one, but implied by the definition of a data table
and it's parameters. Thus getCellFormula() returns an empty string, which
doesn't parse as a valid formula.
I think isFormulaCell() should just not consider these as formula cells
until POI supports data table formulas. Currently saying they are formulas
but not actually figuring out what that formula is doesn't make sense.
Anyone think of a case this would be bad? It should only affect data table
master cells, changing the current expression from:
if (_cell.getF() != null
|| getSheet().isCellInArrayFormulaContext(this))
to
if ( (_cell.isSetF() && _cell.getF().getT() != STCellFormulaType.DATA_TABLE
)
|| getSheet().isCellInArrayFormulaContext(this))
doesn't break anything for me.