In this blog, we will how to export table data into an xlsx file with a button click. To achieve this I have created a simple Java web application that displays user data in table format and a download button below the table when the user will click on the button all the table data will be converted into excel format and downloaded into an xlsx file.
Download table data into xlsx format from the JSP page
Create a JavaScript function “tableToExcel”, Which will call on the click of the download button that defines the data conversion and file formats.
JS
<script>
var tableToExcel = (function() {
var uri = 'data:application/vnd.ms-excel;base64,'
, template = '<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"><head><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>{worksheet}</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head><body><table>{table}</table></body></html>'
, base64 = function(s) { return window.btoa(unescape(encodeURIComponent(s))) }
, format = function(s, c) { return s.replace(/{(\w+)}/g, function(m, p) { return c[p]; }) }
return function(table, name) {
if (!table.nodeType) table = document.getElementById(table)
var ctx = {worksheet: name || 'Worksheet', table: table.innerHTML}
window.location.href = uri + base64(format(template, ctx))
}
})()
</script>
Below is the JSP page that contains the table data, as well as the important thing, is the table id =”testTable” make sure you also have to current id in the table tag. the same id will be mentioned in the download button.
emplist.jsp
<table id="testTable" class="table">
<thead>
<tr>
<th scope="col">empNumber</th>
<th scope="col">FirstName</th>
<th scope="col">LastName</th>
<th scope="col">PhoneNumber</th>
<th scope="col">Position</th>
<th scope="col">Email</th>
<th scope="col">Action</th>
</tr>
</thead>
<tbody>
<c:forEach items="${emplist}" var="list" varStatus="u">
<tr>
<c:choose>
<c:when test="${list.empNumber<10}">
<th scope="row">00${list.empNumber}</th>
</c:when>
<c:when test="${list.empNumber>=10 && list.empNumber<100}">
<th scope="row">0${list.empNumber}</th>
</c:when>
<c:otherwise>
<th scope="row">${list.empNumber}</th>
</c:otherwise>
</c:choose>
<td>${list.firstName}</td>
<td>${list.lastName}</td>
<td>${list.phoneNumber}</td>
<td>${list.position}</td>
<td>${list.email}</td>
<td>
<a href="${pageContext.request.contextPath}/empEdit?id=${list.id}">Edit</a>
<a href="${pageContext.request.contextPath}/empDelete?id=${list.id}">Delete</a>
</td>
</tr>
</c:forEach>
</tbody>
</table>
</sf:form>
<input type="button" onclick="tableToExcel('testTable', 'codebun Example Table')" value="Download table into xlsx">
Output:
