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