Asp.Net mvc read excel file using ExcelDataReader

Reading data from XLSX file using ExcelDataReader in Asp.Net mvc, while uploading excel file using ajax method.

 Asp.Net   
 

Posted: April 22, 2017. | By: mustafa

In this article I will explain how to read excel file as .xlsx in Asp.Net MVC using ExcelDataReader and update in sql database. ExcelDataReader is a fast and lightweight library written in C# for reading Microsoft Excel files('97-2007).

 

Lets start by adding reference to ExcelDataReader libraray in Asp.Net mvc application. Also add reference to SharpZipLib library to enable ExcelReaderFactory to get reader from excel stream.

  AddReferencetoExcelDataReader.png, Asp.Net, Excel, ExcelDataReader,

First define a class file ExcelReader.cs in Asp.Net mvc project.

public class ExcelReader
 {
 string _path;
 Stream _stream;

public ExcelReader(string path)
 {
 _path = path;
 }

public ExcelReader(Stream excel)
 {
 _stream = excel;
 }

public IExcelDataReader getExcelReader()
 {
 // ExcelDataReader works with the binary Excel file, so it needs a FileStream
 // to get started. This is how we avoid dependencies on ACE or Interop:
 FileStream stream = File.Open(_path, FileMode.Open, FileAccess.Read);

// We return the interface, so that
 IExcelDataReader reader = null;
 try
 {
 if (_path.EndsWith(".xls"))
 {
 reader = ExcelReaderFactory.CreateBinaryReader(stream);
 }
 if (_path.EndsWith(".xlsx"))
 {
 reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
 }
 return reader;
 }
 catch (Exception)
 {
 throw;
 }
 }

public IExcelDataReader getExcelReaderStream()
 {
 // ExcelDataReader works with the binary Excel file, so it needs a FileStream
 // to get started. This is how we avoid dependencies on ACE or Interop:
 Stream stream = _stream;

// We return the interface, so that
 IExcelDataReader reader = null;
 try
 {
 //if (_path.EndsWith(".xls"))
 //{
 // reader = ExcelReaderFactory.CreateBinaryReader(stream);
 //}
 //if (_path.EndsWith(".xlsx"))
 //{
 reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
 //}
 return reader;
 }
 catch (Exception)
 {
 throw;
 }
 }

public IEnumerable<string> getWorksheetNames()
 {
 var reader = this.getExcelReader();
 var workbook = reader.AsDataSet();
 var sheets = from DataTable sheet in workbook.Tables select sheet.TableName;
 return sheets;
 }

public IEnumerable<DataRow> getData(string sheet, bool firstRowIsColumnNames = true)
 {
 var reader = this.getExcelReaderStream();
 reader.IsFirstRowAsColumnNames = firstRowIsColumnNames;
 var workSheet = reader.AsDataSet().Tables[sheet];
 //var rows = from DataRow a in workSheet.Rows select a;
 var filteredRows = workSheet.Rows.Cast<DataRow>().Where(row => row.ItemArray.Any(field => !(field is System.DBNull)));//remove blank data row from excel table
 return filteredRows;
 }
 }

 

Now add ExcelUpload() method in our controller. We get Request file check for type ".xlsx" and pass the file input stream to ExcelReader() constructor. Then call excelReader getData() with parameter excel sheet name required. This will return enumerable DataRow we can access rows using column header from which we can create an object and we can later save in our database.

[HttpPost]
 public ActionResult ExcelUpload()
 {
 foreach (string inputTagName in Request.Files)
 {
 HttpPostedFileBase Infile = Request.Files[inputTagName];
 if (Infile.ContentLength > 0 && (Path.GetExtension(Infile.FileName) == ".xls" || Path.GetExtension(Infile.FileName) == ".xlsx" || Path.GetExtension(Infile.FileName) == ".xlsm"))
 {
 List<TestProject.Models.ExcelModel> itemList = new List<TestProject.Models.ExcelModel>();
 //vehicleList = ReadAsDataTable(Infile.InputStream);

var excelReader = new ExcelReader(Infile.InputStream);
 var items = excelReader.getData("Sheet1");

foreach (var v in items)
 {
 TestProject.Models.ExcelModel excelModel = new TestProject.Models.ExcelModel()
 {
 SerialNo = Convert.ToInt32(v["Srno"]),
 FullName = v["FullName"].ToString(),
 Mobile = v["Mobile"].ToString(),
 //Email = v["Email"].ToString(),
 Address = v["Address"].ToString(),
 };

itemList.Add(excelModel);
 }


 if (itemList.Count > 0)
 {
 return Json(new
 {
 Valid = true,
 ErrorMessage = "Excel uploaded successfully."
 });
 }
 else
 {
 return Json(new
 {
 Valid = false,
 ErrorMessage = "Please try again."
 });
 }
 }
 }
 return Json(new
 {
 Valid = false,
 ErrorMessage = "Please try again."
 });
 }

 

Example excel file:

ExcelFile.png, undefined, Excel, ExcelDataReader,

 

Now add html div having excel upload button and file input type.

<h3 style="padding: 5px 0px 10px 50px;">Upload Excel</h3>
 <div>
 <input type="file" id="excelUpload" name="excelUpload" />
 <input type="button" class="frmbutton" value="Upload Excel" id="btnUploadExcel" />
 </div>

 

Add jquery ajax method call to ExcelUpload() defined in controller inside document.ready(). Add enctype as 'multipart/form-data' to allow excel files to upload and assign data as excel file append to FormData.

$("#btnUploadExcel").click(function () {
 var $file = new FormData();
 var files = $("#excelUpload").get(0).files;

// Add the uploaded image content to the form data collection
 if (files.length > 0) {
 $file.append("ExcelFile", files[0]);
 }

$.ajax({
 type: "POST",
 url: "/Message/ExcelUpload",
 data: $file,
 async: false,
 cache: false,
 contentType: false,
 enctype: 'multipart/form-data',
 processData: false,
 error: function (xhr, status, error) {
 alert(error);
 },
 success: function (response) {
 if (!response.Valid) {
 alert(response.ErrorMessage);
 }
 else {
 alert("File uploaded successfully");
 }
 }
 });
 return false;// if it's a link to prevent post
 });

 


related posts

Back to top