Live Chat Icon For mobile
Live Chat Icon
Popular Categories.NET  (149).NET Core  (24)Angular  (42)ASP.NET  (48)ASP.NET Core  (56)ASP.NET MVC  (84)Azure  (28)Blazor  (69)DocIO  (18)Essential JS 2  (67)Essential Studio  (161)Flutter  (81)JavaScript  (140)Microsoft  (101)PDF  (54)React  (31)Succinctly series  (120)Syncfusion  (457)TypeScript  (30)Uno Platform  (2)UWP  (4)Vue  (26)Webinar  (17)Windows Forms  (54)WPF  (106)Xamarin  (117)XlsIO  (22)Other CategoriesBarcode  (4)BI  (29)Bold BI  (3)Build conference  (6)Business intelligence  (53)Button  (4)C#  (104)Chart  (43)Cloud  (9)Company  (445)Dashboard  (6)Data Science  (3)Data Validation  (3)DataGrid  (36)Development  (244)Doc  (7)DockingManager  (1)eBook  (91)Enterprise  (22)Entity Framework  (5)Essential Tools  (14)Excel  (16)Extensions  (9)File Manager  (3)Gantt  (7)Gauge  (5)Git  (3)Grid  (25)HTML  (9)Installer  (2)Knockout  (2)LINQPad  (1)Linux  (1)M-Commerce  (1)Metro Studio  (11)Mobile  (182)Mobile MVC  (9)OLAP server  (1)Open source  (1)Orubase  (12)Partners  (21)PDF viewer  (19)Performance  (2)PHP  (1)PivotGrid  (4)Predictive Analytics  (6)Report Server  (3)Reporting  (10)Reporting / Back Office  (11)Rich Text Editor  (5)Road Map  (9)Scheduler  (19)SfDataGrid  (8)Silverlight  (21)Sneak Peek  (14)Solution Services  (2)Spreadsheet  (4)SQL  (5)Stock Chart  (1)Surface  (4)Tablets  (5)Theme  (9)Tips and Tricks  (41)UI  (102)Uncategorized  (68)Unix  (2)User interface  (66)Visual State Manager  (1)Visual Studio  (16)Visual Studio Code  (8)Web  (157)What's new  (101)Windows 8  (19)Windows App  (1)Windows Phone  (15)Windows Phone 7  (9)WinRT  (26)
Share on twitter
Share on facebook
Share on linkedin
4 Easy Steps to Export Excel Files to JSON Using C#

4 Easy Steps to Export Excel Files to JSON Using C#

Syncfusion Excel (XlsIO) Library is a .NET Excel library that allows users to convert Excel documents to various file formats such as PDF, image, HTML, ODS, and JSON. Among them, the Excel-to-JSON conversion is supported from the 18.3 version onward. The resultant JSON files can be bound to any control in any platform. This is one of the major advantages for users.

In this blog, we are going to see how an Excel file can be converted to a JSON stream in C# using the Syncfusion XlsIO library. We’ll also look at the procedure to bind the JSON stream to a Syncfusion DataGrid control. The following options are available in the Excel to JSON conversion:

  • Excel workbook to a JSON file.
  • Excel worksheet to a JSON file.
  • Excel worksheet range to a JSON file.

The following screenshot shows the input template to be converted into a JSON stream.

Input template document
Input template document

Steps to export Excel data to JSON format

Let’s see how to convert an Excel file to a JSON stream and display the JSON stream in a data grid in an ASP.NET Core web application.

Step 1: Create an ASP.NET Core project.

Step 2: Install the following NuGet packages as references to your .NET Core applications from NuGet.org:

Step 3: Add the following code example in the index.cshtml file to design the UI screen.

@{
    ViewData["Title"] = "Home Page";
}

