mithilesh kumar singh: Import Excel file in Angular 7 using Web API and SQL Server

In this article I'm going to perform, how to import excel file in an Angular 7 Web application using web api and backend is a SQL Server database. And after upload excel file it will display in 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 in your system then first you have to download and install. Here is Visual Studio Code download link: Download Visual Studio Code Editor

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 create a database table called User Details with 7 columns.

Note: You can choose 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 bind records from database. Visual Studio >> File >> New >> Project >> 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 Models folder >> Right click >>Add >> New Item >> select Data in left panel >>ADO.NET Entity Data Model

Now, click Add button then select EF Designer from database >> Next >> After that give your SQL credential and select the database where, your database table and data.

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

Step 4. Create Web Api Controller

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

Go to the Controller folder in our API Application and right click >> Add >> Controller

Select Web API 2 Controller-Empty.

Now, we will go to controller class but before the write any logic we will install ExcelDataReader.DataSet although many ways to import excel file in mvc but I am going to use a easy way to import excel file. So now right click of project and select Manage nuget packages and search ExcelDataReader.DataSet then install this.

Step 5. Write the logic for import and retrieve records

Go to 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 Build UI Application

Now, we create the Web application in Angular 7 that will consume Web API.

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

Open command prompt and type below code and press ENTER:

npm install -g @angular/cli

Now, open Visual Studio Code and create a project.

Open TERMINAL in Visual Studio Code and type the following syntax to create a new project. We 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 the 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 service

ng g s service/user --spec=false

Open Index.html file and set the bootstrap library.

Step 7. Add library in app.module

Now, open 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, Frist write all properties of the User class related to user details that matches 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 excelimport.component.ts and write 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 code for design of view page in angular ui. Open excelimport.component.html and write below html code

<br>

</div>

<button class="btn btn-primary" (click)="uploadFile();">Upload</button>

</div>

<div>

<h4 class="alert-success">{{message}}</h4>

</div>

<div>

<th>UserName</th>

<th>Email Id</th>

<th>Gender</th>

<th>Address</th>

<th>MobileNo</th>

<th>PinCode</th>

</tr>

<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>

Almost code functionality has completed so now app.component.html and set the page

<app-excelimport></app-excelimport>

Now we have completed all code functionality now we will run the out project but before that we 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.