Import Excel File In Angular 7 Using Web API And SQL Server

Let us learn the process of importing the Excel file in an Angular 7 Web Application using Web API with a back-end of the SQL Server database. And after uploading the Excel file, it will display in the UI. A Web API is used to provide data connectivity between the database and the front-end application.

I'm using Visual Studio Code as a tool to build my application. If you don't have Visual Studio Code, you have to download and install it first. Here is the Visual Studio Code download link: Download Visual Studio Code Editor.

You can read my previous articles related to Angular 7 from the following links.

Step 1. Create a database table

Create a database. Open SQL Server and create a new database table. As you can see from the following image, I have created a database table called User Details with 7 columns.

Import Excel File In Angular 7 Using Web API And SQL Server

Note
You can choose the size of the column according to your requirement.

Note
If you already have an existing database and table, you can skip this step.

Step 2. Create a Web API Project

Now, we will create a Web API with the functionality of binding records from database. Go to Visual Studio >> File >> New >> Project, and select Web Application. After that, click OK and you will see the templates. Select Web API template.

Click OK.

Step 3. Add ADO.NET Entity Data Model

Now, select the Models folder and right-click. Then, go to Add >> New Item >> select Data in left panel >>ADO.NET Entity Data Model.

Now, click the Add button and select EF Designer from the database, click Next. After that, give your SQL credentials and select the database where your database table and data are located.

Click the "Add" button and select your table and click on the "Finish" button.

Step 4. Create Web API Controller

Now, we will write the code to perform import and binding operations.

Go to the Controller folder in your API application and right-click >> Add >> Controller.

Select Web API 2 Controller-Empty.

Now, we will go to the controller class but before we write any logic, we will install ExcelDataReader.DataSet although many ways to import Excel file in MVC but I am going to use an easy way to import excel file. So, now right-click on the project and select "Manage NuGet Packages" and search for ExcelDataReader.DataSet. Then, install this.

Step 5. Write the logic for import and retrieve records

Go to the Controller class and set the routing to make it more user-friendly by writing the below code.

