In this article, we are going to learn how to upload large excel data in angular and save that data using .net API and stored procedure.
In my recent project, I was facing an issue that, when I upload excel whose data is less than 20k then it adds data successfully while, when the data is more than 20k then it doesn’t allow me to store data. So for that, I found one solution that to convert excel as base64 string on the angular side and pass that base64 string to API and then convert base64 string to JSON data and save data using store procedure. Stored Procedures help us to save data fast.
Prerequisites:
- Basic knowledge of Angular
- Code editor like Visual Studio Code
- Visual Studio 2019
- .NET 5.0 SDK
- Basic knowledge of SQL Stored Procedure
Let us understand it by example.
Create a new Angular project by typing the following command in the VSCode terminal.
ng new UploadExcelDemo
Create a new component.
ng g c upload-excel
Open upload-excel.component.ts and copy the below code.
import { Component, OnInit, ElementRef, ViewChild } from '@angular/core';
import { CommonService } from '../common.service';
@Component({
selector: 'app-upload-excel',
templateUrl: './upload-excel.component.html',
styleUrls: ['./upload-excel.component.scss']
})
export class UploadExcelComponent implements OnInit {
@ViewChild('inputFile', { static: false }) inputFile!: ElementRef;
selectedItem: any;
constructor(private common: CommonService) { }
saveStudentExcelData: any = {};
filemodel: any = {};
ngOnInit(): void {
}
openFileDialog() {
const e: HTMLElement = this.inputFile.nativeElement;
e.click();
}
imageUpload(event: any) {
this.filemodel.fileName = event.target.files[0].name;
const reader: FileReader = new FileReader();
reader.readAsDataURL(event.target.files[0]);
reader.onload = () => {
this.filemodel.base64String = (reader.result as string).substring((reader.result as string).indexOf(',') + 1);
this.saveExcelData();
}
}
saveExcelData() {
this.saveStudentExcelData = Object.assign({ ClientId: 1 });
this.saveStudentExcelData.fileVMS = [];
this.saveStudentExcelData.fileVMS.push(this.filemodel);
this.common.callPostApi('api/UploadExcel/SaveExcelData', this.saveStudentExcelData).subscribe((result: any) => {
console.log(result);
if (result.success) {
console.log("result success");
this.common.success("Data save successfully.");
}
else {
this.common.failed("data is not saved");
this.common.showError(result.message);
}
});
this.ngOnInit();
this.selectedItem = '';
}
}
Open upload-excel.component.html copy below code.
<div class="contentbox">
<div class="allcontent">
<h2>Upload File</h2>
<div class="select_file">
<input type="file" id="profile" (change)="imageUpload($event)" placeholder="Upload Image" class="form-control " />
</div>
</div>
</div>
Open upload-excel.component.css and add the below code in it.
.contentbox {
position: relative;
margin: 10px 10px 10px 10px;
border-top-left-radius: 25px;
border-bottom-left-radius: 25px;
border-bottom-right-radius: 25px;
color: white;
}
.allcontent {
width: 50%;
min-height: 36vh;
border-top-right-radius: 25px;
background-color: #343c63;
border-bottom-left-radius: 25px;
border-bottom-right-radius: 25px;
padding: 10px;
}
.select_box {
display: inline-block;
position: relative;
margin: 38px 0px 10px 38px;
}
.select_box select {
background-color: rgb(40, 50, 84);
color: white;
padding: 12px;
width: 300px;
border: none;
-webkit-appearance: button;
appearance: button;
outline: none;
-webkit-appearance: none;
border-radius: 10px;
}
.select_box::before {
content: "\f107";
font-family: FontAwesome;
position: absolute;
top: 0;
right: 0;
width: 40px;
height: 50%;
font-size: 35px;
color: #fff;
background-color: rgb(60, 68, 110);
pointer-events: none;
border-radius: 0px 10px 10px 0;
display: flex;
justify-content: center;
align-items: center;
}
.select_file {
display: grid;
border: 3px dashed #fff;
border-radius: 5px;
width: 300px;
position: relative;
margin: 38px 0px 10px 38px;
padding: 30px 60px;
place-content: center;
cursor: pointer;
}
.sampledwnld {
margin-left: 125px;
color: rgb(146, 177, 195);
}
Create services by using the following command.
ng g s common
Open common.services.ts and paste the below code in it.
import { Injectable } from '@angular/core';
import { HttpHeaders, HttpRequest, HttpClient, HttpErrorResponse } from '@angular/common/http';
import { environment } from '../environments/environment';
import { MatSnackBar, MatSnackBarHorizontalPosition, MatSnackBarVerticalPosition } from '@angular/material/snack-bar';
import { BehaviorSubject } from 'rxjs';
@Injectable({
providedIn: 'root'
})
export class CommonService {
public errormessage = new BehaviorSubject('');
public iserror = new BehaviorSubject(false);
horizontalPosition: MatSnackBarHorizontalPosition = 'end';
verticalPosition: MatSnackBarVerticalPosition = 'top';
url: string;
header: any;
constructor(
private http: HttpClient,
private _snackBar: MatSnackBar,
) {
this.url = environment.baseurl;
}
warning(message: string) {
this._snackBar.open(message, '', {
duration: 2000,
horizontalPosition: this.horizontalPosition,
verticalPosition: this.verticalPosition,
panelClass: ['warning']
});
}
success(message: string) {
this._snackBar.open(message, '', {
duration: 2000,
horizontalPosition: this.horizontalPosition,
verticalPosition: this.verticalPosition,
panelClass: ['success']
});
}
failed(message: string) {
this._snackBar.open(message, '', {
duration: 2000,
horizontalPosition: this.horizontalPosition,
verticalPosition: this.verticalPosition,
panelClass: ['danger']
});
}
showError(message: string) {
this.errormessage.next(message);
this.iserror.next(true);
}
callPostApi(apiUrl: string, data: any): any {
return this.http.post<any>(this.url + apiUrl, data);
}
}
Open app.module.ts add the following line in it.
import { NgModule } from '@angular/core';
import { BrowserModule } from '@angular/platform-browser';
import { HttpClientModule } from '@angular/common/http';
import { MatSnackBarModule } from '@angular/material/snack-bar';
import { AppRoutingModule } from './app-routing.module';
import { AppComponent } from './app.component';
import { UploadExcelComponent } from './upload-excel/upload-excel.component';
import { BrowserAnimationsModule } from '@angular/platform-browser/animations';
@NgModule({
declarations: [
AppComponent,
UploadExcelComponent
],
imports: [
BrowserModule,
AppRoutingModule,
BrowserAnimationsModule,
HttpClientModule,
MatSnackBarModule
],
providers: [],
bootstrap: [AppComponent]
})
export class AppModule { }
Open environment.ts and add your .net project Url in it.
export const environment = {
production: false,
baseurl : 'https://localhost:44376/',
};
Now open a .Net Core project and create API.
Create UploadExcelController and add the below code to it.
using Microsoft.AspNetCore.Mvc;
using ReadExcelFileDemo.Model;
using ReadExcelFileDemo.Services.Interface;
using System.IO;
using System.Threading.Tasks;
namespace ReadExcelFileDemo.Controllers
{
[Route("api/[controller]")]
[ApiController]
public class UploadExcelController : ControllerBase
{
IUploadServices proScr;
public UploadExcelController(IUploadServices uploadservice)
{
proScr = uploadservice;
}
[HttpPost]
[Route("SaveExcelData")]
public async Task<IActionResult> SaveUploadData(FileUploadVM model)
{
return Ok(await proScr.SaveExcelData(model, Directory.GetCurrentDirectory()));
}
}
}
Create IUploadServices in the Services folder and paste the below code into it.
using ReadExcelFileDemo.Model;
using System.Threading.Tasks;
namespace ReadExcelFileDemo.Services.Interface
{
public interface IUploadServices
{
Task<DCResultVM> SaveExcelData(FileUploadVM model, string baseUrl);
}
}
Create UploadServices in the Services folder and paste the below code into it.
using Newtonsoft.Json;
using ReadExcelFileDemo.Data;
using ReadExcelFileDemo.Model;
using ReadExcelFileDemo.Services.Interface;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Threading.Tasks;
using Dapper;
namespace ReadExcelFileDemo.Services
{
public class UploadServices: BaseClass,IUploadServices
{
public async Task<DCResultVM> SaveExcelData(FileUploadVM model, string baseUrl)
{
DCResultVM resultObj = new DCResultVM();
DCExcelReader dcReader = new DCExcelReader();
DataTable dt = new DataTable();
string sClearSQL = string.Empty;
foreach (var file in model.FileVMS)
{
var fileName = DateTime.Now.ToString("mm-ss") + file.FileName;
var uniqFolderUrl = await dcReader.ServerUploadFile(file.Base64String, fileName, model.ClientId, baseUrl);
var ds = dcReader.GetExcelDataSet(Path.Combine(baseUrl, uniqFolderUrl, fileName));
if (ds.Tables.Count != 0 && ds.Tables[0].Rows.Count != 0)
{
dt = ds.Tables[0];
}
using (IDbConnection db = new SqlConnection(DBConnectionString))
{
var ReqFilter = new
{
jsonData = JsonConvert.SerializeObject(dt),
};
resultObj.Model = db.QueryAsync<dynamic>("uspSaveStudentData", ReqFilter, commandType: CommandType.StoredProcedure).Result.FirstOrDefault();
resultObj.Success = true;
return await Task.Run(() => resultObj);
}
}
return resultObj;
}
}
}
In the above code, you can see that uspSaveStudentData is our stored procedure in that we are passing serialize data.
Now create a Model folder that creates one class DCExcelReader and paste the below code in it.
using ExcelDataReader;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Threading.Tasks;
namespace ReadExcelFileDemo.Model
{
public class DCExcelReader
{
public async Task<string> ServerUploadFile(string base64Str, string fileName, int clientId, string serverPath)
{
try
{
var fileByte = Convert.FromBase64String(base64Str);
Stream fileStream = new MemoryStream(fileByte);
string uniqueFolder = Path.Combine("wwwroot", "DocumentFiles", $"Client-{ clientId.ToString()}", "RawData");
serverPath = Path.Combine(serverPath, uniqueFolder);
if (!Directory.Exists(serverPath))
System.IO.Directory.CreateDirectory(serverPath);
var pathToSave = Path.Combine(serverPath, fileName);
using (var stream = new FileStream(pathToSave, FileMode.Create))
{
await fileStream.CopyToAsync(stream);
await stream.FlushAsync();
}
return uniqueFolder;
}
catch (Exception ex)
{
throw ex;
}
}
public DataSet GetExcelDataSet(string filepath)
{
System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);
FileStream stream = new FileStream(filepath, FileMode.Open, FileAccess.Read);
try
{
var reader = ExcelReaderFactory.CreateOpenXmlReader(stream, new ExcelReaderConfiguration()
{
LeaveOpen = false
});
var result = reader.AsDataSet(new ExcelDataSetConfiguration()
{
UseColumnDataType = true,
FilterSheet = (tableReader, sheetIndex) => true,
ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
{
EmptyColumnNamePrefix = "Column",
UseHeaderRow = true,
ReadHeaderRow = (rowReader) =>
{
},
FilterRow = (rowReader) =>
{
return true;
},
FilterColumn = (rowReader, columnIndex) =>
{
return true;
}
}
});
return result;
}
catch (Exception ex)
{
return new DataSet();
}
}
}
}
Create DCResultVM class in the same folder and paste the below code in it.
public class DCResultVM
{
public dynamic Model { get; set; }
public string Message { get; set; }
public bool Success { get; set; }
}
Create FileUploadVM class in the same folder and paste the below code in it.
public class FileUploadVM
{
public int ClientId { get; set; }
public FileUploadVM()
{
this.FileVMS = new List<FileVM>();
}
public List<FileVM> FileVMS { get; set; }
}
public class FileVM
{
public string FileType { get; set; }
public string Base64String { get; set; }
public string FileName { get; set; }
}
Create BaseClass get a connection in it.
public class BaseClass
{
IConfiguration config = new ConfigurationBuilder().SetBasePath(Directory.GetCurrentDirectory()).AddJsonFile("appsettings.json").Build();
protected string DBConnectionString
{
get
{
return config["ConnectionString"];
}
}
}
Add your Database connection string in appsettings.json.
{
"ConnectionString": "Your connection string",
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft": "Warning",
"Microsoft.Hosting.Lifetime": "Information"
}
},
"AllowedHosts": "*"
}
Add the following line in Startup.cs.
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using ReadExcelFileDemo.Services;
using ReadExcelFileDemo.Services.Interface;
namespace ReadExcelFileDemo
{
public class Startup
{
public Startup(IConfiguration configuration)
{
Configuration = configuration;
}
public IConfiguration Configuration { get; }
// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
services.AddControllers();
services.AddSingleton<IUploadServices, UploadServices>();
services.AddCors(option =>
{
option.AddPolicy("AllCors", builder =>
{
builder.AllowAnyOrigin();
builder.AllowAnyHeader();
builder.AllowAnyMethod();
});
});
}
// This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
{
if (env.IsDevelopment())
{
app.UseDeveloperExceptionPage();
}
app.UseHttpsRedirection();
app.UseCors("AllCors");
app.UseRouting();
app.UseAuthorization();
app.UseEndpoints(endpoints =>
{
endpoints.MapControllers();
});
}
}
}
Create the uspSaveStudentData store procedure in your database.
USE [StudendDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[uspSaveStudentData]
@JSONData nvarchar(max)=''
as
begin
select @JSONData, 1, 1, 'trns'
SELECT * into #tmp
FROM OPENJSON(@JSONData, '$')
WITH(Student varchar(50) '$.Student',
Mathematics varchar(50) '$.Mathematics',
[Geography] varchar(50) '$.Geography',
Science varchar(50) '$.Science'
);
insert [dbo].[Students](Student,Mathematics,[Geography],Science)
select Student,Mathematics,[Geography],Science from #tmp
select 1 respCd, 'Record saved successfully!!' respMsg
end
This stored procedure adds all excel data to my student table.
That’s it.
Output
Also check, Basic Authentication In .NET Core 5.0
