文章目的

本文主要介绍如何使用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）

@Slf4j
public class PoiExcelUtil {/*** 返回Excel名称管理器中所有名称对应的单元格引用** @param path Excel工作簿路径* @return List<CellReference>*/public static List<CellReference> listNameCell(String path) throws IOException {List<CellReference> result = null;if (StrUtil.isNotBlank(path)) {log.info("excelFilePath:{}", path);Workbook book = new XSSFWorkbook(new FileInputStream(path));result = listNameCell(book);book.close();}return result;}/*** 返回Excel名称管理器中所有名称对应的单元格引用** @param book Excel工作簿对象* @return List<CellReference>*/public static List<CellReference> listNameCell(Workbook book) throws IOException {List<CellReference> result = null;// 打开Excel文件if (book != null && book.getAllNames() != null) {result = new ArrayList<>(book.getAllNames().size());// 获取所有的名称管理器for (Name namedRange : book.getAllNames()) {String refersToFormula = namedRange.getRefersToFormula();CellReference cellReference = new CellReference(refersToFormula);result.add(cellReference);}}return result;}/*** 根据名称得到名称管理器中的名称单元格引用** @param filePath* @param nameName* @return CellReference*/public static CellReference getNameCell(String filePath, String nameName) throws IOException {CellReference result = null;log.info("excelFilePath:{},nameName:{}", filePath, nameName);if (StrUtil.isNotBlank(filePath) && StrUtil.isNotBlank(nameName)) {Workbook book = new XSSFWorkbook(new FileInputStream(filePath));result = getNameCell(book, nameName);book.close();}return result;}/*** 根据名称得到名称管理器中的名称单元格引用** @param book* @param nameName* @return CellReference*/public static CellReference getNameCell(Workbook book, String nameName) {CellReference result = null;// 打开Excel文件if (book != null) {Name name = book.getName(nameName);if (name != null) {result = new CellReference(name.getRefersToFormula());}}return result;}
}

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

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

class PoiExcelUtilTest {String excelPath = "E:\\resource\\20230801.xlsx";@Testvoid getNameCell()  throws IOException {Workbook book = new XSSFWorkbook(new FileInputStream(excelPath));CellReference cell = PoiExcelUtil.getNameCell(book, "name1");//先找到SheetSheet sheet = book.getSheet(cell.getSheetName());//再找到单元格Cell c = sheet.getRow(cell.getRow()).getCell(cell.getCol());System.out.println(c.getNumericCellValue());}@Testvoid listNameCell() throws IOException {List<CellReference> cells = PoiExcelUtil.listNameCell(excelPath);System.out.println(StrUtil.toString(cells));}@Testvoid getNameCell2()  throws IOException {CellReference cell = PoiExcelUtil.getNameCell(excelPath, "name1");System.out.println(StrUtil.toString(cell));}}