java - 如何基于Apache POI中的“名称框”值查找列索引号

我需要根据命名单元格处理excel列（而不是将A1，A2单元格重命名为独立检索，而不考虑列的位置）
我试图获取命名范围，但无法获取列索引。

最佳答案

如果具有命名范围的Name对象，则可以从中获取RefersToFormula。

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

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

 }
}