.
Intro
| Excerpt |
|---|
Use a variety of .NET data types to populate ExcelTemplate including |
This example demonstrates how Excel Writer can use arrays, array lists and hash tables as data sources for an ExcelTemplate report.
BindData
Methods used:
- BindData - For using 2-dimensional, grid-like data stored in an array. Arrays can be arranged in [row,col] (normal) or [col,row] (transposed) fashions.
- BindColumnData
...
- - For use with a 1-dimensional array, collection, or dictionary. Data is filled in the column.
- BindRowData
...
- - For use with a 1-dimensional array, collection, or dictionary. Populates with only a single row of the data source. There must be a data marker for every column you want to be shown.
Code
| Code Block |
|---|
public void GenerateReport() { { // Generate the new workbook with ExcelTemplate // Create an instance of SoftArtisans ExcelTemplate ExcelTemplate xlt = new ExcelTemplate();\ // Open the template workbook string string templatePath = @"..\..\TemplatesExcelTemplateFiles\ArrayDataSourceTemplate.xlsx"; xlt.Open(templatePath);\ // Create arrays/IEnumerables and bind them to the template // data markers. The template data markers bound to each array // are shown below in comments. \ DataBindingProperties basicBindingProperties = xlt.CreateDataBindingProperties();\ // %%=$SimpleArray string\[\ string[] onedim = { "SoftArtisans", "OfficeWriter", "ExcelTemplate" }; xlt.BindColumnData(onedim, "SimpleArray", basicBindingProperties);\ // %%=$ArrayList ArrayList arrayList = new ArrayList(onedim); xlt.BindColumnData(arrayList, "ArrayList", basicBindingProperties);\ // By default, ExcelTemplate expects an array orientation of // \[rows\]\[cols\]. You can pass a transposed array by setting // the Transpose argument to true. Notice how twoDimNormal // and twoDimTranspose differ. This gives you flexibility // in how you arrange your data source arrays. string\[,\ string[,] twoDimNormal = {{"Nancy", "Davolio", "Sales Manager"}, {"Michael", "Suyama", "HR Representative"}, {"Adrian", "King", "IS Support"}};\ // When binding to two-dimensional arrays, the second parameter // to BindData (dataMarkerName) must be a String\[\] array. // The values in that array correpond to the data marker names: // %%=TwoDimArray.FirstName // %%=TwoDimArray.LastName // %%=TwoDimArray.Position string\[\ string[] names = { "FirstName", "LastName", "Position" }; xlt.BindData(twoDimNormal, names, "TwoDimArray", basicBindingProperties);\ // %%=TwoDimTrans.FirstName // %%=TwoDimTrans.LastName // %%=TwoDimTrans.Position string\[,\ string[,] twoDimTranspose = {{"Nancy", "Michael", "Adrian"}, {"Davolio", "Suyama", "King"}, {"Sales Manager", "HR Representative", "IS Support"}}; DataBindingProperties bindTransposed = xlt.CreateDataBindingProperties(); bindTransposed.Transpose = true; xlt.BindData(twoDimTranspose, names, "TwoDimTrans", bindTransposed);\ // When binding to an IDictionary collection, the "Key" is // the Data Marker Name. // // // The following entries bind to the data marker names: // %%=HashTable.FirstName // %%=HashTable.LastName // %%=HashTable.Position IDictionary hashTable = new Hashtable(); hashTable.Add("FirstName", "Nancy"); hashTable.Add("LastName", "Davolio"); hashTable.Add("Position", "Sales Manager");\ xlt.BindRowData(hashTable, "HashTable", basicBindingProperties);\ // %%=Address.Street // %%=Address.City // %%=Address.State string\[\ string[] addressvalues = { "3 Brook St.", "Watertown", "MA" }; string\[\ string[] addressnames = { "Street", "City", "State" }; xlt.BindRowData(addressvalues, addressnames, "Address", basicBindingProperties);\ // Process the template to populate it with the Data Source data xlt.Process();\ xlt.Save(@"..\..\OutputExcelOutputFiles\ArrayDataSource_output.xlsx"); } } |
Downloads
...
xlt.Save(@"..\..\ExcelOutputFiles\ArrayDataSource_output.xlsx");
}
}
|
Downloads
- Template:ArrayDataSourceTemplate.xlsx
- Output:ArrayDataSource_output.xlsx