使用Apache POI及其名称获取Excel公式的内容(Get Content of a Excel Formula with Apache POI and its name)

 问题是如何通过名称获取公式的内容。 在Excel中，您可以使用公式名称对表进行分组。 我想用Apache POI获取这样一个名为table的公式的内容。 因此，即使表格得到扩展，我也始终获得最新数据。 由于名称没有改变。 
 
 我做了一些编码，但到目前为止，我只能获得一张纸的所有内容，而不仅仅是一张带有公式名称的表。 我的代码在下面，谢谢。 
 
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();
        }
    }
}

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

原文：https://stackoverflow.com/questions/36030143