ExternalWorkbook Interface

An external workbook.

Namespace: DevExpress.Spreadsheet

Assembly: DevExpress.Spreadsheet.v19.1.Core.dll

Declaration

C#

public interface ExternalWorkbook :
    ISupportsContentChanged

VB.NET

Public Interface ExternalWorkbook
    Inherits ISupportsContentChanged

Remarks

A workbook can be a non-visual object, such as the Workbook, or the document loaded into the spreadsheet control (available via the SpreadsheetControl.Document property).

The following code snippet demonstrates how to create an external workbook, fill it with data and add to the ExternalWorkbookCollection.

Note

A complete sample project is available at https://github.com/DevExpress-Examples/how-to-reference-an-external-workbook-t144947

Form1.vb

Dim externalWorkbook As New Workbook()
externalWorkbook.Worksheets(0).Import(CreateDataTable(10), False, 0, 0)
externalWorkbook.Options.Save.CurrentFileName = "ExternalDocument.xlsx"
For Each item As IWorkbook In spreadsheetControl1.Document.ExternalWorkbooks
    If item.Options.Save.CurrentFileName = externalWorkbook.Options.Save.CurrentFileName Then
        Return
    End If
Next item
spreadsheetControl1.Document.ExternalWorkbooks.Add(externalWorkbook)

Form1.cs

Workbook externalWorkbook = new Workbook();
externalWorkbook.Worksheets[0].Import(CreateDataTable(10), false, 0, 0);
externalWorkbook.Options.Save.CurrentFileName = "ExternalDocument.xlsx";
foreach (IWorkbook item in spreadsheetControl1.Document.ExternalWorkbooks)
{
    if (item.Options.Save.CurrentFileName == externalWorkbook.Options.Save.CurrentFileName)
        return;
}
spreadsheetControl1.Document.ExternalWorkbooks.Add(externalWorkbook);

Subsequently the external workbook can be referenced in cell formulas. The following code inserts reference to the external workbook contained in the IWorkbook.ExternalWorkbooks collection.

Note

A complete sample project is available at https://github.com/DevExpress-Examples/how-to-reference-an-external-workbook-t144947

Form1.vb

If spreadsheetControl1.Document.ExternalWorkbooks.Count = 0 Then
    MessageBox.Show("Add a workbook to the ExternalWorkbooks collection")
    Return
End If
Dim extWorkbook As IWorkbook = DirectCast(spreadsheetControl1.Document.ExternalWorkbooks(0), IWorkbook)
Dim extWorkbookName As String = extWorkbook.Options.Save.CurrentFileName
Dim sFormula As String = String.Format("=[{0}]Sheet1!A1", extWorkbookName)
spreadsheetControl1.Document.Worksheets(0).Cells("A1").Formula = sFormula

Form1.cs

if (spreadsheetControl1.Document.ExternalWorkbooks.Count == 0)
{
    MessageBox.Show("Add a workbook to the ExternalWorkbooks collection");
    return;
}
IWorkbook extWorkbook = (IWorkbook) spreadsheetControl1.Document.ExternalWorkbooks[0];
string extWorkbookName = extWorkbook.Options.Save.CurrentFileName;
string sFormula = String.Format("=[{0}]Sheet1!A1", extWorkbookName);
spreadsheetControl1.Document.Worksheets[0].Cells["A1"].Formula = sFormula;

For more information on workbooks in the SpreadsheetControl, see the Workbook document.