POI 关于单元格名字获得单元格内容的中文文档Named Ranges and Named Cells

因为有个需求是根据自定义的单元格名字去获得单元格内容，网上翻了半天也没有找到有人写过比较完善的，所以只好看令人头疼的官方文档自己写了。

http://poi.apache.org/components/spreadsheet/quick-guide.html#NamedRanges
先来翻译一下官方文档，这里就直接翻成驴唇不对马嘴的中文的内容了

Named Ranges and Named Cells

命名范围和命名单元格

Named Range is a way to refer to a group of cells by a name. Named Cell is a degenerate case of Named Range in that the ‘group of cells’ contains exactly one cell. You can create as well as refer to cells in a workbook by their named range. When working with Named Ranges, the classes
org.apache.poi.ss.util.CellReference and org.apache.poi.ss.util.AreaReference are used.

命名范围是一种通过名称引用一组单元格的方法。命名单元格是命名范围的一种退化情况，因为“单元组”只包含一个单元格。您可以根据指定的范围在工作簿中创建并引用单元格。在处理命名范围时，使用类org.apache.poi.ss.util.CellReference 和org.apache.poi.ss.util.AreaReference

Note: Using relative values like ‘A1:B1’ can lead to unexpected moving of the cell that the name points to when working with the workbook in Microsoft Excel, usually using absolute references like ‘$A$1:$B$1’ avoids this, see also this discussion.

注意:在使用Microsoft Excel的工作簿时，使用’A1:B1’这样的相对值可能导致名称所指向的单元格发生意外移动，通常使用’$A$1:$B$1’这样的绝对引用可以避免这种情况，请参阅本讨论。

Creating Named Range / Named Cell

创建命名范围/命名单元格

// setup code 设置代码
String sname = "TestSheet", cname = "TestName", cvalue = "TestVal";
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet(sname);
sheet.createRow(0).createCell(0).setCellValue(cvalue);
// 1. create named range for a single cell using areareference 使用areareference为单个单元格创建命名范围
Name namedCell = wb.createName();
namedCell.setNameName(cname + "1");
String reference = sname+"!$A$1:$A$1"; // area reference
namedCell.setRefersToFormula(reference);
// 2. create named range for a single cell using cellreference 使用cellreference为单个单元格创建命名范围
Name namedCel2 = wb.createName();
namedCel2.setNameName(cname + "2");
reference = sname+"!$A$1"; // cell reference
namedCel2.setRefersToFormula(reference);
// 3. create named range for an area using AreaReference 使用AreaReference为一个区域创建命名范围
Name namedCel3 = wb.createName();
namedCel3.setNameName(cname + "3");
reference = sname+"!$A$1:$C$5"; // area reference
namedCel3.setRefersToFormula(reference);
// 4. create named formula 创建名为公式
Name namedCel4 = wb.createName();
namedCel4.setNameName("my_sum");
namedCel4.setRefersToFormula("SUM(" + sname + "!$I$2:$I$6)");

Reading from Named Range / Named Cell

从命名范围/命名单元格读取数据

// setup code
String cname = "TestName";
Workbook wb = getMyWorkbook(); // retrieve workbook 检索工作簿
// retrieve the named range 检索指定的范围
int namedCellIdx = wb.getNameIndex(cellName);
Name aNamedCell = wb.getNameAt(namedCellIdx);
// retrieve the cell at the named range and test its contents 在指定的范围检索单元格并测试其内容
AreaReference aref = new AreaReference(aNamedCell.getRefersToFormula());
CellReference[] crefs = aref.getAllReferencedCells();
for (int i=0; i<crefs.length; i++) {
    Sheet s = wb.getSheet(crefs[i].getSheetName());
    Row r = sheet.getRow(crefs[i].getRow());
    Cell c = r.getCell(crefs[i].getCol());
    // extract the cell contents based on cell type etc.根据单元类型等提取单元内容。
}

Reading from non-contiguous Named Ranges

从非连续的命名范围中读取

// Setup code
String cname = "TestName";
Workbook wb = getMyWorkbook(); // retrieve workbook 检索工作簿
// Retrieve the named range 检索指定的范围
// Will be something like "$C$10,$D$12:$D$14";大概是“$C$10，$D$12:$D$14”;
int namedCellIdx = wb.getNameIndex(cellName);
Name aNamedCell = wb.getNameAt(namedCellIdx);
// Retrieve the cell at the named range and test its contents 在指定的范围检索单元格并测试其内容
// Will get back one AreaReference for C10,会为C10拿回一个区域吗  and 和
//  another for D12 to D14  D12到D14的另一个
AreaReference[] arefs = AreaReference.generateContiguous(aNamedCell.getRefersToFormula());
for (int i=0; i<arefs.length; i++) {
    // Only get the corners of the Area 只得到区域的角落
    // (use arefs[i].getAllReferencedCells() to get all cells) (使用arefs[i].getAllReferencedCells()获取所有单元格)
    CellReference[] crefs = arefs[i].getCells();
    for (int j=0; j<crefs.length; j++) {
        // Check it turns into real stuff
        Sheet s = wb.getSheet(crefs[j].getSheetName());
        Row r = s.getRow(crefs[j].getRow());
        Cell c = r.getCell(crefs[j].getCol());
        // Do something with this corner cell
    }
}

Note, when a cell is deleted, Excel does not delete the attached named range. As result, workbook can contain named ranges that point to cells that no longer exist. You should check the validity of a reference before constructing AreaReference

注意，当一个单元格被删除时，Excel不会删除附加的命名范围。因此，工作簿可以包含指向不再存在的单元格的已命名范围。在构造一个引用之前，你应该检查引用的有效性

if(name.isDeleted()){
  //named range points to a deleted cell. 已命名的范围指向已删除的单元格。
} else {
  AreaReference ref = new AreaReference(name.getRefersToFormula());
}