I recently worked on a project with a feature requirement to allow bulk import of data from Microsoft Excel files. My choice of framework to use would have been either Open XML SDK or LinqToExcel because I have used both libraries in the various projects in the past and they performed well. But common we are in 2016, I wanted to try out a new library. I asked a programmer friend, Ahmed Opeyemi if he has used any library lately and he recommended SheetJs. I decided to do some check on the library to see if it will meet my requirement.
SheetJS
After reading the library’s online documentation on Github, I like the fact that it can convert the data extracted from the excel file to CSV or JSON or even generate a list of formulae from the excel file being parsed. The generated data that can later be uploaded to the server by an Ajax Post. Also, since the extraction and JSON conversion is done directly on the user's browser it frees the server of the processing that hitherto would have been done on it.
Building the application
Navigate to https://github.com/SheetJS/js-xlsx to download the library and include the xlsx.core js library in the view where you want the data upload feature
<script src="~/js/xlsx.core.min.js"> </script>
I decided to handle the onchange event of the html file input so that whenever the user selects the excel file, the data contained therein can be extracted and posted to the server through.
The code snippet for performing the excel file parsing and uploading it to the server is as shown below
<script>
var X = XLSX;
var fileUpload = document.getElementById('fileUpload');
function uploadFile(e) {
var files = e.target.files;
var f = files[0];
{
var reader = new FileReader();
var name = f.name;
reader.onload = function (e) {
var data = e.target.result;
var workbook = XLSX.read(data, { type: 'binary' });
var result = {};
workbook.SheetNames.forEach(function (sheetName) {
var roa = X.utils.sheet_to_row_object_array(workbook.Sheets[sheetName]);
if (roa.length > 0) {
result[sheetName] = roa;
}
});
var output = JSON.stringify(result, 2, 2);
jQuery.ajax({
type: "POST",
url: "@Url.Action("UploadData", "MyController")",
dataType: "json",
data: {
dataToUpload: output
},
success: function (successMsg) {
alert(successMsg.records);
}
});
}
reader.readAsBinaryString(f);
}
}
if (fileUpload.addEventListener)
fileUpload.addEventListener('change', uploadFile, false);
</script>
The portion of codes below does the magic of extracting the data from the excel file.
var workbook = XLSX.read(data, { type: 'binary' });
var result = {};
workbook.SheetNames.forEach(function (sheetName) {
var roa = X.utils.sheet_to_row_object_array(workbook.Sheets[sheetName]);
if (roa.length > 0) {
result[sheetName] = roa;
}
});
And to deserialize the JSON data to a list of .net object , I use the popular Json.NET library. The code of the ActionResult method in the Controller class is below
[HttpPost]
public ActionResult DataUpload(FormCollection formCollection)
{
string dataToUpload = formCollection["dataToUpload"];
int recordsUploaded = 0;
GroupDTO groupDTO = JsonConvert.DeserializeObject(dataToUpload);
// do something with the data
return Json(new { records = string.Concat(“Total records”, recordsUploaded)});
}
The GroupDTO class definition is below
public class GroupDTO
{
public List Data { get; set; }
public long ChurchId { get; set; }
}
public class GroupJson
{
public string Name { get; set; }
public string Description { get; set; }
}
And that is it, you have the data extracted and uploaded with little work at the server end.