CODEWITHSUNDEEP
javaexcelapache-poi
Bisu
Bisu

Reputation: 183

How to find the column index number based on "Name Box" value in Apache POI

I need to process excel columns based on Named cell (instead of A1,A2 the cells are renamed to be retrieved independently irrespective of column placement) I have tried to get the Named Range but not able to get the column Index.

Upvotes: 0

Views: 1830

Answers (1)

Axel Richter
Axel Richter

Reputation: 61852

If you have the Name object of the named range, you can get the RefersToFormula from that.

Having this you can get the cell reference of the first cell in that refers to formula. This is the tricky part because a RefersToFormula can be a AreaReference too. So I would first try to get it as CellReference. If that fails, try get it as AreaReference. If that succeed the get the CellReference via AreaReference.getFirstCell.

Having the CellReference you can get column index via CellReference.getCol.

Example:

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

 }
}

Upvotes: 1

Related Questions