Apache Poi setActiveCell() for multiple cells

Question

I'm trying to use the method sheet.setActiveCell(CellAddress addr) to set a range of multiple cells active at the same time. I've tryed with multiple versions of Apache poi-ooxml library and now i'm using 3.16 which also supports the method sheet.setActiveCell(String addr)(I know 3.16 is old but the issue stays the same also with the latest version).

Following the suggestions on this question: Is it possible to set the active range with Apache POI XSSF? I've managed to get it to work, both with the custom CellAddress and the String in the format "A1:B5".

The problem is that every time I try to open an xlsx in which a range of cells has been set to active using apache poi, I get an error message from Excel saying that the file is damaged and need to be recovered. If I do, the recovery completes correctly, but this error is annoying since I have to open a great number of these files each day.

Is there a way to avoid this error from excel (maybe modifying the creation of the xlsx or changing some setting in Excel)?

Answer 1

Only one cell can be the active cell. And Sheet.setActiveCell only sets that one active cell. So sheet.setActiveCell("A1:B5") will work if setActiveCell(String addr) is available but it leads to a corrupted sheet. That's why it was removed.

Multiple cells can be selected. But there are no methods to set the selected cells in apache poi's high level classes. So the underlying low level classes needs to be used. Doing this one needs differentiate between XSSF and HSSF because different low level classes needs to be used.

Following complete example sets active cell to B2. This also sets sheet view having selection and active cell to that one given cell B2. Then it uses low level methods of XSSF and HSSF to set the selection to B2:E5.

import java.io.*;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFSheet;

class CreateExcelSelectMultipleCells {

 public static void main(String[] args) throws Exception {

  try (Workbook workbook = new XSSFWorkbook(); FileOutputStream out = new FileOutputStream("Excel.xlsx") ) {
  //try (Workbook workbook = new HSSFWorkbook(); FileOutputStream out = new FileOutputStream("Excel.xls") ) {

   Sheet sheet = workbook.createSheet();
   Row row;
   Cell cell;
     
   for (int r = 0; r < 6; r++) {
    row = sheet.createRow(r);
    for (int c = 0; c < 6; c++) {
     cell = row.createCell(c);
     cell.setCellValue("R" + (r+1) + "C" + (c+1));
    }
   }

   // set active cell; this also sets sheet view having selection and active cell to one given cell
   sheet.setActiveCell(new CellAddress("B2"));
   // set selected cells 
   if (sheet instanceof XSSFSheet) {
    XSSFSheet xssfSheet = (XSSFSheet) sheet;
    xssfSheet.getCTWorksheet().getSheetViews().getSheetViewArray(0).getSelectionArray(0).setSqref(
     java.util.Arrays.asList("B2:E5"));
   } else if (sheet instanceof HSSFSheet) {
    HSSFSheet hssfSheet = (HSSFSheet) sheet; 
    org.apache.poi.hssf.record.SelectionRecord selectionRecord = hssfSheet.getSheet().getSelection();
    java.lang.reflect.Field field_6_refs = org.apache.poi.hssf.record.SelectionRecord.class.getDeclaredField("field_6_refs");
    field_6_refs.setAccessible(true);
    field_6_refs.set(
     selectionRecord, 
     new org.apache.poi.hssf.util.CellRangeAddress8Bit[] { new org.apache.poi.hssf.util.CellRangeAddress8Bit(1,4,1,4) }
    );
   }
   
   workbook.write(out);
  }
 }
}