乐趣区

关于java:导入Excel文件的时候公式为Ref应该怎么解决

jiezi

前言

在咱们应用 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!】问题的全过程，如果您想理解更多详细信息，欢送点击这里查看。

退出移动版