[Bug 61905] New: Sheet.setActiveCell() does nothing

classic Classic list List threaded Threaded
21 messages Options
12
Reply | Threaded
Open this post in threaded view
|

[Bug 61905] New: Sheet.setActiveCell() does nothing

Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61905

            Bug ID: 61905
           Summary: Sheet.setActiveCell() does nothing
           Product: POI
           Version: 3.17-FINAL
          Hardware: PC
            Status: NEW
          Severity: normal
          Priority: P2
         Component: HSSF
          Assignee: [hidden email]
          Reporter: [hidden email]
  Target Milestone: ---

Created attachment 35612
  --> https://bz.apache.org/bugzilla/attachment.cgi?id=35612&action=edit
active cell is A1, instead of E11

The method setActiceCell seems to be not working on latest stable version. The
same method on XSSF is working as expected.

Please find a minimal reproducer below:


    @Test
    public void xlsx() throws IOException {
        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet("new sheet");
        sheet.setActiveCell(new CellAddress("E11"));
        wb.write(new FileOutputStream("c:/temp/yyy.xlsx"));
        wb.close();
    }

    @Test
    public void xls() throws IOException {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("new sheet");
        sheet.setActiveCell(new CellAddress("E11"));
        wb.write(new FileOutputStream("c:/temp/yyy.xls"));
        wb.close();
    }

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 61905] Sheet.setActiveCell() does nothing

Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61905

--- Comment #1 from Davide Angelocola <[hidden email]> ---
Created attachment 35613
  --> https://bz.apache.org/bugzilla/attachment.cgi?id=35613&action=edit
active cell is E11, as expected

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 61905] Sheet.setActiveCell() does nothing

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61905

Dominik Stadler <[hidden email]> changed:

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

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 61905] Sheet.setActiveCell() does nothing

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61905

Dominik Stadler <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|NEW                         |NEEDINFO

--- Comment #2 from Dominik Stadler <[hidden email]> ---
This works for me with LibreOffice on Linux, can you provide details about your
environment?

Also does the following unit test pass?

    @Test
    public void test61905xlsx() throws IOException {
        Workbook wb = new XSSFWorkbook();
        checkActiveSheet(wb, XSSFITestDataProvider.instance);
        //wb.write(new FileOutputStream("/tmp/yyy.xlsx"));
        wb.close();
    }

    @Test
    public void test61905xls() throws IOException {
        Workbook wb = new HSSFWorkbook();
        checkActiveSheet(wb, HSSFITestDataProvider.instance);
        //wb.write(new FileOutputStream("/tmp/yyy.xls"));
        wb.close();
    }

    private void checkActiveSheet(Workbook wb, ITestDataProvider instance)
throws IOException {
        Sheet sheet = wb.createSheet("new sheet");
        sheet.setActiveCell(new CellAddress("E11"));
        assertEquals("E11", sheet.getActiveCell().formatAsString());

        Workbook wbBack = instance.writeOutAndReadBack(wb);
        sheet = wbBack.getSheetAt(0);
        assertEquals("E11", sheet.getActiveCell().formatAsString());
        wbBack.close();
    }

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 61905] Sheet.setActiveCell() does nothing

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61905

Davide Angelocola <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|NEEDINFO                    |NEW

--- Comment #3 from Davide Angelocola <[hidden email]> ---
I'm using Windows 10 Enterprise with Excel 2016 (MSO 16.0.8201.2207 32-bit).
When I open the file yyy.xls with Excel the cell A1 is marked as active,
instead of E11 (please see the attached screenshot).
Whereas when I open the file yyy.xlsx the cell E11 is marked as active, as
expected.

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 61905] Sheet.setActiveCell() does nothing

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61905

--- Comment #4 from Davide Angelocola <[hidden email]> ---
Created attachment 35629
  --> https://bz.apache.org/bugzilla/attachment.cgi?id=35629&action=edit
proof

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 61905] Sheet.setActiveCell() does nothing

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61905

