0

I am using Apache POI XSSF to read and write Excel Sheets.

I know that I can set the active cell on a worksheet by using Sheet.setActiveCell(CellAddress address).

However, I'd like to set it to a Range containing more than one cell on the sheet, as illustrated by the picture below:

Range selected in Excel Sheet

When I save a sheet with multiple cells selected using Excel those are selected upon opening the saved file. Is there a way to do this with POI XSSF?

padrino
  • 299
  • 3
  • 14

1 Answers1

3

you can use following line to achieve a ranke as active cell in excel:

    sheet.setActiveCell("A1:B2");

Hope it helps.

As from 3.16 onwards the setActiveCell(String) method is deprecated and you do not want to use a deprecated method I would suggest to create your own CellAddress:

public class CellRangeAddress extends CellAddress {

    private CellAddress start;
    private CellAddress end;

    public CellRangeAddress(final CellAddress start, final CellAddress end) {
        super(start);
        this.start = start;
        this.end = end;
    }


    @Override
    public String formatAsString() {
        if (end != null) {
            return start.formatAsString() + ":" + end.formatAsString();
        }
        return super.formatAsString();
    }
}

and use ist like:

sheet.setActiveCell(new CellRangeAddress(new CellAddress("A1"), new CellAddress("B2")));

Not the cleanest and best way, but works without warnings.

kism3t
  • 1,343
  • 1
  • 14
  • 33
  • Thank you for your answer, this works. However, this method seems to be deprecated, but I cannot find an adequate replacement. For instance, this does not work, since setActiveCell only accepts CellAddress as input: `CellRangeAddress adr = CellRangeAddress.valueOf("A1:B2"); wb.getSheetAt(wb.getActiveSheetIndex()).setActiveCell(adr);` – padrino Jun 14 '17 at 11:42
  • which version are you using – kism3t Jun 14 '17 at 11:44
  • I am using 3.16, which seems to be the most recent one. – padrino Jun 14 '17 at 11:45
  • I edited my answer, still not the best solution, but one option – kism3t Jun 14 '17 at 12:10
  • Thank you very much, that'll do fine. It is a little unfortunate that this functionality was deprecated in an update without any replacement, I think. – padrino Jun 14 '17 at 12:50
  • 2
    Why are you using such a complicated way to build the `CellRangeAddress`? Why not just do [`CellRangeAddress.valueOf("A1:B2")](https://poi.apache.org/apidocs/org/apache/poi/ss/util/CellRangeAddress.html#valueOf(java.lang.String))? – Gagravarr Jun 14 '17 at 15:44
  • Because in 3.16 `CellRangeAddress.valueOf()` is giving you a `CellRangeAddress` but in 3.16 `Sheet.setActiveCell` does not accept it. Only `String` which is deprecated or `CellAddress`. – kism3t Jun 19 '17 at 05:32