Excel export does not export the data in the table just the name and table headings

Excel export does not export the data in the table just the name and table headings

GlyndwrGlyndwr Posts: 117Questions: 32Answers: 0

I am trying to export a table of data extracted by Java, passed back to Ajax using JSON, and populating the table with Ajax in HTML. Using a table populated with data in the HTML works fine.

Java:

    if (packSummaryList == null || packSummaryList.isEmpty()) {
        response.sendError(HttpServletResponse.SC_BAD_REQUEST, "No one joined.");
    } else {
        String json = new Gson().toJson(packSummaryList);
        response.setContentType("application/json");
        response.setCharacterEncoding("UTF-8");
        response.getWriter().write(json);
    }

Ajax:

      .done(function(responseJson1a){
            dataType: "json";
    var tablebody = "";
    try{
          for (i=0; i < responseJson1a.length; i++) {
              tablebody += "<tr><td>"+responseJson1a[i].section+"</td><td>"+responseJson1a[i].metricTotal+"</td></tr>";
          }
          $("#mytablebody").empty();
          $("#mytablebody").append(tablebody);
    }
    catch(e){
    console.log(e);
    }
})

HTML:

    <div class="col-lg-3 col-md-3 col-sm-3 col-xs-3">
        <table class="table table-hover table-bordered" id="joinedTable">
            <thead>
                 <tr>
                    <th>Section</th>
                    <th>Joined</th>
                 </tr>
            </thead>
            <tbody id="mytablebody"></tbody>
        </table>
        <div style="text-align: center;">
            <span id="ajaxGetUserServletResponse1" style="color: red;"></span>
        </div>
     </div>

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Can you link to a page showing this issue please? I've not seen that problem before and haven't been able to reproduce it in the examples.

    Allan

  • GlyndwrGlyndwr Posts: 117Questions: 32Answers: 0

    Hi Allan,

    I am not a developer and am trying to do this for my Scout Group. So please bear with me if I do not understand exactly what you are requesting. I do not have a link to this as I am developing it on my laptop. I will create a very cut down version of the code and post the lot. Thank you very much for your help.

    Kind regards,

    Glyn

  • GlyndwrGlyndwr Posts: 117Questions: 32Answers: 0

    Hi Allan,

    Looks like I can not edit the original post or add my code in comments. Do you want me to send you the code or create a new question please?

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736

    I'm thinking that your order of operations is causing the problem. My guess is the you are initializing Datatables before the ajax request. The table is built in the ajax request by appending the rows to the tbody using this code:

              $("#mytablebody").empty();
              $("#mytablebody").append(tablebody);
    

    Doing this after Datatables is initialized is a problem because Datatables is not aware of the new data. The export will not show contain the data.

    Thats just a guess since you haven't shown your full JS code. If it is the case then you can either initialize Datatables right after the above code or you can use rows.add() to add the data instead of the above code.

    This example may help.
    http://live.datatables.net/latebixa/1/edit

    First is shows the problem with initializing Datatables first by printing the data Datatables knows about in the console. The 2nd example uses rows.add() and the third changes the order. Both these examples display the data.

    HTH,

    If not then please post you full JS code.

    Kevin

  • GlyndwrGlyndwr Posts: 117Questions: 32Answers: 0

    Hi Kevin,

    Thank you for your reply. I tried option three and the console shows an error:

    TypeError: $(...).DataTable is not a function
    at Object.<anonymous> (groupSummary-ajax.js:80)
    at i (eval at globalEval (jquery-2.2.4.min.js:2), <anonymous>:2:27449)
    at Object.fireWith [as resolveWith] (eval at globalEval (jquery-2.2.4.min.js:2), <anonymous>:2:28213)
    at y (eval at globalEval (jquery-2.2.4.min.js:2), <anonymous>:4:22721)
    at XMLHttpRequest.c (eval at globalEval (jquery-2.2.4.min.js:2), <anonymous>:4:26925)

    I must have some error with the includes. I have checked and they seem OK to me. I will provide a link to the code.

    Kind regards,

    Glyn

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    That error normally means that jQuery is being loaded multiple times. Looking at your code I don't immediately see anything that would cause that, but there might be something I'm missing in one of the references I can't see.

    Are you able to publish the page on the web somewhere or port forward to your laptop so we can debug it directly?

    Allan

  • GlyndwrGlyndwr Posts: 117Questions: 32Answers: 0

    Sorry you are talking to a novice. I have not got a way to publish this on the web as I am developing it on my laptop for my Scouts. I do not know how to port forward.

  • GlyndwrGlyndwr Posts: 117Questions: 32Answers: 0

    I have just tried:

    .done(function(responseJson1a){
    dataType: "json";
    $('#joinedTable').DataTable( {
    "ajax": responseJson1a,
    } );

    And I still get the "TypeError: $(...).DataTable is not a function" error.

    Could the problem be in the html that is being passed to DataTable?

    section subSection metric metricTotal
  • GlyndwrGlyndwr Posts: 117Questions: 32Answers: 0

    The error is not due to the HTML table being populated from JSON. The "$('#joinedTable').DataTable();" being in the ".done(function(responseJson1a)" causes the error. The below works until the "$('#joinedTable').DataTable();" is un-commented. However, only the

    <tr>
       <td>Cubs</td>
       <td>3</td>
    </tr>
    <tr>
       <td>Scouts</td>
       <td>5</td>
    </tr>
    

    is exported. Not

    <tr>
       <td>Cubs</td>
       <td>9</td>
    </tr>
    <tr>
       <td>Scouts</td>
       <td>10</td>
    </tr>
    

    HTML:

    <!DOCTYPE html>
    <html>
        <head>
            <title>Group Summary</title>
            <meta http-equiv=Content-Type content="text/html; charset=utf-8">
            <meta name="viewport" content="width=device-width, initial-scale=1.0">
            <meta name="description" content="A Scout award tracking application">
            <meta name="author" content="Glyndwr (Wirrin) Bartlett">
    
            <!-- JQuery -->
            <script src="https://code.jquery.com/jquery-2.2.4.min.js"></script>
    
            <!-- Validate -->
            <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery-validate/1.17.0/jquery.validate.min.js"></script>
            <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery-validate/1.17.0/additional-methods.min.js"></script>
    
            <!-- Bootstrap -->
            <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.1.1/js/bootstrap.min.js"></script>
    
            <!-- Bootstrap Date Picker-->
            <script src="https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.20.1/moment.min.js"></script>    
            <script src="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-datepicker/1.8.0/js/bootstrap-datepicker.min.js"></script>
    
            <!-- DataTables -->
            <script src="https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js"></script>
    
    
            <!-- Le styles -->
            <!-- Bootstrap -->
            <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
    
            <!-- DataTables -->
            <link rel="stylesheet" href="https://cdn.datatables.net/1.10.16/css/jquery.dataTables.min.css">
            <link rel="stylesheet" href="https://cdn.datatables.net/buttons/1.5.1/css/buttons.dataTables.min.css">
        </head>
    
      <body>
    
        <div id="groupSummary"  class="container-fluid" style="background-repeat: repeat; background-image: url('images/body-bg.jpg');">
    
            <div id="includedContent"></div>
    
            <form data-toggle="validator" role="form" id="showGroupSummaryForm">
                <div class="row">
                    <div class="container-fluid">
                        <div class="col-lg-12 col-md-12 col-sm-12 col-xs-12">
                            <div class="jumbotron">
                                <h3>Group Summary</h3>
    
                                <div class="container">
    
                                    <div class="col-lg-3 col-md-3 col-sm-3 col-xs-3">
                                        <div class="form-group">
                                            <div class="input-group date" id="datepicker1">
                                                <input type="text" id="startDate" class="form-control" placeholder="Start Date">
                                                <span class="input-group-addon">
                                                    <span class="glyphicon glyphicon-calendar"></span>
                                                </span>
                                            </div>
                                        </div>
                                    </div>
    
                                    <div class="col-lg-3 col-md-3 col-sm-3 col-xs-3">
                                        <div class="form-group">
                                            <div class='input-group date' id='datepicker2'>
                                                <input type='text' id="endDate" class="form-control" placeholder="End Date">
                                                <span class="input-group-addon">
                                                    <span class="glyphicon glyphicon-calendar"></span>
                                                </span>
                                            </div>
                                        </div>
                                    </div>
    
                                    <button id='submit' class='btn btn-primary btn-lg'>Display Details</button>
    
                                </div>
                                <div class="container" id=joined>
                                    <div class="col-lg-3 col-md-3 col-sm-3 col-xs-3">
                                        <table class="table table-hover table-bordered" id="joinedTable">
                                            <thead>
                                                <tr>
                                                  <th>Section</th>
                                                  <th>Joined</th>
                                                </tr>
                                            </thead>
                                                <tbody id="mytablebody">
                                                    <tr>
                                                        <td>Cubs</td>
                                                        <td>3</td>
                                                      </tr>
                                                      <tr>
                                                        <td>Scouts</td>
                                                        <td>5</td>
                                                      </tr>
                                                 </tbody>
                                            <!--<tbody id="mytablebody"></tbody> -->
                                        </table>
                                        <div style="text-align: center;">
                                            <span id="ajaxGetUserServletResponse1" style="color: red;"></span>
                                        </div>
                                    </div>
                                </div><!-- /container -->
    
                            </div>
                        </div>
                    </div>
                </div><!-- /row -->
            </form>
    
        </div> <!-- /container -->
    
    
    
        <!-- Le javascript
        ================================================== -->
        <!-- Placed at the end of the document so the pages load faster -->
        <script src="https://cdn.datatables.net/buttons/1.5.1/js/dataTables.buttons.min.js"></script>
        <script src="https://cdn.datatables.net/buttons/1.5.1/js/buttons.flash.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.32/pdfmake.min.js"></script>
        <script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.32/vfs_fonts.js"></script>
        <script src="https://cdn.datatables.net/buttons/1.5.1/js/buttons.html5.min.js"></script>
        <script src="https://cdn.datatables.net/buttons/1.5.1/js/buttons.print.min.js"></script>
    
        <script src="js/DataTable.js"></script>
    
      </body>
    </html>
    
  • GlyndwrGlyndwr Posts: 117Questions: 32Answers: 0

    AJAX:

    $(document).ready(function(){
        $('[data-toggle="tooltip"]').tooltip(); 
    
        $('#joinedTable').DataTable( {
            "paging":   false,
            "ordering": false,
            "info":     false,
            "searching": false,
            dom: 'Bfrtip',
            buttons: ['copy', 'csv', 'excel', 'pdf', 'print'],
        } );
    
    
    
        $("#showGroupSummaryForm").validate({
            //debug: true,
    
            rules: {
                startDate: {
                    required: true
                },
    
                endDate: {
                    required: true
                }
            },
    
            messages: {
                startDate: {
                    required: "Please select a start date."
                },
    
                endDate: {
                    required: "Please select an end date."
                }
            },
    
            submitHandler : function(showGroupSummaryForm) {
    
                $('#joinedTable tbody > tr').remove();
                $('#ajaxGetUserServletResponse1').text('');
    
                var dataToBeSent  = {
                        ssAccountID : sessionStorage.getItem('ssAccountID'),
                        startDate : $("#startDate").val(),
                        endDate: $("#endDate").val()
                };
    
                //Joined
                $.ajax({
                    url : 'GroupSummaryJoinedView', // Your Servlet mapping or JSP(not suggested)
                    data : dataToBeSent, 
                    type : 'POST',  
                })
                .fail (function(jqXHR, textStatus, errorThrown) {
                    //alert(jqXHR.responseText);
                    if(jqXHR.responseText.includes('No members joined in this date range')){
                        $('#ajaxGetUserServletResponse1').text('No members joined in this date range.');
                    }else{
                        $('#ajaxGetUserServletResponse1').text('Error getting joined data.');
    
                    }
                    $("#datepicker1").focus();
                })
                .done(function(responseJson1a){
                    dataType: "json";
    
    //              alert(JSON.stringify(responseJson1a)); 
    //              Result of alert is:
    //                  [{"section":"Cub","subSection":"Explorer","metric":"Joined","metricTotal":5},{"section":"Cub","subSection":"Pioneer","metric":"Joined","metricTotal":8},{"section":"Joey","subSection":"blank","metric":"Joined","metricTotal":1},{"section":"Leader","subSection":"blank","metric":"Joined","metricTotal":5},{"section":"Rover","subSection":"blank","metric":"Joined","metricTotal":1},{"section":"Scout","subSection":"blank","metric":"Joined","metricTotal":2}]
    
                     var tablebody2 = '          <tr>' + 
                        '<td>Cubs</td>' +
                        '<td>9</td>' +
                      '</tr>' +
                      '<tr>' +
                        '<td>Scouts</td>' +
                        '<td>10</td>' +
                      '</tr>';
    
                     $("#mytablebody").empty();
                     $("#mytablebody").append(tablebody2);
    //               $('#joinedTable').DataTable(); // Uncomment this and the error happens
                })
            }
        })
    
    }); // end document.ready
    
    $(function(){
        $("#includedContent").load("Menu.html");
    
        $('#datepicker1').datepicker({
            format: 'dd/mm/yyyy',
                });
        $('#datepicker2').datepicker({
            useCurrent: false, //Important! See issue #1075
            format: 'dd/mm/yyyy',
        });
        $("#datepicker1").on("dp.change", function (e) {
            $('#datepicker2').data("DatePicker").minDate(e.date);
        });
        $("#datepicker2").on("dp.change", function (e) {
            $('#datepicker1').data("DatePicker").maxDate(e.date);
        });
    });
    
  • GlyndwrGlyndwr Posts: 117Questions: 32Answers: 0

    Do you have a HTML and Ajax that works with $('#joinedTable').DataTable(); in the .done (or .always) please? I will start with that and rebuild the solution.

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736

    To start with it looks like you are loading datatables.js twice. Once in line 24-25 in your HTML code snippet:

            <!-- DataTables -->
            <script src="https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js"></script>
    

    Then again in line 125:

     <script src="js/DataTable.js"></script>
    

    I would remove line 125. This is likely the cause of the "TypeError: $(...).DataTable is not a function" error.

    The way you are loading the table seems ok but you could also use rows.add() and not have to convert to HTML. Although you probably don't need to change it but it may be more efficient to use rows.add().

    One row of your JSON response looks like this:

    {"section":"Cub","subSection":"Explorer","metric":"Joined","metricTotal":5}
    

    In line 4 of your "AJAX" code snippet you initialize Datatables. You could add columns.data to look like this:

         var table = $('#joinedTable').DataTable( {
            "paging":   false,
            "ordering": false,
            "info":     false,
            "searching": false,
            dom: 'Bfrtip',
            buttons: ['copy', 'csv', 'excel', 'pdf', 'print'],
            columns: [
              {data: 'section'},
              {data: 'metric'}
            ]
        } );
    

    Note the "var table = " in the code. This is needed for the next steps.

    Starting at line 81 you have this:

                     $("#mytablebody").empty();
                     $("#mytablebody").append(tablebody2);
    //               $('#joinedTable').DataTable(); // Uncomment this and the error happens
    

    In either case you don't need the $('#joinedTable').DataTable();. Replace the empty() and append() lines with this:

    table.clear();
    table.rows.add( responseJson1a );
    

    The reason that the export was exporting the original data is that you initialized Datatables with that data. Then you used the jQuery methods (empty() and append()) to clear and load new data. Datatables does not see this change. For Datatables to see the change you have to use Datatables API's to clear and add the data or to invalidate the data using something like rows().invalidate();

    Instead of the above you could do this:

    ```js
                     $("#mytablebody").empty();
                     $("#mytablebody").append(tablebody2);
                     $('#joinedTable').DataTable().rows().invalidate(); 
    

    The rows.add() is preferred and more efficient than rows().invalidate() but either should work.

    Hope this helps more than it is confusing :smile: Please post any questions.

    Kevin

  • GlyndwrGlyndwr Posts: 117Questions: 32Answers: 0

    Hi Kevin,

    Thanks for that. I have replaced the code with that provided and now I have the table emptied and nothing displayed, just the table headings are left. There is no error in the console. My code is:

    $(document).ready(function(){
    $('[data-toggle="tooltip"]').tooltip();

    var table = $('#joinedTable').DataTable( {
        "paging":   false,
        "ordering": false,
        "info":     false,
        "searching": false,
        dom: 'Bfrtip',
        buttons: ['copy', 'csv', 'excel', 'pdf', 'print'],
        columns: [
                  {data: 'section'},
                  {data: 'metricTotal'}
                  ]
    } );
    
    
    
    $("#showGroupSummaryForm").validate({
        //debug: true,
    
        rules: {
            startDate: {
                required: true
            },
    
            endDate: {
                required: true
            }
        },
    
        messages: {
            startDate: {
                required: "Please select a start date."
            },
    
            endDate: {
                required: "Please select an end date."
            }
        },
    
        submitHandler : function(showGroupSummaryForm) {
    
            $('#joinedTable tbody > tr').remove();
            $('#ajaxGetUserServletResponse1').text('');
    
            var dataToBeSent  = {
                    ssAccountID : sessionStorage.getItem('ssAccountID'),
                    startDate : $("#startDate").val(),
                    endDate: $("#endDate").val()
            };
    
            //Joined
            $.ajax({
                url : 'GroupSummaryJoinedView', // Your Servlet mapping or JSP(not suggested)
                data : dataToBeSent, 
                type : 'POST',  
            })
            .fail (function(jqXHR, textStatus, errorThrown) {
                //alert(jqXHR.responseText);
                if(jqXHR.responseText.includes('No members joined in this date range')){
                    $('#ajaxGetUserServletResponse1').text('No members joined in this date range.');
                }else{
                    $('#ajaxGetUserServletResponse1').text('Error getting joined data.');
    
                }
                $("#datepicker1").focus();
            })
            .done(function(responseJson1a){
                dataType: "json";
    

    // alert(JSON.stringify(responseJson1a));
    // Result of alert is:
    // [{"section":"Cub","subSection":"Explorer","metric":"Joined","metricTotal":5},{"section":"Cub","subSection":"Pioneer","metric":"Joined","metricTotal":8},{"section":"Joey","subSection":"blank","metric":"Joined","metricTotal":1},{"section":"Leader","subSection":"blank","metric":"Joined","metricTotal":5},{"section":"Rover","subSection":"blank","metric":"Joined","metricTotal":1},{"section":"Scout","subSection":"blank","metric":"Joined","metricTotal":2}]

                 table.clear();
                 table.rows.add(responseJson1a);
            })
        }
    })
    

    }); // end document.ready

    $(function(){
    $("#includedContent").load("Menu.html");

    $('#datepicker1').datepicker({
        format: 'dd/mm/yyyy',
            });
    $('#datepicker2').datepicker({
        useCurrent: false, //Important! See issue #1075
        format: 'dd/mm/yyyy',
    });
    $("#datepicker1").on("dp.change", function (e) {
        $('#datepicker2').data("DatePicker").minDate(e.date);
    });
    $("#datepicker2").on("dp.change", function (e) {
        $('#datepicker1').data("DatePicker").maxDate(e.date);
    });
    

    });

  • GlyndwrGlyndwr Posts: 117Questions: 32Answers: 0

    I forgot to add that js is my javascrip/Ajax library so js/DataTable.js is the name of my test file. Sorry about the formatting above.

  • GlyndwrGlyndwr Posts: 117Questions: 32Answers: 0

    This produces the error above:

    '''js
    var tablebody = "";
    try{
    for (i=0; i < responseJson1a.length; i++) {
    tablebody += "<tr><td>"+responseJson1a[i].section+"</td><td>"+responseJson1a[i].metricTotal+"</td></tr>";
    }
    $("#mytablebody").empty();
    alert("tablebody: " + tablebody);
    $("#mytablebody").append(tablebody);

                    var table4 = $('#joinedTable').DataTable();
                    var data4 = table4.rows().data().toArray();
                    console.log('Table 4 data:', data4);
                 }
                 catch(e){
                    console.log(e);
                 }
    
                $("#mytablebody").empty();
                $("#mytablebody").append(tablebody);
                $('#joinedTable').DataTable().rows().invalidate();
    
  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736
    Answer ✓

    Sorry I forgot to add draw() to the code. Try this:

                 table.clear();
                 table.rows.add(responseJson1a).draw();
    

    Kevin

  • GlyndwrGlyndwr Posts: 117Questions: 32Answers: 0

    Well my friend, persistence pays of. My sincere thanks to you and everyone else who have helped resolve this matter.

    Kind regards,

    Glyn

This discussion has been closed.