How to Convert Excel Data Into DataTable?

To deal with Microsoft Excel, the.NET Excel framework is fast and feature-rich. This framework includes an Excel creation and editing library, which enables you to create, read, and edit Microsoft Excel files in any.NET application. It includes a Spreadsheet UI control inspired by Excel for creating, editing, viewing, and formatting Excel documents. Its robust conversion APIs make it simple to convert Excel files to PDF, pictures, and other formats. We shall convert Excel data to datable in this article.

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

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.

HostForLIFE is Best Option for ASP.NET Core 8 Hosting in Europe

Frankly speaking, HostForLIFE is best option to host your ASP.NET Core 8 Hosting in Europe. You just need to spend €2.97/month to host your site with them and you can install the latest ASP.NET Core 8 via their Plesk control panel. We would highly recommend them as your ASP.NET Core 8 Hosting in Europe.