Exporting checkbox data to excel using jQuery datatables

Question

I have an <ASP:GridView> with few columns with boolean values returned from SQL Server. I am using datatables plugin looks good but when I export the data to Excel, all are exporting to Excel except the bool values that show empty.

Here is my simple jQuery:

$(document).ready(function () {
         $('#<%=GridView1.ClientID%>').DataTable({
            dom: 'Bfrtip',
            buttons: [
                'excel'
            ]
        });
    });

I have tried as stated in the link https://m.datatables.net/forums/discussion/45691/excelhtml5-how-to-export-checkbox-data but in vain.

Tried code 1

columnDefs: [
                {
                    "render": function (data, type, row) {
                        var i = $(data).prop("checked")===true?"Yes":"No";
                        return i;
                    },
                    "targets": [1,2,13,16,17,19]
                }
            ]

It displays "NO" even bool col value is true.

Tried code 2

buttons: [            
        {
            extend: 'excel',
            exportOptions: { orthogonal: 'export' }
        }
    ]

Tried code 3

buttons: [            
        {
            extend: 'excelHtml5',
            exportOptions: { orthogonal: 'export' }
        }
    ]

Any help, thanks in advance.

It cannot return No. It can only return true or false. Please read my question carefully cuz I am only asking about $(data).prop("checked") — CodingYoshi
– CodingYoshi, Commented Dec 2, 2017 at 15:24
$(data).prop("checked") returns 'false' and var i contains 'No' — Ramesh
– Ramesh, Commented Dec 2, 2017 at 15:45
So should it not be retuning false? I do not have access to data and you did not post it so I cannot tell. — CodingYoshi
– CodingYoshi, Commented Dec 2, 2017 at 16:06

CodingYoshi · Accepted Answer · 2017-12-03 21:00:56Z

I have never used the DataTable plugin, but we are developer so let's go figure out the issue. You created a JSFiddle and the important bit in your HTML is this:

  <td>72</td>
  <td><span class="aspNetDisabled" title="Is Credit"><input id="CentreContent_GridView1_ctl00_0" type="checkbox" name="ctl00$CentreContent$GridView1$ctl02$ctl00" disabled="disabled" /></span></td>
  <td><span class="aspNetDisabled" title="Is Quotation"><input id="CentreContent_GridView1_ctl01_0" type="checkbox" name="ctl00$CentreContent$GridView1$ctl02$ctl01" checked="checked" disabled="disabled" /></span></td>

You have three td tags so you have 3 columns. Here is what the documentation for the plugin says about if you were to provide a function for render:

If a function is given, it will be executed whenever DataTables needs to get the data for a cell in the column.

This is your callback function:

function (data, type, row){
    var i = $(data).prop("checked")===true?"Yes":"No";
    return i;
}

So basically it will call your function with three arguments. The argument we are interested in (for your question) is data. The data parameter will contain the html for the cell so you can do some decision making based on its contents. Since your HTML for cell has this structure

<td><span><input></span></td>

then when you do $(data), JQuery will select all of the HTML and then look for the checked attribute but obviously that will not work. You have to specifically tell JQuery which element to select and then look for the attribute. Therefore, your code need to be like this:

var $element = $(data).find(':input')
var i;
if ($element && $element.is(':checkbox')) {
    i = $element.prop('checked') === true ? "Yes" : "No";
} 
else {
    // What will you do if not checkbox?
}

Now we are specifically looking for an input element which is a checkbox and checking if the checkbox is checked.

¯_(ツ)_/¯ Here is the JSFiddle.

Collectives™ on Stack Overflow

Exporting checkbox data to excel using jQuery datatables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related