重新命名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);

就这样，我能细胞的AreaReference：

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

这所有的作品就好。伯特我有，我必须重新定义该范围涵盖领域的用例。有没有办法做到这一点？我注意到 range.getRefersToFormula（）方法返回一个字符串，类似 MySheet的工作$ A $ 1：$ B $ 8个。有一个 range.setRefersToFormula（字符串公式），但我必须相信有比诉诸写我自己的Excel范围公式解析器等方式。请问有没有办法生成一组的AreaReference到细胞的东西多引用类型安全的？我是不是真的要生成一个字符串重新present新的范围？我想会有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.

推荐答案

您可以更新现有的参考，并将其设置为按您的要求。
假设引用包含 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); 

会给你列引用（会给你B如果当前单元格B5）。现在，

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

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

会给你行索引（给你5，如果当前单元格B5）。

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屋！