Datatables Export data to Excel Issues
Datatables Export data to Excel Issues
mujahidateeb
Posts: 3Questions: 1Answers: 0
Hello All,
How to export datatables to Excel. Excel Sheet I want Border to all data, Time new Roman font family, font size and Cell background color using if condition.
<html>
<head>
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" rel="stylesheet">
<link href="https://cdn.datatables.net/1.10.12/css/dataTables.bootstrap.min.css" rel="stylesheet">
<link href="https://cdn.datatables.net/buttons/1.2.2/css/buttons.bootstrap.min.css" rel="stylesheet">
<link href="https://cdn.datatables.net/1.10.12/css/jquery.dataTables.min.css" rel="stylesheet">
<script src="https://code.jquery.com/jquery-1.12.3.js"></script>
<script src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.10.12/js/dataTables.bootstrap.min.js"></script>
<script src="https://cdn.datatables.net/buttons/1.2.2/js/dataTables.buttons.min.js"></script>
<script src="https://cdn.datatables.net/buttons/1.2.2/js/buttons.bootstrap.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/2.5.0/jszip.min.js"></script>
<script src="https://cdn.rawgit.com/bpampuch/pdfmake/0.1.18/build/pdfmake.min.js"></script>
<script src="https://cdn.rawgit.com/bpampuch/pdfmake/0.1.18/build/vfs_fonts.js"></script>
<script src="https://cdn.datatables.net/buttons/1.2.2/js/buttons.html5.min.js"></script>
<script src="https://cdn.datatables.net/buttons/1.2.2/js/buttons.print.min.js"></script>
<script src="https://cdn.datatables.net/buttons/1.2.2/js/buttons.colVis.min.js"></script>
<script src="https://cdn.datatables.net/select/1.2.0/js/dataTables.select.min.js"></script>
<script>
$(document).ready(function() {
$('#example').DataTable({
dom: 'Bfrtip',
buttons: [{
extend: 'excelHtml5',
title: 'Excel Export',
extension: '.xlsx',
text: 'Export to Excel',
exportOptions: {
format: {
body: function ( data, column, row ) {
//if it is html, return the text of the html instead of html
if (/<\/?[^>]*>/.test(data)) {
return $(data).text();
} else {
return data;
}
}
}
},
customize: function(xlsx) {
var sheet = xlsx.xl.worksheets['Sheet1.xml'];
$('row c[r*="3"]', sheet).attr( 's', '20' );
$('row c[r*="2"]', sheet).attr( 's', '25' );
}
},'pdf']
});
});
</script>
</head>
<body>
<table id="example" class="table table-striped table-bordered" cellspacing="0" width="100%">
<thead>
<tr style="font-family: Times New Roman, Times, serif;">
<th>Name</th>
<th>Position</th>
<th>Office</th>
<th>Age</th>
<th>Start date</th>
<th>Salary</th>
</tr>
</thead>
<tfoot>
<tr>
<th>Name</th>
<th>Position</th>
<th>Office</th>
<th>Age</th>
<th>Start date</th>
<th>Salary</th>
</tr>
</tfoot>
<tbody>
<tr>
<td style="background-color:red;">Tiger Nixon</td>
<td>System Architect</td>
<td>Edinburgh</td>
<td>61</td>
<td>2011/04/25</td>
<td>$320,800</td>
</tr>
<tr>
<td>Garrett Winters</td>
<td>Accountant</td>
<td>Tokyo</td>
<td>63</td>
<td>2011/07/25</td>
<td>$170,750</td>
</tr>
<tr>
<td>Ashton Cox</td>
<td>Junior Technical Author</td>
<td>San Francisco</td>
<td>66</td>
<td>2009/01/12</td>
<td>$86,000</td>
</tr>
<tr>
<td>Cedric Kelly</td>
<td>Senior Javascript Developer</td>
<td>Edinburgh</td>
<td>22</td>
<td>2012/03/29</td>
<td>$433,060</td>
</tr>
<tr>
<td>Airi Satou</td>
<td>Accountant</td>
<td>Tokyo</td>
<td>33</td>
<td>2008/11/28</td>
<td>$162,700</td>
</tr>
</tbody>
</table>
</body>
</html>
This discussion has been closed.
Replies
You would need to use the
customizemethod of theexcelHtml5button type if you want to control the file's formatting. You'll need to understand the Open Spreadsheet document format to be able to modify the XML to add the styles as required as well. There is no trivial solution for that.Allan
Hai Allan Thanks for your reply. Can you give some example links related to this.
Did you ever figure the syntax to apply a border to all of your data?
Just use the customize method and apply style 25 for example.
F12Magic thank you for the reply. Two questions:
1) Where is the best place to try and understand the syntax 'row c*', 'row c[r^="B6"]', 'row:first c'?
2) I noticed that when using
if the cell is blank the style is not applied?
Partial answer to 1 - Not being a jquery user I found this URL to explain certain aspects
https://api.jquery.com/category/selectors/. I still need to understand the tags row c r.
Question 2 still open.
Interesting. That's related to a different bug fix which was to not create the cell at all if it was empty (i.e. it doesn't exist so it can't have a style).
The two issues appear to be mutually exclusive and one can't be resolved without breaking the other!
Allan
Using buttons 1.2.2 everything works fine. See my example on codepen
In it, empty table cells are bordered just like the non empty ones.
And I have to apologize. In my previous post 'row c*' is wrong. It needs to be 'row c'
About the selector syntax, just play around with it. Some explanations:
row c = all cells
row:first c = only the first row
row:nth-child(2) c = all cells of the third row
row c:nth-child(3) = fourth column
row c[r^="B6"] = One cell with the excel reference B6
Yeah - the change to not write cells which are empty was in 1.2.3.
Allan