Document Solutions for Excel, .NET Edition | Document Solutions
Features / Formulas / Cross Workbook Formula
In This Topic
    Cross Workbook Formula
    In This Topic

    Cross workbook formulas allow you to calculate values by referring and using data from different 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 property 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.

    C#
    Copy Code
    //create a new workbook
    var workbook = new GrapeCity.Documents.Excel.Workbook();
    
    workbook.Worksheets[0].Range["B1"].Formula = @"='D:\[A.xlsx]Sheet1'!A1";
    // create a new workbook as the intance of external workbook.
    var workbook2 = new GrapeCity.Documents.Excel.Workbook();
    workbook2.Worksheets[0].Range["A1"].Value = "Hello, World!";
    workbook2.Worksheets[0].Range["A2"].Value = "Hello";
    // update the caches of external workbook data.
    foreach (var item in workbook.GetExcelLinkSources())
    {
        workbook.UpdateExcelLink(item, workbook2);
    }
    
    //save to an Excel file
    workbook.Save("crossworkbookformulafolderpath.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.

    C#
    Copy Code
    //create a new workbook
    var workbook = new GrapeCity.Documents.Excel.Workbook();
    workbook.Worksheets[0].Range["B1"].Formula = "= 'https://developer.mescius.com/dsexcel/[SourceWorkbook.xlsx]Sheet1'!A1";
    // create a new workbook as the intance of external workbook.
    var workbook2 = new GrapeCity.Documents.Excel.Workbook();
    workbook2.Worksheets[0].Range["A1"].Value = 100;
    // update the caches of external workbook data.
    foreach (var item in workbook.GetExcelLinkSources())
    {
        workbook.UpdateExcelLink(item, workbook2);
    }
    //save to an excel file
    workbook.Save("crossworkbookformulawebpath.xlsx");