Convert Excel Data Into DataTable

Introduction

.NET Excel framework is high-performance and feature-rich to work with Microsoft Excel. This framework has an Excel creation and editing library that allows you to create, read, and edit Microsoft Excel files in any .NET application. It offers an Excel-inspired Spreadsheet UI control to create, edit, view, and format Excel documents. Its powerful conversion APIs make it easy to convert Excel files to PDF, images, and more.

In this article, we will convert Excel data into datable.

Topics Covered

This article demonstrates how to build the following,

1. Create a sample ASP.NET Core Web API.
2. Add a controller and respective functionality.
3. Testing.

Pre-requisites

1. Download and install Visual Studio 2022.

Tools

1. Visual Studio 2022

Task 1. Create a sample ASP.NET Core Web API

In this task, you will see how to create a new sample .NET ASP.NET Core Web API using Visual Studio 2022 and the respective code of conversion from the Excel sheet to the data table.

Step 1. Open Visual Studio 2022 and click Create a new project.

Step 2. Search ASP.NET in the search bar, select ASP.NET Core Web API project template, and click Next.

Step 3. Enter the project name as ConvertExcelToDataTable Next.

Step 4. Select .NET Core as Framework. Click Create.

Step 5. Expand the Controller folder in the solution explorer, right-click WeatherForecastController.cs file, and click Delete. Right-click WeatherForecast.cs file and click Delete.

Step 6. Right-click the Controllers folder, click Add, and then click Controller.

Step 7. Select API -> API Controller with read/write actions. Click Add.

Step 8. Leave the default name and click Add.

Task 2. Add Code in the ConvertController project

Step 1. Create a folder ‘’Model’ and add a class in it with the name of FileModel.cs file and replace the code with the following as shown below.

using Microsoft.AspNetCore.Http;
using Newtonsoft.Json;

namespace ConvertExcelToDataTable.Model

{
    public class FileModel
    {
        [JsonProperty(PropertyName = "IFormFile")]

        public IFormFile FromFile { get; set; }
    }
}

Install Nuget Package Newtonsoft.json (13.0.3)

Step 2. Create a folder ‘’Classes’ and add a class in it with the name of  ConvertCSVtoDataTable.cs file and replace the code with the following as shown below.

using ConvertExcelToDataTable.Model;
using System;
using System.Data;
using System.IO;
using System.Threading.Tasks;

namespace ConvertExcelToDataTable.Classes
{
    public class ConvertCSVtoDataTable
    {
        public async Task<DataTable> ConvertData(FileModel fileModel)
        {
            DataTable dt = new DataTable();

            try
            {
                string strFilePath = Path.Combine(Directory.GetCurrentDirectory(), "UploadedFiles", fileModel.FromFile.FileName);

                // Create file in local path to read

                using (Stream stream = new FileStream(strFilePath, FileMode.Create))
                {
                    fileModel.FromFile.CopyTo(stream);
                }

                using (StreamReader sr = new StreamReader(strFilePath))
                {
                    string[] headers = sr.ReadLine().Split(',');

                    foreach (string header in headers)

                    {
                        dt.Columns.Add(header);
                        dt.Columns[header].DataType = typeof(String);
                    }

                    if (!sr.EndOfStream)
                    {
                        while (!sr.EndOfStream)
                        {
                            string[] rows = sr.ReadLine().Split(',');
                            DataRow dr = dt.NewRow();
                            for (int i = 0; i < headers.Length; i++)
                            {
                                dr[i] = rows[i];
                            }
                            dt.Rows.Add(dr);
                        }
                    }

                    return await Task.FromResult(dt);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}

Step 3. Open controller “ConvertController” and replace the code with the following one.

using ConvertExcelToDataTable.Classes;
using ConvertExcelToDataTable.Model;
using Microsoft.AspNetCore.Mvc;
using System.Threading.Tasks;

namespace ConvertExcelToDataTable.Controllers
{
    public class ConvertController : Controller
    {
        [HttpPost]
        [Route("api/FileUpload")]
        public async Task<JsonResult> Index(FileModel fileModel)
        {
            var obj = new ConvertCSVtoDataTable();
            var response = await obj.ConvertData(fileModel);
            return new JsonResult(fileModel.FromFile.FileName);
        }
    }
}

Step 4. Open StartUp.js for swagger configuration and replace the code with the following one.

public void ConfigureServices(IServiceCollection services)
        {
            services.AddControllers();
            services.AddSwaggerGen();
            services.AddSwaggerGen(c =>
            {
                c.SwaggerDoc("v1", new OpenApiInfo
                {
                    Version = "v1",
                    Title = "ConvertExToDt",
                    Description = "ConvertExToDt",
                });
            });
        }

        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.

        public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
        {
            app.UseSwagger();
            app.UseSwaggerUI(c =>
            {
                c.SwaggerEndpoint("/swagger/v1/swagger.json", "WHS");
            });

            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
            }
            app.UseHttpsRedirection();
            app.UseRouting();
            app.UseAuthorization();
            app.UseEndpoints(endpoints =>
            {
                endpoints.MapControllers();

            });

        }

• Install Nuget Package Swashbuckle.AspNetCore.Swagger (6.5.0)
• Install Nuget Package Swashbuckle.AspNetCore.SwaggerUI (6.5.0)

Task 3. Testing

In this task, you will see how to test the API to generate the logs in the respective file.

Hit F5 to run the API locally, and Swagger will be displayed. Expand /api/values and click Try it out. Click Execute to get the response as shown below.

Click on Choose File button and select an Excel File.

The code will execute, and it will show you the expected result as below.

In the swagger response, you can see the Excel file name.

Note. If you want to ass additional validation and conditions, then please use the data table response and use the loop conditions. Create a folder in your root project path with the name of UploadedFiles.

Task 4. Clean up resources

Once execution will be done, remove the file from the local folder.