Import data from dynamic collection to Excel worksheet in C#, VB.NET
Syncfusion Excel (XlsIO) library is a .NET Excel library used to create, read, and edit Excel documents. Also, converts Excel documents to PDF files. Using this library, you can import data from a dynamic collection in C# and VB.NET.
How to import dynamic collection?
XlsIO provides support to import data into a worksheet from data table, array, and business objects. The data source can be a dynamic object collection.
To import data from a dynamic collection, you need to follow the below steps.
Steps to import data from a dynamic collection programmatically:
Step 1: Create a new C#/VB.NET console application project.
Create a new C#/VB.NET console application
Step 2: Install Syncfusion.XlsIO.WinForms NuGet package as a reference to your .NET Framework applications from the NuGet.org.
Install NuGet package
Step 3: Include the following namespaces in the Program.cs file.
C#
using Syncfusion.XlsIO; using System.Collections.Generic; using System.Dynamic; using System.IO;
VB.NET
Imports Syncfusion.XlsIO Imports System.Dynamic Imports System.IO Imports System.Runtime.InteropServices
Step 4: To import data using dynamic object list, the dynamic object should override three methods from System.Dynamic.DynamicObject which are,
- TryGetMember(GetMemberBinder binder, out object result),
- TrySetMember(SetMemberBinder binder, object value) and
- GetDynamicMemberNames().
To get the value for a member, the TryGetMember (GetMemberBinder binder, out object result) method is used.
C#
public override bool TryGetMember(GetMemberBinder binder, out object result) { result = default(object); if (properties.ContainsKey(binder.Name)) { result = properties[binder.Name]; return true; } return false; }
VB.NET
Public Overrides Function TryGetMember(ByVal binder As GetMemberBinder, <Out> ByRef result As Object) As Boolean result = Nothing If properties.ContainsKey(binder.Name) Then result = properties(binder.Name) Return True End If Return False End Function
To set the value for a member, the TrySetMember(SetMemberBinder binder, object value) is used.
C#
public override bool TrySetMember(SetMemberBinder binder, object value) { properties[binder.Name] = value; return true; }
VB.NET
Public Overrides Function TrySetMember(ByVal binder As SetMemberBinder, ByVal value As Object) As Boolean properties(binder.Name) = value Return True End Function
To get the members from the dynamic object GetDynamicMemberNames() method is used.
C#
public override IEnumerable<string> GetDynamicMemberNames() { return properties.Keys; }
VB.NET
Public Overrides Function GetDynamicMemberNames() As IEnumerable(Of String) Return properties.Keys End Function
If these methods can’t be found, then it will not able to import the data into the worksheet which results in throwing an exception.
Step 5: Add the following code snippet to import the dynamic collection into the worksheet.
C#
//Instantiate the spreadsheet creation engine using (ExcelEngine excelEngine = new ExcelEngine()) { //Instantiate the excel application object. IApplication application = excelEngine.Excel; //The workbook is created IWorkbook workbook = application.Workbooks.Create(1); //The first worksheet object in the worksheets collection is accessed IWorksheet worksheet = workbook.Worksheets[0]; // The dynamic collection is imported worksheet.ImportData(GetMembersReport(), 1, 1, true); //Saving and closing the workbook Stream stream = File.Create("Output.xlsx"); worksheet.UsedRange.AutofitColumns(); workbook.SaveAs(stream); }
VB.NET
'Instantiate the spreadsheet creation engine Using excelEngine As ExcelEngine = New ExcelEngine() 'Instantiate the Excel application object Dim application As IApplication = excelEngine.Excel 'The workbook is created Dim workbook As IWorkbook = application.Workbooks.Create(1) 'The first worksheet object in the worksheets collection is accessed Dim worksheet As IWorksheet = workbook.Worksheets(0) ' The dynamic collection is imported worksheet.ImportData(GetMembersReport(), 1, 1, True) 'Saving and closing the workbook Dim stream As Stream = File.Create("Output.xlsx") worksheet.UsedRange.AutofitColumns() workbook.SaveAs(stream) End Using
Step 6: Add the dynamic collection objects in GetmembersReport() method as below.
C#
public static List<CustomDynamicObject> GetMembersReport() { List<CustomDynamicObject> reports = new List<CustomDynamicObject>(); dynamic dynamicObject = new CustomDynamicObject(); dynamicObject.Id = 01; dynamicObject.Name = "Andy Bernard"; dynamicObject.Age = 21; reports.Add(dynamicObject); dynamicObject = new CustomDynamicObject(); dynamicObject.Id = 02; dynamicObject.Name = "Jim Halpert"; dynamicObject.Age = 23; reports.Add(dynamicObject); dynamicObject = new CustomDynamicObject(); dynamicObject.Id = 03; dynamicObject.Name = "Karen Fillippe"; dynamicObject.Age = 20; reports.Add(dynamicObject); return reports; }
VB.NET
Public Function GetMembersReport() As List(Of CustomDynamicObject) Dim reports As List(Of CustomDynamicObject) = New List(Of CustomDynamicObject)() Dim dynamicObject As Object = New CustomDynamicObject() dynamicObject.Id = 1 dynamicObject.Name = "Andy Bernard" dynamicObject.Age = 21 reports.Add(dynamicObject) dynamicObject = New CustomDynamicObject() dynamicObject.Id = 2 dynamicObject.Name = "Jim Halpert" dynamicObject.Age = 23 reports.Add(dynamicObject) dynamicObject = New CustomDynamicObject() dynamicObject.Id = 3 dynamicObject.Name = "Karen Fillippe" dynamicObject.Age = 20 reports.Add(dynamicObject) Return reports End Function
A complete working example to import data from dynamic collection can be downloaded Import data from dynamic collection.zip.
By executing the program, you will get the output Excel file as below.
Output Excel file
Take a moment to peruse the documentation, where you can find basic worksheet data manipulation options along with features like Conditional Formatting, worksheet calculations through Formulas, adding Charts in worksheet or workbook, organizing and analyzing data through Tables and Pivot Tables, appending multiple records to worksheet using Template Markers, and most importantly PDF and Image conversions with code examples.
Refer here to explore the rich set of Syncfusion Excel (XlsIO) library features.
An online sample link to generate Excel file.
See Also:
Export DataTable to Excel in C#, VB.NET
How to use jump argument in template markers using XlsIO?
Create a pivot table in Excel workbook with data exported using template markers
How to use template marker with collection object?
How to implement nested template marker in XlsIO?
Starting with v16.2.0.x, if you reference Syncfusion assemblies from trial setup or from the NuGet feed, include a license key in your projects. Refer to the link to learn about generating and registering Syncfusion license key in your application to use the components without trial message.