Javascript Datatable not exporting Merged column header

Question

User-1355965324 posted

I am using the javascript datatable and it exporting all the column except the first level heading Employee,Monday, Tuesday, SysHoliday. It is keeping as merged  heading. But when I exporting  the first heading will not be exported . This is the code . The first <Tr> Heading not exported. Please can you given any help on this Javascript Datatable

@section Styles {
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.19/css/jquery.dataTables.css" />
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/buttons/1.5.2/css/buttons.dataTables.min.css" />
}


<table id="empview"   class="display nowrap" cellspacing="0" style='font-size:80%'>
                                <thead class="thead-light">
                                    <tr>
                                        <th rowspan="2">Employee</th>
                                        <th colspan="4">Monday</th>
                                        <th colspan="4">Tuesday</th>
 
                                        <th rowspan="2"> Sys<br />Holiday<br /> Per<br   </th>
</tr>
<tr>
                                        <th>In<br />Time</th>    
                                        <th>Out<br />Time</th>   
                                        <th>Break<br />Hrs</th>   
                                        <th>Net<br />Hrs</th>   

                                        <th>In<br /> Time</th>    
                                        <th>Out<br /> Time</th>   
                                        <th>Break<br /> Hrs</th>   
                                        <th>Net<br /> Hrs</th>   

                                         

                                    </tr>
                                </thead>

  <tbody>


                                    @foreach (EmployeeWorkTimeSettingsModel EmpSetting in @ViewBag.EmpviewReport)
                                    {
                                        <tr>
                                            <td style="max-width:500px;overflow:hidden; white-space:nowrap">@EmpSetting.EmployeeName </td>

                                            <td>@EmpSetting.MondayInTime </td>
                                            <td>@EmpSetting.MondayOutTime </td>
                                            <td>@EmpSetting.MondayBreakHrs </td>
                                            <td>@EmpSetting.MondayNetHrs </td>

                                            <td>@EmpSetting.TuesdayInTime </td>
                                            <td>@EmpSetting.TuesdayOutTime </td>
                                            <td>@EmpSetting.TuesdayBreakHrs </td>
                                            <td>@EmpSetting.TuesdayNetHrs </td>
   <td>@EmpSetting.SystemHoliday</td>
</tr>

                                    }



                                </tbody>


                            </table>

@section Scripts
    {
    <script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></script>
    <script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/buttons/1.5.2/js/dataTables.buttons.min.js"></script>
    <script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/buttons/1.5.2/js/buttons.flash.min.js"></script>
    <script type="text/javascript" charset="utf8" src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js"></script>
    <script type="text/javascript" charset="utf8" src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.36/pdfmake.min.js"></script>
    <script type="text/javascript" charset="utf8" src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.36/vfs_fonts.js"></script>
    <script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/buttons/1.5.2/js/buttons.html5.min.js"></script>
    <script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/buttons/1.5.2/js/buttons.print.min.js"></script>

    <script type="text/javascript">
        $(document).ready(function () {
            $("#empview").DataTable({
                scrollY: "300px",
                scrollX: true,
                scrollCollapse: false,
                paging: false,
                fixedColumns: {
                    leftColumns: 1
                },
                dom: 'Bfrtip',
                buttons: [
                    'copy', 'csv', 'excel', 'pdf', 'print'
                ]
            });
        });
    </script>

Answer 1

User839733648 posted

Hi polachan,

According to your description and code, I suggest that you should download the buttons.html5.js file and modify part of the code then quote.

1. Add a local function to buttons.html5.js.

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;
};

2. Change the code in DataTable.ext.buttons.excelHtml5 in buttons.html5.js file to:

    if ( config.header ) {//search 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
    }

result:

For more about this, you could refer to: https://stackoverflow.com/questions/40302985/how-to-export-multiple-row-headers-in-jquery-datatables 

Best Regards,

Jenifer

Answer 2

User-1355965324 posted

Many Many Thanks