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.
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".
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.
Perfect!!!
ReplyDeleteHi Man!
ReplyDeleteFirst of all, this is very superb post!!!
Appreciate if you can share code for SharePoint 2010?
Thanks
awesome
ReplyDeleteis it feasible with morw then 50 columns and 5000 items, Appreciate if you can share your thoughts on this
ReplyDeleteThis is brilliant. Thanks for sharing!
ReplyDeleteThank for sharing. This is very helpful to me
ReplyDeletehi, I attempted to use this code but the 'choose file' button and the 'export to table and SPList' function never appeared. I followed the directions as stated above. Any help is appreciated, thanks
ReplyDeleteGreat post. Is it possible to do this for multiple excel uploads?
ReplyDeleteExcel is a powerful yet easy to use spreadsheet program. In many cases, Excel charts can be created to visually display complex information to the users. Excel 2010 has powerful new features to create impressive charts. ppt translator
ReplyDelete