Reading an excel file using HTML 5 and jQuery and save in SharePoint list
Reference
https://github.com/SheetJS/js-xlsx
https://github.com/SheetJS/js-xls
Step 1: First create a SharePoint list and give list name as "EmployeeDetails".
Step 2: Write a below code in HTML file and name it as "ExcelToSPListItems.html". Upload this file into SharePoint assets library. This HTML code file will refer in Content Editor WebPart so please copy file url after upload into assets library.
The below code first will check "Employee ID" in list, if "Employee ID" exist then that item will update current item else new item will create in SharePoint list.
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>EmployeeDetails</title>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.7.7/xlsx.core.min.js" type="text/javascript"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/xls/0.7.4-a/xls.core.min.js" type="text/javascript"></script>
<script src="https://code.jquery.com/jquery-3.2.1.min.js" type="text/javascript"></script>
<style>
table {
margin-top: 20px;
border-collapse: collapse;
display: none;
}
table th, table td {
border: 1px solid #000;
padding: 5px 10px;
}
</style>
<script type="text/javascript">
function ExportToTable() {
$('#loading').show();
var regex = /^([a-zA-Z0-9\s_\\.\-:])+(.xlsx|.xls)$/;
if (regex.test($("#excelfile").val().toLowerCase())) {
var xlsxflag = false;
if ($("#excelfile").val().toLowerCase().indexOf(".xlsx") > 0) {
xlsxflag = true;
}
if (typeof (FileReader) != "undefined") {
var reader = new FileReader();
reader.onload = function (e) {
var data = e.target.result;
if (xlsxflag) {
var workbook = XLSX.read(data, { type: 'binary' });
}
else {
var workbook = XLS.read(data, { type: 'binary' });
}
var sheet_name_list = workbook.SheetNames;
var cnt = 0;
sheet_name_list.forEach(function (y) {
if (xlsxflag) {
var exceljson = XLSX.utils.sheet_to_json(workbook.Sheets[y]);
}
else {
var exceljson = XLS.utils.sheet_to_row_object_array(workbook.Sheets[y]);
}
if (exceljson.length > 0 && cnt == 0) {
BindTable(exceljson, '#exceltable');
exceljson.forEach(function (excelRow) {
if (excelRow != null && Object.keys(excelRow).length > 0 && excelRow["Employee ID"] != null && excelRow["Employee ID"].toString() != "") {
getEmployeeDetailsByEmployeeID(excelRow["Employee ID"].toString().trim(), excelRow);
}
});
cnt++;
}
});
$('#loading').hide();
$('#exceltable').show();
}
if (xlsxflag) {
reader.readAsArrayBuffer($("#excelfile")[0].files[0]);
}
else {
reader.readAsBinaryString($("#excelfile")[0].files[0]);
}
}
else {
$('#loading').hide();
alert("Sorry! Your browser does not support HTML5!");
}
}
else {
$('#loading').hide();
alert("Please upload a valid Excel file!");
}
}
function BindTable(jsondata, tableid) {
var columns = BindTableHeader(jsondata, tableid);
for (var i = 0; i < jsondata.length; i++) {
var row$ = $('<tr/>');
for (var colIndex = 0; colIndex < columns.length; colIndex++) {
var cellValue = jsondata[i][columns[colIndex]];
if (cellValue == null)
cellValue = "";
row$.append($('<td/>').html(cellValue));
}
$(tableid).append(row$);
}
}
function BindTableHeader(jsondata, tableid) {
var columnSet = [];
var headerTr$ = $('<tr/>');
for (var i = 0; i < jsondata.length; i++) {
var rowHash = jsondata[i];
for (var key in rowHash) {
if (rowHash.hasOwnProperty(key)) {
if ($.inArray(key, columnSet) == -1) {
columnSet.push(key);
headerTr$.append($('<th/>').html(key));
}
}
}
}
$(tableid).append(headerTr$);
return columnSet;
}
function getEmployeeDetailsByEmployeeID(EmployeeIDValue, excelRow) {
var objHeaders = {
type: "GET",
headers: {
"accept": "application/json;odata=verbose"
},
async: false,
mode: 'cors',
cache: 'no-cache',
credentials: 'include'
}
fetch(_spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/GetByTitle('Employee Details')/items?$filter=Employee_x0020_ID eq '" + EmployeeIDValue + "'&$select=ID,Title,Employee_x0020_ID,Address&$orderby=ID", objHeaders)
.then(function (response) {
return response.json()
})
.then(function (json) {
var results = json.d.results;
if (results.length > 0) {
for (i in results) {
updateEmployeeDetailsListItem(results[i].ID, excelRow);
}
}
else {
createEmployeeDetailsListItem(excelRow);
}
})
.catch(function (ex) {
console.log("error");
});
}
function updateEmployeeDetailsListItem(itemID, excelRow) {
$.ajax
({
url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/GetByTitle('Employee Details')/items(" + itemID + ")",
type: "POST",
data: JSON.stringify
({
__metadata:
{
type: "SP.Data.EmployeeDetailsListItem"
},
Employee_x0020_ID: excelRow["Employee ID"],
Address: excelRow["Address"]
}),
headers:
{
"Accept": "application/json;odata=verbose",
"Content-Type": "application/json;odata=verbose",
"X-RequestDigest": $("#__REQUESTDIGEST").val(),
"IF-MATCH": "*",
"X-HTTP-Method": "MERGE"
},
async: false,
success: function (data, status, xhr) {
console.log("success");
},
error: function (xhr, status, error) {
console.log("errro");
}
});
}
function createEmployeeDetailsListItem(excelRow) {
$.ajax
({
url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/GetByTitle('Employee Details')/items",
type: "POST",
data: JSON.stringify
({
__metadata:
{
type: "SP.Data.EmployeeDetailsListItem"
},
Title: excelRow["Employee Name"],
Employee_x0020_ID: excelRow["Employee ID"],
Address: excelRow["Address"]
}),
headers:
{
"Accept": "application/json;odata=verbose",
"Content-Type": "application/json;odata=verbose",
"X-RequestDigest": $("#__REQUESTDIGEST").val(),
"X-HTTP-Method": "POST"
},
success: function (data, status, xhr) {
console.log("success");
},
error: function (xhr, status, error) {
console.log("error");
}
});
}
</script>
</head>
<body>
<input type="file" id="excelfile" />
<input type="button" id="viewfile" value="Export To Table and SPList" onclick="ExportToTable()" /> <br />
Reference <br />
<a href="https://github.com/SheetJS/js-xlsx">https://github.com/SheetJS/js-xlsx</a> <br />
<a href="https://github.com/SheetJS/js-xls">https://github.com/SheetJS/js-xlsx</a> <br />
<p id="loading" style="display:none;">Loading please wait. <i class="fa fa-spinner fa-spin" style="font-size:24px;"></i> </p> <br />
<table id="exceltable"></table> <br />
</body>
</html>
Now upload above "ExcelToSPListItems.html" file into site assets library.
Step 3: Create an webpart page and save in Page library. Now edit this page and add Content Editor Webpart.
Step 4: To test the application first create an excel file with below values and save as "EmployeeDetails.xlsx".
Step 5: Now test the application. upload excel file and check in SharePoint list. Excel list must created or updated.
