SharePoint Journey: Upload Data from Excel in sharepoint list or library Using Rest Api

Hi All this one of the post is very interesting to upload data in list using Rest Api :

1-ASPX Page:

<asp:Content ContentPlaceHolderId="PlaceHolderAdditionalPageHead" runat="server">

<style>

html, body {

font-family: sans-serif !important;

}

.row

{

margin-left:0px !important;

margin-right:0px !important;

}

#TitlePanelID{

margin-bottom: 0px;}

#pageStatusBar{

display:none!important;

}

.bg-dark{

z-index: 1 !important;

}

#frmProductDetail{

margin-top: -5%;

position: absolute;

}

#TxtCalendarEnd{

min-width: fit-content;

}

/*#btnUpload1{

margin-top: 11%;

}*/

#attachFilesHolder{

margin-top: 2%;

}

.bl-content{

display:none;

}

.bl-footer button:nth-of-type(2){

display:none;

}

.container-fluid{

padding-right: 2%!important;

padding-left: 1%!important;

}

.modalloader

{

position: fixed;

z-index: 999;

height: 100%;

width: 100%;

top: 0;

left: 0;

background-color: Black;

filter: alpha(opacity=60);

opacity: 0.6;

-moz-opacity: 0.8;

}

.center

{

z-index: 1000;

margin: 300px auto;

padding: 10px;

width: 130px;

border-radius: 10px;

filter: alpha(opacity=100);

opacity: 1;

-moz-opacity: 1;

}

.center img

{

margin: -3px;

}

@media (min-width:1200px){.container{max-width:1320px}}

</style>

</asp:Content>

<asp:Content ContentPlaceHolderId="PlaceHolderMiniConsole" runat="server">

<SharePoint:FormComponent TemplateName="WikiMiniConsole" ControlMode="Display" runat="server" id="WikiMiniConsole"/>

</asp:Content>

<asp:Content ContentPlaceHolderId="PlaceHolderLeftActions" runat="server">

<SharePoint:RecentChangesMenu runat="server" id="RecentChanges"/>

</asp:Content>

<asp:Content ContentPlaceHolderId="PlaceHolderMain" runat="server">

</div>

<div>

<div class="trheader">NGO Review System</div>

</div>

<h3 class="panel-title">Bulk Upload Excel

</h3>

</div>

<label for="traveller-name">Download Excel Format</label> <a href="../SiteAssets/NGODetails.xlsx"><span title="Download the format of excel"><i class="fa fa-download fa-2x" aria-hidden="true"></i></span> </a>

</div>

</div>

<button type="button" id="btnUpload1" class="btn btn-primary btn-sm" >Upload</button>

</div>

<br>

</div>

<br>

</div>

<div>

</div>

</asp:Content>

2- JS File:

var pageName = "AdminSection";

var oJS = "";

var vaildColumns = false;

var employeeCode = "";

var loggedInUserEmail = "";

var loggedInUserName ="";

var loggedInBUHead = "";

var loggedInBUEmail ="";

var from = "noreply@india.nec.com";

var countLength = 0;

var checkValidity = true;

var isDateValid = true;

var formattedDate ="";

setInterval(function() {

UpdateFormDigest(_spPageContextInfo.webServerRelativeUrl, _spFormDigestRefreshInterval);

}, 4 * 60000);

$(document).ready(function(){

//AddUserinGroupForSendEmail('ashwani.kumar@india.nec.com');

//AddUserinGroupForSendEmail('Manish.Kumar1@india.nec.com');

//ensureUser('ashwani.kumar');

GetEmployeeDetailofLogUser();

formattedDate= getFormattedDate();

$(".modalloader").hide();

});

$(document).on('click','#btnUpload1',function(){

debugger;

if($("#my_file_input1").val().trim()==""){

$("#my_file_input1").css({"border": "2px solid red"});

blurt("The file upload failed. Please choose a valid file and try again.", "", "success");

return;

}else{

$("#my_file_input1").css('border', '1px solid #ccc');

}

if($("#my_file_input1").val() != ""){

$(".modalloader").show();

setTimeout(function() {

var files=$("#my_file_input1")[0].files;

filePicked1(files[0]);

},50);

setTimeout(function() {

$(".modalloader").hide();

},5000);

}else{

blurt("Please select a file.", "", "success");

return;

}

});

