Table formatting in export

Table formatting in export

jmcshan1jmcshan1 Posts: 9Questions: 3Answers: 0

Hello! First of all, DataTables is awesome and we are able to produce some amazing tables for our customers to use. I am using the Buttons extension extensively and I am wondering if there would be any options to support hidden/filtered columns in the table export. I'd like to allow people to search or filter specific rows, hide some columns, then export the data that appears in the table.

If this is not in the plans for datatables, could you point me in the direction of the place to begin in the code and I'll try to implement myself.

Thanks!

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 57,737Questions: 1Answers: 9,204 Site admin
    Answer ✓

    This already exists. Have a look at this example and the documentation that the example links to.

    Allan

  • jmcshan1jmcshan1 Posts: 9Questions: 3Answers: 0

    Ok, awesome.

    I'm looking through the documentation for pdfHtml5 and pdfMake and trying to see how to preserve table coloration in export as well. Anyone given this a shot?

    If not, I'll post my code for the customize function when I complete it.

  • jmcshan1jmcshan1 Posts: 9Questions: 3Answers: 0

    I had access to the table object through a variable named instance.tbl, so this is my customize function:

    customize: function(doc) {
        var rgbaStrToHex = function(str) {
            var firstParens = str.split('('),
                nums = firstParens[1].split(')')[0].split(','),
                a = 1;
            if (nums.length == 4) {
                a = parseFloat(nums[3]);
            }
            return '#' + nums.filter(function(x,ix) { return ix < 3;})
                .map(function(x) {
                    x = Math.round(parseInt(x) * a, 0).toString(16);
                    return ((x.length == 1) ? "0" + x : x);
                }).join("");
        };
        var tblBody = doc.content[1].table.body;
        $(instance.tbl.context[0].nTable).find('tr').each(function(ix, row) { 
            var index = ix; 
            var rowElt = row;
            $(row).find('th,td').each(function(ind, elt) {
                if (elt.tagName === "TH") return;
                var color = $(elt).css('background-color');
                if (color === 'rgba(0, 0, 0, 0)') {
                    color = $(rowElt).css('background-color');
                }
                if (color !== 'rgba(0, 0, 0, 0)') {
                    delete tblBody[index][ind].style;
                    tblBody[index][ind].fillColor = rgbaStrToHex(color);
                }
            });
        });
    }
    
  • allanallan Posts: 57,737Questions: 1Answers: 9,204 Site admin

    Cool - thanks for sharing your code with us :-)

    Allan

  • liudaxingtxliudaxingtx Posts: 2Questions: 1Answers: 0

    Says instance is not defined in the console, how to solve it then. can someone help me. I just want to change the margin and font size of the table.

  • allanallan Posts: 57,737Questions: 1Answers: 9,204 Site admin

    instance.tbl in the above code is the result from $().DataTable() which jmcshan1 has saved into a variable for his system. You'll need to update the code to match your own.

    Allan

  • warmnet2001warmnet2001 Posts: 6Questions: 0Answers: 0

    And to format text in any column?
    This is my code, but does not work:

    {Extend 'csv' fieldSeparator "" extension '.txt' header: false, text: 'Export'
    ExportOptions: {
    columns: '3: visIdx'
    render: function (data, type, row) {
    return data + '1234';
    }
    }
    }

    Long that I can not solve.
    Best regards

  • allanallan Posts: 57,737Questions: 1Answers: 9,204 Site admin
    edited September 2015

    @warmnet2001 - Please link to a test case showing the issue as required in the forum rules.

    Edit - The is no render function in the export buttons - columns.render if the function you want. Worth also reading over the orthogonal data manual since the export options provide the ability to read orthogonal data.

    Allan

  • warmnet2001warmnet2001 Posts: 6Questions: 0Answers: 0

    I give up, I can not fix it and I can not lose more time. I'll have to find another tool. Thank you too.
    Best regards

  • TklaversmaTklaversma Posts: 12Questions: 1Answers: 0

    "The is no render function in the export buttons - columns.render if the function you want".

    Can you adjust my example in such a way that I can see how to use columns.render, so that I can adjust content of cells before explicitally exporting to Excel?

    // Define export buttons for DataTables
    var exportButtons = [
        {
            extend        : 'excel',
            className     : 'fa fa-file-excel-o',
            defaultContent: '',
            title         : $('h1').html(),
            text          : '',
            exportOptions : {
                columns: ':visible'
            },
            somethingLikeRender : function (data, type, row, meta) {
                // EDIT CELLS...
            }
        },
        ...
    ];
    

    Thanks in advance!

  • allanallan Posts: 57,737Questions: 1Answers: 9,204 Site admin

    There is an example available in github here. It hasn't been published on this site yet, but it will be soon.

    There is an alternative method which you might prefer, but it requires the unreleased version of Buttons. It will be released next week most likely.

    Allan

  • chrisShickchrisShick Posts: 4Questions: 0Answers: 0

    Hey Allan,

    Is there a way the exporting option can be modified to apply additional header rows? We are trying to supply additional information at the top of the files, such as the name of the report and the parameters at which it was made from.

  • seyad96seyad96 Posts: 4Questions: 1Answers: 0
    edited January 2016

    chrisShick,

    The way I got around it was to create variables prior to initializing the datatable and then passing the variables to the MESSAGE option. EXCEPT: I cannot retrieve the total number of records that are returned in order to display it in the PRINT and PDF features.

    filter += ($('#txtSDG').val() > '') ? ' SDG = ' + $('#txtSDG').val() + "; " : '';
    filter += ($('#txtSampleNo').val() > '') ? ' SAMPLE NUMBER = ' + $('#txtSampleNo').val() + "; " : '';
    filter += ($('#txtParameter').val() > '') ? ' PARAMETER = ' + $('#txtParameter').val() + "; " : '';
    filter += ($('#txtCNo').val() > '') ? ' CASE NUMBER = ' + $('#txtCasNo').val() + "; " : '';
    var sMsg = 'Check Print No: ____________  Project No: ____________  Artist: ________________________  ' + $.datepicker.formatDate('mm/dd/yy', new Date()) + '<br><br>Checked By: ____________ Date: ____________';
    sMsg += '<br><br>' + filter;
    
    $('#myDTable').dataTable({
    ...
    ...
       buttons: [
          {
            extend: 'print',
            exportOptions: {
                 columns: ':visible'
            },
            orientation: 'portrait',
            defaultStyle: {
                 alignment: 'center',
            },
            message: '<p style="font-size:11px">' + msg + '<br><br>Total Records: ' + $('#cpTable').DataTable().data().length +  '</p>',
             customize: function (win) {
                 $(win.document.body)
                      .css('font-size', '14pt')
                      .prepend('<img src="../Images/company_logo.png" height=79 width=116 style="position:absolute; top:0; left:0;" />');
                 $(win.document.body).find('table')
                      .addClass('compact')
                      .css('font-size', '10px')
                      .attr('align', 'center')
                      .css('width', '870px');
                 }
          }
       ],
    });
    
  • allanallan Posts: 57,737Questions: 1Answers: 9,204 Site admin

    Is there a way the exporting option can be modified to apply additional header rows?

    Currently no - sorry (unless you use a workaround like that suggested by @seyad96). Currently Buttons will export only one row in the header and footer.

    Allan

  • chrisShickchrisShick Posts: 4Questions: 0Answers: 0
    edited January 2016

    But, wouldn't the above only work only with the print option?

    Allan, is there a function I can override to make this work? It would be great if you can point me in that direction.

    If it is all being created into a string and then the file creation, I would love to see if I could just prepend additional rows.

  • allanallan Posts: 57,737Questions: 1Answers: 9,204 Site admin
  • daitdait Posts: 3Questions: 1Answers: 0

    @chrisShick
    I had the same problem and solved it by using a default message, which can be updated at the customize section:

                extend: 'pdfHtml5',
                    // default line above table
                    message: 'Report without a Date ',
                    //pageSize: 'A4',
                    //download: 'open',
                    customize: function(doc) {
                        // doc.content[0] -> title
                        // doc.content[1] -> message
                        // doc.content[2] -> table 
                        // change message
                        if ($('#datepicker-myDate').val().length != 0) {
                            doc.content[1].text = 'Report Date: ' + $('#datepicker-myDate').val();
                        }
                        doc.content[2].table.body[0][0].text = 'colTitle1';
                        doc.content[2].table.body[0][1].text = 'colTitle2';
                        doc.content[2].table.body[0][2].text = 'colTitle3';
                        doc.content[2].table.body[0][3].text = 'colTitle4';
                        doc.content[2].table.body[0][6].text = 'colTitle7';
    

    PS: i am setting the column titles manually here, because i used input fields in the table header for filtering.

    Hope this helps!

  • TLeM4TLeM4 Posts: 2Questions: 0Answers: 0

    I'm in the same case as dait (input fields in table header for filtering), and i've found i simpler way.
    You can just add in the header, just after the input field, a hidden div with column title like this :

    <div style="display:none">Column title</div>
    
    

    Very easy, just one line, it work for all export ;)

    PS: here, the use of "display:none" instead of visibility:hidden is better as it will not show a blank space.

  • allanallan Posts: 57,737Questions: 1Answers: 9,204 Site admin

    Clever trick - nice one. Thanks for suggesting that.

    Allan

  • kumar pkvelkumar pkvel Posts: 6Questions: 1Answers: 0
    edited August 2016

    TypeError: win.content is undefined

    $(document).ready(function() {
    $('#tblexport').DataTable( {
    //"dom": 'T<"clear">rftip',
    "paging": false,
    "ordering": false,
    "info": false,
    dom: 'Bfrtip',
    buttons: [ {
    extend: 'excel',
    customize: function (win) {
    console.log(win);

                            var tblBody = win.content[1].table.body;
                            $("#tblexport").find('tr').each(function (ix, row) {
                                var index = ix;
                                var rowElt = row;
                                $(row).find('th,td').each(function (ind, elt) {
                                    if (elt.tagName === "TH") return;
                                    if (tblBody[index][ind].text == "Soft") {
                                        tblBody[index][ind].fillColor = "#FFA500";
                                    } else if (tblBody[index][ind].text == "Soft") {
                                        tblBody[index][ind].fillColor = "#7CFC00";
                                    }
                                });
                            });
                        }
    
        } ]
    
    
    } );
    

    });

  • JoyrexJoyrex Posts: 92Questions: 14Answers: 3

    I am using @jmschan1's excellent code to preserve the table colors on export via Buttons, but I am running into an error:
    Uncaught TypeError: Cannot read property '1' of undefined

    It's from his code here:
    var tblBody = doc.content[1].table.body;

    I only have one dataTable in my script, so I am not sure what it is erroring on - I changed the 'instance' variable to reflect the variable I set for my DataTable (oTable).

    I guess I'm not understanding what element doc.content[1] is referring to. Can anyone point me in the right direction?

  • JoyrexJoyrex Posts: 92Questions: 14Answers: 3

    OK, I think I am getting closer - it looks like doc.content is in reference to PDF export, and in my case, I am using the above code for an HTML5 Excel export - I'll take a look at the HTML5 Excel export and see if there is something similar - hopefully it doesn't break the rest of the code...

  • allanallan Posts: 57,737Questions: 1Answers: 9,204 Site admin

    Yes, for the Excel export you have to modify the XML files that are used.

    This example shows a very basic example of that. To insert information you need to have a bit of an understanding of the Open Spreadsheet format.

    Allan

  • JoyrexJoyrex Posts: 92Questions: 14Answers: 3

    Yes, thanks - I am looking into it now - if I figure it out, I'll be sure to post my solution to benefit others!

  • SaiMadhanRockzzSaiMadhanRockzz Posts: 2Questions: 0Answers: 0

    can i replace header text for each and every column while export to excel , please help me

    thanks

  • allanallan Posts: 57,737Questions: 1Answers: 9,204 Site admin
    edited November 2016

    @SaiMadhanRockzz - Please do not post duplicates. This is specifically requested in the forum rules.

    Allan

This discussion has been closed.