Hi everyone, I hope everyone is safe!
In this blog, we will create a dynamic application for exporting list items to excel using the XLSX library.
Let's get started.
Screenshot of our application,
![Export SharePoint List Items - JSON to Excel]()
![Export SharePoint List Items - JSON to Excel]()
We will achieve it in 3 simple steps,
Step 1 - Build the front end
I have used bootstrap 4 for the buttons, alert, and cards to display the lists, which is dynamically loaded using SharePoint REST API GET Method.
Refer to the required JS and CSS CDN files, here XLSX CDN is used for exporting excel.
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.16.0/umd/popper.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/js/bootstrap.min.js"></script>
<!-- XLSX Library -->
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.13.1/xlsx.full.min.js"></script>
Step 1(a)
Create the elements,
<body>
<div class="card">
<div class="container">
<h4>Choose a list to export</h4>
<table id="listNamesID">
<!-- Dynamic Content -->
</table>
<div id="exportListDiv">
<button type="button" class="btn btn-primary" id="exportListBtn">Export to Excel</button>
<div class="alert alert-success" id="alert" style="display: none;">
<strong id="alertMsg"></strong>
</div>
</div>
</div>
</div>
</body>
Step 1(b)
Add Style to the Card and the table,
<style>
.card {
box-shadow: 0 4px 8px 0 rgba(0,0,0,0.2);
transition: 0.3s;
width: 40%;
margin: 25px auto;
}
.card:hover {
box-shadow: 0 8px 16px 0 rgba(0,0,0,0.2);
}
.container {
padding: 2px 16px;
}
h4, #exportListDiv, #listNamesID{
text-align: center;
margin: 20px auto;
}
#listNamesID td {
padding: 0px 20px;
}
</style>
Step 2 - On load get List Name using REST API Get Method
In this condition, we will get the lists by defining Base Template is equal to 100 and should display only visible lists in the site content.
function fnGetListNames() {
var htmlContent = '';
var listNamesID = document.getElementById("listNamesID");
$.ajax({
url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/",
type: "GET",
headers: {
"Accept": "application/json;odata=verbose",
},
success: function (data, status, xhr) {
var dataResults = data.d.results;
for (var i = 0; i < dataResults.length; i++) {
if (dataResults[i].BaseTemplate === 100 && dataResults[i].Hidden === false) {
var listTitle = dataResults[i].Title;
htmlContent += '<tr>' + '<td><input type="radio" id="ValueID' + i + '" name="listNames" value="' + listTitle + '"></td><td>' + listTitle + '</td></tr>';
}
}
listNamesID.innerHTML = htmlContent;
},
error: function (xhr, status, error) {
console.log("Failed to Get List Names");
}
});
}
Step 3
On the Export to Excel Button click, call the fnExport2Excel() function,
function fnExport2Excel(selectedValue) {
$.ajax({
url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getByTitle('"+selectedValue+"')/items?",
type: "GET",
headers: {
"Accept": "application/json;odata=verbose"
},
success: function(data, status, xhr) {
var listData = data.d.results;
var fileName = selectedValue+'.xlsx';
var jsonToSheet = XLSX.utils.json_to_sheet(listData);
var excelBook = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(excelBook, jsonToSheet, selectedValue);
XLSX.writeFile(excelBook, fileName);
},
error: function(xhr, status, error) {
console.log("Failed to download");
}
});
}
Hooray! That's it, your excel with list items is ready!
![Export SharePoint List Items - JSON to Excel]()
Note: Get the full code from my GitHub
Happy Learning!