重新定义命名 Excel 范围，然后使用 Apache POI 保存 [英] Redefine Named Excel Range then Save using Apache POI

问题描述

使用 Apache POI，我可以找到一个命名范围:

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();

一切正常.Burt 我有一个用例，我必须重新定义范围覆盖的区域.有没有办法做到这一点?我注意到 range.getRefersToFormula() 方法返回一个字符串，类似于 MySheet!$A$1:$B$8.有一个 range.setRefersToFormula(String formula)，但我必须相信除了自己编写一个 excel 范围公式解析器之外，还有一种方法.有没有办法用一组更类型安全的 Cell 引用来生成 AreaReference ?我真的必须生成一个 String 来表示新的范围吗?我认为某个地方会有 API 可以帮助我解决这个问题，但我似乎找不到它.

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.

更新

我找到了一些 API，但它似乎不起作用，至少它没有正确保存.这就是我所做的.

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.

推荐答案

您可以更新现有的 Reference 并根据您的要求进行设置.假设引用包含 TestSheet!$A$1:$B$8 并且您想将其更改为 MySheet!$B$5:$C$12

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

对于任何单元格，在运行时说B5"，

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

cell.getReference(); 

会给你单元格引用(就像在例子中......它会返回B5")

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

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

会给你列参考(如果当前单元格是 B5，会给你B").现在

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

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

会给你行索引(如果当前单元格是 B5，会给你5").

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

通过同样的方式，您可以获得开始和结束单元格引用(例如 B5 和 C12).

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);

这篇关于重新定义命名 Excel 范围，然后使用 Apache POI 保存的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！