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

[bu...@apache.org]

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

            Bug ID: 62948
           Summary: Parser error: (degenerate?) row ranges is parsed
                    incorrectly
           Product: POI
           Version: 4.0.x-dev
          Hardware: PC
            Status: NEW
          Severity: normal
          Priority: P2
         Component: SS Common
          Assignee: dev@poi.apache.org
          Reporter: gallon.fizik@gmail.com
  Target Milestone: ---

Cell cell = new XSSFWorkbook().createSheet().createRow(0).createCell(0);
cell.setCellFormula("SUM(sheet!85:85)");

EvaluationWorkbook evaluationWorkbook =
XSSFEvaluationWorkbook.create((XSSFWorkbook) cell.getSheet().getWorkbook());

Ptg[] ptgs = FormulaParser.parse(cell.getCellFormula(),
(FormulaParsingWorkbook) evaluationWorkbook, FormulaType.CELL, 0, 0);

String reconstructed =
FormulaRenderer.toFormulaString((FormulaRenderingWorkbook) evaluationWorkbook,
ptgs);
        System.out.println(reconstructed);

// reconstructed == "SUM(sheet!$A85:$XFD85)" // wuut

I'm on it and will hopefully publish a fix soon

-- 
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

[Bug 62948] Row ranges are transformed to cell ranges during parsing

[bu...@apache.org]

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

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

           What    |Removed                     |Added
----------------------------------------------------------------------------
            Summary|Parser error: (degenerate?) |Row ranges are transformed
                   |row ranges is parsed        |to cell ranges during
                   |incorrectly                 |parsing

-- 
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

[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

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

[bu...@apache.org]

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

--- Comment #2 from gallon.fizik@gmail.com <ga...@gmail.com> ---
Created attachment 36277
  --> https://bz.apache.org/bugzilla/attachment.cgi?id=36277&action=edit
file for the testcase

-- 
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