Java使用POI读取Excel名称管理器

文章目的

本文主要介绍如何使用poi读取到Excel的名称管理器中的内容。并且定位到单元格。

在企业的开发中可能需要通过名称管理器定位到某个单元格，然后在单元格上生成签名。

环境配置

Java：Jdk1.8

poi：5.2.3

maven依赖(pom.xml)：

<dependencies>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>5.2.3</version>
        </dependency>

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>5.2.3</version>
        </dependency>

        <!-- 以下依赖非必须，可根据项目情况选择性依赖 -->
        <!-- poi案例代码，可以去掉 -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-examples</artifactId>
            <version>5.2.3</version>
        </dependency>

        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.8.15</version>
        </dependency>

        <!-- 使用slf4j 作为日志门面 -->
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
            <version>1.7.36</version>
        </dependency>
        <!-- 使用 log4j2 的适配器进行绑定 -->
        <dependency>
            <groupId>org.apache.logging.log4j</groupId>
            <artifactId>log4j-slf4j-impl</artifactId>
            <version>2.9.1</version>
        </dependency>

        <!-- log4j2 日志门面 -->
        <dependency>
            <groupId>org.apache.logging.log4j</groupId>
            <artifactId>log4j-api</artifactId>
            <version>2.17.2</version>
        </dependency>
        <!-- log4j2 日志实面 -->
        <dependency>
            <groupId>org.apache.logging.log4j</groupId>
            <artifactId>log4j-core</artifactId>
            <version>2.17.2</version>
        </dependency>

        <!-- log4j2-异步日志依赖 -->
        <dependency>
            <groupId>com.lmax</groupId>
            <artifactId>disruptor</artifactId>
            <version>3.4.4</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.26</version>
        </dependency>

        <dependency>
            <groupId>org.junit.jupiter</groupId>
            <artifactId>junit-jupiter-engine</artifactId>
            <version>5.9.3</version>
            <scope>test</scope>
        </dependency>
    </dependencies>

实现思路

poi的WorkBook有个getNames方法可以读到名称。

Excel操作

Excel的名称在下图中新建

参考代码

以下代码用于得到单元格引用对象（CellReference）

import cn.hutool.core.util.StrUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

/**
 * POI-Excel-工具类<br>
 *
 * @author namelessmyth
 * @version 1.0
 * @date 2023/8/4
 */
@Slf4j
public class PoiExcelUtil {
    /**
     * 根据名称管理器中的名称得到单元格
     *
     * @param book Excel工作簿对象
     * @param nameName 名称的名称
     * @return Cell Excel单元格对象
     */
    public static Cell getNameCell(Workbook book, String nameName) {
        Cell result = null;
        if (book != null && StrUtil.isNotBlank(nameName)) {
            Name name = book.getName(nameName);
            if (name != null) {
                CellReference cr = new CellReference(name.getRefersToFormula());
                if (cr != null) {
                    result = getCell(book, cr);
                }
            }
        } else {
            throw new IllegalArgumentException("The input parameter of method can't be null");
        }
        return result;
    }

    /**
     * 根据单元格引用对象得到单元格
     *
     * @param book Excel工作簿对象
     * @param cr 单元格引用对象，例如：sheetName!$A$3
     * @return Cell Excel单元格对象
     */
    public static Cell getCell(Workbook book, CellReference cr) {
        Cell cell = null;
        if (book != null && cr != null) {
            Sheet sheet = null;
            if (StrUtil.isNotBlank(cr.getSheetName())) {
                sheet = book.getSheet(cr.getSheetName());
            }
            if (sheet == null) {
                throw new RuntimeException(String.format("Unable to find Sheet based on sheet name in CellReference! %s", cr.getSheetName()));
            }
            cell = getCell(sheet, cr);
        } else {
            throw new IllegalArgumentException("the input parameter of method can not be null!");
        }
        return cell;
    }

    public static Cell getCell(Sheet sheet, CellReference cr) {
        Cell cell = null;
        if (sheet != null && cr != null) {
            Row row = sheet.getRow(cr.getRow());
            if (row != null) {
                cell = row.getCell(cr.getCol());
            }
        } else {
            throw new IllegalArgumentException("the input parameter of method can not be null!");
        }
        return cell;
    }

    /**
     * 返回Excel名称管理器中所有名称对应的单元格引用
     *
     * @param filePath Excel文件路径
     * @return List<CellReference>
     */
    public static List<Cell> listNameCell(String filePath) throws IOException {
        List<Cell> result = null;
        if (StrUtil.isNotBlank(filePath)) {
            log.info("excelFilePath:{}", filePath);
            Workbook book = new XSSFWorkbook(new FileInputStream(filePath));
            result = listNameCell(book);
            book.close();
        }
        return result;
    }

    /**
     * 返回Excel名称管理器中所有名称对应的单元格引用
     *
     * @param book Excel工作簿对象
     * @return List<CellReference> Excel单元格引用对象列表
     */
    public static List<Cell> listNameCell(Workbook book) throws IOException {
        List<Cell> result = null;
        // 打开Excel文件
        if (book != null && book.getAllNames() != null) {
            result = new ArrayList<>(book.getAllNames().size());
            // 获取所有的名称管理器
            for (Name name : book.getAllNames()) {
                String refersToFormula = name.getRefersToFormula();
                if (StrUtil.isNotBlank(refersToFormula)) {
                    CellReference cr = new CellReference(refersToFormula);
                    if (cr != null) {
                        Cell cell = getCell(book, cr);
                        if (cell != null) {
                            result.add(cell);
                        }
                    }
                }
            }
        }
        return result;
    }

    /**
     * 根据名称得到名称管理器中的名称单元格引用
     *
     * @param book Excel工作簿对象
     * @param nameName 名称的名称
     * @return CellReference Excel单元格引用对象
     */
    public static CellReference getCellReference(Workbook book, String nameName) {
        CellReference result = null;
        if (book != null && StrUtil.isNotBlank(nameName)) {
            Name name = book.getName(nameName);
            if (name != null) {
                result = new CellReference(name.getRefersToFormula());
            }
        }
        return result;
    }
}

在单元测试代码中通过CellReference来获取单元格。

CellReference中记录着sheet名字，行号，列号。

import cn.hutool.core.util.StrUtil;
import cn.hutool.poi.excel.ExcelUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.jupiter.api.Test;

import java.io.FileInputStream;
import java.io.IOException;
import java.util.List;

class PoiExcelUtilTest {
    String excelPath = "E:\\resource\\方正璞华\\方正璞华\\绩效考核\\项目开发-绩效考核-20230801.xlsx";

    /**
     * 测试，根据excel文件和名称管理器中的名称得到单元格
     * @throws IOException
     */
    @Test
    void getNameCell() throws IOException {
        Workbook book = new XSSFWorkbook(new FileInputStream(excelPath));
        Cell cell = PoiExcelUtil.getNameCell(book, "name1");
        System.out.println(StrUtil.toString(cell));
    }

    /**
     * 测试，根据excel文件和单元格引用得到单元格
     * @throws IOException
     */
    @Test
    void getCellByReference() throws IOException {
        Workbook book = new XSSFWorkbook(new FileInputStream(excelPath));
        Cell cell = PoiExcelUtil.getCell(book, new CellReference("版本!$A$4"));
        System.out.println(StrUtil.toString(cell));
    }

    /**
     * 测试，读出Excel名称管理器中的所有单元格
     * @throws IOException
     */
    @Test
    void listNameCell() throws IOException {
        List<Cell> cells = PoiExcelUtil.listNameCell(excelPath);
        System.out.println(StrUtil.toString(cells));
    }
}