[Bug 62948] Parser error: (degenerate?) row ranges is parsed incorrectly

[bu...@apache.org]

https://bz.apache.org/bugzilla/show_bug.cgi?id=62948

gallon.fizik@gmail.com <ga...@gmail.com> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 OS|                            |All

--- Comment #1 from gallon.fizik@gmail.com <ga...@gmail.com> ---
1. HSSF converts row range to cell range right away at setCellFormula. Demo:
        Cell cell = new
HSSFWorkbook().createSheet().createRow(0).createCell(0);
        cell.setCellFormula("SUM(85:85)");
        System.out.println(cell.getCellFormula()); // SUM($A85:$IV85)
2. XSSF does parse the formula string during a Cell.setCellFormula() but the
original string is stored. So for XSSF the case may seem insignificant but...

3. However I found a valid showcase: an expression like SUM(5:5) may be a
master formula for a shared formula. To produce formula strings for secondary
cells, the master formula is parsed, shifted as Ptg[] and rendered back to
string. And here's the case that the original form is overwritten.

    @Test
    public void demo() throws IOException {
        Row row = new
XSSFWorkbook("res/test.xlsx").getSheet("sheet").getRow(0);
        System.out.println(row.getCell(0).getCellFormula()); // SUM(5:5)
        System.out.println(row.getCell(1).getCellFormula()); // SUM($A5:$XFD5)
    }

Although seemingly insignificat for any evaluations, it's bad that formulas get
to look different.

4. And another thing. A case of a reference with column range (F:F) is handled
in AreaPtgBase.formatReferenceAsString() but with row range is not. It's pretty
easy to add. However,

*** formatReferenceAsString (and the whole FormulaRenderer) uses hard-coded
SpreadSheetVersion == EXCEL97 ***

so the formatter has now way to detect correctly if a reference covers a whole
row/range of rows.

Furthermore, FormulaParser *is* aware of the version... therefore the formatter
won't detect a reference as a row reference because it ises excel97, and the
sizes won't match. 

*** A QUESTION TO THE MAINTAINERS ***
My proposition:
* add FormulaRenderingWorkbook.getSpreadSheetVersion()
* pass the version to any Ptg.formatReferenceString()
* use EXCEL97 as the default version
* version-sensitive ptgs (well... a ptg doesn't care much about the version but
at least it formats itself) will use the version to produce a correct string.


It may also be a good thing gor the future in case the next spreadsheet format
has some differences in syntax/formatting.

-- 
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org