Hi friends, My previous article was that how
to download records from the database in excel file. So today, In this
article, I will explain how to upload
records from excel file in the database using stored procedure and also check all
type validation. I.e Here I checked required filed validation, excel format
validation, null validation, employee
number should be unique(here I used an example employee records), etc.
That's it. Thanks for reading this article. I hope you enjoyed it.
Step1:
First, let's create a table
Then let's create store procedure, In this store procedure
have created procedure for insert records and also check the duplicate employee
number.
Create proc
usp_InsertNewEmployeeDetails
(
@EmployeeNo int = null,
@FirstName varchar(50) = null,
@LastName varchar(50) = null,
@DateOfBirth datetime
= null,
@Address varchar(50) = null,
@MobileNo varchar(200) = null,
@PostelCode varchar(50) = null,
@EmailId varchar(50) = null
)
as
begin
IF EXISTS(select EmployeeNo from
EmployeeInfo where EmployeeNo=@EmployeeNo)
return 0
ELSE
begin
Insert into EmployeeInfo(
EmployeeNo,FirstName,LastName,
DateOfBirth,Address,MobileNo,PostelCode,
EmailId
)
values
(
@EmployeeNo,@FirstName,@LastName,
@DateOfBirth,@Address,@MobileNo,@PostelCode,
@EmailId)
end
end
Step2
Now, we have to add the tables in my MVC
application. I have used Entity Framework with a LINQ query.
For that, I created an MVC application
and went through, File->New ->Web application ->select MVC ->OK.
Go to Model folder ->Right click -> Add
-> New item -> ADO.NET Entity Data Model -> click Add -> select
database first approach->Click Next.
Select New Connection
and give the connection details, then select database ->Click OK.
Choose tables and
store procedure and click OK.
Step3
Now we have
to download LinqToExcel.dll file so for this, right
click of the project file. After that select 'Manage
NueGet Package' then search, LinqToExcel
and download this dll
Step4
Now
we have to create a controller class.
First
put a namespace
using LinqToExcel;
then create a
action method for view
public ActionResult ExcelUpload()
{
return View();
}
Now we have
to create a view so for this, right click action name and select view and click
ok.
<div align="center">
@if (ViewBag.Message != null)
{
<span class="alert alert-warning"> @ViewBag.Message</span>
}
<div align="right" class="btn
btn-default">
@using (Html.BeginForm("UploadExcel", "ExcelDemo", FormMethod.Post, new { @enctype = "multipart/form-data" }))
{
<input type="file" id="fileUpload" class="btn
btn-primary" name="FileUpload" /><br />
<input type="submit" class="btn
btn-primary" name="UploadNewEmployee" id="fileUploadExcel" value="Upload New
Employee Details" />
}
</div>
</div>
Here I used sweetAlert to check the required field
validation of file upload. so first we have to download the library for this and
add.
<link href="~/Scripts/sweetalert.css" rel="stylesheet" />
<script src="~/Scripts/jquery-1.10.2.js"></script>
<script src="~/Scripts/sweetalert.js"></script>
<script>
$('#fileUploadExcel').click(function (e) {
if ($('#fileUpload').val() === "")
{
sweetAlert("Oops...!!!", "Please select
file",
"error");
return false;
}
});
</script>
Suppose we
click without select file then see the out put
Step5
Now lt's
write code for insert recodes from excel file in the controller
In this,
- I checked excel file format.
if (FileUpload.ContentType == "application/vnd.ms-excel" ||
FileUpload.ContentType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
{
string filename =
FileUpload.FileName;
if (filename.EndsWith(".xlsx"))
{
}
- I checked Uploaded excel file
string targetpath = Server.MapPath("~/DetailFormatInExcel/");
FileUpload.SaveAs(targetpath + filename);
string pathToExcelFile =
targetpath + filename;
string sheetName = "Sheet1";
- I checked employe number null or not
if (a.EmployeeNo != null)
{
}
- I created a separated method for post data
public int PostExcelData(int employeeNo,string firstName, string lastName, DateTime? dateOfBirth, string address,string mobileNo,string postelCode, string emailId)
{
EmployeeDBEntities DbEntity = new EmployeeDBEntities();
var InsertExcelData =
DbEntity.usp_InsertNewEmployeeDetails(employeeNo,firstName, lastName,
dateOfBirth,address,mobileNo,postelCode, emailId);
return InsertExcelData;
}
- called the postdata method
int resullt =
PostExcelData(a.EmployeeNo, a.FirstName, a.LastName, myBirthdate, a.Address,
a.MobileNo, a.PostelCode, a.EmailId);
if (resullt <= 0)
{
data = "Hello User, I found some duplicate values! Only unique employee number has inserted and
duplicate values(s) are not inserted";
ViewBag.Message = data;
continue;
- I checked also, Suppose we have 3 recodes and 2 records are duplicate so these are automatic skip duplecate records
so now final
code below.
[HttpPost]
public ActionResult UploadExcel(EmployeeInfo objEmpDetail, HttpPostedFileBase FileUpload)
{
EmployeeDBEntities objEntity = new EmployeeDBEntities();
string data = "";
if (FileUpload != null)
{
if (FileUpload.ContentType == "application/vnd.ms-excel" ||
FileUpload.ContentType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
{
string filename =
FileUpload.FileName;
if (filename.EndsWith(".xlsx"))
{
string targetpath =
Server.MapPath("~/DetailFormatInExcel/");
FileUpload.SaveAs(targetpath + filename);
string pathToExcelFile = targetpath + filename;
string sheetName = "Sheet1";
var excelFile = new ExcelQueryFactory(pathToExcelFile);
var empDetails = from a in excelFile.Worksheet<EmployeeInfo>(sheetName) select a;
foreach (var a in empDetails)
{
if (a.EmployeeNo != null)
{
DateTime? myBirthdate = null;
if (a.MobileNo.Length
> 12)
{
data = "Phone number
should be 10 to 12 disit";
ViewBag.Message = data;
}
myBirthdate = Convert.ToDateTime(a.DateOfBirth);
int resullt =
PostExcelData(a.EmployeeNo, a.FirstName, a.LastName, myBirthdate, a.Address,
a.MobileNo, a.PostelCode, a.EmailId);
if (resullt <= 0)
{
data = "Hello User, I found some duplicate values! Only unique employee number has inserted and
duplicate values(s) are not inserted";
ViewBag.Message = data;
continue;
}
else
{
data = "Successful
upload records";
ViewBag.Message = data;
}
}
else
{
data =
a.EmployeeNo + "Some fields are null, Please check your excel sheet";
ViewBag.Message
= data;
return View("ExcelUpload");
}
}
}
else
{
data = "This file is
not a valid format";
ViewBag.Message = data;
}
return View("ExcelUpload");
}
else
{
data = "Only Excel the file format is allowed";
ViewBag.Message = data;
return View("ExcelUpload");
}
}
else
{
if (FileUpload == null)
{
data = "Please choose
Excel file";
}
ViewBag.Message = data;
return View("ExcelUpload");
}
}
public int PostExcelData(int employeeNo,string firstName, string lastName, DateTime? dateOfBirth, string address,string mobileNo,string postelCode, string emailId)
{
EmployeeDBEntities DbEntity = new EmployeeDBEntities();
var InsertExcelData =
DbEntity.usp_InsertNewEmployeeDetails(employeeNo,firstName, lastName,
dateOfBirth,address,mobileNo,postelCode, emailId);
return InsertExcelData;
}
So finally
coding part has done now we have to check excel file upload or not.
Now I have
created an excel file and filled some records
No comments:
Post a Comment