猿问

Apache Poi 参考另一个工作簿识别公式

我有一个工作簿，我必须清除所有对其他工作簿的引用。我目前正在尝试解析单元格公式，以检查它们是否引用了任何 excel 文件。

为此，我使用这条线

cell.getCellFormula().matches(".*\\[.*\\.xls[xm]?\\].*")

这个问题是，单元格在 XML 格式中看起来像这样：

 <c r="K64" s="2128">
    <f>[5]Segments!$AS$7/Annual!AF38</f>
    <v>0.0</v>
 </c>

如您所见，该公式实际上不包含.xls, '.xlsx' 或根本不包含.xlsm。据我所知[5]，它表示一个共享字符串，它包含实际路径以及公式的实际值。

现在可以说并将正则表达式更改为.*\\[\d+\\].*，但我认为这很容易出错。此外，我认为并非每个外部引用都像这样。

所以我的问题是：

如何识别引用外部工作簿的公式？

如果你有任何问题随时问。

编辑：

我准备了一个示例 excel 文件来展示这个问题。可在workupload.com下载


跃然一笑
浏览 233回答 1
1回答

慕的地10843

动态添加外部（跨工作簿）引用中显示的方法绝对是可行的方法。遍历所有公式标记，如果其中一个具有外部工作表索引，则此公式引用外部工作表。使用您上传的文件的示例：import org.apache.poi.ss.usermodel.*;import org.apache.poi.ss.formula.*;import org.apache.poi.ss.formula.ptg.*;import org.apache.poi.ss.formula.EvaluationWorkbook.ExternalSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.hssf.usermodel.HSSFEvaluationWorkbook;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook;import java.io.FileInputStream;public class ExcelReadExternalReference {&nbsp; &nbsp; public static void main(String[] args) throws Exception {&nbsp; &nbsp; &nbsp; &nbsp; String filePath = "TestExternalLinks.xlsx";&nbsp; &nbsp; &nbsp; &nbsp; // String filePath = "TestExternalLinks.xls";&nbsp; &nbsp; &nbsp; &nbsp; Workbook workbook = WorkbookFactory.create(new FileInputStream(filePath));&nbsp; &nbsp; &nbsp; &nbsp; EvaluationWorkbook evalWorkbook = null;&nbsp; &nbsp; &nbsp; &nbsp; if (workbook instanceof HSSFWorkbook) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; evalWorkbook = HSSFEvaluationWorkbook.create((HSSFWorkbook) workbook);&nbsp; &nbsp; &nbsp; &nbsp; } else if (workbook instanceof XSSFWorkbook) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; evalWorkbook = XSSFEvaluationWorkbook.create((XSSFWorkbook) workbook);&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; Sheet sheet = workbook.getSheetAt(0);&nbsp; &nbsp; &nbsp; &nbsp; EvaluationSheet evalSheet = evalWorkbook.getSheet(0);&nbsp; &nbsp; &nbsp; &nbsp; for (Row row : sheet) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; for (Cell cell : row) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if (cell.getCellType() == CellType.FORMULA) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; String cellFormula = cell.getCellFormula();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; System.out.println(cellFormula);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; EvaluationCell evaluationCell = evalSheet.getCell(cell.getRowIndex(), cell.getColumnIndex());&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Ptg[] formulaTokens = evalWorkbook.getFormulaTokens(evaluationCell);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; for (Ptg formulaToken : formulaTokens) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; int externalSheetIndex = -1;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if (formulaToken instanceof Ref3DPtg) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Ref3DPtg refToken = (Ref3DPtg) formulaToken;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; externalSheetIndex = refToken.getExternSheetIndex();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; } else if (formulaToken instanceof Area3DPtg) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Area3DPtg refToken = (Area3DPtg) formulaToken;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; externalSheetIndex = refToken.getExternSheetIndex();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; } else if (formulaToken instanceof Ref3DPxg) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Ref3DPxg refToken = (Ref3DPxg) formulaToken;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; externalSheetIndex = refToken.getExternalWorkbookNumber();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; } else if (formulaToken instanceof Area3DPxg) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Area3DPxg refToken = (Area3DPxg) formulaToken;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; externalSheetIndex = refToken.getExternalWorkbookNumber();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if (externalSheetIndex >= 0) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; System.out.print("We have extrenal sheet index: " + externalSheetIndex&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; + ". So this formula refers an external sheet in workbook: ");&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ExternalSheet externalSheet = null;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if (workbook instanceof HSSFWorkbook) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; externalSheet = evalWorkbook.getExternalSheet(externalSheetIndex);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; } else if (workbook instanceof XSSFWorkbook) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; externalSheet = evalWorkbook.getExternalSheet(null, null, externalSheetIndex);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; String linkedFileName = externalSheet.getWorkbookName();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; System.out.println(linkedFileName);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; workbook.close();&nbsp; &nbsp; }}
0
随时随地看视频慕课网APP

相关分类

Java
我要回答