Articles in this section
Category / Section

Import data from dynamic collection to Excel worksheet in C#, VB.NET

4 mins read

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

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

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

 

Note:

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.

Excel file after importing data from dynamic collection using XlsIO

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?

Note:

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.

 

Did you find this information helpful?
Yes
No
Help us improve this page
Please provide feedback or comments
Comments (0)
Please sign in to leave a comment