How to import JSON data to predefined Excel templates 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.

This article explains how to import JSON data to predefined Excel templates using C# and VB.NET.

Include the following namespace in the Program.cs file.

C#

using Syncfusion.XlsIO;
using System.Dynamic;
using Newtonsoft.Json;

VB.NET

Imports Syncfusion.XlsIO
Imports System.Dynamic
Imports Newtonsoft.Json

Use the following code snippet for using template markers with JSON data.

C#

public static void TemplateMarker()
{
    using (ExcelEngine excelEngine = new ExcelEngine())
    {
        IApplication application = excelEngine.Excel;
        application.DefaultVersion = ExcelVersion.Xlsx;
        FileStream inputStream = new FileStream(Path.GetFullPath(@"Data/Input.xlsx"), FileMode.Open, FileAccess.Read);
        IWorkbook workbook = application.Workbooks.Open(inputStream);
        IWorksheet worksheet = workbook.Worksheets[0];

        //Create template marker processor
        ITemplateMarkersProcessor marker = workbook.CreateTemplateMarkersProcessor();

        //Add collection to marker variable
        marker.AddVariable("Employee", GetJsondata());

        //Process the markers in the template
        marker.ApplyMarkers();

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

        //Dispose streams
        outputStream.Dispose();
        inputStream.Dispose();
    }
}

public static List<object> GetJsondata()
{
    string jsonString = System.IO.File.ReadAllText((@"Data/Json Data.json"));
    dynamic obj = JsonConvert.DeserializeObject<ExpandoObject>(jsonString);

    //Extract the employee array
    var employeeList = (List<object>)obj.Employee;

    return employeeList;
}

VB.NET

Public Sub TemplateMarker()
    Using excelEngine As New ExcelEngine()
        Dim application As IApplication = excelEngine.Excel
        application.DefaultVersion = ExcelVersion.Xlsx

        Dim workbook As IWorkbook = excelEngine.Excel.Workbooks.Open("Data/Input.xlsx")
        Dim worksheet As IWorksheet = workbook.Worksheets(0)

        'Create template marker processor
        Dim marker As ITemplateMarkersProcessor = workbook.CreateTemplateMarkersProcessor()

        'Add collection to marker variable
        marker.AddVariable("Employee", GetJsonData())

        'Process the markers in the template
        marker.ApplyMarkers()

        'Saving the workbook 
        workbook.SaveAs("Output.xlsx")
    End Using
End Sub

Public Function GetJsonData() As List(Of Object)
    Dim jsonString As String = File.ReadAllText("Data/Json Data.json")
    Dim obj As Object = JsonConvert.DeserializeObject(Of ExpandoObject)(jsonString)

    'Extract the employee array
    Dim employeeList As List(Of Object) = CType(obj.Employee, List(Of Object))

    Return employeeList
End Function  

You can get the complete sample for using template markers with JSON data from

Conclusion

I hope you enjoyed learning about how to import JSON data to predefined Excel templates in C#, VB.NET.

You can refer to our XIsIO’s feature tour page to learn about its other groundbreaking features. Explore our .NET Excel documentation and .NET Excel 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!