<div class="Common">
    @{ Html.BeginForm("Index", "Home", FormMethod.Post);

    <div class="tablediv">
        <div class="rowdiv">
            <p style="font-weight:normal">
                Click the "Input Template" button to view the input Excel document. Please note that the Microsoft Excel viewer or Microsoft Excel is required to view the Excel document.
            </p>
            <p style="font-weight:normal">
                Click the "Convert to JSON" button to view the JSON data.
            </p>
        </div>
        <br />
    </div>
    <div class="rowdiv">
        <div class="celldiv">
            <label style="font-weight:normal">
                Convert
            </label>
               
            <select name="ConvertOptions" id="convertOption" style="width:120px;">
                <option>Workbook</option>
                <option>Worksheet</option>
                <option>Range</option>
            </select>
        </div>
        <br />
    </div>
    <div>        
        <ejs-tab id="ej2Tab">
            <e-tab-tabitems>
                <e-tab-tabitem >
                    <e-content-template>
                        <div>
                            <div class="e-tab-header">
                                <div>Sheet1</div>
                                <div>Sheet2</div>
                            </div>
                            <div class="e-content">
                                <div>
                                    <ejs-grid id="Sheet1" dataSource="@ViewBag.Tab1" height="250" width="900">
                                        <e-grid-columns>
                                            <e-grid-column field="Category" headerText="Category" width="120"></e-grid-column>
                                            <e-grid-column field="Category Detail" headerText="Category Detail" textAlign="Left" width="150"></e-grid-column>
                                            <e-grid-column field="Title(s)" headerText="Title(s)" width="150"></e-grid-column>
                                            <e-grid-column field="Firm Name" headerText="Firm Name" textAlign="Left" width="150"></e-grid-column>
                                            <e-grid-column field="Client" headerText="Client" textAlign="Left" width="100"></e-grid-column>
                                            <e-grid-column field="Award" headerText="Award" width="100"></e-grid-column>
                                        </e-grid-columns>
                                    </ejs-grid>
                                </div>
                                <div>
                                    <div>
                                        <ejs-grid id="Sheet2" dataSource="@ViewBag.Tab2" height="250" width="900">

                                            <e-grid-columns>
                                                <e-grid-column field="Date" headerText="Date" textAlign="Left" width="120"></e-grid-column>
                                                <e-grid-column field="Region" headerText="Region" textAlign="Left" width="150"></e-grid-column>
                                                <e-grid-column field="Employee" headerText="Employee" width="150"></e-grid-column>
                                                <e-grid-column field="Item" headerText="Item" textAlign="Left" width="150"></e-grid-column>
                                                <e-grid-column field="Units" headerText="Units" textAlign="Left" width="100"></e-grid-column>
                                                <e-grid-column field="Unit Cost" headerText="Unit Cost" width="100"></e-grid-column>
                                                <e-grid-column field="Total" headerText="Total" textAlign="Left" width="150"></e-grid-column>
                                            </e-grid-columns>
                                        </ejs-grid>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </e-content-template>
                </e-tab-tabitem>
            </e-tab-tabitems>
        </ejs-tab>
    </div>
    <br/>
    <div class="rowdiv">
        <div class="celldiv">
            <input id="inputTemplate" class="e-btn" type="submit" name="button" value="Input Template" style="width:150px;" />
               
            <input id="convertToJSON" class="e-btn" type="submit" name="button" value="Convert to JSON" style="width:150px;" />
        </div>
    </div>
    }
</div>

<script>
    var GridData =@Html.Raw( @Json.Serialize(ViewBag.data));
function load() {
    var grid = document.getElementById('Grid').ej2_instances[0];
    grid.dataSource = GridData;
}
</script>

That code example contains the following components:

  • ComboBox : To choose to convert the entire workbook to a JSON file, or a particular worksheet to a JSON file, or a specific range of worksheets to a JSON file.
    Combo Box
  • DataGrid: To display the JSON data that is converted from the Excel file.

    JSON data bound to DataGrid
    JSON data bound to DataGrid
  • Input Template button: To download the input Excel file.
  • Convert to JSON button: To convert the input Excel file to a JSON file.

This code will create a webpage as shown in the following image.

The UI design of the example
The UI design of the example

Step 4: Click Convert to JSON. The input Excel document will be converted to a JSON stream according to the option selected (workbook, worksheet, or range) in the combo box. Then, the converted JSON data will be bound to the DataGrid created on the webpage.

The following is the code written in the HomeController.cs class.

using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Mvc;
using Newtonsoft.Json.Linq;
using Syncfusion.EJ2.Navigations;
using Syncfusion.XlsIO;
using System.Collections.Generic;
using System.Dynamic;
using System.IO;
using System.Text;