function filePicked1(oEvent) {

// Get The File From The Input

//var oFile = oEvent.target.files[0];

var oFile = oEvent;

var sFilename = oFile.name;

vaildColumns = false;

//Reference the FileUpload element.

//var fileUpload = document.getElementById("fileUpload");

//Validate whether File is valid Excel file.

//var regex = /^([a-zA-Z0-9\s_\\.\-:])+(.xls|.xlsx)$/;

var regex = /^.*\.(xls|xlsx)$/;

if (regex.test(sFilename.toLowerCase())) {

if (typeof (FileReader) != "undefined") {

// Create A File Reader HTML5

var reader = new FileReader();

// Ready The Event For When A File Gets Selected

reader.onload = function(e) {

var data = e.target.result;

var cfb = XLSX.read(data, {

type: 'binary'

});

console.log(cfb);

var oJS = "";

cfb.SheetNames.forEach(function(sheetName) {

if(sheetName.toLowerCase()=="sheet1")

{

// Obtain The Current Row As CSV

var sCSV = XLS.utils.make_csv(cfb.Sheets[sheetName]);

oJS = XLS.utils.sheet_to_json(cfb.Sheets[sheetName]);

console.log(oJS);

var col = [];

var header = sCSV.split(/\n/ig)[0].split(",");

if(header.length == 10){

var headers = ["PurchaseOrder", "VendorName", "Location", "Category", "MaterialDescription", "UserName", "UserEmail", "UserBUHeadName", "UserBUHeadEmail","UserComment"];

for(var i=0; i<header.length; i++){

if(header[i] == headers[i] )

{

//headers.splice( headers.indexOf(header[i]), 1 );

vaildColumns = true;

}

else{

vaildColumns = false;

break;

}

else{

vaildColumns = false;

//break;

}

});

if(vaildColumns == false){

//alert("Please upload a valid excel file in correct format!");

blurt("The file upload failed. Please choose a valid file and try again.", "", "success");

$(".modal").hide();

//break;

$("#my_file_input1").val(null);

return;

} else{

if(oJS.length!=0)

{

for(var i = 0; i< oJS.length; i++){

/////////////////////////////////////////////

/*isDateValid = validateDate(oJS[i].ContractStartDate);

if(isDateValid == false){

//alert("Please enter a valid start date format as dd-mmm-yyyy!");

blurt("Please enter a valid start date format as dd-mmm-yyyy in uploaded excel!", "", "success");

$(".modal").hide();

return;

//break;

}

isDateValid = validateDate(oJS[i].ContractEndDate);

if(isDateValid == false){

//alert("Please enter a valid end date format as dd-mmm-yyyy!");

blurt("Please enter a valid start date format as dd-mmm-yyyy in uploaded excel!", "", "success");

$(".modal").hide();

return;

//break;

}

/////////////////////////////////////////////

var id = checkEmployeeIdExists("SAEmployeesDetail", oJS[i].Title);*/

var id=0;

if(id == 0){

saveDataItems("PurchaseDetails", i, oJS);

}

/*else{

updateDataItems("PurchaseDetails", id, i, oJS);

}*/

}

if(checkValidity == true){

//alert("Saved successfully!");

blurt("Data upload successfully.", "", "success");

$("#my_file_input1").val(null);

$(".modal").hide();

}else{

//alert("Please upload excel in correct format!");

blurt("Please upload excel in correct format.", "", "success");

$("#my_file_input1").val(null);

$(".modal").hide();

}

else

{

blurt("Please do not upload empty excel.", "", "success");

$("#my_file_input1").val(null);

$(".modal").hide();

checkValidity == false;

}

};

// Tell JS To Start Reading The File.. You could delay this if desired

reader.readAsBinaryString(oFile);

}

}else{

blurt("Please upload a valid Excel file.", "", "success");

//alert("Please upload a valid Excel file.");

$(".modal").hide();

$("#my_file_input1").val(null);

return;

}

function validateDate(date) {

var isValid = IsValidDateFormat(date);

return isValid;

}

function capitalize(s){

if (typeof s !== 'string') return ''

return s.charAt(0).toUpperCase() + s.slice(1)

}

function IsValidDateFormat(myDate) {

return filter.test(myDate);

}

function saveDataItems(listName, index, data){

AddUserinGroupForSendEmail(data[index].UserEmail);

var userNameInList = data[index].UserName;

var buHeadUserNameInList = data[index].UserBUHeadName;

if(userNameInList != null && userNameInList != ""){

userNameInList = capitalize((userNameInList).toString());

}

if(buHeadUserNameInList != null && buHeadUserNameInList != ""){

buHeadUserNameInList = capitalize((buHeadUserNameInList).toString());

}

var item = "";

if(listName == "PurchaseDetails"){

item = {

"__metadata": {

"type": "SP.Data." + listName + "ListItem"

"Title": loggedInUserName,

"LoggedInUserEmail":loggedInUserEmail,

"FeedbackInitiated":"No",

"PurchaseOrder": data[index].PurchaseOrder,

"VendorName": data[index].VendorName,

"Location": data[index].Location,

"MaterialDescription": data[index].MaterialDescription,

"UserName": userNameInList,

"UserEmailId":data[index].UserEmail,

"Category": data[index].Category,

"UserComment": data[index].UserComment,

"FdbkInitiatedDate": formattedDate.trim(),

"BUHeadName": loggedInBUHead,

"BUHeadEmail": loggedInBUEmail,

"UserBUHeadName": buHeadUserNameInList,

"UserBUHeadEmail": data[index].UserBUHeadEmail,

"FdbkInitiatedMailSent": "No"

};

}

$.ajax({

url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle('" + listName + "')/items",

type: "POST",

async: false,

contentType: "application/json;odata=verbose",

data: JSON.stringify(item),

headers: {

"Accept": "application/json;odata=verbose",

"X-RequestDigest": $("#__REQUESTDIGEST").val()

success: function(data) {

//var userFDBKIDId = data.d.ID;

//body = "<p>Dear " +data.d.UserName+ ",</p><p><a href='"+_spPageContextInfo.webAbsoluteUrl+"/SitePages/FeedbackDetails.aspx?FDBKID=" + userFDBKIDId + "''>Click Here</a> to fill the feedback form for Purchase Order.</p><p style='font-weight: bold; color: rgb(36, 36, 109);'>Regards,<br>Purchase Team</p>";

//subject = "Give your valuable feedback!";

//cc = loggedInUserEmail;

//sendEmail(from, data.d.UserEmailId, loggedInUserEmail, body, subject);

checkValidity = true;

error: function(data) {

debugger;

//alert(data);

checkValidity = false;

//saveErrorList(pageName, loggedInUserEmail, employeeCode, "saveDataItems()", data.responseJSON.error.code);

}

});

}

function AddUserinGroupForSendEmail(UserLoginName)

{

//: 'i:0#.w|SharePointHOL\\Priyaranjan'

var newUserUrl = _spPageContextInfo.webAbsoluteUrl + "/_api/web/sitegroups/getbyname('SendEmailProcurementReviewSystem')/users";

var metadata = {

__metadata: {

'type': 'SP.User'

LoginName: 'i:0#.w|'+UserLoginName+''

//Email: UserLoginName

};

addUserInGroup(newUserUrl, metadata,UserLoginName)

}

function addUserInGroup(newUserUrl, metadata,emailId) {

$.ajax({

url:newUserUrl,

type: "POST",

async:false,

headers: {

"Accept": "application/json;odata=verbose",

"Content-Type": "application/json;odata=verbose",

"X-RequestDigest": $("#__REQUESTDIGEST").val(),

data: JSON.stringify(metadata),

success: function(data) {

console.log(data);

console.log("New User has been successfully to the SharePoint Group.");

//GetAccountName(emailId);

error: function(data) {

debugger;

alert(JSON.stringify(error));

}

});

}

function GetAccountName(WorkEmail){

$.ajax({

url:"https://kyoyu.nectechnologies.in/_api/Web/SiteUsers?$filter=Email eq '"+WorkEmail+"'",

method: "GET",

async: false,

headers: { "Accept": "application/json; odata=verbose" },

success: function (data) {

debugger;

var AccName=data.d.results[0].LoginName;

//ensureUser(AccName.split('\')[1]);

error: function (data) {

//window[alertType1]("Error: "+ data);

}

});

}

function GetEmployeeDetailofLogUser()

{

debugger;

var firstName = "";

var lastName = "";

$.ajax({

url: _spPageContextInfo.webAbsoluteUrl + "/_api/SP.UserProfiles.PeopleManager/GetMyProperties?$select=UserProfileProperties",

async: false,

headers: { Accept: "application/json;odata=verbose" },

success: function (data) {

debugger;

var properties = data.d.UserProfileProperties.results;

for (var i = 0; i < properties.length; i++)

{

var property = properties[i].Key;

if(properties[i].Key=="WorkEmail")

{

loggedInUserEmail = properties[i].Value;

//loggedInUserEmail = "";

}

if(properties[i].Key=="FirstName"){

firstName = properties[i].Value;

}

if(properties[i].Key=="LastName"){

lastName = properties[i].Value;

}

if(properties[i].Key=="BUheadName"){

loggedInBUHead= properties[i].Value;

}

if(properties[i].Key=="BUHeadEmail"){

loggedInBUEmail= properties[i].Value;

}

loggedInUserName = firstName + " " + lastName;

error: function (jQxhr, errorCode, errorThrown)

{

//alert(errorThrown);

//saveErrorList(pageName, loggedInUserEmail, "GetEmployeeDetailofLogUser()", errorThrown);

}

});

}

function getFormattedDate(){

var today = new Date();

var date = (today.getFullYear()) + '/' + ("0" + (today.getMonth() + 1)).slice(-2) + '/' + ("0" + today.getDate()).slice(-2);

return date;

}

//to ensure users before sending mail to them.

function ensureUser(accountName)

{

var endpointUrl = _spPageContextInfo.webAbsoluteUrl + "/_api/web/ensureUser('" + encodeURIComponent(accountName) + "')";

return $.ajax({

url: endpointUrl,

type: "POST",

contentType: "application/json;odata=verbose",

headers: {

"Accept": "application/json;odata=verbose",

"X-RequestDigest": $("#__REQUESTDIGEST").val()

}

});

}

//send email

function sendEmail(from, to, cc, body, subject) {

var appWebUrl = window.location.protocol + "//" + window.location.host

+ _spPageContextInfo.webServerRelativeUrl;

var hostUrl = _spPageContextInfo.siteAbsoluteUrl;

var constructedURL = appWebUrl + "/_api/SP.Utilities.Utility.SendEmail";

var formDigest = document.getElementById("__REQUESTDIGEST").value;

$.ajax({

contentType: 'application/json',

url: constructedURL ,

async: false,

type: 'POST',

data: JSON.stringify({

'properties': {

'__metadata': {

'type': 'SP.Utilities.EmailProperties'

'From': from,

'To': {

'results': [to]

'CC': {

'results': [cc]

'Subject': subject,

'Body': body

}

}),

headers: {

"Accept": "application/json;odata=verbose",

"content-type": "application/json;odata=verbose",

"X-RequestDigest": formDigest

success: function(data) {

//alert("mail sent");

console.log("mail sent");

error: function(err) {

debugger;

//alert("mail not sent");

//console.log(err);

saveErrorList(pageName, loggedInUserEmail, "sendEmail()", err);

}

});

}

Search This Blog

Friday, December 11, 2020

Upload Data from Excel in sharepoint list or library Using Rest Api

No comments:

Post a Comment

My Latest Top 10 Post Of Ram Share Point Journey With Details