Page History

The following sample generates an Excel spreadsheet from a template using arrays as data sources.

h3. Code sample: Using an Array as the Data Source

\[[C#|http://windemo.softartisans.com/OfficeWriter/latest/ExcelWriter/Web/CSharp/ExcelTemplate/ArrayDataSource.aspx]\]     \| \[[VB.NET|http://windemo.softartisans.com/OfficeWriter/latest/ExcelWriter/Web/VB/ExcelTemplate/ArrayDataSource.aspx]\]
The first data marker in the template is *%%=$SimpleArray*. *%%=$* indicates that the data source is either a simple variable or a 1-dimensional array that is a data source for a single column.

{info} *Data Marker Format for 1-D Arrays*\For a single column data marker that will be bound to a 1-dimensional array data source by the method [BindColumnData|ExcelTemplate.BindColumnData], use the data marker format *%%=$DataMarkerName*.  For a set of data markers with multiple fields that will be bound to a 1-dimensional array data source by the method [BindRowData|ExcelTemplate.BindRowData], use the data marker format *%%=DataMarkerName.Field* (without a *$*).  For more information, see [Creating Data Markers]. {info}

{info} *Notes on Backward Compatibility*\In ExcelWriter 7, the [SetDataSource|ExcelTemplate.SetDataSource], [SetCellDataSource|ExcelTemplate.SetCellDataSource(Object, String)], [SetColumnDataSource|ExcelTemplate.SetColumnDataSource], and [SetRowDataSource|ExcelTemplate.SetRowDataSource] methods have been deprecated in favor of the new data binding methods [BindData|ExcelTemplate.BindData], [BindCellData|ExcelTemplate.BindCellData(Object, String, DataBindingProperties)], [BindColumnData|ExcelTemplate.BindColumnData], and [BindRowData|ExcelTemplate.BindRowData] respectively.  In ExcelWriter 4, a *$* was required for all data markers that bind to 1-dimensional arrays.  In ExcelWriter 5 and above, if a 1-dimensional array binds to a row of data markers by the method [BindRowData|ExcelTemplate.BindRowData] and the deprecated method [SetRowDataSource|ExcelTemplate.SetRowDataSource], the *$* should be omitted.  However, for backward compatibility, if you include the *$*, the data marker will be populated without error. {info}

In ArrayDataSource.aspx.cs, the [BindColumnData|ExcelTemplate.BindColumnData] method is called to set the data source for *%%=$SimpleArray* to a one-dimensional array:

{csharp:1}
string[] onedim = {"SoftArtisans", "OfficeWriter", "ExcelTemplate"};
xlt.BindColumnData(onedim,
    "SimpleArray",
    xlt.CreateDataBindingProperties());
{csharp}

{vbnet}
Dim onedim As String() = {"SoftArtisans", "OfficeWriter", "ExcelTemplate"}
xlt.BindColumnData(onedim, _
    "SimpleArray", _
    xlt.CreateDataBindingProperties())
{vbnet}

[ExcelTemplate.BindColumnData] sets a data source for a template column to a 1-dimensional array of objects.  The method's first parameter, onedim, is the data source array.  The second parameter, "SimpleArray", is the name of the template data marker to which the data source binds.  The third parameter, xlt.CreateDataBindingProperties(), is a [DataBindingProperties] object that contains values that change how the data are bound to the template; in this case, the default property values are used.

The method [BindData|ExcelTemplate.BindData] binds the data markers *%%=TwoDimArray.#1*, *%%=TwoDimArray.#2*, and *%%=TwoDimArray.#3* to a 2-dimensional string array:

{csharp:2}
string[][] twodim = {
    new string[]{"Nancy", "Davolio", "Sales Manager"},
    new string[]{"Michael", "Suyama", "HR Representative"},
    new string[]{"Adrian", "King", "IS Support"}
    };
string[] names = {"FirstName", "LastName", "Position"};
xlt.BindData(twodim,
    names,
    "TwoDimArray",
    xlt.CreateDataBindingProperties());
{csharp}

{vbnet}
Dim twodim()() As String = New String()() { _
    New String(){"Nancy", "Davolio", "Sales Manager"}, _
    New String(){"Michael", "Suyama", "HR Representative"}, _
    New String(){"Adrian", "King", "IS Support"} _
    }
Dim names As String() = {"FirstName", "LastName", "Position"}
xlt.BindData(twodim, _
    names, _
    "TwoDimArray", _
    xlt.CreateDataBindingProperties())
{vbnet}

BindData's first parameter is the 2-dimensional array of strings to use as the data source.  The second parameter specifies an array of column names.  The third parameter - "TwoDimArray" - is the name of the template data marker to which the data source binds.  The fourth parameter - xlt.CreateDataBindingProperties() - is a [DataBindingProperties] object that contains values that change how the data are bound to the template; in this case, the default property values are used.  There are three "TwoDimArray" data markers; their fields are specified by ordinal (#1, #2, and #3).  The columns of values in the data source bind to the data markers by order. That is, the first column of values will populate *%%=TwoDimArray.#1*, the second will populate *%%=TwoDimArray.#2*, and the third *%%=TwoDimArray.#3*.

[BindRowData|ExcelTemplate.BindRowData] sets a data source for the row of data markers *%%=Address.Street*, *%%=Address.City*, and *%%=Address.State* to a 1-dimensional array:

{csharp:3}
string[] addressvalues = {"3 Brook St.", "Watertown", "MA"};
string[] addressnames = {"Street", "City", "State"};
xlt.BindRowData(addressvalues,
    addressnames,
    "Address",
    xlt.CreateDataBindingProperties());
{csharp}

{vbnet}
Dim addressvalues As String() = {"3 Brook St.", "Watertown", "MA"}
Dim addressnames As String() = {"Street", "City", "State"}
xlt.BindRowData(addressvalues, _
    addressnames, _
    "Address", _
    xlt.CreateDataBindingProperties())
{vbnet}

The first parameter of BindRowData, addressvalues, specifies the array of values to bind to the data markers.  The second parameter, addressnames, is the array of data marker field names.  The values in the data source array will bind to the data marker fields by field name.  The third parameter, "Address", is the name of the data marker to which ExcelTemplate binds the data.  The fourth parameter, xlt.CreateDataBindingProperties(), is a [DataBindingProperties] object that contains values that change how the data are bound to the template; in this case, the default property values are used.
|| Data Marker || Code ||
| *%%=$SimpleArray* | {csharp:4}
string[] onedim = {"SoftArtisans",
    "OfficeWriter",
    "ExcelTemplate"};
xlt.BindColumnData(onedim,
    "SimpleArray",
    xlt.CreateDataBindingProperties());
{csharp}
{vbnet}
Dim onedim As String() = {"SoftArtisans", _
    "OfficeWriter", _
    "ExcelTemplate"}
xlt.BindColumnData(onedim, _
    "SimpleArray", _
    xlt.CreateDataBindingProperties()){vbnet} |
| *%%=TwoDimArray.#1* \\ *%%=TwoDimArray.#2* \\ *%%=TwoDimArray.#3* | {csharp:5}
string[][] twodim = {
    new string[]{"Nancy", "Davolio", "Sales Manager"},
    new string[]{"Michael", "Suyama", "HR Representative"},
    new string[]{"Adrian",
        "King",
        "IS Support"}
    };
string[] names = {"FirstName", "LastName", "Position"};
xlt.BindData(twodim,
    names,
    "TwoDimArray",
    xlt.CreateDataBindingProperties());
{csharp}
{vbnet}
Dim twodim()() As String = New String()() { _
    New String(){"Nancy", "Davolio", "Sales Manager"}, _
    New String(){"Michael", "Suyama", "HR Representative"}, _
    New String(){"Adrian", _
        "King", _
        "IS Support"}
    }
Dim names As String() = {"FirstName", "LastName", "Position"}
xlt.BindData(twodim, _
    names, _
    "TwoDimArray", _
    xlt.CreateDataBindingProperties()){vbnet} |
| *%%=Address.Street* \\ *%%=Address.City* \\ *%%=Address.State* | {csharp:12}
string[] addressvalues = {"3 Brook St.",
    "Watertown",
    "MA"};
string[] addressnames = {"Street", "City", "State"};
xlt.BindRowData(addressvalues,
    addressnames,
    "Address",
    xlt.CreateDataBindingProperties());
{csharp}
{vbnet}
Dim addressvalues As String() = {"3 Brook St.", _
    "Watertown", _
    "MA"}
Dim addressnames As String() = {"Street", "City", "State"}
xlt.BindRowData(addressvalues, _
    addressnames, _
    "Address", _
    xlt.CreateDataBindingProperties()) {vbnet}|

\
{scrollbar}