Upload and Read Excel File in WEB API using C#

Codingvila
4
In this article, I will explain how to upload and read Excel files in excel in web API using c#. I will also explain how to insert the data from an excel file into the SQL server database table using the entity framework in web API. While you working with any application either that is a windows application or a web application, this is a common requirement, to read an excel file and insert the data into the database.

I got many emails from my readers to write an article on uploading and reading excel files in WEB API using c#. So, today in this article I will explain how to read the data from excel files in web API as well as a way to insert that data into the database table using the entity framework.

In this tutorial, we are using Visual Studio 2019 and explaining how to create a table in an SQL server database,  how to create an ADO.NET entity data model (EDM) as well as how to create a WEB API controller and a way to write an action method in the WEB API controller and a way to test the created WEB API using postman.

upload and read excel files in net core, read excel files in .net core API, read excel files in web API using c#,c# web API upload and read data from excel file without saving the file, read excel files using stream reader in c#, read excel file from for file c#, read excel file in c# .net core, import data from excel to SQL server using web API

Upload and Read Excel File in WEB API using C#

in my previous article, I explained how to create an Angular 11 CURD Application Using Web API With Material Design that you might like to read.

Requirement 

  1. Create a WEB API to upload and read the excel file.
  2. Read excel files and insert records into database tables using entity framework.
  3. Test created WEB API using postman.

Implementation

In this article we will create a WEB API to store basic details of students from an excel file to a database table, So, let's prepare a sample excel file with a few dummy records of students and then will start with a database and create a new table for store records of students using SQL Server.

Step 1

Prepare Excel File 

Excel

Create Table Student

CREATE TABLE [dbo].[Students](
	[Student_Id] [int] IDENTITY(1,1) NOT NULL,
	[RollNo] [int] NULL,
	[EnrollmentNo] [nvarchar](15) NULL,
	[Name] [nvarchar](50) NULL,
	[Branch] [nvarchar](50) NULL,
	[University] [nvarchar](50) NULL
	)

Step 2

Now, we have to create a WEB API to read the records of students from the excel file and insert them into the student table using an entity framework. To create web API, open visual studio 2019 >> file >> new >> project >> Select Web Application.

When you click the OK button, another window will appear on your screen for template selection where you have to select WEB API and click on the OK button.

Step 3

Now, you have to go to solution explorer and right-click on Model folder >> Add >> New Item >> select Data from the left panel >> Select ADO.NET Entity Data Model.

Now, click on the Add button and select the EF Designer from the database >> Next >> Gives your credential of SQL Server and select the database. Now, you have to click on the Add button and choose your table and click on the finish button.

Entity Data Model

If you are a beginner or need any help to add an entity data model then you can read this article where I explained how to create an ADO.NET entity data model (EDM) in asp.net step by step.

Step 4

To read the data from the excel file we have to install ExcelDataReader.DataSet library. There are many ways to read the excel file but this library provides the easiest way to read excel, so I will go with this library. To install ExcelDataReader.DataSet library go to solution explorer and right-click on the project name >> Select "Manage NuGet Packages" >> Search for ExcelDataReader.DataSet library >> Install.

Excel Data Reader

Step 5

Now, we will add a new empty controller to our WEB API project to create a WEB API to perform the CURD operation. For that, you have to go to solution explorer and right-click on the Controllers folder >> Add >> Controller >> Select Web API 2 Controller-Empty >> Click on Add.

Now, you have to open our controller class and write the following APIs.

ReadExcelController.cs

using Codingvila_ReadExcel_API.Models;
using ExcelDataReader;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web;
using System.Web.Http;
 
