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.

• Cascading Dropdown List Using MVC, Web API And Angular7
• CRUD operations in angular7 using Web API
• Infinite Scroll In Angular 7 Using Web API And SQL Server
• Filtration, Sorting, And Pagination In Angular 7 using Web API And SQL Server
  

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.

 

 

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

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.