Documents for Excel, Java Edition Documentation
Features / Formulas / Cross Workbook Formula
In This Topic
    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.

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

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