Table formatting in export

jmcshan1

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!

allan

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

Allan

jmcshan1

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.

jmcshan1

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

allan

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

Allan

liudaxingtx

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.

allan

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

warmnet2001

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

allan

@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

warmnet2001

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

Tklaversma

"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!

allan

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

chrisShick

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.

seyad96

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

allan

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

chrisShick

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.

allan

This would be the starting point.

Allan

dait

@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!

TLeM4

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.

allan

Clever trick - nice one. Thanks for suggesting that.

Allan

kumar pkvel

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

    } ]


} );

});

Joyrex

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?

Joyrex

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

allan

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

Joyrex

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

SaiMadhanRockzz

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

thanks

allan

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

Allan