Redefine Named Excel Range then Save using Apache POI

Question

Using Apache POI, I'm able to find a named range:

XSSFName[] ranges = new XSSFName[workbook.getNumberOfNames()];
for (int i = 0; i < _wb.getNumberOfNames(); i++)
    ranges[i] = workbook.getNameAt(i);

With that, I'm able to cell an AreaReference:

AreaReference area = new AreaReference(ranges[0].getRefersToFormula());

And then finally I can get all the cells within that range:

CellReference[] cells = area.getAllReferencedCells();

That all works just fine. Burt I have a use case where I have to redefine the area that the range covers. Is there a way to do that? I notice that the range.getRefersToFormula() method return a String, something like MySheet!$A$1:$B$8. There is a range.setRefersToFormula(String formula), but I've got to believe there's a way other than resorting to writing an excel range formula parser on my own. Is there no way to generate an AreaReference with a set to Cell references of something more type-safe? Do I actually have to generate a String to represent the new range? I would think there would be API somewhere to help me with this but I can't seem to find it.

Update

I found some API, but it doesn't seem to work, at least it doesn't save properly. Here's what I did.

AreaReference newArea = new AreaReference(firstCell, lastCell);
ranges[0].setRefersToFormula(newArea.formatAsString())

It seems to set the formula correctly, but when I stream the workbook back out to disk, the range is completely wrong.

Answer 1

you can update the existing Reference and set it as per your requirement. Suppose the reference contains TestSheet!$A$1:$B$8and you want to change it to MySheet!$B$5:$C$12

For any cell, say "B5", at runtime,

cell.getReference(); 

will give you cell reference (like in example... it will return you "B5")

char startCellColRef = cell.getReference().toString().charAt(0); 

will give you the Column Reference (will give you "B" if the current cell is B5). Now

int startCellRowRef = cell.getReference().toString().charAt(1);

will give you Row Index (will give you "5" if the current cell is B5).

By the same way you can get your start and end cell references (say B5 and C12).

Now comes how can I update the existing references. Just update its value with newly created reference string

Name reference = wb.getName("NameReferenceInExcelSheet");
referenceString = sheetName+"!$"+startCellColRef+"$"+startCellRowRef+":$"+endCellColRef+"$"+endCellRowRef;
reference.setRefersToFormula(referenceString);