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

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.

5 Comments

Join the conversation! Your thoughts help the community grow.

Hameedullah Salik5y ago
The view has made, which is the method for this view ?
Sourav Kumar Das6y ago
Nice and useful article.
Rushi Mehta6y ago
Thanks for sharing. Nice technical stuff
Prabakaran M6y ago
Nice Blog Thanks for sharing
Ankur Mistry6y ago
Helpful stuff thank you for sharing