Menu
I am working on a dashboard app and I would like to implement "download table as .xls" feature.
On this link you can see how the table looks like dashboard
I have found a library which also includes the tutorial that explains the set up. As you can see in the code below I have done more or less everything like it was explained. However it does not work and for some reason the table will not be exported.
As you can see, I have included jquery.table2excel.js in the resources together with all other resources which are used for this page. I have also checked if the .js file is available after the page is loaded and it also looks good.
I have also tried this
$(function () {
document.getElementById('btnExport').addEventListener("click", function () {
document.getElementById('myTable').table2excel({
exclude: ".noExl",
name: "Excel Document Name",
filename: "myFileName"
});
});
});
but it also does not look well and when I execute the function I get this message in the debugg console
TypeError: document.getElementById(...).table2excel is not a function
This is how my index.jsp looks like at the moment
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%@taglib uri="http://www.springframework.org/tags/form" prefix="form"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html lang="en">
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>KPI Admin</title>
<link href="<c:url value="/resources/css/bootstrap.min.css" />" rel="stylesheet">
<link href="<c:url value="/resources/css/addition.css" />" rel="stylesheet">
<link rel="stylesheet" href="http://code.jquery.com/ui/1.9.2/themes/base/jquery-ui.css" />
<script src="<c:url value="http://ajax.googleapis.com/ajax/libs/jquery/2.0.0/jquery.min.js" />"></script>
<script src="<c:url value="http://code.jquery.com/ui/1.9.2/jquery-ui.js" />"></script>
<script src="<c:url value="/resources/js/bootstrap.js" />"></script>
<script src="<c:url value="/resources/js/addition.js" />"></script>
<script src="<c:url value="/resources/js/jquery.table2excel.js" />"></script>
<script>
$(function () {
document.getElementById('btnExport').addEventListener("click", function () {
$(".table2excel").table2excel({
exclude: ".noExl",
name: "Excel Document Name",
filename: "myFileName"
});
});
});
</script>
</head>
<body>
<nav class="navbar navbar-inverse navbar-fixed-top">
<!-- /.nav -->
</nav>
<div class="container">
<div class="starter-template">
<ul class="nav nav-tabs">
<!-- /.tabs -->
</ul>
<!-- Tab panes -->
<div class="tab-content">
<div class="tab-pane fade in active" id="A">
<form:form action="/KPIAdmin/kpis" method="get">
<div class="row form-inline">
<div class="form-group">
<label for="date">Date</label>
<input id="startDatePicker" type="text" class="form-control" name="date" value="${date}" >
</div>
<button type="submit" class="btn btn-default">Submit</button>
</div>
<br>
<div class="table-responsive">
<table id="myTable" class="table table-bordered table2excel">
<thead>
<tr>
<td>Name</td>
<td>Last import</td>
<td>Last value</td>
<td colspan="4">Values</td>
<td colspan="3">Targets</td>
<td colspan="3">Score</td>
<td>Action</td>
</tr>
</thead>
<tr>
<td></td>
<td></td>
<td></td>
<td class="text-center" style="font-weight: 700;">DTD</td>
<td class="text-center" style="font-weight: 700;">WTD</td>
<td class="text-center" style="font-weight: 700;">MTD</td>
<td class="text-center" style="font-weight: 700;">YTD</td>
<td class="text-center" style="font-weight: 700;">0</td>
<td class="text-center" style="font-weight: 700;">100</td>
<td class="text-center" style="font-weight: 700;">150</td>
<td class="text-center" style="font-weight: 700;">WTD</td>
<td class="text-center" style="font-weight: 700;">MTD</td>
<td class="text-center" style="font-weight: 700;">YTD</td>
<td></td>
</tr>
<c:forEach var="row" items="${rows}" varStatus="loop">
<!-- /.loop that creates the table -->
</c:forEach>
<tr>
<!-- /. last row mean -->
</tr>
</table>
</div>
</form:form>
<button id="btnExport" class="btn btn-default">Export as .xls</button>
</div>
<div class="tab-pane fade" id="B"> <!-- Content inside tab B --> </div>
<div class="tab-pane fade" id="C"> <!-- Content inside tab C --> </div>
</div>
</div>
</div><!-- /.container -->
I am realy not sure what could cause this problem or do I have an error in the jQuery syntax. It could be also that the lib is not inportet correctly within .jsp page but this is the way which was working for me in the past. I guess that I am using right jQuery version since jQuery datePicker works fine.
If you are able to see what could case this issue, please help me to fix it. If you have any better idea how to export a table as an excel file please suggest.
Thx in advance.
Edit 1:
I have changed the function and it looks like the code I have posted below. If I execute console.log("exporting..."); before or after $(".table2excel").table2excel({ ... }); "exporing..." will be printed out in the console. Obviously that excludes jQuery as a potential cause of the problem.
<script>
$(function () {
$('#btnExport').click(function () {
console.log("exporting...");
$(".table2excel").table2excel({
exclude: ".noExl",
name: "Excel Document Name",
filename: "myFileName"
});
});
});
</script>
Edit 2:
Since I was not able to fix this, I have tried to try something new. I have found this solutions and it works but still not as I would really like to, so I hope that you can help me to improve it.
My table looks like this
and this is what I get as a result
First of all it really looks strange without excel grid, do you have an Idea why the file is exported without it and how can I add it?
Second I would like to remove the column after YTD where the additional infos are presented. Is it somehow possible to adjust the tab_text.replace(...) below in order to achive this
in html it looks like this
</td><td width='20px'>
<a class='infobox' href=''>
<img src='img/info.jpg' alt='info' width='18' height='18'>
<span> Service Engineer: ... <br>
Datasource: ...
</span>
</a>
</tr>
JavaScript function looks like this
function exportExcelReport(tblId) {
var tab_text = "<table border='2px'><tr>";
var table = document.getElementById(tblId);
var style;
for (var j = 0; j < table.rows.length; j++) {
style = table.rows[j].className.split(" ");
if (style.length < 2)
tab_text = tab_text + table.rows[j].innerHTML + "</tr>";
}
tab_text = tab_text + "</table>";
tab_text = tab_text.replace(/<a[^>]*>|<\/a>/g, "");
tab_text = tab_text.replace(/<img[^>]*>/gi, "");
tab_text = tab_text.replace(/<input[^>]*>|<\/input>/gi, "");
return window.open('data:application/vnd.ms-excel,' + encodeURIComponent(tab_text));
}
Thx!
289
add this before return
tab_text = tab_text.replace(/<a class='infobox'[\s\S]*?<\/a>/gi, "");
you can play around the string in the replace (before the comma) on this site: regexr.com
more info on multiline match here
63
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
