Summary: | Sheet.setActiveCell() does nothing | ||
---|---|---|---|
Product: | POI | Reporter: | Davide Angelocola <davide.angelocola> |
Component: | HSSF | Assignee: | POI Developers List <dev> |
Status: | RESOLVED FIXED | ||
Severity: | normal | ||
Priority: | P2 | ||
Version: | 3.17-FINAL | ||
Target Milestone: | --- | ||
Hardware: | PC | ||
OS: | All | ||
Attachments: |
active cell is A1, instead of E11
active cell is E11, as expected proof xls made with excel |
Created attachment 35613 [details]
active cell is E11, as expected
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(); } 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. Created attachment 35629 [details]
proof
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. 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. 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. 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? 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. (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 could try my POI-Visualizer [1] and check the properties tab. [1] https://github.com/kiwiwings/poi-visualizer Created attachment 35886 [details]
xls made with excel
E11 is the active cell
Attached also a XLS made with MS Excel (Office 365). 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. (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(); } (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. (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! 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); } } This should be fixed via r1830115 by populating the field6_refs every time col or row is changed. |
Created attachment 35612 [details] 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(); }