--- Comment #5 from Dominik Stadler <[hidden email]> ---
On a quick look we do set the SelectionRecord correctly in HSSF, not sure why
Excel does not use this. No obvious difference between the way POI sets the
current cell and the way Excel stores it in the file.

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 61905] Sheet.setActiveCell() does nothing

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61905

--- Comment #6 from Javen O'Neal <[hidden email]> ---
Isn't the concept of the active cell and a selected cell different? Multiple
calls can be selected, but only one can be active.

I thought I remembered the Javadocs talking about this differentiation, but
this appears to be on marking a sheet as active or a set of sheets as selected
in need workbook.

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 61905] Sheet.setActiveCell() does nothing

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61905

--- Comment #7 from Javen O'Neal <[hidden email]> ---
Davide, can you create 2 xlsx workbooks in Excel: one with A1 selected and the
other with E11 selected, then unzip the xlsx workbooks and diff the
xl/sheet1.xml files and xl/workbook.xml files?

Then compare the diff of POI's output. That might hint at what POI is doing
differently.

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 61905] Sheet.setActiveCell() does nothing

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61905

--- Comment #8 from Davide Angelocola <[hidden email]> ---
Thanks for looking into this.

POI:
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <dimension ref="A1"/>
  <sheetViews>
    <sheetView workbookViewId="0" tabSelected="true">
      <selection activeCell="E11" sqref="E11"/>
    </sheetView>
  </sheetViews>
...

Excel:
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <dimension ref="A1:I30"/>
  <sheetViews>
    <sheetView workbookViewId="0">
      <selection activeCell="E11" sqref="E11"/>
    </sheetView>
 </sheetViews>

Perhaps E11 is outside dimension?

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 61905] Sheet.setActiveCell() does nothing

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61905

--- Comment #9 from Mark Murphy <[hidden email]> ---
It looks like there is some confusion over where the error is. By my reading
the problem is with HSSF format though the initial description doesn't specify
that. You are going to have to make .xls spreadsheets to compare against. I
think there is a tool to look at the HSSF formats, not sure where it is though.

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 61905] Sheet.setActiveCell() does nothing

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61905

--- Comment #10 from Nick Burch <[hidden email]> ---
(In reply to Mark Murphy from comment #9)
> I think there is a tool to look at the HSSF formats, not sure where
> it is though.

BiffViewer is probably what you're thinking of - it lets you dump the record
structures to compare

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 61905] Sheet.setActiveCell() does nothing

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61905

--- Comment #11 from Andreas Beeker <[hidden email]> ---
You could try my POI-Visualizer [1] and check the properties tab.

[1] https://github.com/kiwiwings/poi-visualizer

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 61905] Sheet.setActiveCell() does nothing

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61905

--- Comment #12 from Davide Angelocola <[hidden email]> ---
Created attachment 35886
  --> https://bz.apache.org/bugzilla/attachment.cgi?id=35886&action=edit
xls made with excel

E11 is the active cell

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 61905] Sheet.setActiveCell() does nothing

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61905

--- Comment #13 from Davide Angelocola <[hidden email]> ---
Attached also a XLS made with MS Excel (Office 365).

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 61905] Sheet.setActiveCell() does nothing

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61905

--- Comment #14 from Davide Angelocola <[hidden email]> ---
A brief recap, just to avoid confusion:

- this bug is a basically an interoperability problem: when POI writes a file
with a given active cell, MS excel ignores it;

- this bug happens only with XLS (see attached screenshot);

- XLSX is working fine.

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 61905] Sheet.setActiveCell() does nothing

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61905

--- Comment #15 from Davide Angelocola <[hidden email]> ---


