动态添加外部(跨工作簿)引用 [英] Dynamically add External (Cross-Workbook) references

问题描述

在我们的项目中，我们有不同版本的 excelsheets，它们相互引用:

In our project, we have different versions of excelsheets which reference each other:

C:\V1\Sample.xls//没有引用

C:\V1\Sample.xls //no references

C:\V2\Sample.xls//引用 V1

C:\V2\Sample.xls //references V1

C:\V3\Sample.xls//引用 V2

C:\V3\Sample.xls //references V2

单元格值示例:

=MID('C:\V1\[Sample.xls]Sheet1'!$AB2;21;1)

现在我想使用 apache POI 评估 V3 的公式，我找到了以下示例 这里

Now I want to evaluate formulas of V3 using apache POI, I found the following example here

// Create a FormulaEvaluator to use
FormulaEvaluator mainWorkbookEvaluator = workbook.getCreationHelper().createFormulaEvaluator();

// Track the workbook references
Map<String,FormulaEvaluator> workbooks = new HashMap<String, FormulaEvaluator>();
// Add this workbook
workbooks.put("report.xlsx", mainWorkbookEvaluator);
// Add two others
workbooks.put("input.xls", WorkbookFactory.create("c:\temp\input22.xls").getCreationHelper().createFormulaEvaluator());
workbooks.put("lookups.xlsx", WorkbookFactory.create("/home/poi/data/tmp-lookups.xlsx").getCreationHelper().createFormulaEvaluator());

// Attach them
mainWorkbookEvaluator.setupReferencedWorkbooks(workbooks);

// Evaluate
mainWorkbookEvaluator.evaluateAll();

现在我的问题:我不知道文件的位置，因此我需要从主工作簿中获取所有引用，然后自动(并且可能递归地)添加它们，而不是像在上面的例子.是否有获取引用的函数，或者有人知道实现此目的的方法吗?

Now my problem: I do not know the locations of the files, I therefore need to get all references from the mainworkbook and then automatically (and probably recursively) add them, not static like in the example above. Is there a function to get the references or does anyone know a way to achieve this?

此外，我想知道是否必须将所有 FormulaEvaluator 添加到 V3，还是必须将 V2 添加到 V3 并将 V1 添加到 V2 才能使其工作?

Additionally, I am wondering if I have to add all FormulaEvaluator to V3 or do I have to add V2 to V3 and V1 to V2 for this to work?

我目前已经实现了 setIgnoreMissingWorkbooks(true)，但由于值会发生变化，我们不想手动打开每个 excel 文件来更新我想要实现此解决方案的引用.任何帮助表示赞赏

I currently have setIgnoreMissingWorkbooks(true) implemented, but as the values will change and we do not want to open each excel file manually to update the references I want to implement this solution. Any help is appreciated

推荐答案

要获取所有外部引用，请使用以下方法:

To get all external references use following method:

private static Set<String> getReferencedWorkbooks(Workbook workbook) {
    Set<String> workbookNames = new HashSet<>();
    final EvaluationWorkbook evalWorkbook;
    if (workbook instanceof HSSFWorkbook) {
        evalWorkbook = HSSFEvaluationWorkbook.create((HSSFWorkbook) workbook);
    } else if (workbook instanceof XSSFWorkbook) {
        evalWorkbook = XSSFEvaluationWorkbook.create((XSSFWorkbook) workbook);
    } else {
        throw new IllegalStateException();
    }
    for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
        Sheet sheet = workbook.getSheetAt(i);
        final EvaluationSheet evalSheet = evalWorkbook.getSheet(i);
        for (Row r : sheet) {
            for (Cell c : r) {
                if (c.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
                    final EvaluationCell cell = evalSheet.getCell(c.getRowIndex(), c.getColumnIndex());
                    final Ptg[] formulaTokens = evalWorkbook.getFormulaTokens(cell);
                    for (Ptg formulaToken : formulaTokens) {
                        final int externalSheetIndex;
                        if (formulaToken instanceof Ref3DPtg) {
                            Ref3DPtg refToken = (Ref3DPtg) formulaToken;
                            externalSheetIndex = refToken.getExternSheetIndex();
                        } else if (formulaToken instanceof Ref3DPxg) {
                            Ref3DPxg refToken = (Ref3DPxg) formulaToken;
                            externalSheetIndex = refToken.getExternalWorkbookNumber();
                        } else {
                            externalSheetIndex = -1;
                        }

                        if (externalSheetIndex >= 0) {
                            final ExternalSheet externalSheet = evalWorkbook.getExternalSheet(externalSheetIndex);
                            workbookNames.add(externalSheet.getWorkbookName());
                        }
                    }
                }
            }
        }
    }
    return workbookNames;
}

如果您的所有工作簿都是 XLSX/XLSM，您可以使用以下代码:

If your all of your workbooks are XLSX/XLSM you can use following code:

private static Set<String> getReferencedWorkbooksXssf(XSSFWorkbook workbook) {
    Set<String> workbookNames = new HashSet<>();
    final List<ExternalLinksTable> externalLinksTable = workbook.getExternalLinksTable();
    for (ExternalLinksTable linksTable : externalLinksTable) {
        final String linkedFileName = linksTable.getLinkedFileName();
        workbookNames.add(linkedFileName);
    }

    return workbookNames;
}

这篇关于动态添加外部(跨工作簿)引用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！