Introduction
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.