Question:
I have a table in Jquery Datatables which has child and grandchild tables (A table with sub -tables that in turn have sub-tables) the table is formed as follows:
function cargarTabla() {
//Función interna para realizar un ajax y obtener las columnas
mvc.Datos.GetColumnas.post({ IdTabla: idTabla }, res => {
var datos = res;
datos = JSON.parse(datos);
var ruta = idTabla != 17 ? "/Datos/GetDatosPorPuesto?idPuesto=" + idPuesto : "/Datos/GetDatosDocumentacion?idLinea=" + idLinea;
table = $('#tablaDatos').DataTable({
"ajax": {
url: ruta,
dataSrc: function (data) {
return JSON.parse(data);
}
},
"bDestroy": true,
"language": {
"url": "//cdn.datatables.net/plug-ins/1.10.16/i18n/Spanish.json"
}, select: {
style: 'multi'
},
"iDisplayLength": 10,
"columns":
datos
,
"order": [[3, 'desc']],
"fnDrawCallback": function (oSettings) {
runAllCharts();
},
"columnDefs": [{
"render": function (data, type, row) {
return (data == 1) ? '<center><i class="fa fa-check"></i></center>' : ' <center><i class="fa fa-minus"></i></center>';
},
}]
});
//botones personalizados para exportacion
var buttons = new $.fn.dataTable.Buttons(table, {
buttons: [
{
exportOptions: {
columns: [ 1, 2, 3, 4, 5, 6,7,8,9,10,11,12]
},
orientation: 'landscape',
title: '',
text: '<i class="fa fa-3x fa-file-code" style="padding: 3px 3px 3px 3px"></i>',
className: 'btn btn-default jarvismetro-tile txt-color-white btnTablas',
attr: {
style: 'background-color:#203864;display:none',
id: 'exportJSON'
},
action: function (e, dt, button, config) {
var data = dt.buttons.exportData();
$.fn.dataTable.fileSave(
new Blob([JSON.stringify(data)]),
'Export.json'
);
}
},
{
exportOptions: {
columns: [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12]
},
title: '',
extend: 'excel',
text: '<i class="fa fa-3x fa-file-excel" style="padding: 3px 3px 3px 3px"></i>',
className: 'btn btn-default jarvismetro-tile txt-color-white btnTablas',
attr: {
style: 'background-color:#203864;display:none',
id: 'exportXls'
}
}
]
}).container().appendTo($('#buttons'));
});
}
cargarTabla();
function to generate the subtable and subsubtable:
$('#tablaDatos').on('click', 'td.details-control', function () {
var tr = $(this).closest('tr');
var row = table.row(tr);
if (row.child.isShown()) {
// This row is already open - close it
row.child.hide();
tr.removeClass('shown');
}
else {
// Open this row
try {
var d = row.data();
var parametros = d.Registros;
nieto = parametros;
var subtabla;
subtabla = '<table id = "child_details" cellpadding="5" cellspacing="0" border="0" style="padding-left:50px;">' +
'<thead><tr>' +
'<th></th>' +
'<th>campo1</th>' +
'<th>campo2</th>' +
'<th>campo3</th>' +
'<th>campo4</th>' +
'<th>campo5</th>' +
'<th>campo6</th>' +
'<th class="hide">campo7</th>' +
'</tr></thead><tbody>';
$.each(parametros, function (index, value) {
subtabla += '<tr><td class="details-control"></td>';
subtabla += '<td>' + value.campo1 + '</td>';
subtabla += '<td>' + value.campo2 + '</td>';
subtabla += '<td>' + value.campo3 + '</td>';
subtabla += '<td>' + value.campo4 + '</td>';
subtabla += '<td>' + value.campo5 + '</td>';
//valores de la tabla nieta
subtabla += '<td class="hide">' + value.campo6 + '</td>';
subtabla += '<td>' + value.campo7 + '</td>';
});
subtabla += '</tbody></table>';
row.child(subtabla).show();
childTable = $('#child_details').DataTable({
searching: false,
paging: false,
info: false,
destroy: true
});
childTable
.on('select', function (e, dt, type, indexes) {
if (type === 'row') {
var data = childTable.rows(indexes).data();
// do something with the ID of the selected items
}
});
tr.addClass('shown');
} catch {
var d = childTable.row($(this).closest('tr')).data();
var parametros = JSON.parse(d[6]);
$.each(parametros, function (index, value) {
console.log(index, value);
if (index == "Aprietes") {
nieceTable = '<table id = "niece_details" cellpadding="5" cellspacing="0" border="0" style="padding-left:50px;">' +
'<thead></thead><tbody>' +
'<tr><td>Apriete:</td>';
$.each(value, function () {
nieceTable += '<td>' + this.APRIETE + '</td>';
});
nieceTable += '</tr><tr><td>Par:</td>';
$.each(value, function () {
nieceTable += '<td>' + this.PAR + '</td>';
});
nieceTable += '</tr><tr><td>Angulo:</td>';
$.each(value, function () {
nieceTable += '<td>' + this.ANGULO + '</td>';
});
nieceTable += '</tr></tbody></table>';
} else {
nieceTable = '<table id = "niece_details" cellpadding="5" cellspacing="0" border="0" style="padding-left:50px;">' +
'<thead><tr><th>Nombre</th><th>Valor</th></tr></thead><tbody>' +
'<tr><td>' + this.Title + '</td>' +
'<td> ' + this.Value + '</td>' +
'</tr></tbody></table>';
}
});
childTable.row($(this).closest('tr')).child(nieceTable).show();
var nieceTable = $('#niece_details').DataTable({
searching: false,
paging: false,
info: false,
destroy: true,
scrollY: '50px',
select: true,
});
nieceTable
.on('select', function (e, dt, type, indexes) {
if (type === 'row') {
var data = childTable.rows(indexes).data();
// do something with the ID of the selected items
}
});
tr.addClass('shown');
}
}
});
With this configuration, the export buttons are shown correctly, but when pressing it, only the parent table is exported. I am using Html5 buttons To export I am using this library .
I have tried to get the child table through button action with this but to no avail.
I cannot do the same as the child table (add the fields in hidden), I have not been able to do it better.
How can I get the table and its subtables to export to me?
Answer:
I am a more active user in the English-speaking forum but today I decided to take a walk here, I must say that this took me a long time and that despite having done complex things in DataTables this was a headache but after a few how many cups of coffee and tests in jsfiddle I hope it is useful.
- First, it should be clarified that I looked for an example made in the DataTables forum of a table that contains child and granddaughter tables in order to solve this problem, unfortunately I lost the link to it because chrome died from so many open tabs 🙄.
- Currently the library that is used to export by DataTables cannot capture the data from the grandchild or child tables, this can be seen in detail in the customizedData method of buttons, if you wonder why? it is because the library sends the text of each td equating it to a cell, therefore it does not capture complex objects.
- customizedData as reflected in this question on the DataTables forum can modify the content of our data so it is possible to overwrite this data to obtain the desired result from Ajax or for this case using orthogonal this allows us to export data from the source of data of a cell and since in this example both the daughter and granddaughter table have their source data in a cell we will pass this data using
JSON.stringifyto convert our object to a string in this way we can mold the data in the customizedData method -
To do this we must render on the cell that contains the children and grandchildren elements in the columns section.
data: "termGPA", render: function (data, type, row) { return type === 'export' ? JSON.stringify(data) : '<imgsrc="http://i.imgur.com/SD7Dz.png">' ; }This element of our JSON contains the values of our daughter and granddaughter table, here we indicate that we will return the encoded data when the type is of the export type and otherwise the default value of our icon will be shown.
-
Finally, in the buttons section, we must define the way in which the data will be modified, for the case I will show it as it would be if the daughter and granddaughter tables were expanded, for this we create an arrangement that will replace the body sub-arrangement of our object.
buttons: [{ extend: 'excelHtml5', customizeData: function(data) { let subchild = data.body; var exportBody = []; var secondTable = ["", "Term", "GPA Value"]; var thirdTableName = ["", "Course Name", "Letter Grade"]; subchild.forEach((e, i) => { let row = JSON.parse(e[0]); exportBody.push([i + 1, e[1], e[2], e[3], e[4]]); row.forEach((el, idx) => { let subrow = el; exportBody.push(secondTable); exportBody.push(["", el.term, el.gpaValue]); exportBody.push(thirdTableName); subrow.termCourseGrades.forEach((grade, pos) => { exportBody.push(["", grade.courseName, grade.letterGrade]); }) }) }) data.body.length = 0; data.header[0] = "Row"; data.body.push.apply(data.body, exportBody); }, exportOptions: { orthogonal: 'export' } }]
In this section we must bear in mind that since the data attribute of our column is different from null, it will be taken into account to generate the document, so you can leave a space for each cell or add data so that the column that equals the number of record row.
Finally I did not decide to use the library that you mention in the question since I have never used it before and I preferred to use the libraries that DataTables provides to export.
I hope it is helpful, greetings.
var iTermGPACounter = 1;
var iCourseCounter = 1;
$(document).ready(function() {
loadDetailsByCourse();
});
function loadDetailsByCourse() {
var yearTable = $('#msGrades').DataTable({
data: [{
"__type": "DMC.WebServices.detailGPA",
"schoolYearLabel": "14-15",
"schoolLevel": "02",
"location": "Highland",
"grade": "7",
"gpaValue": "3.119",
"termGPA": [{
"term": "1",
"gpaValue": "3.857",
"termCourseGrades": [{
"courseNo": "38929712",
"sectionNo": "200",
"courseName": "HEALTH 2",
"letterGrade": "A+",
"department": "EL"
}, {
"courseNo": "32320711",
"sectionNo": "10",
"courseName": "LANG ARTS 2",
"letterGrade": "A+",
"department": "EL"
}, {
"courseNo": "32720711",
"sectionNo": "10",
"courseName": "MATH 2",
"letterGrade": "B",
"department": "MA"
}]
}, {
"term": "2",
"gpaValue": "3.714",
"termCourseGrades": [{
"courseNo": "38929712",
"sectionNo": "200",
"courseName": "HEALTH 2",
"letterGrade": "A",
"department": "EL"
}, {
"courseNo": "32320711",
"sectionNo": "10",
"courseName": "LANG ARTS 2",
"letterGrade": "A",
"department": "EL"
}, {
"courseNo": "32720711",
"sectionNo": "10",
"courseName": "MATH 2",
"letterGrade": "A-",
"department": "MA"
}]
}]
}, {
"__type": "DMC.WebServices.detailGPA",
"schoolYearLabel": "15-16",
"schoolLevel": "02",
"location": "Highland",
"grade": "8",
"gpaValue": "3.123",
"termGPA": [{
"term": "1",
"gpaValue": "3.143",
"termCourseGrades": [{
"courseNo": "32320711",
"sectionNo": "12",
"courseName": "LANG ARTS 2",
"letterGrade": "A",
"department": "EL"
}, {
"courseNo": "32720711",
"sectionNo": "12",
"courseName": "MATH 2",
"letterGrade": "D",
"department": "MA"
}]
}]
}],
paging: false,
columns: [
{
className: 'term-details-control',
orderable: false,
data: "termGPA",
render: function (data, type, row) {
return type === 'export' ?
JSON.stringify(data) : '<img src="http://i.imgur.com/SD7Dz.png">' ;
},
defaultContent: '<img src="http://i.imgur.com/SD7Dz.png">'
}, {
data: "schoolYearLabel"
}, {
data: "grade"
}, {
data: "location"
}, {
data: "gpaValue"
}
],
order: [
[1, 'asc']
],
dom: 'Bfrtip',
buttons: [{
extend: 'excelHtml5',
customizeData: function(data) {
let subchild = data.body;
var exportBody = [];
var secondTable = ["","Term","GPA Value"];
var thirdTableName = ["","Course Name","Letter Grade"];
subchild.forEach((e,i)=>{
let row = JSON.parse(e[0]);
exportBody.push([i+1,e[1],e[2],e[3],e[4]]);
row.forEach((el,idx)=>{
let subrow = el;
exportBody.push(secondTable);
exportBody.push(["",el.term,el.gpaValue]);
exportBody.push(thirdTableName);
subrow.termCourseGrades.forEach((grade,pos)=>{
exportBody.push(["",grade.courseName,grade.letterGrade]);
})
})
})
data.body.length = 0;
data.header[0] = "Row";
data.body.push.apply(data.body, exportBody);
},
exportOptions: { orthogonal: 'export' }
}]
});
// Add event listener for opening and closing details
$('#msGrades tbody').on('click', 'td.term-details-control', function() {
var tr = $(this).closest('tr');
var row = yearTable.row(tr);
if (row.child.isShown()) {
// This row is already open - close it
row.child.hide();
tr.removeClass('shown');
} else {
// Open this row
row.child(formatTermGPA(iTermGPACounter)).show();
tr.addClass('shown');
var termTable = $('#termGPA_' + iTermGPACounter).dataTable({
data: row.data().termGPA,
paging: false,
searching: false,
columns: [{
className: 'course-details-control',
orderable: false,
data: null,
defaultContent: '<img src="http://i.imgur.com/SD7Dz.png">'
}, {
data: "term"
}, {
data: "gpaValue"
}],
order: [
[1, 'asc']
]
});
// Add event listener for opening and closing details
$('#termGPA_' + iTermGPACounter + ' tbody').on('click', 'td.course-details-control', function() {
var tr = $(this).closest('tr');
var closestTable = tr.closest("table");
var row = closestTable.DataTable().row(tr);
if (row.child.isShown()) {
// This row is already open - close it
row.child.hide();
tr.removeClass('shown');
} else {
// Open this row
row.child(formatTermCourseGrades(iCourseCounter)).show();
tr.addClass('shown');
var courseTable = $('#courseGrades_' + iCourseCounter).DataTable({
data: row.data().termCourseGrades,
paging: false,
searching: false,
columns: [{
data: "courseName"
}, {
data: "letterGrade"
}],
order: [
[1, 'asc']
]
});
}
iCourseCounter += 1;
});
iTermGPACounter += 1;
}
});
}
function formatTermGPA(table_id) {
return '<table class="table table-striped" id="termGPA_' + table_id + '">' +
'<thead><tr><th></th><th>Term #</th><th>Term GPA</th></tr></thead></table>';
}
function formatTermCourseGrades(table_id) {
return '<table class="table table-striped" id="courseGrades_' + table_id + '">' +
'<thead><tr><th>Course Name</th><th>Letter Grade</th></tr></thead></table>';
} <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
<script src="https://cdn.datatables.net/1.10.15/js/jquery.dataTables.min.js"></script>
<link href="https://cdn.datatables.net/1.10.15/css/jquery.dataTables.min.css"/>
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css"/>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
<link href="https://cdn.datatables.net/buttons/1.6.1/css/buttons.dataTables.min.css"/>
<script src="https://cdn.datatables.net/buttons/1.6.1/js/dataTables.buttons.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/pdfmake.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/vfs_fonts.js"></script>
<script src="https://cdn.datatables.net/buttons/1.6.1/js/buttons.html5.min.js"></script>
<script src="https://cdn.datatables.net/buttons/1.6.1/js/buttons.print.min.js"></script>
<div class="tab-pane fade in col-xs-12" id="Grades">
<div class="col-xs-12 col-sm-12 col-md-6" align="center">
<label style="width: 75%">
<h3>Middle School Grades</h3>
</label>
<table id="msGrades" class="tblGrades" cellspacing="0" width="100%">
<thead>
<tr>
<th></th>
<th>School Year</th>
<th>Grade</th>
<th>School</th>
<th>GPA</th>
</tr>
</thead>
</table>
</div>
</div>