3

I'm trying to create an old-style Excel document (HSSFWorkbook) with a particular cell preselected, using Apache POI 3.17. The below code is really ugly (it uses reflection and private fields) but gets the job done.

Is there a better way to achieve the same goal?

import java.io.File;
import java.io.IOException;
import java.lang.reflect.Field;

import org.apache.poi.hssf.model.InternalSheet;
import org.apache.poi.hssf.record.SelectionRecord;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress8Bit;
import org.apache.poi.ss.util.CellAddress;

public class ExcelGeneratorDemo {

    public static void main(String[] args) throws IOException {
        writeExcelFile("D21");
    }

    private static void writeExcelFile(String activeCell) throws IOException {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet();
        CellAddress address = new CellAddress(activeCell);
        setActiveCell(sheet, address);
        wb.write(new File(activeCell + ".xls"));
    }

    /**
     * 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);
        }
    }
}

A similar question regarding HSSF also has some workaround code. It doesn't use reflection but its workaround code is also not straight-forward.

Roland Illig
  • 31
  • 4
  • 1
    It should be mentioned that simply the [sheet.setActiveCell(address);](https://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFSheet.html#setActiveCell-org.apache.poi.ss.util.CellAddress-) instead of your `setActiveCell(sheet, address);` does not work because of [Bug-61905](http://apache-poi.1045710.n5.nabble.com/Bug-61905-New-Sheet-setActiveCell-does-nothing-td5729658.html). – Axel Richter Apr 25 '18 at 05:31
  • Seems there is a "uncertainty" (Microsoft ™ ;--)) in description of a `SELECTION` record in `BIFF`. The fields 2, 3 and 4 are the active cell in the current pane. But what if there is not a pane because the sheet does not contain any splits? So the [setActiveCellRow(int row) ff.](https://svn.apache.org/viewvc/poi/tags/REL_3_17_FINAL/src/java/org/apache/poi/hssf/record/SelectionRecord.java?view=markup#l76) changes the wrong fields simply. – Axel Richter Apr 25 '18 at 05:43
  • Oh and after reading the [Bug-61905](http://apache-poi.1045710.n5.nabble.com/Bug-61905-New-Sheet-setActiveCell-does-nothing-td5729658.html) again: Whether something works using `LibreOffice` on `Linux` is insignificant since `apache poi` is made for creating files in `Excel` fornats. Yes `LibreOffice` does interpreting the active cell position from fields 2, 3 and 4. But `Excel` does not. It uses first cell of cell range address list containing all selected cell ranges. – Axel Richter Apr 25 '18 at 06:08
  • Best fix would be setting the first of all selected cell ranges in `field_6_refs` to be the active cell **additionally** to setting `field_2_row_active_cell`, `field_3_col_active_cell` and `field_4_active_cell_ref_index`. So we would further be compatible with Libreoffice/OpenOffice Calc. – Axel Richter Apr 25 '18 at 08:19
  • Thanks for the notes, that should help in getting it fixed in POI. Having it work in LibreOffice does not suffice for sure, however it usually indicates that the spec is not fully clear or not fully adhered to by Excel itself. – centic Apr 25 '18 at 14:23

1 Answers1

0

It's a bug in POI up to 3.17, which has been fixed in the current development version.

It will probably be fixed in 3.18, once that is released.

Roland Illig
  • 31
  • 4