locked
Javascript Datatable not exporting Merged column header RRS feed

  • 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>
    
    
    
    

    Thursday, January 31, 2019 3:48 PM

Answers

  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, February 1, 2019 7:05 AM

All replies

  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, February 1, 2019 7:05 AM
  • User-1355965324 posted

    Many Many Thanks

    Friday, February 1, 2019 7:16 AM