Read Excel Data and Convert to DataTable without using OLE DB in C#

This post shows a useful technique to convert Excel file data to a DataTable. There are many ways available to convert excel file to DataTable, I found this is the best approach to do so.

Introduction

 

There are many ways available to convert excel file to DataTable, I found this is the best approach to do so. 

 

In your controller, you can add an action method UploadRewardsMembership(), which will read the excel file and convert the content into a data table. Then form the UploadRewardsMembership method, you can call the repository method InsertFCReward() to insert the data to the database.

public ActionResult UploadRewardsMembership()
{
   try
   {
      HttpPostedFileBase fileBase;
      DataTable dataTable = new DataTable();
      if(Request.Files.Count > 0)
      {
         foreach (string file in Request.Files)
            {
               fileBase = Request.Files[file] as HttpPostedFileBase;
               if (fileBase != null && fileBase.ContentLength > 0)
               {
                  Stream stream = fileBase.InputStream;
                  IExcelDataReader reader = null;
                  if (fileBase.FileName.EndsWith(".xls"))
                  {
                     reader = ExcelReaderFactory.CreateBinaryReader(stream);
                  }
                  else if (fileBase.FileName.EndsWith(".xlsx"))
                  {
                     reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
                  }
                  var result = reader.AsDataSet(new ExcelDataSetConfiguration()
                  {
                     ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
                     {
                        UseHeaderRow = true
                     }
                  });
                  dataTable = result.Tables[0];
                  int resultCount = InsertFCReward(dataTable);
                  if (resultCount == -1)
                  {
                     ViewBag.FCRewardMessage = fileBase.FileName + " is not in correct format.";
                     break;
                  }
                  reader.Close();
            }
         }
      }
      else
      {
         ViewBag.FCRewardMessage = "Please select a file for upload.";
      }
      var admin = new Admin
      {
         Page = new AdminPage()
         {
            CurrentPage = 1,
            PageSize = 50,
         },
      };
      admin.RewardMemberships = GetRewardsMembershipDetails(admin.Page);
      return PartialView("_MembershipGrid", admin);
   }
   catch(Exception ex)
   {
      log.Error("UploadRewardsMembership" + string.Concat(DateTime.Now.ToString(CultureInfo.InvariantCulture), " ", ex.Message), ex);
      return base.DisplaySessionError();
   }
}

In repository, you can define InsertFCReward(), which will accept a data table. By using entity frame work, you can push the data to the data base. This method will return, the number of records inserted into the DB.

public int InsertFCReward(DataTable importdt)
{
int insertCount = 0;
DataColumnCollection columns = importdt.Columns;
if (!(columns.Count == 9 && columns.Contains("FirstName") && columns.Contains("LastName") && columns.Contains("Email") && columns.Contains("IceMemberId")
&& columns.Contains("expiration") && columns.Contains("created") && columns.Contains("Phone") && columns.Contains("Lead Source")
&& columns.Contains("Member Class")))
{
return -1;
}

using (var context = new FCRewardsMebershipEntities())
{
for (int i = 0; i < importdt.Rows.Count; i++)
{
   FCReward fCReward = new FCReward();
   fCReward.FirstName = importdt.Rows[i]["FirstName"].ToString();
   fCReward.LastName = importdt.Rows[i]["LastName"].ToString();
   fCReward.Email = importdt.Rows[i]["Email"].ToString();
   fCReward.MemberID = importdt.Rows[i]["IceMemberId"].ToString();
   fCReward.Expiration = Convert.ToDateTime(importdt.Rows[i]["expiration"]);
   fCReward.CreatedDate = DateTime.Now;
   fCReward.CreatedBy = "RedDox";
   fCReward.Phone = importdt.Rows[i]["Phone"].ToString();
   fCReward.LeadSource = importdt.Rows[i]["Lead Source"].ToString();
   fCReward.MemberClass = importdt.Rows[i]["Member Class"].ToString();

   context.FCRewards.Add(fCReward);
}
insertCount = context.SaveChanges();
}
return insertCount;
}

Add a script tag, where the js method uploadFCReward() will be defined. It will call the UploadRewardsMembership() method of Admin controller and will bind a partial view with the data inserted to the data base.

<script type="text/javascript">
function uploadFCReward(e) {
var fileUpload = $("#postedFile").get(0);
var files = fileUpload.files;
var fileData = new FormData();
for (var i = 0; i < files.length; i++) {
fileData.append(files[i].name, files[i]);
}
$.ajax({
url: '@Url.Action("UploadRewardsMembership", "Admin")',
type: "POST",
contentType: false,
processData: false,
data: fileData,
success: function (data) {
$('#postedFile').val('');
$('#MembershipGrid').html(data);
console.log("success");
},
error: function (e) {
console.log(e);
$('#MembershipGrid').html('');
},
});
}
</script>

In view, one file upload field with a upload button will be there. On click of Upload button, uploadFCReward() method will be invoked.

@using (Html.BeginForm("GetRewardsMembership", "Admin", FormMethod.Post, new { enctype = "multipart/form-data", id = "GetRewardsMembershipForm" }))
{
   @Html.AntiForgeryToken()
   @Html.ValidationSummary()
   <div class="form-horizontal">
   <hr />
   @Html.ValidationSummary(true, "", new { @class = "text-danger" })
   <div class="form-group">
   @Html.LabelFor(model => model.FileModel.files, htmlAttributes: new { @class = "control-label col-md-2" })
   <div class="col-lg-3 col-md-3 col-xs-3">
   @Html.TextBoxFor(model => model.FileModel.files, "", new { @class = "form-control", @type = "file", @multiple = "multiple", id = "postedFile"    })
   @Html.ValidationMessageFor(model => model.FileModel.files, "", new { @class = "text-danger" })
   </div>
   <div class="col-lg-3 col-md-3 col-xs-3">
   <input type="button" onclick="return uploadFCReward(event);" title="Upload" value="Upload" class="btn btn-default btn-sm" />
   </div>
   </div>
   </div>
}

The reason why I chose this way is if you want to use OLEDB, then you have to install that in your machine. If you want to publish it then in the remote system, OLEDB has to be installed as well.