How to Read/Import Excel Sheet in SharePoint List using SPServices and JQuery.

Recently I passed through an interesting requirement, had to import Excel sheet in SharePoint List.

Let’s Check how does it works.

For any demonstration I use my favorite web part Script Editor with lovely SPServices and JQuery. 

1. First Add a Custom List named it ExcelImport.

2. Create five Columns to the ExcelImport List.

First Name (Rename of Title Column), LastName, Position, Location, Country.

3. Now Next step is to add Script Editor Web Part to page.

4. Add Reference of SPServices and JQuery in Script Editor.

  (For frequent use I would suggest you to add reference in your master page)

5. Paste the below code in Script Editor.

<script src="Your JQuery Reference URL"></script>
<script src="Your SPServices Reference URL"></script>
<script>
var excel;
function GetData(cell,row){
if (window.ActiveXObject) {
try {
excel = new ActiveXObject("Excel.Application");
}
catch (e) {
alert (e.message);
}
}
else {
alert ("Opss... inconvenience caused is deeply regretted. Kindly Upload from IE");// Only work in IE Browser
return;
}

var excel_file = excel.Workbooks.Open("D:\MyExcelSheetName.xlsx");//Read excel from D drive by MyExcelSheetName Name
var sht = excel.Worksheets("Sheet1"); // Reading Sheet1 from Worksheets

for(var i=5; i<i+1; i++){ // will start reading data from 5th row
var FirstName = sht.Cells(i,3).Value; // Start reading First Name from 3rd cell
var LastName = sht.Cells(i,5).Value; // Start reading Last Name from 5th cell
var Position = sht.Cells(i,7).Value; // Start reading Position from 7th cell
var Location = sht.Cells(i,9).value; // Start reading Location from 9th cell
var Country = sht.Cells(i,11).Value; //Start reading Country from 11th cell

if (FirstName == undefined || LastName == undefined || Position == undefined || Location == undefined || Country == undefined){ // will stop reading data when excel data end.
break;
}
$().SPServices({
operation: "UpdateListItems",
webURL: _spPageContextInfo.webAbsoluteUrl,
async: false,
batchCmd: "New",
listName: "ExcelImport",
valuepairs: [["Title",FirstName],["LastName",LastName],["Position",Position],["Location",Location],["Country",Country]],
completefunc: function(xData,status){
}
});
}
alert("Data Imported");
excel_file.Close();

}

$(document).ready(function(){
$("#btnload").click(function(){
GetData(1,1);
});
});
</script>
<input type="button" style="float: left;" id="btnload" value="Import Excel"/>

6.Create your Excel file and save it to your D drive by  MyExcelSheetName.xlsx name.

Make sure your Excel Sheet must be in below format if you are using exactly above code.

7. Click on Import Excel button from your page. And your List will be updated with desired result.

Note : If you are getting Error like “Automation server can't create object”

Enable Allow ActiveX filtering.

8. Verify your List.

Cheers ...

Thanks.

by Amir Reza via Everyone's Blog Posts - SharePoint Community