如何用Apache POI通过自定义名称定位Excel单元格并赋值

问题描述 #

有一个包含自定义名称单元格的Excel文件（例如单元格B5的名称为vehicleCost），需要将UI传递的数据赋值给这些单元格。目前使用Java的Apache POI可通过A1格式的单元格标识获取单元格，但无法直接通过自定义名称获取。若手动维护名称与单元格标识的映射表，会因数据量过大导致代码冗余，需要更高效的实现方式。

解决方案 #

Apache POI提供了对Excel名称管理器的支持，可以直接通过自定义名称解析出对应的单元格引用，无需手动维护映射表。核心步骤如下：

• 从Workbook中获取所有定义的名称（Name对象集合）
• 遍历找到目标名称，解析其引用的单元格地址
• 根据解析出的地址定位到对应的Sheet、Row和Cell，完成赋值

代码实现 #

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellReference;

import java.io.FileInputStream;
import java.io.FileOutputStream;

public class ExcelNamedCellDemo {
    public static void main(String[] args) throws Exception {
        // 加载Excel文件
        try (Workbook workbook = WorkbookFactory.create(new FileInputStream("your-excel-file.xlsx"))) {
            // 目标自定义名称（注意Excel名称不区分大小写）
            String targetName = "VehicleCost";
            Name namedCell = null;

            // 遍历所有名称，找到目标名称
            for (Name name : workbook.getAllNames()) {
                if (name.getNameName().equalsIgnoreCase(targetName)) {
                    namedCell = name;
                    break;
                }
            }

            if (namedCell != null) {
                // 解析名称对应的单元格引用字符串，格式类似"VehicleSheet!$B$5"
                String refString = namedCell.getRefersToFormula();
                // 去除可能的$符号，转换为可识别的单元格引用
                refString = refString.replaceAll("\\$", "");
                CellReference cellReference = new CellReference(refString);

                // 获取对应的Sheet、Row、Cell
                Sheet sheet = workbook.getSheet(cellReference.getSheetName());
                Row row = sheet.getRow(cellReference.getRow());
                Cell cell = row.getCell(cellReference.getCol());

                // 若单元格不存在则创建
                if (cell == null) {
                    cell = row.createCell(cellReference.getCol());
                }

                // 给单元格赋值（示例为字符串，可根据需求改为数值等类型）
                cell.setCellValue("UI传递的目标数据");
            }

            // 保存修改后的Excel文件
            try (FileOutputStream fos = new FileOutputStream("modified-excel-file.xlsx")) {
                workbook.write(fos);
            }
        }
    }
}

注意事项 #

• Excel的自定义名称不区分大小写，匹配时建议用equalsIgnoreCase避免大小写问题
• 若名称引用的是单元格区域而非单个单元格，需额外处理（比如取区域第一个单元格，或根据需求遍历区域）
• 解析引用字符串时，要去除$符号，否则CellReference可能无法正确识别
• 若目标单元格不存在，需调用row.createCell()创建后再赋值

内容的提问来源于stack exchange，提问作者Deepu