How to import the ExpandoObject dynamic collection to Excel worksheet using C#?

Syncfusion Excel (XlsIO) library is a .NET Excel library used to create, read, and edit Excel documents. Also, converts Excel documents to PDF files. It allows to import data from various data sources such as array, list, data table and collection objects.

This article will show how to import the ExpandoObject dynamic collection to Excel worksheet using C#.

Include the following namespace in the Program.cs file.

using System.IO;
using Syncfusion.XlsIO;
using System.Dynamic;
using System.Collections.Generic;

Create a Custom Dynamic Object Class

A custom class CustomDynamicObject is created by extending DynamicObject. This enables dynamic properties to be added, retrieved, and listed at runtime using a dictionary.

TryGetMember: Implements getting property values dynamically.

TrySetMember: Implements setting property values dynamically.

GetDynamicMemberNames: Returns the dynamic property names.

public class CustomDynamicObject : DynamicObject
{
    internal Dictionary<string, object> properties = new Dictionary<string, object>();
    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;
    }
 
    public override bool TrySetMember(SetMemberBinder binder, object value)
    {
        properties[binder.Name] = value;
        return true;
    }
 
    public override IEnumerable<string> GetDynamicMemberNames()
    {
        return properties.Keys;
    }
}

Create Method to Add Dynamic Properties

The AddDynamicProperty method is used to add properties to an ExpandoObject. This method casts the ExpandoObject to a dictionary, allowing you to dynamically add properties.

public static void AddDynamicProperty(ExpandoObject dynamicObj, string propertyName, object propertyValue)
{
    var expandoDict = dynamicObj as IDictionary<string, object>;
    expandoDict[propertyName] = propertyValue;
}

Create a Dynamic Collection of Data

In the GetMembersReport() method, a list of ExpandoObject is created to represent the dynamic data collection. This method constructs three objects, each representing a person’s ID, name, and age.

public static List<ExpandoObject> GetMembersReport()
{
    List<ExpandoObject> report = new List<ExpandoObject>();
 
    ExpandoObject obj = new ExpandoObject();
    string propertyName = "Id";
    object propertyValue = 01;
    AddDynamicProperty(obj, propertyName,propertyValue);
    propertyName = "Name";
    propertyValue = "Karen Fillippe";
    AddDynamicProperty(obj, propertyName,propertyValue);
    propertyName = "Age";
    propertyValue = 23;
    AddDynamicProperty(obj, propertyName,propertyValue);
    report.Add(obj);
 
    obj = new ExpandoObject();
    propertyName = "Id";
    propertyValue = 02;
    AddDynamicProperty(obj, propertyName,propertyValue);
    propertyName = "Name";
    propertyValue = "Andy Bernard";
    AddDynamicProperty(obj, propertyName,propertyValue);
    propertyName = "Age";
    propertyValue = 20;
    AddDynamicProperty(obj, propertyName,propertyValue);
    report.Add(obj);
 
    obj = new ExpandoObject();
    propertyName = "Id";
    propertyValue = 03;
    AddDynamicProperty(obj, propertyName,propertyValue);
    propertyName = "Name";
    propertyValue = "Jim Halpert";
    AddDynamicProperty(obj, propertyName,propertyValue);
    propertyName = "Age";
    propertyValue = 21;
    AddDynamicProperty(obj, propertyName,propertyValue);
    report.Add(obj);
 
    return report;
}

Add the following code snippet to import the dynamic collection using ExpandoObject into the worksheet

using (ExcelEngine excelEngine = new ExcelEngine())
{
    //Instantiate the excel application object.
    IApplication application = excelEngine.Excel;
 
    //Create a workbook
    IWorkbook workbook = application.Workbooks.Create(1);
 
    //Access the first worksheet
    IWorksheet worksheet = workbook.Worksheets[0];
 
    //Import the dynamic collection
    worksheet.ImportData(GetMembersReport(), 1, 1, true);
 
    //Auto-fit the columns 
    worksheet.UsedRange.AutofitColumns();

    //Saving the workbook
    FileStream outputStream = new FileStream(Path.GetFullPath("Output/Output.xlsx"), FileMode.Create, FileAccess.Write);
    workbook.SaveAs(outputStream);

    //Dispose streams
    outputStream.Dispose();
}

You can get the complete sample for importing the ExpandoObject dynamic collection to Excel worksheet using C# from

I hope you enjoyed learning about how to import the ExpandoObject dynamic collection to Excel worksheet using C# in XlsIO.

You can refer to our XIsIO’s feature tour page to learn about its other groundbreaking features. Explore our UG documentation and online demos to understand how to manipulate data in Excel documents.

Take a moment to peruse the documentation where you can find basic Excel document processing options along with the features like import and export data, chart, formulas, conditional formatting, data validation, tables, pivot tables and, protect the Excel documents, and most importantly, the PDF, CSV and Image conversions with code examples.

If you are an existing user, you can access our latest components from the License and Downloads page. For new users, you can try our 30-day free trial to check out XlsIO and other Syncfusion components.

If you have any queries or require clarification, please let us know in the comments below or contact us through our support forums, Support Tickets, or feedback portal. We are always happy to assist you!