跨工作簿公式

跨工作簿公式允许您通过创建引用外部工作簿的公式来引用其他工作簿中的数据。例如，如果不同主题有5个工作簿，则可以使用跨工作簿公式添加工作表中所有5个主题的分数。

GcExcel 提供 getExcelLinkSources 方法，用于获取链接的excel工作簿的名称, updateExcelLinks 方法用于更新excel链接的缓存。

请参阅以下示例代码以使用跨工作簿公式并更新excel链接。

// Create a new workbook
Workbook workbook = new Workbook();

workbook.getWorksheets().get(0).getRange("B1").setFormula("='[SourceWorkbook.xlsx]Sheet1'!A1");
// Create a new workbook as the instance of external workbook
Workbook workbook2 = new Workbook();
workbook2.getWorksheets().get(0).getRange("A1").setValue("Hello, World!");
workbook2.getWorksheets().get(0).getRange("A2").setValue("Hello");
// Update the caches of external workbook data.
for (String item : workbook.getExcelLinkSources()) {
    workbook.updateExcelLink(item, workbook2);
}
// Save to an excel file
workbook.save("CrossWorkbookFormula.xlsx");

参考以下示例代码，通过使用外部工作簿的web路径来使用跨工作簿公式，并更新Excel链接。

//create a new workbook
Workbook workbook = new Workbook();
IWorksheet worksheet = workbook.getWorksheets().get(0);
worksheet.getRange("B1").setFormula("='http://wwww.grapecity.com/gcexcel/[SourceWorkbook.xlsx]Sheet1'!A1");

//create a new workbook as the instance of eternal workbook
Workbook workbook2 = new Workbook();
workbook2.getWorksheets().get(0).getRange("A1").setValue(100);
        
//update the caches of external workbook data.
for (String item : workbook.getExcelLinkSources()) {
    workbook.updateExcelLink(item, workbook2);
}
    
//save to an excel file
workbook.save("ExternalWorkbookLinks.xlsx");

参考以下示例代码，通过使用外部工作簿的路径以及包含表格的跨工作簿公式，并更新Excel链接：

// Create a new workbook.
var workbook = new Workbook();

// Add data to the cell.
workbook.getWorksheets().get(0).getRange("A1").setValue("Total Sales");

// Set the table formula across workbook.
workbook.getWorksheets().get(0).getRange("B1").setFormula("=SUM('[SalesTable.xlsx]'!Table1[Sales])");
workbook.getWorksheets().get(0).getRange("B1").setNumberFormat("$#,##0.00");
workbook.getWorksheets().get(0).getRange("A:B").setColumnWidth(12);

// Create another new workbook as the intance of external workbook.
var workbook2 = new Workbook();
            
// Add data for the table.
        
Object[][] data = new Object[][]
{
{ "Product", "Type", "Sales" },
{ "Apple", "Fruit", 25000},
{ "Grape", "Fruit", 30000 },
{ "Carrot", "Vegetable", 28000 },
{ "Strawberry", "Fruit", 50000 },
{ "Onion", "Vegetable", 23000 }

};
workbook2.getActiveSheet().getRange("A1:C6").setValue(data);

// Create the table.
workbook2.getActiveSheet().getTables().add(workbook2.getActiveSheet().getRange("A1:C6"), true);
workbook2.getActiveSheet().getRange("C2:C6").setNumberFormat("$#,##0.00");
workbook2.getActiveSheet().getRange("A:C").setColumnWidthInPixel(100);

// Update the caches of external workbook data.
for (String item : workbook.getExcelLinkSources())
{
    workbook.updateExcelLink(item, workbook2);
}

// Save both Excel files.
workbook.save("CrossWorkbookTableFormula.xlsx");
workbook2.save("SalesTable.xlsx");

type=warning
注意： 您需要在打开包含跨工作簿公式的工作簿的同时打开外部链接的工作簿，以便重新计算并显示正确的结果。