namespace Codingvila_ReadExcel_API.Controllers
{
    [RoutePrefix("Api/Excel")]
    public class ReadExcelController : ApiController
    {
        [Route("ReadFile")]
        [HttpPost]
        public string ReadFile()
        {
            try
            {
                #region Variable Declaration
                string message = "";
                HttpResponseMessage ResponseMessage = null;
                var httpRequest = HttpContext.Current.Request;
                DataSet dsexcelRecords = new DataSet();
                IExcelDataReader reader = null;
                HttpPostedFile Inputfile = null;
                Stream FileStream = null;
                #endregion
 
                #region Save Student Detail From Excel
                using (dbCodingvilaEntities objEntity = new dbCodingvilaEntities())
                {
                    if (httpRequest.Files.Count > 0)
                    {
                        Inputfile = httpRequest.Files[0];
                        FileStream = Inputfile.InputStream;
 
                        if (Inputfile != null && FileStream != null)
                        {
                            if (Inputfile.FileName.EndsWith(".xls"))
                                reader = ExcelReaderFactory.CreateBinaryReader(FileStream);
                            else if (Inputfile.FileName.EndsWith(".xlsx"))
                                reader = ExcelReaderFactory.CreateOpenXmlReader(FileStream);
                            else
                                message = "The file format is not supported.";
 
                            dsexcelRecords = reader.AsDataSet();
                            reader.Close();
 
                            if (dsexcelRecords != null && dsexcelRecords.Tables.Count > 0)
                            {
                                DataTable dtStudentRecords = dsexcelRecords.Tables[0];
                                for (int i = 0; i < dtStudentRecords.Rows.Count; i++)
                                {
                                    Student objStudent = new Student();
                                    objStudent.RollNo = Convert.ToInt32(dtStudentRecords.Rows[i][0]);
                                    objStudent.EnrollmentNo = Convert.ToString(dtStudentRecords.Rows[i][1]);
                                    objStudent.Name = Convert.ToString(dtStudentRecords.Rows[i][2]);
                                    objStudent.Branch = Convert.ToString(dtStudentRecords.Rows[i][3]);
                                    objStudent.University = Convert.ToString(dtStudentRecords.Rows[i][4]);
                                    objEntity.Students.Add(objStudent);
                                }
 
                                int output = objEntity.SaveChanges();
                                if (output > 0)
                                    message = "The Excel file has been successfully uploaded.";
                                else
                                    message = "Something Went Wrong!, The Excel file uploaded has fiald.";
                            }
                            else
                                message = "Selected file is empty.";
                        }
                        else
                            message = "Invalid File.";
                    }
                    else
                        ResponseMessage = Request.CreateResponse(HttpStatusCode.BadRequest);
                }
                return message;
                #endregion
            }
            catch (Exception)
            {
                throw;
            }
        }
    }
}

Step 6

Now, we need a root URL to execute the action method of the above controller, So go to the project's property window, select URL, and fetch the root URL from there as shown in the screen below.

Property

Finally, create a full URL to execute the action method ReadFile() of ReadExcelController, with a combination of the main domain, specified route prefixes, and specified route URL of the ReadFile() action method shown below.

URL: http://localhost:57542/Api/Excel/ReadFile

Step 7

Now, we will test the created WEB API using postman.

Open postman

Now, you have to select the method as POST for action ReadFile, then enter the URL of API, Then Select the Body, and in that select the form-data option, Finally select parameter type as File as shown in the screen below.

Postman WEB API

Now, select the excel file and click on the send button, and you can see we get a successful response as shown on the screen below.

Postman

Let's check our database table, and you can see all the records of the excel sheet are inserted in the table successfully.

SELECT [Student_Id]
      ,[RollNo]
      ,[EnrollmentNo]
      ,[Name]
      ,[Branch]
      ,[University]
  FROM [dbCodingvila].[dbo].[Students]
Output

Summary

In this article, we learned how to create a WEB API to read the Excel file and insert the records into the database.


You can contact at info.codingvila@gmail.com, if you want source code or need any live support.

Price for Source Code: $10

Price for Live Support: $12 / hour


Post a Comment

4 Comments

Thank you for your valuable time, to read this article, If you like this article, please share this article and post your valuable comments.

Once, you post your comment, we will review your posted comment and publish it. It may take a time around 24 business working hours.

Sometimes I not able to give detailed level explanation for your questions or comments, if you want detailed explanation, your can mansion your contact email id along with your question or you can do select given checkbox "Notify me" the time of write comment. So we can drop mail to you.

If you have any questions regarding this article/blog you can contact us on info.codingvila@gmail.com

  1. Hi, great example. Could you please share code for this?

    ReplyDelete
  2. I implemented this today. Its a 100% genuine and authentic code by Nikunj. Wonderful article and very helpful in real life usecase. Nikunj is a very helpful person, he replied to me instantly on LinkedIn when I reached out with certain errors I got. Very few people who help an unknown! Kudos to you, you have much knowledge!

    ReplyDelete
  3. Thanks Bro
    I have Question How Can Consume THIS Web Api from web page not from postman

    ReplyDelete
  4. Hi Could you please share the scource code of this article.

    ReplyDelete
Join the conversation (4)
To Top