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

前言

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