Datatable unable to export updated checkbox values in excel as 0 or 1, alway showing the initially checked values only

1
votes

Trying to download Datatables, able to get 0 or 1 values for checkboxes (for one of the column fields "Verified") initially, but when I am toggling the checkbox and exporting as excel, unable to retrieve the latest check box values. Initially I only in the first excel imagehad the second checkbox as not ticked in the database so its correctly exporting its values as 0 in the excel sheet but when I change the values and export it again its not taking theupdated checkbox ticks updated latest values as you can see in the snapshot attached as well.

   var j=0;
    $(document).ready(function(){






    var table;
    function Xhr(id)
    {
    return $.ajax(
    {type:"GET",
    url:'http://localhost:8080/SSRSReports/v1/test',
    dataType:'json',
    cache:false,
    async:true,

    beforeSend:
    function(){$(id).append('<img src="images/spinner.gif" class="spin"/>');
    }})
    .always(function(){
    $(id).find('.spin').fadeOut(4000,function(){$(this).remove();
    });
    })
    .fail(function()
    {console.log("There was a problem while trying to get data at "+d);});
    }
    var radio=[];
    var j=0;
    $(document).ready(function(){
    	Xhr('#report').done(function (json) {
    		console.log(json);
    		var x;
    	
    			jQuery_2_2_2.each(json, function (i, report) {
    		
    				radio.fill(0);
    				if(report.testSampleOutOfStabilityIndt==1)
    					{
    						x += '<tr><td>' + report.studyId + '</td>';
    					    x += '<td>' + report.country + '</td>';
    					    x += '<td>' + report.investigatorName + '</td>';
    					    x += '<td>' + report.screenId + '</td>';
    						x += '<td>' + report.subjectId + '</td>';
    						x += '<td>' + report.subjectInitials + '</td>';
    						x += '<td>' + report.requisitionNumber + '</td>';
    						x += '<td>' + report.testName + '</td>';
    						x += '<td>' + report.visitName + '</td>';
    						x += '<td>' + report.collectionDate + '</td>';
    						x += '<td>' + report.collectionTime + '</td>';
    						x += '<td>' + report.resultDate + '</td>';
    						x += '<td>' + report.resultTime + '</td>';
    						x += '<td>' + report.minutesToResult + '</td>';
    						x += '<td>' + report.minutesSampleStabilityLimit + '</td>';
    						x += '<td>' + report.testResult + '</td>';
    						x += '<td>' + report.daysSampleStabilityLimit + '</td>';
    						x += '<td>' + report.daysToResult + '</td>';
    						x += '<td>' + report.testSampleOutOfStabilityIndt + '</td>';
    					//console.log(i);
    						if(j==1)
    							{
    							x += '<td id="verified'+j+'"><input id="verified'+j+'" style="font-size:xx-small;" type="checkbox" onclick="handleChange(this)"></td></tr>';
    							}
    						else{
    							x += '<td id="verified'+j+'"><input id="verified'+j+'" style="font-size:xx-small;" type="checkbox" checked onclick="handleChange(this)"></td></tr>';
    						}
    						j++;
    					
    					}
    				
    			});//.each
    			jQuery_2_2_2('#reportbody').append(x);
    			
    			
    			table=jQuery_2_2_2('#reporttable').DataTable({
    				"stateSave": true,
    				checkboxRefresh:true,
    				dom: 'Bfrtip',
    			    buttons: [
    			    	{
    			    	extend: 'excel',
    		            exportOptions: {
    		                orthogonal: 'sort'
    		            }
    		        }        
    		    ],
    		    columnDefs: [{
    		       targets:[19],
    		       render: function(data, type, row, meta){
    		          if(type === 'sort'){
    		             var $input = $(data).find('input[type="checkbox"]').addBack();
    		             data = ($input.prop('checked')) ? "1" : "0";
    		          }

    		          return data;    
    		       }
    		    }],
    			     
    			    
    			    scrollY:        true,
    			    scrollX:        true,
    			    scrollCollapse: true,
    			    fixedColumns:   {
    			        leftColumns: 1,
    			        rightColumns: 1
    			    }
    			
    			    });//.datatable

    		});//.done
    		
    	
    		
    });//document.ready
    var k;

    function handleChange(e){
[enter image description here][1]
       $(e).attr('checked', !($(e).attr('checked')));  
    	console.log(e.id);
    	
    	

    	
    }


    th, td {
    	white-space: nowrap;
    }

    div.dataTables_wrapper {
    	width: 1600px;
    	margin: 0 auto;
    }


    <!DOCTYPE html>
    <html lang="en">
    <head>
    <meta charset="UTF-8">
    <title>Reports</title>
    <script type="text/javascript"
    	src="https://code.jquery.com/jquery-3.3.1.min.js"></script>
    <link
    	href="https://cdn.datatables.net/1.10.19/css/jquery.dataTables.min.css"
    	type="text/css" rel="stylesheet" />
    <link
    	href="https://cdn.datatables.net/fixedcolumns/3.2.6/css/fixedColumns.dataTables.min.css"
    	type="text/css" rel="stylesheet" />
    <script src="https://code.jquery.com/jquery-2.2.2.min.js"
    	integrity="sha256-36cp2Co+/62rEAAYHLmRCPIych47CvdM+uTBJwSzWjI="
    	crossorigin="anonymous"></script>
    <script

    	src="https://cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></script>
    <script type="text/javascript"
    	src="https://cdn.datatables.net/fixedcolumns/3.2.6/js/dataTables.fixedColumns.min.js"></script>
    <script
    	src="https://cdn.datatables.net/buttons/1.5.2/js/dataTables.buttons.min.js"></script>
    	<link
    	href="https://cdn.datatables.net/buttons/1.5.2/css/buttons.dataTables.min.css" type="text/css" rel="stylesheet" />
    <script type="text/javascript"
    	src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js"></script>
    <script type="text/javascript"
    	src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.36/pdfmake.min.js"></script>
    <script type="text/javascript"
    	src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.36/vfs_fonts.js"></script>
    <script type="text/javascript"
    	src="https://cdn.datatables.net/buttons/1.5.2/js/buttons.html5.min.js"></script>
    <script type="text/javascript">var jQuery_2_2_2=$.noConflict(true);</script>

    </head>
    <body>
    	<form id="myform">
    		<div id="report" style="padding: 1% 1%">
    			<h2 class="section_header">
    				<span style="color: #18d1f2">RE</span>PORT
    			</h2>
    			</br>

    			<table id="reporttable" class="display" cellspacing="0" width="100%"
    				border="1px solid black" ; border-collapse="collapse";>
    				<thead>
    					<tr>
    						<th>StudyID</th>
    						<th>Country</th>
    						<th>Investigator_Name</th>
    						<th>ScreenID</th>
    						<th>SubjectID</th>
    						<th>Subject_Initials</th>
    						<th>Requisition_Number</th>
    						<th>Test_Name</th>
    						<th>Vist_Name</th>
    						<th>Collection_Date</th>
    						<th>Collection_Time</th>
    						<th>Result_Date</th>
    						<th>Result_Time</th>
    						<th>MinutesToResult</th>
    						<th>Minutes_Sample_Stability_Limit</th>
    						<th>Test_Result</th>
    						<th>Days_Sample_Stability_Limit</th>
    						<th>DaysToResult</th>
    						<th>Taste_Sample_Out_of_Stability_Ind</th>
    						<th>Verified ?</th>


    					</tr>
    				</thead>
    				<tbody id="reportbody">

    				</tbody>
    				<tfoot>
    					<tr>
    						<th>StudyID</th>
    						<th>Country</th>
    						<th>Investigator_Name</th>
    						<th>ScreenID</th>
    						<th>SubjectID</th>
    						<th>Subject_Initials</th>
    						<th>Requisition_Number</th>
    						<th>Test_Name</th>
    						<th>Vist_Name</th>
    						<th>Collection_Date</th>
    						<th>Collection_Time</th>
    						<th>Result_Date</th>
    						<th>Result_Time</th>
    						<th>MinutesToResult</th>
    						<th>Minutes_Sample_Stability_Limit</th>
    						<th>Test_Result</th>
    						<th>Days_Sample_Stability_Limit</th>
    						<th>DaysToResult</th>
    						<th>Taste_Sample_Out_of_Stability_Ind</th>
    						<th>Verified?</th>


    					</tr>
    				</tfoot>
    			</table>

    		</div>
    		<input
    			style="color: white; font-weight: bold; font-size: larger; width: 80px; height: 30px; background-color: deepskyblue; border: 1px solid #223445; border-radius: 18px; -webkit-border-radius: 5px; -moz-border-radius: 5px"
    			type="submit">
    	</form>
    </body>
    <script>







        Xhr('#report').done(function (json) {
            console.log(json);
            var x;

                jQuery_2_2_2.each(json, function (i, report) {

                    radio.fill(0);
                    if(report.testSampleOutOfStabilityIndt==1)
                        {
                            x += '<tr><td>' + report.studyId + '</td>';
                            x += '<td>' + report.country + '</td>';
                            x += '<td>' + report.investigatorName + '</td>';
                            x += '<td>' + report.screenId + '</td>';
                            x += '<td>' + report.subjectId + '</td>';
                            x += '<td>' + report.subjectInitials + '</td>';
                            x += '<td>' + report.requisitionNumber + '</td>';
                            x += '<td>' + report.testName + '</td>';
                            x += '<td>' + report.visitName + '</td>';
                            x += '<td>' + report.collectionDate + '</td>';
                            x += '<td>' + report.collectionTime + '</td>';
                            x += '<td>' + report.resultDate + '</td>';
                            x += '<td>' + report.resultTime + '</td>';
                            x += '<td>' + report.minutesToResult + '</td>';
                            x += '<td>' + report.minutesSampleStabilityLimit + '</td>';
                            x += '<td>' + report.testResult + '</td>';
                            x += '<td>' + report.daysSampleStabilityLimit + '</td>';
                            x += '<td>' + report.daysToResult + '</td>';
                            x += '<td>' + report.testSampleOutOfStabilityIndt + '</td>';
                        //console.log(i);
                            if(j==1)
                                {
                                x += '<td id="verified'+j+'"><input id="verified'+j+'" style="font-size:xx-small;" type="checkbox" onclick="handleChange(this)"></td></tr>';
                                }
                            else{
                                x += '<td id="verified'+j+'"><input id="verified'+j+'" style="font-size:xx-small;" type="checkbox" checked onclick="handleChange(this)"></td></tr>';
                            }
                            j++;

                        }

                });//.each
                jQuery_2_2_2('#reportbody').append(x);


                table=jQuery_2_2_2('#reporttable').DataTable({
                    "stateSave": true,
                    checkboxRefresh:true,
                    dom: 'Bfrtip',
                    buttons: [
                        {
                        extend: 'excel',
                        exportOptions: {
                            orthogonal: 'sort'
                        }
                    }        
                ],
                columnDefs: [{
                   targets:[19],
                   render: function(data, type, row, meta){
                      if(type === 'sort'){
                         var $input = $(data).find('input[type="checkbox"]').addBack();
                         data = ($input.prop('checked')) ? "1" : "0";
                      }

                      return data;    
                   }
                }], 
                    scrollY:        true,
                    scrollX:        true,
                    scrollCollapse: true,
                    fixedColumns:   {
                        leftColumns: 1,
                        rightColumns: 1
                    }

                    });//.datatable

            });//.done
    });/
    function handleChange(e){
        $(e).attr('checked', !($(e).attr('checked'))); 
    }
