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