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

In this article, I'm going to tell how to import an Excel file in an Angular 7 Web application using Web API.

Let us learn the process of importing the Excel file in an Angular 7 Web Application using Web API with a back-end of 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 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.
 
Import Excel File In Angular 7 Using Web API And SQL Server
 
Click OK.
 
Step 3. Add ADO.NET Entity Data Model
 
Now, select the Models folder and right click. The, go to Add >> New Item >> select Data in left panel >>ADO.NET Entity Data Model.
 
Import Excel File In Angular 7 Using Web API And SQL Server
 
Import Excel File In Angular 7 Using Web API And SQL Server
 
Import Excel File In Angular 7 Using Web API And SQL Server
 
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.
 
Import Excel File In Angular 7 Using Web API And SQL Server
 
Import Excel File In Angular 7 Using Web API And SQL Server
 
Import Excel File In Angular 7 Using Web API And SQL Server
 
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 operation.
 
Go to the Controller folder in your API application and right click >> Add >> Controller.
 
Import Excel File In Angular 7 Using Web API And SQL Server
 
Select Web API 2 Controller-Empty.
 
Import Excel File In Angular 7 Using Web API And SQL Server
 
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.
 
Import Excel File In Angular 7 Using Web API And SQL Server
 
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.
  1. using ExcelDataReader;  
  2. using System.Collections.Generic;  
  3. using System.Data;  
  4. using System.IO;  
  5. using System.Linq;  
  6. using System.Net;  
  7. using System.Net.Http;  
  8. using System.Web;  
  9. using System.Web.Http;  
  10. using ExcelUploadAPI.Models;  
  11.   
  12. namespace ExcelUploadAPI.Controllers  
  13. {  
  14.     [RoutePrefix("Api/Excel")]  
  15.     public class ExcelExampleController : ApiController  
  16.     {  
  17.         [Route("UploadExcel")]  
  18.         [HttpPost]  
  19.         public string ExcelUpload()  
  20.         {  
  21.             string message = "";  
  22.             HttpResponseMessage result = null;  
  23.             var httpRequest = HttpContext.Current.Request;  
  24.             using (AngularDBEntities objEntity = new AngularDBEntities())  
  25.             {  
  26.   
  27.                 if (httpRequest.Files.Count > 0)  
  28.                 {  
  29.                     HttpPostedFile file = httpRequest.Files[0];  
  30.                     Stream stream = file.InputStream;  
  31.   
  32.                     IExcelDataReader reader = null;  
  33.   
  34.                     if (file.FileName.EndsWith(".xls"))  
  35.                     {  
  36.                         reader = ExcelReaderFactory.CreateBinaryReader(stream);  
  37.                     }  
  38.                     else if (file.FileName.EndsWith(".xlsx"))  
  39.                     {  
  40.                         reader = ExcelReaderFactory.CreateOpenXmlReader(stream);  
  41.                     }  
  42.                     else  
  43.                     {  
  44.                         message = "This file format is not supported";  
  45.                     }  
  46.   
  47.                     DataSet excelRecords = reader.AsDataSet();  
  48.                     reader.Close();  
  49.   
  50.                     var finalRecords = excelRecords.Tables[0];  
  51.                     for (int i = 0; i < finalRecords.Rows.Count; i++)  
  52.                     {  
  53.                         UserDetail objUser = new UserDetail();  
  54.                         objUser.UserName = finalRecords.Rows[i][0].ToString();  
  55.                         objUser.EmailId = finalRecords.Rows[i][1].ToString();  
  56.                         objUser.Gender = finalRecords.Rows[i][2].ToString();  
  57.                         objUser.Address = finalRecords.Rows[i][3].ToString();  
  58.                         objUser.MobileNo = finalRecords.Rows[i][4].ToString();  
  59.                         objUser.PinCode = finalRecords.Rows[i][5].ToString();  
  60.   
  61.                         objEntity.UserDetails.Add(objUser);  
  62.   
  63.                     }  
  64.   
  65.                     int output = objEntity.SaveChanges();  
  66.                     if (output > 0)  
  67.                     {  
  68.                         message = "Excel file has been successfully uploaded";  
  69.                     }  
  70.                     else  
  71.                     {  
  72.                         message = "Excel file uploaded has fiald";  
  73.                     }  
  74.   
  75.                 }  
  76.   
  77.                 else  
  78.                 {  
  79.                     result = Request.CreateResponse(HttpStatusCode.BadRequest);  
  80.                 }  
  81.             }  
  82.             return message;  
  83.         }  
  84.   
  85.         [Route("UserDetails")]  
  86.         [HttpGet]  
  87.         public List<UserDetail> BindUser()  
  88.         {  
  89.             List<UserDetail> lstUser = new List<UserDetail>();  
  90.             using (AngularDBEntities objEntity = new AngularDBEntities())  
  91.             {  
  92.                 lstUser = objEntity.UserDetails.ToList();  
  93.             }  
  94.             return lstUser;  
  95.         }  
  96.     }  
  97. }  
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.
 
Import Excel File In Angular 7 Using Web API And SQL Server
 
Now, we will create components to provide UI.
 
I'm going to create a new component, User.
 
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 user

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
 
Import Excel File In Angular 7 Using Web API And SQL Server
 
