导入Excel文件的时候公式为【#Ref!】应该怎么解决?

原创
03/04 11:56
阅读数 348

前言

在我们使用Excel时,经常会遇到一个问题,就是导入Excel时公式显示为【#Ref!】的情况。这通常是因为公式中引用的单元格已被删除或对应的工作表被删除,导致原公式无法识别对应的参数而显示为【#Ref!】。

比如在一张Excel表中,sheet1 中 A1 单元格的公式为‘=Sheet2!B1’,如果 Sheet2 由于各种历史原因丢失,那么此时 sheet1 中 A1 计算结果为【#Ref!】,如果此时想查找到 Sheet2 怎么办呢?今天小编就将为大家介绍如何用葡萄城公司的Java API 组件——GrapeCity Documents for Excel(以下简称GcExcel)来查找丢失的Sheet页。

具体操作步骤

1)准备

首先创建公式

Workbook workbook = new Workbook();
IWorksheet workSheet = workbook.getWorksheets().get(0);
workSheet.setName("sheet1");
workSheet.getRange(1, 1).setFormula("sheet2!F7");
workSheet.getRange(3, 3).setFormula("Sheet3!A1");

2)查找

接下来,通过Find进行遍历查询所有的【#Ref!】公式,GcExcel提供了各种类型的查找替换。

FindOptions tempVar = new FindOptions();
//设置通过文本查找
tempVar.setLookIn(FindLookIn.Texts);
IRange range = null;
do {
    range = searchRange.find("Ref", range, tempVar);
    if (range == null) {
        break;
    } else {
        //在这里做相应的逻辑
    }
} while (true);

上述代码是查找替换的基础代码,我们发现上述代码 searchRange 未定义,searchRange 可以是整个 sheet, 也可以是一片区域,接下来我们定义searchRange 。

3)特殊单元格

GcExcel 提供了找到错误公式的能力,通过 specialCells 可以查找到错误公式,并返回错误公式的区域为第二步中的searchRange变量 。

IRange searchRange = workSheet.getCells().specialCells(SpecialCellType.Formulas, SpecialCellsValue.Errors);

现在我们已经找到了对应的所有为【#Ref!】的单元格,接下来开始做查找成功之后的逻辑。

4)公式解析

查找成功后,可以通过 range.getFormula() 获取到公式,接下来对公式进行解析,由于 Excel 公式有的简单,有的复杂,不能单纯判断等号后,感叹号前的字符串为sheet 名称,我们要通过公式树去遍历解析。
GcExcel 提供了公式解析器,调用 parse 拿到公式树,之后可以通过 getWorksheetName 获取 sheetName,相关代码如下:

//将公式中等号去掉,并进行解析
FormulaSyntaxTree syntaxTree = FormulaSyntaxTree.Parse(range.getFormula().replaceFirst("=", ""));
addNotFoundSheet(syntaxTree.getRoot(), workbook);

addNotFoundSheet 定义如下:

private static void addNotFoundSheet(SyntaxNode node, Workbook workbook) {
        if (node == null) {
            return;
        }
        if (node instanceof ReferenceNode) {
            String sheetName = ((ReferenceNode) node).getReference().getWorksheetName();
            if (workbook.getWorksheets().get(sheetName) == null) {
                IWorksheet tempSheet = workbook.getWorksheets().add();
                tempSheet.setName(sheetName);
            }
        }
        for (SyntaxNode child : node.getChildren()) {
            addNotFoundSheet(child, workbook);
        }
    }

在上述代码中首先判断node是否是 ReferenceNode 类型,如果是的话,通过 node.getReference().getWorksheetName() 获取 sheetName,并判断当前工作簿是否存在此sheet,如果不存在则进行添加。

处理后,对其子节点进行递归判断,重复上述步骤,直到 node 节点为 null,退出递归查询。

最后附上完整版的代码:

public static void main(String[] args) throws Exception {
        Workbook workbook = new Workbook();
        IWorksheet workSheet = workbook.getWorksheets().get(0);
        workSheet.setName("sheet1");
        workSheet.getRange(1, 1).setFormula("sheet2!F7");
        workSheet.getRange(3, 3).setFormula("Sheet3!A1");


        FindOptions tempVar = new FindOptions();
        tempVar.setLookIn(FindLookIn.Texts);
        IRange searchRange = workSheet.getCells().specialCells(SpecialCellType.Formulas, SpecialCellsValue.Errors);

        IRange range = null;
        do {
            range = searchRange.find("Ref", range, tempVar);
            if (range == null) {
                break;
            } else {
                FormulaSyntaxTree syntaxTree = FormulaSyntaxTree.Parse(range.getFormula().replaceFirst("=", ""));
                addNotFoundSheet(syntaxTree.getRoot(), workbook);
            }
        } while (true);

    }

    private static void addNotFoundSheet(SyntaxNode node, Workbook workbook) {
        if (node == null) {
            return;
        }
        if (node instanceof ReferenceNode) {
            String sheetName = ((ReferenceNode) node).getReference().getWorksheetName();
            if (workbook.getWorksheets().get(sheetName) == null) {
                IWorksheet tempSheet = workbook.getWorksheets().add();
                tempSheet.setName(sheetName);
            }
        }
        for (SyntaxNode child : node.getChildren()) {
            addNotFoundSheet(child, workbook);
        }
    }

通过上述代码,可以查找到”sheet2“与”sheet3“,并进行添加。

总结

以上就是使用GcExcel解决导入Excel文件的时候公式为【#Ref!】问题的全过程,如果您想了解更多详细信息,欢迎点击这里查看。

扩展链接:

如何使用 Blazor 框架在前端浏览器中导入/导出 Excel XLSX

简便实用:在 ASP.NET Core 中实现 PDF 的加载与显示

如何在.NET电子表格应用程序中创建流程图

展开阅读全文
加载中
点击引领话题📣 发布并加入讨论🔥
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部