Q: Get Content of a Excel Formula with Apache POI and its name Question is how I can get the content of a formula by name.
In Excel you can group Tables with a formula name.
I want to get the content of such a formula named table with Apache POI.
So that I always get up-to-date data even if the table get extended.
Since the name does not change then.
I did some coding but so far I only archieved to get all contents of a sheet and not just of a table with formula name. My code is below, thanks.
package getTablesPOI;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.Iterator;


public class readExcel {

    // Creates a new instance of POIExcelReader
    public readExcel() {
    }

    /**
     * The main executable method to test readExcel method.
     *
     * @param args
     */

    public static void main(String[] args) throws InvalidFormatException {
        readExcel excelStuff = new readExcel();
        String cisFile = "C://Users//agoebbel//Desktop//PS//# Downloads - Files//POI_Test.xlsx";
        String sheetName = "Main";
        String searcher = "Something";

        excelStuff.displayFromExcel(cisFile, sheetName, searcher);
    }


    /**
     * This method is used to display the Excel content to command line.
     *
     * @param cisFile
     * @param sheetName
     * @param searcher
     */
    public void displayFromExcel(String cisFile, String sheetName, String searcher) throws InvalidFormatException {
        InputStream inputStream = null;

        try {
            inputStream = new FileInputStream(cisFile);
        } catch (FileNotFoundException e) {
            System.out.println("File not found in the specified path.");
            e.printStackTrace();
        }

        try {
            OPCPackage opc = OPCPackage.open(inputStream);
            Workbook workbook;
            workbook = WorkbookFactory.create(opc);
            XSSFSheet sheet = (XSSFSheet) workbook.getSheet(sheetName);
            Iterator<Row> rows = sheet.rowIterator();
            // String searcher = (new CellReference((Cell) rows)).formatAsString();

            while (rows.hasNext()) {
                XSSFRow row = (XSSFRow) rows.next();

                // display row number in the console.
                System.out.println("\n" + "Row No.: " + new Integer(row.getRowNum() + 1));

                // once get a row its time to iterate through cells.
                Iterator<Cell> cells = row.cellIterator();

                while (cells.hasNext()) {
                    Cell cell = cells.next();


                    // Now we will get the cell type and display the values accordingly.
                    switch (cell.getCellType())  //cell.getCellType ()
                    {
                        case Cell.CELL_TYPE_FORMULA: {
                            cell.getCellFormula();
                            switch (cell.getCachedFormulaResultType()) {

                                case Cell.CELL_TYPE_NUMERIC:
                                    System.out.println("Last evaluated as: " + cell.getNumericCellValue());
                                    break;
                                case Cell.CELL_TYPE_STRING:
                                    System.out.println("Last evaluated as \"" + cell.getRichStringCellValue() + "\"");
                                    break;
                            }

                        }

                        case Cell.CELL_TYPE_NUMERIC: {

                            // cell type numeric.
                            System.out.println(cell.getNumericCellValue());

                            break;
                        }

                        case Cell.CELL_TYPE_STRING: {

                            // cell type string.
                            System.out.println(cell.getStringCellValue());

                            break;
                        }

                        default: {

                            // types other than String and Numeric.
                            System.out.println("Type not known.");

                            break;
                        }
                    }
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}


A: I believe that the term you are looking for is a Named Range (though they are defined in the Formula tab of the ribbon in some versions of Excel). Assuming you mean this:

Then the key methods you need from Apache POI are:


*

*Workbook.getNumberOfNames()

*Workbook.getName(String)

*Workbook.getNameAt(int)

*Workbook.createName()
If you know the name of the range, and want to get the cells it refers to, you'd do something like
Workbook wb = WorkbookFactory.create(new File("input.xls"));
Name name = wb.getName("TestRange");

System.out.println("Named Range '" + name.getNameName() + 
                   "' points to " + name.getRefersToFormula());

Which for the case above would print
 Named Range 'TesRange' points to Sheet1!$A$1:$C$5