(In reply to Dominik Stadler from comment #2)

> This works for me with LibreOffice on Linux, can you provide details about
> your environment?
>
> Also does the following unit test pass?
>
>     @Test
>     public void test61905xlsx() throws IOException {
>         Workbook wb = new XSSFWorkbook();
>         checkActiveSheet(wb, XSSFITestDataProvider.instance);
>         //wb.write(new FileOutputStream("/tmp/yyy.xlsx"));
>         wb.close();
>     }
>
>     @Test
>     public void test61905xls() throws IOException {
>         Workbook wb = new HSSFWorkbook();
>         checkActiveSheet(wb, HSSFITestDataProvider.instance);
>         //wb.write(new FileOutputStream("/tmp/yyy.xls"));
>         wb.close();
>     }
>
>     private void checkActiveSheet(Workbook wb, ITestDataProvider instance)
> throws IOException {
>         Sheet sheet = wb.createSheet("new sheet");
>         sheet.setActiveCell(new CellAddress("E11"));
>         assertEquals("E11", sheet.getActiveCell().formatAsString());
>
>         Workbook wbBack = instance.writeOutAndReadBack(wb);
>         sheet = wbBack.getSheetAt(0);
>         assertEquals("E11", sheet.getActiveCell().formatAsString());
>         wbBack.close();
>     }

This test is passing on apache-poi 3.15:

    @Test
    public void test61905xls() throws IOException {
        Workbook wb = new HSSFWorkbook();
        Sheet sheet = wb.createSheet("new sheet");
        sheet.setActiveCell(new CellAddress("E11"));
        assertEquals("E11", sheet.getActiveCell().formatAsString());
        wb.write(new FileOutputStream("/tmp/zzz.xls"));
        wb.close();

        Workbook wbBack = new HSSFWorkbook(new
FileInputStream("/tmp/zzz.xls"));
        sheet = wbBack.getSheetAt(0);
        assertEquals("E11", sheet.getActiveCell().formatAsString());
        wbBack.close();
    }

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 61905] Sheet.setActiveCell() does nothing

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61905

--- Comment #16 from Roland Illig <[hidden email]> ---
(In reply to Davide Angelocola from comment #15)
> This test is passing on apache-poi 3.15:

Sadly, whether this test is passing is not relevant here. POI promises to
generate Excel-compatible files, and Excel 2016 does not display the generated
file as intended. Therefore POI should be fixed.

https://stackoverflow.com/q/50008212 mentions this bug and suggests a possible
workaround, which of course should be provided with a nicer API than using
brute-force Java reflection.

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 61905] Sheet.setActiveCell() does nothing

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61905

--- Comment #17 from Davide Angelocola <[hidden email]> ---
(In reply to Roland Illig from comment #16)
> (In reply to Davide Angelocola from comment #15)
> > This test is passing on apache-poi 3.15:
>
> Sadly, whether this test is passing is not relevant here. POI promises to
> generate Excel-compatible files, and Excel 2016 does not display the
> generated file as intended. Therefore POI should be fixed.

I just provided working code for an earlier comment.

> https://stackoverflow.com/q/50008212 mentions this bug and suggests a
> possible workaround, which of course should be provided with a nicer API
> than using brute-force Java reflection.

Thanks!

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 61905] Sheet.setActiveCell() does nothing

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61905

--- Comment #18 from Dominik Stadler <[hidden email]> ---
The Stackoverflow discussion shed some light on this, the following seems to
make it work, in POI itself we can do this much cleaner, naturally:


    /**
     * Calling just {@code sheet.setActiveCell} has no effect when opening
     * the file with Microsoft Excel 2016.
     */
    private static void setActiveCell(HSSFSheet sheet, CellAddress address) {
        sheet.setActiveCell(address);

        // Following three private fields in a row cannot be the correct path.
        InternalSheet internalSheet = getField(sheet, "_sheet");
        SelectionRecord selection = getField(internalSheet, "_selection");
        CellRangeAddress8Bit[] ranges = getField(selection, "field_6_refs");

        ranges[0].setFirstColumn(address.getColumn());
        ranges[0].setLastColumn(address.getColumn());
        ranges[0].setFirstRow(address.getRow());
        ranges[0].setLastRow(address.getRow());
    }

    private static <T> T getField(Object obj, String fieldName) {
        try {
            Field field = obj.getClass().getDeclaredField(fieldName);
            field.setAccessible(true);
            return (T) field.get(obj);
        } catch (ReflectiveOperationException e) {
            throw new IllegalStateException(e);
        }
    }

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

12