excelHtml5: How to export checkbox data

rdm

In this scenario, I have a table with checkboxes, like this.

However, when exporting to Excel, I see blank cells like this.

How do i need to configure the button so that true/false columns do not appear as blanks? I read Buttons for DataTables but did not see any cases for treatment of checkboxes or boolean fields.

This is the code I have so far. In this scenario, let's say that only columns 1 and 2 are checkboxes. What would I need to add?

$(() => {
            $(".display").DataTable({
                dom: 'Bfrtip',
                buttons: [{
                    extend: 'excelHtml5',
                    exportOptions: {
                        columns: [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20]
                    }
                }]
            });
        });

allan

Is the checkbox based on underlying data (i.e. is it being rendered) or is it just a checkbox that is not based on saved data? The first can be done - the second is a whole lot harder.

Allan

rdm

Here's a subsample of the HTML as shown on the Chrome Developer Tools.

This is what a checked box looks like:

<td>
    <input checked="checked" class="check-box" disabled="disabled" type="checkbox">
</td>

And this is what an unchecked box looks like.

<td>
    <input class="check-box" disabled="disabled" type="checkbox">
</td>

rdm

So, inspired by Column Rendering, I created this block. If I apply it to the table that is visible on the screen, the checkboxes are replaced by "Yes" and "No". When I export to Excel, it's the string "Yes" and "No" are exported. There are no blanks anymore.

Is there a way I can get the desired rendering in the exported Excel without also changing the screen rendering?

$(() => {
            $(".display").DataTable({
                dom: 'Bfrtip',
                scrollX: true,
                buttons: [
                    {
                        extend: 'excelHtml5',
                        exportOptions: {
                            columns: [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20]
                        }
                    }
                ],
                columnDefs: [
                    {
                        "render": function (data, type, row) {
                            var i = $(data).prop("checked")===true?"Yes":"No";
                            return i;
                        },
                        "targets": [1,2,13,16,17,19]
                    }
                ]
            });
        });

allan

I'm very surprised that works since data is normally just a string and $(data) would therefore be relatively meaningless. I'm fairly sure that will break in some way, but I'm not sure how, as I'm not even sure how it is working!

If it is working, then you could use orthogonal export to get different values.

If you have a link to the page I can take a look to see how the checkbox is being generated a little closer. Because what I want to know is if it is based on data in the database (i.e. are you setting the checked property for it)?

Allan

rdm

@allan -- The code under discussion is trapped within an intranet system and the data itself is confidential. I looked for Fiddle sites with both C# MVC and SQL Server so I could replicate this using dummy data. No such luck. I suppose I could try creating a separate Visual Studio solution that I could share via Google Drive, but because it's connected to internal parts I can't share, I don't know how to truly replicate in a shareable way what I have.

But as for my use of the $(data) line, I stumbled into it. It hasn't broken for me yet.

This is strictly a reporting page and does not use Editor, so yes -- all data is supplied by the database and generated by the controller. (This is a .NET MVC system).

allan

With the checkbox - when the page loads, are they always unchecked? Or can they be set to be checked when the data is loaded? If so, how is that done?

Allan

rdm

The checkbox is the default rendering of SQL bit fields (true/false) that are either checked or unchecked when the user enters or edits a record on separate Create or Edit views. (The page under discussion is the Index View). The field can be checked or unchecked, depending on what was entered for that record. So the HTML possibilities are:

Checkbox is checked:

<td>
   <input checked="checked" class="check-box" disabled="disabled" type="checkbox">
</td>

Checkbox is not checked:

<td>
   <input class="check-box" disabled="disabled" type="checkbox">
</td>

This is what you would see without the jQuery code above. The render function I worked out renders the field as

<td>Yes</td>

or

<td>No</td>

The Excel export button also shows the field as Yes or No.

allan

Super - thanks. I see how it is working now - the HTML <input ...> string is being converted to be a node there and there only, allowing it to work on initial load. If you toggle the checkbox value, I think it will start failing, unless you update the HTML for the cell at that point.

The Yes and No should actually be shown in the export there (although as I say, it won't take into account a local toggle).

You could perhaps try using an orthogonal option if that isn't working, but as far as I can see it should. I'd need a test case showing the issue to be able to debug why it isn't.

Allan

rdm

Now that I'm taking a wider view, I think I've reached the good-enough place. It would have been nice to export the checkbox as yes/no without needing to change the underlying HTML, but I've run out of time and need to move on to other tasks.

After another day of testing, the code hasn't broken. As this is a view-only instance -- data is inserted and edited on other pages -- there is no fear of attempts to change data.

Xampy

I have fixed it. I know this is from more than a year ago but I got the solution.

$(".display").DataTable({
    dom: 'Bfrtip',
    columnDefs: [{
        "render": function (data, type, row) {
            var i = (type === 'export' ? ($(data).prop("checked")===true ? 'Yes' : 'No') : data);
            return i;
        },
        "targets": [1,2,13,16,17,19]
    }],
    buttons: [{
        extend: 'excelHtml5',
        exportOptions: {,
            columns: [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20],
            orthogonal: 'export'
        }
    }]
});