Open the Index.html file and set the bootstrap library.
  1. <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.
  1. import { BrowserModule } from '@angular/platform-browser';  
  2. import { NgModule } from '@angular/core';  
  3. import { HttpClientModule, HttpClient } from '@angular/common/http';  
  4.   
  5. import { AppRoutingModule } from './app-routing.module';  
  6. import { AppComponent } from './app.component';  
  7. import { ExcelimportComponent } from './excelimport/excelimport.component';  
  8.   
  9. @NgModule({  
  10.   declarations: [  
  11.     AppComponent,  
  12.     ExcelimportComponent  
  13.   ],  
  14.   imports: [  
  15.     BrowserModule,  
  16.     HttpClientModule,  
  17.     AppRoutingModule  
  18.   ],  
  19.   providers: [],  
  20.   bootstrap: [AppComponent]  
  21. })  
  22. 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.
  1. export class User {  
  2.     UserId: string;    
  3.     UserName: string;    
  4.     EmailId: string;    
  5.     Gender: string;    
  6.     Address: string;    
  7.     MobileNo: string;    
  8.     PinCode: string;    
  9. }  
open user.service.ts and first import necessary class and libraries and then make calls to the WebAPI methods.
  1. import { Injectable } from '@angular/core';  
  2. import { HttpHeaders } from '@angular/common/http';  
  3. import { HttpClient } from '@angular/common/http'  
  4. import { User } from '../model/user';  
  5. import { Observable } from 'rxjs';  
  6.   
  7. @Injectable({  
  8.   providedIn: 'root'  
  9. })  
  10. export class UserService {  
  11.   
  12.   constructor(private http: HttpClient) { }  
  13.   
  14.   url = 'http://localhost:63376/Api/Excel';  
  15.   
  16.   UploadExcel(formData: FormData) {  
  17.     let headers = new HttpHeaders();  
  18.   
  19.     headers.append('Content-Type''multipart/form-data');  
  20.     headers.append('Accept''application/json');  
  21.   
  22.     const httpOptions = { headers: headers };  
  23.   
  24.     return this.http.post(this.url + '/UploadExcel', formData, httpOptions)  
  25.   }  
  26.   BindUser(): Observable<User[]> {  
  27.     return this.http.get<User[]>(this.url + '/UserDetails');  
  28.   }  
  29. }  
Open the excelimport.component.ts and write the below code.
  1. import { Component, OnInit, ViewChild } from '@angular/core';  
  2. import { HttpClient, HttpHeaders } from '@angular/common/http';  
  3. import { Observable } from 'rxjs';  
  4. import { UserService } from '../service/user.service';  
  5. import { User } from '../model/user';  
  6.   
  7. @Component({  
  8.   selector: 'app-excelimport',  
  9.   templateUrl: './excelimport.component.html',  
  10.   styleUrls: ['./excelimport.component.css']  
  11. })  
  12. export class ExcelimportComponent implements OnInit {  
  13.   @ViewChild('fileInput') fileInput;  
  14.   message: string;  
  15.   allUsers: Observable<User[]>;  
  16.   constructor(private http: HttpClient, private service: UserService) { }  
  17.   
  18.   ngOnInit() {  
  19.     this.loadAllUser();  
  20.   }  
  21.   loadAllUser() {  
  22.     this.allUsers = this.service.BindUser();  
  23.   }  
  24.   uploadFile() {  
  25.     let formData = new FormData();  
  26.     formData.append('upload'this.fileInput.nativeElement.files[0])  
  27.   
  28.     this.service.UploadExcel(formData).subscribe(result => {  
  29.       this.message = result.toString();  
  30.       this.loadAllUser();  
  31.     });   
  32.   
  33.   }  
  34. }  
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.
  1. <div class="container">  
  2.     <br>  
  3.     <div class="row">  
  4.         <div class="col-md-6">  
  5.             <input class="form-control" type="file" #fileInput />  
  6.         </div>  
  7.         <div class="col-md-6">  
  8.             <button class="btn btn-primary" (click)="uploadFile();">Upload</button>  
  9.         </div>  
  10.     </div>  
  11.     <div>  
  12.         <h4 class="alert-success">{{message}}</h4>  
  13.     </div>  
  14.     <div>  
  15.         <table class="table">  
  16.             <tr class="btn-primary">  
  17.                 <th>User Id</th>  
  18.                 <th>UserName</th>  
  19.                 <th>Email Id</th>  
  20.                 <th>Gender</th>  
  21.                 <th>Address</th>  
  22.                 <th>MobileNo</th>  
  23.                 <th>PinCode</th>  
  24.   
  25.             </tr>  
  26.             <tr *ngFor="let user of allUsers | async">  
  27.                 <td style="width: 100px">{{user.UserId}}</td>  
  28.                 <td>{{user.UserName }}</td>  
  29.                 <td>{{user.EmailId}}</td>  
  30.                 <td>{{user.Gender}}</td>  
  31.                 <td>{{user.Address}}</td>  
  32.                 <td style="width: 200px">{{user.MobileNo}}</td>  
  33.                 <td>{{user.PinCode}}</td>  
  34.   
  35.             </tr>  
  36.   
  37.         </table>  
  38.     </div>  
  39. </div>  
The core functionality has almost been completed, so now go to app.component.html and set the page.
  1. <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.
 
Import Excel File In Angular 7 Using Web API And SQL Server
 
Add namespace
  1. using System.Web.Http.Cors;  
After that, add the below code inside Register method.
  1. var cors = new EnableCorsAttribute("*""*""*"); //origins,headers,methods  
  2.           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.
 
Import Excel File In Angular 7 Using Web API And SQL Server
 
Now, we will create the Excel file to upload in the database for demo.
 
Import Excel File In Angular 7 Using Web API And SQL Server
 
After that, let us import the Excel file.
 
Import Excel File In Angular 7 Using Web API And SQL Server
 
Let's check the full functionality.
 
Import Excel File In Angular 7 Using Web API And SQL Server