Uploading a Excel File With Angular Website

In this article I'm going to perform, how to import excel file in an Angular 7 Web awarding using web api and backend is a SQL Server database. And afterwards upload excel file it will display in ui. A Spider 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 y'all don't have Visual studio code in your system then beginning you have to download and install. Here is Visual Studio Lawmaking download link: Download Visual Studio Lawmaking Editor

Stride ane. Create a database tabular array

Create a database. Open SQL Server and create a new database tabular array. Every bit you lot can run across from the following image, I create a database tabular array chosen User Details with 7 columns.

Notation: You tin can choose size of the column according to your requirement.

Notation: If y'all already have an existing database and tabular array, you lot tin can skip this step.

Step two. 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 Spider web Application. After that click OK and you will see the templates. Select Web API template.

Click OK.

Step iii. Add ADO.NET Entity Data Model

Now, Select Models binder >> Right click >>Add together >> New Item >> select Information in left panel >>ADO.Net Entity Information Model

Now, click Add push so select EF Designer from database >> Next >> Afterward that give your SQL credential and select the database where, your database tabular array and data.

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

Step 4. Create Spider web Api Controller

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

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

 Select Spider web API two Controller-Empty.

Now, we will go to controller class but before the write any logic nosotros will install ExcelDataReader.DataSet although many ways to import excel file in mvc just I am going to use a like shooting fish in a barrel mode to import excel file. And then at present correct click of project and select Manage nuget packages and search ExcelDataReader.DataSet so 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 lawmaking.

using ExcelDataReader;

using System.Collections.Generic;

using System.Data;

using Arrangement.IO;

using System.Linq;

using System.Net;

using System.Net.Http;

using System.Spider web;

using Arrangement.Web.Http;

using ExcelUploadAPI.Models;

namespace ExcelUploadAPI.Controllers

{

    [ RoutePrefix ( "Api/Excel" )]

public class ExcelExampleController : ApiController

    {

        [ Route ( "UploadExcel" )]

        [ HttpPost ]

public cord ExcelUpload()

        {

string message = "" ;

HttpResponseMessage consequence = zippo ;

var httpRequest = HttpContext .Current.Asking;

using ( AngularDBEntities objEntity = new AngularDBEntities ())

            {

if (httpRequest.Files.Count > 0)

                {

HttpPostedFile file = httpRequest.Files[0];

Stream stream = file.InputStream;

IExcelDataReader reader = nothing ;

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][ii].ToString();

                        objUser.Accost = finalRecords.Rows[i][3].ToString();

                        objUser.MobileNo = finalRecords.Rows[i][4].ToString();

                        objUser.PinCode = finalRecords.Rows[i][five].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 bulletin;

        }

        [ Route ( "UserDetails" )]

        [ HttpGet ]

public List < UserDetail > BindUser()

        {

List < UserDetail > lstUser = new Listing < UserDetail >();

using ( AngularDBEntities objEntity = new AngularDBEntities ())

            {

                lstUser = objEntity.UserDetails.ToList();

            }

return lstUser;

        }

    }

}

Step six. Create angular application for Build UI Application

Now, we create the Web application in Athwart 7 that volition consume Web API.

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

Open command prompt and type below code and press ENTER:

npm install -thou @angular/cli

Now, open Visual Studio Lawmaking and create a project.

Open TERMINAL in Visual Studio Code and type the following syntax to create a new project. We name information technology ExcelUploading.

ng new ExcelUploading

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

One time created, the projection should look like this.

Now, we will create components to provide the UI.

I'1000 going to create a new component, User.

Go to the TERMINAL and go our athwart project location using the following control:

cd projectName

At present, write the following command that will create a component.

ng one thousand c user Printing ENTER.

At present, we create a model grade.

Open Final and write the beneath control:

ng k course model/User --spec=false

Then create service

ng k s service/user --spec=false

Open up Alphabetize.html file and set the bootstrap library.

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

Pace 7. Add library in app.module

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

import { BrowserModule } from '@angular/platform-browser' ;

import { NgModule } from '@athwart/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 { }

Footstep eight. Write typescript lawmaking in component and service

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

consign class User {

UserId : string ;

UserName : string ;

EmailId : string ;

Gender : string ;

Accost : string ;

MobileNo : cord ;

PinCode : cord ;

}

Open user.service.ts and start import necessary class and libraries and and so brand 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 . suspend ( 'Content-Type' , 'multipart/form-data' );

headers . suspend ( 'Accept' , 'application/json' );

const httpOptions = { headers: headers };

return this . http . mail ( this . url + '/UploadExcel' , formData , httpOptions )

}

BindUser (): Observable < User []> {

render this . http . become < User []>( this . url + '/UserDetails' );

}

}

Open excelimport.component.ts and write beneath code.

import { Component , OnInit , ViewChild } from '@athwart/core' ;

import { HttpClient , HttpHeaders } from '@angular/common/http' ;

import { Appreciable } 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 : cord ;

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 ();

});

}

}

Footstep 9. Write html code in user.co mponent

At present we volition write code for design of view page in angular ui. Open excelimport.component.html and write below html code

<div class = "container" >

<br>

<div class = "row" >

<div form = "col-doc-6" >

<input class = "grade-control" type = "file" #fileInput />

</div>

<div class = "col-md-half-dozen" >

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

</div>

</div>

<div>

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

</div>

<div>

<table class = "table" >

<tr form = "btn-primary" >

<th> User Id </th>

<thursday> UserName </th>

<th> Email Id </th>

<thursday> Gender </th>

<th> Address </thursday>

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

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

<app-excelimport></app-excelimport>

Now we have completed all code functionality at present we will run the out project but before that we set cors because If y'all swallow the Web API, Athwart blocks the URL and we called this issue CORS(Cross OriginResource Sharing).

Step 9. Set CORS(Cross Origin Resource Sharing)

Get to the Spider web API projection.

Download a Nuget parcel for CORS. Get to NuGet Package Manager and download the following file.

After that, become to App_Start folder in Web API project and open WebApiConfig.cs course. Here, change the Register method with the below code.

Add namespace

using Organisation.Web.Http.Cors;

after that add together below code inside Register method

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

            config.EnableCors(cors);

Pace xi. Run

Nosotros have completed all needed code functionality for our functionality. Earlier running the application, get-go brand certain save your work.

At present, let's run the app and run into how it works.

Open Terminal and write the following command to run the plan.

ng serve -o

The output looks like the following paradigm. It's a stunning UI created .

Now we will create excel file to upload in database for demo.

After import the excel file

Allow'due south cheque full functionality

willinghamhishich.blogspot.com

Source: https://mithileshdotnet.blogspot.com/2019/06/import-excel-file-in-angular-7-using.html

Share this post