Spread Windows Forms 17
Spread Windows Forms 17.0 Product Documentation / Developer's Guide / Formulas in Cells / Managing External Reference
In This Topic
    Managing External Reference
    In This Topic

    Spread for WinForms supports interaction with other Spread instances in order to facilitate data interchange with the help of external references. An external reference in a spreadsheeet refers to the contents of a cell or a range of cells lying in another workbook.

    While using external references, if the referred workbook is available in the memory, then the data is fetched directly from the external workbook; else the required information is fetched from the cached data. Usually, an external workbook caches only the cell references and not the complete data in the spreadsheet. In such a scenario, all the remaining cells in the external workbook will remain empty.

    Apart from another workbook, you can also create external reference to an XLSX file. While referring to an XLSX file, if the workbook isn't available, data is imported from XLSX to cached data storage of external book.

    Refer to the following scenarios for more information regarding external reference:

    Spread for WinForms also supports ExternalReference flag for saving an Excel file. If ExternalReference flag is turned on, behavior of the workbook will be same as Excel, else saving the workbook will not change anything. To save external link values with the workbook when exporting to Excel, user needs to set the SaveLinkValues property to true.

    External reference feature is available only for files saved in OpenXML format. XML and binary formatted files do not support this feature.

    Creating a new workbook in existing workbook set

    Refer to the following code snippet to create a new workbook in existing workbook set.

    C#
    Copy Code
    // Create a workbookSet
    IWorkbookSet workbookSet;
    workbookSet = GrapeCity.Spreadsheet.Win.Factory.CreateWorkbookSet();
    
    // Create new workbook in same workbookSet
    IWorkbook workbook1;
    workbook1 = workbookSet.Workbooks.Add();
    
    // Attaching workbook1 to Spread control fpSpread1
    fpSpread1.Attach(workbook1);
    
    VB
    Copy Code
    ' Create a workbookSet
    Dim workbookSet As IWorkbookSet
    workbookSet = GrapeCity.Spreadsheet.Win.Factory.CreateWorkbookSet()
     
    ' Create new workbook in same workbookSet
    Dim workbook1 As IWorkbook
    workbook1 = workbookSet.Workbooks.Add()
     
    ' Attaching workbook1 to Spread control fpSpread1
    fpSpread1.Attach(workbook1)
    

    Adding existing workbook to the workbook set

    Before adding an existing workbook to workbook set, you must make sure that the name of the workbook is unique and that the new workbook contains no data. There can be one worksheet in the new workbook, but that worksheet should be empty too. Refer to the following code snippet for the implementation.

    C#
    Copy Code
    // Add an existing workbook to workbookSet
    IWorkbook workbook2;
    workbook2 = fpSpread2.AsWorkbook();
    workbook2.Name = "Book2"; // Default name is "fpSpread2"
    workbookSet.Workbooks.Add(workbook2);
    
    VB
    Copy Code
    ' Add an existing workbook to workbookSet
    Dim workbook2 As IWorkbook
    workbook2 = fpSpread2.AsWorkbook()
    workbook2.Name = "Book2" ' Default name is "fpSpread2"
    workbookSet.Workbooks.Add(workbook2)
    

    Opening external file in the workbook and displaying it in Spread control

    Refer to the following code snippet to open and display external file in the Spread control.

    C#
    Copy Code
    // Open new workbook from a file
    IWorkbook workbook3;
    workbook3 = workbookSet.Workbooks.Open(@"Test.xlsx");
    workbook3.Name = "Book3"; // Default name is "TEST.xlsx"
    
    // Use an opened workbook to display in spread control
    fpSpread3.Attach(workbook3);
    
    VB
    Copy Code
    ' Open new workbook from a file
    Dim workbook3 As IWorkbook
    workbook3 = workbookSet.Workbooks.Open("Test.xlsx")
    workbook3.Name = "Book3" ' Default name is "TEST.xlsx"
    
    ' Use an opened workbook to display in spread control
    fpSpread3.Attach(workbook3)
    

    Creating external references between workbooks of same workbook set

    Refer to the following code snippet to create external references between workbooks of same workbook set.

    C#
    Copy Code
    // Assigning external cell reference formula in workbook2 referring to cell in workbook1
    fpSpread2.Sheets[0].Cells[1, 1].Formula = "[Book1]Sheet1!$B$2";
    
    //Assigning cell formula in workbook1 referring to range in workbook2 & cell formula in workbook2 referring to range in Workbook1
    fpSpread1.Sheets[0].SetFormula(1, 3, "SUM([Book2]Sheet1!C3:C4)");
    fpSpread2.Sheets[0].SetFormula(1, 3, "SUM([Book1]Sheet1!C3:C4)");
    
    //Create custom name in workbook1 referring to range in workbook2
    fpSpread1.ActiveSheet.AddCustomName("Alpha", "Sum([Book2]Sheet1!C3:C4)", 2, 2);
    fpSpread1.ActiveSheet.SetFormula(2, 2, "Alpha");
    fpSpread2.ActiveSheet.SetValue(2, 2, 10);
    fpSpread2.ActiveSheet.SetValue(3, 2, 20);
    
    //Add cell formula in workbook1 referring to custom name of workbook2
    fpSpread2.ActiveSheet.AddCustomName("Beta", "$C$3:$C$4", 1, 1);
    fpSpread2.ActiveSheet.SetValue(2, 2, 10);
    fpSpread2.ActiveSheet.SetValue(3, 2, 20);
    fpSpread1.ActiveSheet.SetFormula(1, 4, "SUM(Book2!Beta)");
    
    VB
    Copy Code
    ' Assigning external cell reference formula in workbook2 referring to cell in workbook1
    fpSpread2.Sheets(0).Cells(1, 1).Formula = "[Book1]Sheet1!$B$2"
    'Assigning cell formula in workbook1 referring to range in workbook2 & cell formula in workbook2 referring to range in Workbook1
    fpSpread1.Sheets(0).SetFormula(1, 3, "SUM([Book2]Sheet1!C3:C4)")
    fpSpread2.Sheets(0).SetFormula(1, 3, "SUM([Book1]Sheet1!C3:C4)")
     
    'Create custom name in workbook1 referring to range in workbook2
    fpSpread1.ActiveSheet.AddCustomName("Alpha", "Sum([Book2]Sheet1!C3:C4)", 2, 2)
    fpSpread1.ActiveSheet.SetFormula(2, 2, "Alpha")
    fpSpread2.ActiveSheet.SetValue(2, 2, 10)
    fpSpread2.ActiveSheet.SetValue(3, 2, 20)
     
    'Add cell formula in workbook1 referring to custom name of workbook2
    fpSpread2.ActiveSheet.AddCustomName("Beta", "$C$3:$C$4", 1, 1)
    fpSpread2.ActiveSheet.SetValue(2, 2, 10)
    fpSpread2.ActiveSheet.SetValue(3, 2, 20)
    fpSpread1.ActiveSheet.SetFormula(1, 4, "SUM(Book2!Beta)")
    

    Updating values in source workbook

    Modifying cell values in source workbook will automatically update formulas in the referred workbook. Before resetting the source workbook, you must make sure that the workbook is closed. Refer to the following code snippet for the implementation.

    C#
    Copy Code
    //Change reference cell in source workbook, then formula is automatically updated in fpSpread2
    fpSpread1.Sheets[0].Cells[2, 1].Value = 1000;
    
    // Reset Source workbook
    fpSpread1.AsWorkbook().Close();
    fpSpread1.Reset(); // We need to close workbook before reset
    
    VB
    Copy Code
    'Change reference cell in source workbook, then formula is updated in fpSpread2
    fpSpread1.Sheets(0).Cells(2, 1).Value = 1000
    
    ' Reset Source workbook
    fpSpread1.AsWorkbook().Close()
    Dim ' We need to close workbook before reset As fpSpread1.Reset()
    

    Setting break links

    Setting break links in the source workbook replaces external references with values. Refer to the following code snippet for the implementation.

    C#
    Copy Code
    // Support "break links"
    var value1 = workbook1.ActiveSheet.Cells[2, 2].Value;
    fpSpread2.ActiveSheet.SetValue(2, 2, 20);
    var value2 = workbook1.ActiveSheet.Cells[2, 2].Value;
    MessageBox.Show(string.Format("Before break link, Value is changing : value1={0} value2={1} ", value1, value2));
    
    workbook1.BreakLink(workbook2.Name);
    fpSpread2.ActiveSheet.SetValue(2, 2, 30);
    var value3 = workbook1.ActiveSheet.Cells[2, 2].Value;
    MessageBox.Show(string.Format("After Break link, Value isn't changing : value1={0} value2={1} ", value2, value3));
    
    VB
    Copy Code
    ' Support "break links"
    Dim value1 As var = workbook1.ActiveSheet.Cells(2,2).Value
    fpSpread2.ActiveSheet.SetValue(2, 2, 20)
    Dim value2 As var = workbook1.ActiveSheet.Cells(2,2).Value
    MessageBox.Show(String.Format("Before break link, Value is changing : value1={0} value2={1} ", value1, value2))
    
    workbook1.BreakLink(workbook2.Name)
    fpSpread2.ActiveSheet.SetValue(2, 2, 30)
    Dim value3 As var = workbook1.ActiveSheet.Cells(2,2).Value
    MessageBox.Show(String.Format("After Break link, Value isn't changing : value1={0} value2={1} ", value2, value3))
    
    See Also