In the article "Unlocking Excel Data: How to Read Excel Files in a Web API using C#", We will dive into the step-by-step process of setting up the necessary dependencies, handling file uploads, and extracting data from Excel spreadsheets. By the end of this tutorial, you'll have a solid understanding of how to incorporate Excel file reading capabilities into your C# web API, opening up a world of possibilities for data analysis and integration.
So, let's get started and unlock the potential of reading Excel files in your web API using C#!
Importance and use cases of Reading Excel Files in a Web API
Reading Excel files in a web API brings several benefits and serves various important use cases. Here are some key reasons why it is important and how it can be valuable:
Data Integration: Excel files are widely used for data storage and organization. By reading Excel files in a web API, you can seamlessly integrate Excel data with other systems and applications. This allows for efficient data sharing, synchronization, and analysis.
Automation and Efficiency: Automating the process of reading Excel files through a web API eliminates the need for manual data entry and processing. It saves time, reduces errors, and increases overall operational efficiency.
Data Analysis and Reporting: Excel files often contain valuable data that needs to be analyzed and reported on. By reading Excel files in a web API, you can extract relevant data and utilize it for advanced analytics, generating insights, and creating customized reports.
Data Migration and Conversion: Reading Excel files in a web API enables you to migrate data from Excel spreadsheets to other systems or databases seamlessly. It facilitates the conversion of Excel data into a format compatible with other applications or storage solutions.
Real-Time Data Updates: By integrating Excel file reading in a web API, you can enable real-time data updates. This is particularly useful when multiple users need access to the latest information, ensuring data consistency and accuracy across different platforms.
Collaboration and Workflow Integration: Excel files are often shared among team members for collaborative work. Reading Excel files in a web API allows for easy sharing and integration of Excel data into collaborative platforms, workflow management systems, or enterprise applications.
Data Validation and Quality Control: Reading Excel files in a web API enables automated data validation and quality control processes. You can implement checks for data accuracy, completeness, and consistency, ensuring that the data meets predefined criteria or business rules.
Integration with External Systems: Excel files often serve as a bridge between different systems. By reading Excel files in a web API, you can integrate Excel data with external systems, such as CRM platforms, financial systems, or ERP solutions, enhancing data interoperability and system integration.
Compliance and Regulatory Requirements: Many industries have regulatory requirements for data management and reporting. Reading Excel files in a web API allows for implementing specific compliance measures, ensuring adherence to regulations, and facilitating auditing processes.
Customization and Personalization: Reading Excel files in a web API provides the flexibility to customize and personalize data processing and outputs based on specific business needs. It allows for tailored solutions that cater to unique requirements and enables seamless integration with existing workflows.
Requirements:
To accomplish this, we will:
Develop a WEB API that supports file upload functionality, allowing users to submit Excel files.
Implement the necessary logic to read the uploaded Excel file and extract its data.
Utilize Entity Framework to establish a connection with the database and insert the extracted records into a designated table.
Verify the functionality of our created WEB API by testing it using Postman, a popular API testing tool.
By fulfilling these requirements, we'll have a robust and efficient WEB API that enables users to upload Excel files, extract their data, and seamlessly store the information in a database table. So, let's proceed with this tutorial to accomplish these objectives step by step.
A Step-by-step guide to Read Excel Files in a Web API using C#
Here, we will be creating a WEB API that allows users to upload and read Excel files. Our goal is to seamlessly handle file uploads, extract data from the Excel file, and insert the records into a database table using Entity Framework.
Step 1 - Prepare Excel File
First, we need to create a table called "Students" to store student records. This table will have columns like Student_Id, Roll_No, Name, and Branch. We can create this table using SQL queries.
Create Student Table
CREATE TABLE [dbo].[Students](
[Student_Id] [int] IDENTITY(1,1) NOT NULL,
[Roll_No] [int] NULL,
[Name] [nvarchar](50) NULL,
[Branch] [nvarchar](50) NULL,
) Step 2: Creating the Web API
To read the student records from the Excel file and insert them into the student table using the Entity Framework, we need to create a WEB API. To do this, open Visual Studio, go to File > New > Project and select Web Application. Choose the WEB API template and click OK.
Step 3: Setting up the ADO.NET Entity Data Model
In the Solution Explorer, right-click on the Model folder and select Add > New Item. From the left panel, select Data and then choose ADO.NET Entity Data Model. Click Add, select EF Designer from the database, provide your SQL Server credentials, and choose the database. Select the desired table and click Finish to complete the setup.
Step 4: Installing the ExcelDataReader.DataSet Library:
To read data from the Excel file, we will use the ExcelDataReader.DataSet library, which provides an easy way to accomplish this task. In the Solution Explorer, right-click on the project name and select "Manage NuGet Packages." Search for the ExcelDataReader.DataSet library and install it.
By following these steps, we will be ready to proceed with reading the Excel file, extracting the data, and inserting it into the student table using the WEB API and the Entity Framework.
Step 5: Adding a controller for CRUD operations in Web API
Now, we need to add a new empty controller to our WEB API project. To do this, follow these steps:
Open the Solution Explorer in Visual Studio.
Right-click on the "Controllers" folder.
Select "Add" and then choose "Controller".
In the pop-up window, select "Web API 2 Controller - Empty" and click "Add".
After adding the controller, open the controller class and write the following APIs:
ReadExcelController.cs
After adding the controller, open the controller class and write the following APIs:
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
{
// 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;
// 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 upload has failed.";
}
else
message = "Selected file is empty.";
}
else
message = "Invalid File.";
}
else
ResponseMessage = Request.CreateResponse(HttpStatusCode.BadRequest);
}
return message;
}
catch (Exception)
{
throw;
}
}
}
}The above code defines a controller class called ReadExcelController within the Codingvila_ReadExcel_API.Controllers namespace. The controller inherits from ApiController and handles HTTP requests.
The controller has a single action method named ReadFile, which is decorated with the [HttpPost] and [Route("ReadFile")] attributes. This method reads data from an Excel file uploaded via the HTTP request.
Here's a simplified explanation of what the code does:
It imports necessary namespaces and references the required libraries.
The ReadFile method tries to read and process the uploaded Excel file.
It declares variables and objects needed for processing.
Within a using statement, it creates an instance of the database context (dbCodingvilaEntities).
It checks if there are files uploaded in the HTTP request.
If a file is found, it reads its contents using the appropriate reader based on the file format (.xls or .xlsx).
The data from the Excel file is stored in a DataSet object.
If the DataSet contains tables, the code iterates over the rows and creates Student objects with the data from each row.
The Student objects are added to the Students collection in the database context.
The changes are saved to the database using SaveChanges() method, and the output count is checked.
Based on the success or failure of the database operation, a relevant message is returned.
In case of any exceptions, they are rethrown to be handled at a higher level.
Step 6: Obtaining the Root URL
Now, we need a root URL to execute the action method of the above controller. To get the root URL, follow these steps:
Go to the property window of the project.
Select "URL".
Fetch the root URL from there.
You can find it on the screen below.
Finally, create a full URL to execute the ReadFile() action method of ReadExcelController.
URL: http://localhost:57542/Api/Excel/ReadFile
Step 7: Test Web API
Let's test the WEB API we created using Postman:
STEP a: Open Postman, a popular API testing tool.
STEP b: Select the HTTP method as POST for the "ReadFile" action.
STEP c: Enter the URL of the API.
STEP d: In the request body, select the "form-data" option.
STEP e: Add a parameter and set its type as "File".
STEP f: Click on the "Choose Files" button and select the Excel file you want to upload.
STEP g: Finally, click the "Send" button to initiate the request.
After sending the request, you will receive a successful response in Postman, indicating that the operation was completed successfully.
To verify the data insertion, you can check your database table. You will find that all the records from the Excel sheet have been successfully inserted into the table.
Conclusion
In this article, we have explored the process of reading Excel files in a web API using C#. By implementing the discussed techniques and best practices, you can unlock the potential of Excel data integration in your applications.