namespace Excel_JSON_Grid.Controllers
{
    public class HomeController : Controller
    {
        IHostingEnvironment _env;
        public HomeController(IHostingEnvironment env)
        {
            _env = env;
        }
        public IActionResult Index(string button, string ConvertOptions)
        {
            ViewBag.Sheet1 = new TabHeader { Text = "Sheet1" };
            ViewBag.Sheet2 = new TabHeader { Text = "Sheet2" };
            if (button == null)
                return View();
            else if (button == "Input Template")
            {
                //Instantiate the spreadsheet creation engine.
                ExcelEngine excelEngine = new ExcelEngine();

                //Instantiate the Excel application object.
                IApplication application = excelEngine.Excel;
                application.DefaultVersion = ExcelVersion.Xlsx;

                //Load the input Excel file.
                FileStream stream = new FileStream(_env.WebRootPath + "\\ExcelToJSON.xlsx", FileMode.Open, FileAccess.ReadWrite);
                IWorkbook workbook = application.Workbooks.Open(stream);
                stream.Close();

                //Save the input Excel file to a stream.
                MemoryStream ms = new MemoryStream();
                workbook.SaveAs(ms);
                ms.Position = 0;

                excelEngine.Dispose();

                string contentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                string fileName = "Sample.xlsx";

                //Return the input Excel file stream.
                return File(ms, contentType, fileName);
            }
            else
            {
                //Instantiate the spreadsheet creation engine.
                ExcelEngine excelEngine = new ExcelEngine();

                //Instantiate the Excel application object.
                IApplication application = excelEngine.Excel;
                application.DefaultVersion = ExcelVersion.Xlsx;

                //Load the input Excel file.
                FileStream stream = new FileStream(_env.WebRootPath + "\\ExcelToJSON.xlsx", FileMode.Open, FileAccess.ReadWrite);
                IWorkbook book = application.Workbooks.Open(stream);
                stream.Close();

                //Access first worksheet.
                IWorksheet worksheet = book.Worksheets[0];
                
                //Access a range.
                IRange range = worksheet.Range["A1:H10"];

                MemoryStream jsonStream = new MemoryStream();

                if (ConvertOptions == "Workbook")
                    book.SaveAsJson(jsonStream); //Save the entire workbook as a JSON stream.
                else if (ConvertOptions == "Worksheet")
                    book.SaveAsJson(jsonStream, worksheet); //Save the first worksheet as a JSON stream.
                else if (ConvertOptions == "Range")
                    book.SaveAsJson(jsonStream, range); //Save the range as JSON stream.

                excelEngine.Dispose();

                byte[] json = new byte[jsonStream.Length];

                //Read the JSON stream and convert to a JSON object.
                jsonStream.Position = 0;
                jsonStream.Read(json, 0, (int)jsonStream.Length);
                string jsonString = Encoding.UTF8.GetString(json);
                JObject jsonObject = JObject.Parse(jsonString);

                //Bind the converted JSON object to the DataGrid.
                if (ConvertOptions == "Workbook")
                {
                    //The first worksheet in the input document is converted to a JSON stream and bound to the DataGrid in the first tab.
                    ViewBag.Tab1 = ((JArray)(jsonObject["Sheet1"])).ToObject<List<CustomDynamicObject>>();

                    //The second worksheet in the input document is converted to a JSON stream and bound to the DataGrid in the second tab.
                    ViewBag.Tab2 = ((JArray)(jsonObject["Sheet2"])).ToObject<List<CustomDynamicObject>>();

                    return View();
                }
                else if (ConvertOptions == "Worksheet" || ConvertOptions == "Range")
                {
                    ViewBag.Tab1 = ((JArray)(jsonObject["Sheet1"])).ToObject<List<CustomDynamicObject>>();
                    ViewBag.Tab2.Visible = false;
                }

                jsonStream.Position = 0;

                return View();
            }
        }
    }

    #region Helper Classes
    /// <summary>
    /// Custom dynamic object class.
    /// </summary>
    public class CustomDynamicObject : DynamicObject
    {
        /// <summary>
        /// The dictionary property used to store the data.
        /// </summary>
        internal Dictionary<string, object> properties = new Dictionary<string, object>();
        /// <summary>
        /// Provides the implementation for operations that get member values.
        /// </summary>
        /// <param name="binder">Get Member Binder object</param>
        /// <param name="result">The result of the get operation.</param>
        /// <returns>true if the operation is successful; otherwise, false.</returns>
        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;
        }
        /// <summary>
        /// Provides the implementation for operations that set member values.
        /// </summary>
        /// <param name="binder">Set memeber binder object</param>
        /// <param name="value">The value to set to the member</param>
        /// <returns>true if the operation is successful; otherwise, false.</returns>
        public override bool TrySetMember(SetMemberBinder binder, object value)
        {
            properties[binder.Name] = value;
            return true;
        }
        /// <summary>
        /// Return all dynamic member names.
        /// </summary>
        /// <returns>the property name list</returns>
        public override IEnumerable<string> GetDynamicMemberNames()
        {
            return properties.Keys;
        }
    }
    #endregion
}

After executing this code example, we will get output like in the following screenshot. The entire Excel workbook is converted into a JSON stream, the data is displayed in the DataGrid, and each worksheet is shown in separate tabs.

Excel document converted to JSON stream and bound to the DataGrid
Excel document converted to JSON stream and bound to the DataGrid

GitHub samples

You can download the complete source code of this example from this GitHub link.

Wrapping up

As you can see, Syncfusion Excel (XlsIO) Library provides support to convert Excel documents to JSON in C#. You can do this easily by following the steps given in this blog. Take a moment to peruse our documentation, where you’ll find info on other conversions, like Excel-to-PDFExcel-to-HTML, worksheet-to-imageExcel-to-CSV, and Excel-to-ODS, all with accompanying code samples.

Using the XlsIO library, you can also export Excel data to data tables, TSV, collections of objects, and many more file formats.

If you are new to our Excel Library, we highly recommend you follow our Getting Started guide.

Are you already a Syncfusion user? You can download the product setup here. If you’re not yet a Syncfusion user, you can download a free, 30-day trial here.

If you have any questions about these features, please let us know in the comments section below. You can also contact us through our support forumDirect-Trac, or feedback portal. We are always happy to assist you!

Tags:

Share this post:

Share on twitter
Share on facebook
Share on linkedin

Leave a comment

Popular Now

Be the first to get updates

Subscribe RSS feed
Scroll To Top