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.
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) |
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) |
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) |
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)") |
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 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)) |