java - 如何根据 Apache POI 中的 "Name Box"值查找列索引号

Question

我需要根据命名单元格处理 Excel 列(而不是 A1、A2，单元格被重命名为独立检索，无论列位置如何)我尝试获取命名范围，但无法获取列索引。

Answer 1

如果您有 Name命名范围的对象，可以得到RefersToFormula由此而来。

有了这个，您就可以获得引用公式的第一个单元格的单元格引用。这是棘手的部分，因为 RefersToFormula 也可以是 AreaReference。所以我首先尝试将其设为 CellReference 。如果失败，请尝试将其获取为 AreaReference 。如果成功，则通过 AreaReference.getFirstCell 获取 CellReference。

有了CellReference，您可以通过CellReference.getCol获取列索引。

示例:

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.ss.util.AreaReference;

import java.io.FileInputStream;

class ExcelGetNamedCells {

 public static void main(String[] args) throws Exception {
  //Workbook workbook = WorkbookFactory.create(new FileInputStream("Workbook.xls"));
  Workbook workbook = WorkbookFactory.create(new FileInputStream("Workbook.xlsx"));

  String nameName = "";
  String nameRefersTo = "";
  CellReference nameFirstCellReference = null;
  AreaReference nameAreaReference = null;
  short nameFirstColumn = -1;

  for (Name name : workbook.getAllNames()) {

   nameName = name.getNameName();
   System.out.println("Name of named range is: " + nameName);

   nameRefersTo = name.getRefersToFormula();
   System.out.println("Named range refers to : " + nameRefersTo);

   // get cell reference of first cell in refers to formula
   nameFirstCellReference = null;
   try {
    nameFirstCellReference = new CellReference(nameRefersTo);
   } catch (Exception ex) {
    // do nothing. nameFirstCellReference stays null
   }
   if (nameFirstCellReference == null) {
    nameAreaReference = null;
    try {
     nameAreaReference = new AreaReference(nameRefersTo, workbook.getSpreadsheetVersion());
    } catch (Exception ex) {
     // do nothing. nameAreaReference stays null
    }
    if (nameAreaReference != null) {
     System.out.println("Area reference of named range is : " + nameAreaReference);
     nameFirstCellReference = nameAreaReference.getFirstCell();
    }
   }

   if (nameFirstCellReference != null) {
    System.out.println("First cell reference of named range is : " + nameFirstCellReference);
    nameFirstColumn = nameFirstCellReference.getCol();
    System.out.println("First column of named range is: " + nameFirstColumn);
   }
  }

  workbook.close();

 }
}