excelHtml5: How to export checkbox data

excelHtml5: How to export checkbox data

rdmrdm Posts: 192Questions: 54Answers: 4

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]
                    }
                }]
            });
        });

Answers

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

    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

  • rdmrdm Posts: 192Questions: 54Answers: 4

    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>
    
  • rdmrdm Posts: 192Questions: 54Answers: 4

    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]
                        }
                    ]
                });
            });
    
    
  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    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

  • rdmrdm Posts: 192Questions: 54Answers: 4
    edited November 2017

    @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).

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

    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

  • rdmrdm Posts: 192Questions: 54Answers: 4

    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.

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

    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

  • rdmrdm Posts: 192Questions: 54Answers: 4

    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.

  • XampyXampy Posts: 1Questions: 0Answers: 0
    edited March 2019

    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'
            }
        }]
    });
    
This discussion has been closed.