Cross Workbook Formula | Document Solutions for Excel, Java Edition

Cross Workbook Formula

In This Topic

Cross workbook formulas allow you to refer the data in other workbooks by creating formulas referring to external workbooks. For example, if there are 5 workbooks for different subjects, you can add the marks of all five subjects in a worksheet by using cross workbook formulas.

DsExcel supports using cross-workbook formulas by using the folder or web path for external workbook. The getExcelLinkSources method can be used to get the names of linked excel workbooks and updateExcelLinks method to update the caches of excel links.

Refer to the following example code to use cross workbook formula by using the folder path for external workbook and update the excel links.

Java	Copy Code
// 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");

Java

Copy Code

// 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");

Refer to the following example code to use cross workbook formula by using the web path for external workbook and update the Excel links.

Java	Copy Code
//create a new workbook Workbook workbook = new Workbook(); IWorksheet worksheet = workbook.getWorksheets().get(0); worksheet.getRange("B1").setFormula("='http://developer.mescius.com/dsexcel/[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");

Java

Copy Code

//create a new workbook
Workbook workbook = new Workbook();
IWorksheet worksheet = workbook.getWorksheets().get(0);
worksheet.getRange("B1").setFormula("='http://developer.mescius.com/dsexcel/[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");

Refer to the following example code to use the cross-workbook formula by using the path for an external workbook with the table and updating the Excel links:

Java	Copy Code
// 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");

Java

Copy Code

// 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");

Note: You need to open the external link's workbook at the same time you open the workbook where the cross-workbook formula is used to recalculate and show the correct result.