Multiple Row Column Headers in jQuery Data Tables–Excel Export
I had a request to create a report that needed certain columns grouped together. For this report, I decided to use jQuery Data Tables version 1.10.15 for the ability to quickly search and filter data in the table.
Another requirement for the report, and most reports, is to be able to export the report to Excel. jQuery Data Tables handles this very well. The problem is Data Tables does not export multiple row column headers. I found an answer on Stack Overflow that solved it for me.
In the datatables/extensions/Buttons/js/buttons.html5.js file, I added the following local function:
var _fnGetHeaders = function (dt) {
var thRows = $(dt.header()[0]).children();
var numRows = thRows.length;
var matrix = [];
// Iterate over each row of the header and add information to matrix.
for ( var rowIdx = 0; rowIdx < numRows; rowIdx++ ) {
var $row = $(thRows[rowIdx]);
// Iterate over actual columns specified in this row.
var $ths = $row.children("th");
for ( var colIdx = 0; colIdx < $ths.length; colIdx++ ) {
var $th = $($ths.get(colIdx));
var colspan = $th.attr("colspan") || 1;
var rowspan = $th.attr("rowspan") || 1;
var colCount = 0;
// ----- add this cell's title to the matrix
if (matrix[rowIdx] === undefined) {
matrix[rowIdx] = []; // create array for this row
}
// find 1st empty cell
for ( var j = 0; j < (matrix[rowIdx]).length; j++, colCount++ ) {
if ( matrix[rowIdx][j] === "PLACEHOLDER" ) {
break;
}
}
var myColCount = colCount;
matrix[rowIdx][colCount++] = $th.text();
// ----- If title cell has colspan, add empty titles for extra cell width.
for ( var j = 1; j < colspan; j++ ) {
matrix[rowIdx][colCount++] = "";
}
// ----- If title cell has rowspan, add empty titles for extra cell height.
for ( var i = 1; i < rowspan; i++ ) {
var thisRow = rowIdx+i;
if ( matrix[thisRow] === undefined ) {
matrix[thisRow] = [];
}
// First add placeholder text for any previous columns.
for ( var j = (matrix[thisRow]).length; j < myColCount; j++ ) {
matrix[thisRow][j] = "PLACEHOLDER";
}
for ( var j = 0; j < colspan; j++ ) { // and empty for my columns
matrix[thisRow][myColCount+j] = "";
}
}
}
}
return matrix;
};
Then I updated the code in the DataTable.ext.buttons.excelHtml5 function to:
if (config.header) {
/* ----- BEGIN changed Code ----- */
var headerMatrix = _fnGetHeaders(dt);
for ( var rowIdx = 0; rowIdx < headerMatrix.length; rowIdx++ ) {
addRow( headerMatrix[rowIdx], rowPos );
}
/* ----- OLD Code that is replaced: ----- */
//addRow( data.header, rowPos );
/* ----- END changed Code ----- */
$('row c', rels).attr( 's', '2' ); // bold
}
I attempted to create a bundle (in App_Start\BundleConfig.cs) but wasn’t able to get the export to work correctly. I had to include the DataTable JavaScript files in the cshtml file individually.
<script type="text/javascript" src="~/Lib/DataTables-1.10.15/media/js/jquery.dataTables.js"></script> <script type="text/javascript" src="~/Lib/DataTables-1.10.15/extensions/Buttons/js/dataTables.buttons.js"></script> <script type="text/javascript" src="~/Lib/DataTables-1.10.15/extensions/Buttons/js/buttons.bootstrap.js"></script> <script type="text/javascript" src="~/Lib/DataTables-1.10.15/extensions/Buttons/js/buttons.html5.js"></script>
