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["Number"] != null && excelRow["Name"].toString() != "") {
getEmployeeDetailsByEmployeeID(excelRow["Number"].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('BIM%20search')/items?$filter=Number eq '" + EmployeeIDValue + "'&$select=ID,Name", 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) {
var recordID;
/* $.ajax
({
url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/GetByTitle('BIM%20Search')/items(" + itemID + ")",
type: "POST",
data: JSON.stringify
({
__metadata:
{
type: "SP.Data.BIM%20searchListItem"
},
Name: excelRow["Name"]
}),
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");
}
}); */
$().SPServices({
operation: "UpdateListItems",
async: false,
batchCmd: "Update",
listName: "BIM Search",
ID:itemID,
valuepairs: [["Name", "Test2"],["test", "Test2"]],
completefunc: function (xData, Status) {
alert("updated");
}
});
}
function createEmployeeDetailsListItem(excelRow) {
/* $.ajax
({
url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/GetByTitle('BIM%20Search')/items",
type: "POST",
data: JSON.stringify
({
__metadata:
{
type: "SP.Data.BIM%20searchListItem"
},
Name: excelRow["Name"]
}),
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");
}
}); */
/* $.ajax
({
url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/GetByTitle('BIM%20Search')/items",
type: "POST",
cache: true,
data: JSON.stringify
({
__metadata:
{
type: "SP.Data.TestListItem"
},
Name: excelRow["Name"],
Description: excelRow["Description"]
}),
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");
}
}); */
if(excelRow["Business Owner"] !=""){
alert(excelRow["Business Owner"]);
var name = excelRow["Business Owner"];
$().SPServices({
operation: "GetUserInfo",
async: false,
userLoginName: name,
completefunc: function (xData, Status) {
$(xData.responseXML).find("User").each(function() {
userID = $(this).attr("ID");
alert(userID);
});
}
});
}
$().SPServices({
operation: "UpdateListItems",
async: false,
batchCmd: "New",
listName: "BIM Search",
valuepairs: [["Name", excelRow["Name"]],["Description",excelRow[" Description"]],["test",excelRow[" Description"]],["Number",excelRow["Number"]],["BusinessOwner", userID ]],
completefunc: function (xData, Status) {
if(Status == "success"){
alert("Data Saved! and Please check your List");
}
else{
console.log("error to add new item number:"+excelRow["Number"])
}
}
});
}