1
checkboxdatatabledatatablesdatatables-1.10tabletools
Is this data being saved to DB initially when you change the state to verified? If not what is the purpose of this ? – Raheel Shah
No I am doing everything on the client-side only for now.The authorized user should be able to change the verified checkbox and then download the latest entries about the verified status in excel, not saving back to db for now. – Sikha

1 Answers

1
votes

Tried again and basically changed the format when exporting. Also changed the check box data to be manually changed.

$(document).ready(function() {

$('.checkbox').click(function() {
    debugger
    if ($(this).data("checked") === 0) {
        $(this).data("checked", "1")
    } else {
        $(this).data("checked", "0")
    }
});
$('#example').DataTable({
    dom: 'Bfrtip',
    buttons: [{
            extend: 'copyHtml5',
            exportOptions: {
                orthogonal: 'export'
            }
        },
        {
            extend: 'excelHtml5',
            exportOptions: {
                orthogonal: 'export',
                format: {
                    body: function(data, row, column, node) {
                        // Strip $ from salary column to make it numeric
                        debugger;
                        if (column === 0) {
                            data = $(node).children().data("checked");
                        }
                        return data;
                    }
                }
            }
        },
        {
            extend: 'pdfHtml5',
            exportOptions: {
                orthogonal: 'export'
            }
        }
    ]

});

});

Heres is the fiddle for the same https://jsfiddle.net/raheelshah31/fm6a74yu/2/