using ExcelDataReader;
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;
using ExcelUploadAPI.Models;
namespace ExcelUploadAPI.Controllers
{
[RoutePrefix("Api/Excel")]
public class ExcelExampleController : ApiController
{
[Route("UploadExcel")]
[HttpPost]
public string ExcelUpload()
{
string message = "";
HttpResponseMessage result = null;
var httpRequest = HttpContext.Current.Request;
using (AngularDBEntities objEntity = new AngularDBEntities())
{
if (httpRequest.Files.Count > 0)
{
HttpPostedFile file = httpRequest.Files[0];
Stream stream = file.InputStream;
IExcelDataReader reader = null;
if (file.FileName.EndsWith(".xls"))
{
reader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else if (file.FileName.EndsWith(".xlsx"))
{
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
else
{
message = "This file format is not supported";
}
DataSet excelRecords = reader.AsDataSet();
reader.Close();
var finalRecords = excelRecords.Tables[0];
for (int i = 0; i < finalRecords.Rows.Count; i++)
{
UserDetail objUser = new UserDetail();
objUser.UserName = finalRecords.Rows[i][0].ToString();
objUser.EmailId = finalRecords.Rows[i][1].ToString();
objUser.Gender = finalRecords.Rows[i][2].ToString();
objUser.Address = finalRecords.Rows[i][3].ToString();
objUser.MobileNo = finalRecords.Rows[i][4].ToString();
objUser.PinCode = finalRecords.Rows[i][5].ToString();
objEntity.UserDetails.Add(objUser);
}
int output = objEntity.SaveChanges();
if (output > 0)
{
message = "Excel file has been successfully uploaded";
}
else
{
message = "Excel file uploaded has fiald";
}
}
else
{
result = Request.CreateResponse(HttpStatusCode.BadRequest);
}
}
return message;
}
[Route("UserDetails")]
[HttpGet]
public List<UserDetail> BindUser()
{
List<UserDetail> lstUser = new List<UserDetail>();
using (AngularDBEntities objEntity = new AngularDBEntities())
{
lstUser = objEntity.UserDetails.ToList();
}
return lstUser;
}
}
}

Step 6. Create Angular application for building the UI Application

Now, let us create the web application in Angular 7 that will consume the Web API.

First, we have to make sure that we have Angular CLI installed.

Open the command prompt and type the below code and press ENTER.

npm install -g @angular/cli

Now, open the Visual Studio Code and create a project.

Open TERMINAL in Visual Studio Code and type the following syntax to create a new project. Let us name it ExcelUploading.

ng new ExcelUploading

After that, hit ENTER. It will take a while to create the project.

Once created, the project should look like this.

Now, we will create components to provide UI.

I'm going to create a new component, excelimport.

Go to the TERMINAL and go our angular project location using the following command,

cd projectName

Now, write the following command that will create a component.

ng g c excelimport

Press ENTER.

Now, we create a model class.

Open TERMINAL and write the below command.

ng g class model/User --spec=false

Then, create a service.

ng g s service/user --spec=false

Open the Index.html file and set the bootstrap library.

<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.0.0/css/bootstrap.min.css" rel="stylesheet">

Import Excel File In Angular 7 Using Web API And SQL Server

Step 7. Add library in app.module

Now, open the app.module.ts class and write the below code.

import { BrowserModule } from '@angular/platform-browser';
import { NgModule } from '@angular/core';
import { HttpClientModule, HttpClient } from '@angular/common/http';
import { AppRoutingModule } from './app-routing.module';
import { AppComponent } from './app.component';
import { ExcelimportComponent } from './excelimport/excelimport.component';
@NgModule({
declarations: [
AppComponent,
ExcelimportComponent
],
imports: [
BrowserModule,
HttpClientModule,
AppRoutingModule
],
providers: [],
bootstrap: [AppComponent]
})
export class AppModule { }

Step 8. Write typescript code in component and service

Now, first, write all properties of the User class related to user details that match with the database.

export class User {
UserId: string;
UserName: string;
EmailId: string;
Gender: string;
Address: string;
MobileNo: string;
PinCode: string;
}

open user.service.ts and first import necessary class and libraries and then make calls to the WebAPI methods.

import { Injectable } from '@angular/core';
import { HttpHeaders } from '@angular/common/http';
import { HttpClient } from '@angular/common/http'
import { User } from '../model/user';
import { Observable } from 'rxjs';
@Injectable({
providedIn: 'root'
})
export class UserService {
constructor(private http: HttpClient) { }
url = 'http://localhost:63376/Api/Excel';
UploadExcel(formData: FormData) {
let headers = new HttpHeaders();
headers.append('Content-Type', 'multipart/form-data');
headers.append('Accept', 'application/json');
const httpOptions = { headers: headers };
return this.http.post(this.url + '/UploadExcel', formData, httpOptions)
}
BindUser(): Observable<User[]> {
return this.http.get<User[]>(this.url + '/UserDetails');
}
}

Open the excelimport.component.ts and write the below code.

import { Component, OnInit, ViewChild } from '@angular/core';
import { HttpClient, HttpHeaders } from '@angular/common/http';
import { Observable } from 'rxjs';
import { UserService } from '../service/user.service';
import { User } from '../model/user';
@Component({
selector: 'app-excelimport',
templateUrl: './excelimport.component.html',
styleUrls: ['./excelimport.component.css']
})
export class ExcelimportComponent implements OnInit {
@ViewChild('fileInput') fileInput;
message: string;
allUsers: Observable<User[]>;
constructor(private http: HttpClient, private service: UserService) { }
ngOnInit() {
this.loadAllUser();
}
loadAllUser() {
this.allUsers = this.service.BindUser();
}
uploadFile() {
let formData = new FormData();
formData.append('upload', this.fileInput.nativeElement.files[0])
this.service.UploadExcel(formData).subscribe(result => {
this.message = result.toString();
this.loadAllUser();
});
}
}

Step 9. Write HTML code in user.component

Now, we will write the code for the design of view page in Angular UI. Open excelimport.component.html and write the below HTML code.

<div class="container">
<br>
<div class="row">
<div class="col-md-6">
<input class="form-control" type="file" #fileInput />
</div>
<div class="col-md-6">
<button class="btn btn-primary" (click)="uploadFile();">Upload</button>
</div>
</div>
<div>
<h4 class="alert-success">{{message}}</h4>
</div>
<div>
<table class="table">
<tr class="btn-primary">
<th>User Id</th>
<th>UserName</th>
<th>Email Id</th>
<th>Gender</th>
<th>Address</th>
<th>MobileNo</th>
<th>PinCode</th>
</tr>
<tr *ngFor="let user of allUsers | async">
<td style="width: 100px">{{user.UserId}}</td>
<td>{{user.UserName }}</td>
<td>{{user.EmailId}}</td>
<td>{{user.Gender}}</td>
<td>{{user.Address}}</td>
<td style="width: 200px">{{user.MobileNo}}</td>
<td>{{user.PinCode}}</td>
</tr>
</table>
</div>
</div>

The core functionality has almost been completed, so now go to app.component.html and set the page.

<app-excelimport></app-excelimport>

Now, we have completed all the code functionality. Now, we will run the out project but before that, we need to set CORS because if you consume the Web API, Angular blocks the URL and we called this issue CORS(Cross OriginResource Sharing).

Step 9. Set CORS (Cross-Origin Resource Sharing)

Go to the Web API project.

Download a NuGet package for CORS. Go to NuGet Package Manager and download the following file.

After that, go to App_Start folder in Web API project and open WebApiConfig.cs class. Here, modify the Register method with the below code.

Add namespace

using System.Web.Http.Cors;

After that, add the below code inside Register method.

var cors = new EnableCorsAttribute("*", "*", "*"); //origins,headers,methods
config.EnableCors(cors);

Step 11. Run

We have completed all the needed code functionality for our functionality. Before running the application, first, make sure to save your work.

Now, let's run the app and see how it works.

Open TERMINAL and write the following command to run the program.

ng serve -o

The output looks like the following image. It's a stunning UI that's been created.

Now, we will create the Excel file to upload in the database for demo.

After that, let us import the Excel file.

Let's check the full functionality.

20 Comments

Join the conversation! Your thoughts help the community grow.

sujit sahoo4y ago
And I am getting this value httpRequest.Files["upload"] null
- Mithilesh Kumar4y ago
  Can you check your parameter spelling.....
sujit sahoo4y ago
As per your above code i am getting null values, Pls help me, String message = ""; HttpResponseMessage result = null; var httpRequest = HttpContext.Current.Request; //var username= httpRequest.Form["UserName"]; var incomingfile = httpRequest.Files["upload"];
Rohit Pandey5y ago
My application is running, but when I click on upload button nothing is happening it is not uploading data. Their is no error in code so I had click on Inspect screen on chrome and in console tab I found one error is err_connection_refused.Please help me how to solve
- Mithilesh Kumar5y ago
  Are calling hosted api?
Rohit Pandey5y ago
I want to add a button to download the empty template format of the excel sheet. How can I do that?
- Rohit Pandey5y ago
  Can u please help me with the code
- Mithilesh Kumar5y ago
  Yes you can do. Just add a Excel template file in local folder in you Application and you can download from there directly like sample template
Bhumika Patel5y ago
Awesome....I want to make it without using Angular... Can you help me to find the way... or where can I make changes to get data direct in SQL Server..
- Mithilesh Kumar5y ago
  Thank you. Yes you can do. you can use below link for the reference.https://www.c-sharpcorner.com/article/how-to-upload-records-from-excel-file-and-save-in-database-using-store-procedure/
Hamid Khan6y ago
Very Very good explanation Thanks Mithilesh Kumar
- Mithilesh Kumar6y ago
  Thank you Hamid Khan....
ishwar giri6y ago
Sir IExcelDataReader is reading header column also in dataset how to solve this problem ?
- Mithilesh Kumar6y ago
  Can you give me some more details what exact are you facing.
Anahy Yanez7y ago
Can you help me please, at the moment when i put the element on the constructor, all the HTML disappears, i delete it and the HTML appears again, i don't know what to do
- Mithilesh Kumar7y ago
  I think You clear your cookies in your browser...
Hansaka Sandaruwan7y ago
Thanks. Saved My Day
- Mithilesh Kumar7y ago
  Thank you.......................
Nkanyiso Sibanda7y ago
You created a component and named it 'user' but in the list of your components we are seeing 'excelimport' in place of 'user'. How come?
- Mithilesh Kumar7y ago
  Yes thank you I resolved it...