Spread Windows Forms 18 Product Documentation

Managing External Reference

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.

!type=note
Note: 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.

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

' 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.

// Add an existing workbook to workbookSet
IWorkbook workbook2;
workbook2 = fpSpread2.AsWorkbook();
workbook2.Name = "Book2"; // Default name is "fpSpread2"
workbookSet.Workbooks.Add(workbook2);

' 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.

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

' 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.

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

' 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.

//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

'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.

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

' 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))