0

can we build a solution in jquery where we can export excel data to existing sharepoint list number of column in excel will be 56 or more with 5000 rows , i have to use on JSOM

1

You can read excel in jquery and after that you can store that data into SharePoint using rest api or client context, using client context you can store data in bulk.

Read excel using jQuery:

<script src="jquery-1.10.2.min.js" type="text/javascript"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.7.7/xlsx.core.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/xls/0.7.4-a/xls.core.min.js"></script> 

function ReadExcel() {  
 var regex = /^([a-zA-Z0-9\s_\\.\-:])+(.xlsx|.xls)$/;  
 /*Checks whether the file is a valid excel file*/  
 if (regex.test($("#excelfile").val().toLowerCase())) {  
     var xlsxflag = false; /*Flag for checking whether excel is .xls format or .xlsx format*/  
     if ($("#excelfile").val().toLowerCase().indexOf(".xlsx") > 0) {  
         xlsxflag = true;  
     }  
     /*Checks whether the browser supports HTML5*/  
     if (typeof (FileReader) != "undefined") {  
         var reader = new FileReader();  
         reader.onload = function (e) {  
             var data = e.target.result;  
             /*Converts the excel data in to object*/  
             if (xlsxflag) {  
                 var workbook = XLSX.read(data, { type: 'binary' });  
             }  
             else {  
                 var workbook = XLS.read(data, { type: 'binary' });  
             }  
             /*Gets all the sheetnames of excel in to a variable*/  
             var sheet_name_list = workbook.SheetNames;  

             var cnt = 0; /*This is used for restricting the script to consider only first sheet of excel*/  
             sheet_name_list.forEach(function (y) { /*Iterate through all sheets*/  
                 /*Convert the cell value to Json*/  
                 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) {  
                     SaveData(exceljson);  
                     cnt++;  
                 }  
             });    
         }  
         if (xlsxflag) {/*If excel file is .xlsx extension than creates a Array Buffer from excel*/  
             reader.readAsArrayBuffer($("#excelfile")[0].files[0]);  
         }  
         else {  
             reader.readAsBinaryString($("#excelfile")[0].files[0]);  
         }  
     }  
     else {  
         alert("Sorry! Your browser does not support HTML5!");  
     }  
 }  
 else {  
     alert("Please upload a valid Excel file!");  
 } 
}
function SaveData(exceljson){
//Here you can write save data to SharePoint list logic
}
  • let me give a try thanks for this solution – kumar Sep 20 at 5:59

Not the answer you're looking for? Browse other questions tagged or ask your own question.