Upload excel sheet in database table using LinqToExcel package in MVC.

In this section we will show you how to upload excel sheet in database table using LinqToExcel package in MVC.

For Upload Excelsheet data in Table using mvc, first install and add ClosedXML library file. And also install and add reference of LinqToExcel library.

For installing and adding reference of ClosedXML, LinqToExcel library:

•  Right click the Project in Solution Explorer and select 'Manage NuGet Packages'.
•  Now you will need to look for ClosedXML , LinqToExcel package and once found, you need to click the Install Button.
•  The reference of ClosedXML , LinqToExcel library automatically added in your reference file, You can check it.

Add controller and view for Upload Excel Sheet data in table. Write this below code in your view and controller page....

write this following code in your view page:

Upload Excel Sheet : <input type="file" id="FileUpload" /><button id="btnupload" class="btn btn-default" onclick="javascript:generateexcel();" >Upload File</button>

<script>

function generateexcel()
{
debugger;
var fileUpload = $("#FileUpload").get(0);
var files = fileUpload.files;
var test = new FormData();
for (var i = 0; i < files.length; i++) {
test.append("FileUpload", files[i]);
}
if (files.length != 0) {
$.ajax({
url: '@Url.Content("../OANDMMaster/importmaterialexcelsheet")',
type: "POST",
contentType: false,
processData: false,
data: test,
// dataType: "json",
success: function (result) {
alert(result);
},
error: function (result) {
alert(result);
}
});
}
}
</script>

Write this following code in controller:
Used Namespace:
Using ClosedXML.Excel;
Using LinqToExcel;

Write this below action method in controller...

public JsonResult importmaterialexcelsheet(HttpPostedFileBase[] FileUpload)
{
List<string> data = new List<string>();
if (FileUpload != null)
{
// tdata.ExecuteCommand("truncate table OtherCompanyAssets");
if (FileUpload[0].ContentType == "application/vnd.ms-excel" || FileUpload[0].ContentType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
{
string filename = FileUpload[0].FileName;
string targetpath = Server.MapPath("~/Content/requestImages/");
FileUpload[0].SaveAs(targetpath + filename);
string pathToExcelFile = targetpath + filename;
var connectionString = "";
if (filename.EndsWith(".xls"))
{
connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=Excel 8.0;", pathToExcelFile);
}
else if (filename.EndsWith(".xlsx"))
{
connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";", pathToExcelFile);
}
var adapter = new OleDbDataAdapter("SELECT * FROM [MAS_MATERIAL$]", connectionString);
var ds = new DataSet();
adapter.Fill(ds, "MaterialTable");
DataTable dtable = ds.Tables["MaterialTable"];
string sheetName = "MAS_MATERIAL";
var excelFile = new ExcelQueryFactory(pathToExcelFile);
var materials = from a in excelFile.Worksheet<MaterialExcel>(sheetName) select a;
foreach (var a in materials)
{
try
{
if (a.GRP_SR_CODE != null && a.SGRP_SR_CODE != null && a.MATERIAL_ID != null && a.MAT_TYPE!=null && a.MEAS_NO!=null && a.DEL_STATUS!=null && a.ALLOW_SERIAL_NO!=null && a.ALLOW_SALES_PRICE!=null)
{
var t = da.savematerial(Convert.ToInt32(a.GRP_SR_CODE),Convert.ToInt32(a.SGRP_SR_CODE),Convert.ToInt32(a.MATERIAL_ID), a.MAT_TYPE, Convert.ToInt32(a.MEAS_NO), a.ALLOW_SERIAL_NO, a.ALLOW_SALES_PRICE);
}
}
catch
{
}
}
//deleting excel file from folder

if ((System.IO.File.Exists(pathToExcelFile)))
{
System.IO.File.Delete(pathToExcelFile);
}
return Json("success", JsonRequestBehavior.AllowGet);
}
else
{
return Json("Only Excel file format is allowed", JsonRequestBehavior.AllowGet);
}
}
else
{
return Json("Please choose Excel file", JsonRequestBehavior.AllowGet);
}
}

In this way the excel file data will uploaded in table.


For download excel sheet Click Here...