csv, excel and pdf custom columns

diaxis

Hi, i have recently started using datatable' button to generate these 3 export format(csv, excel and pdf), my problem is that one of the columns is a dropdown and when i export it i get all the different option in it instead of the selected one.

i was wondering if anyone had any experience with this?

A lot of answer I find are for table tools, not the actual replacement of it; Buttons.

how i declre my table

var table = $('#calls').DataTable(
{
    'language': { "url": variables.paths.lang },
    'responsive': true,
    'order': [[7, "desc"]],
    'dom': 'B<"row"<"col-sm-12 text-align-right">r><"row"<"col-sm-12"t>><"row"<"col-sm-6"if><"col-sm-6"lp>><"row"<"col-sm-12">>',
    buttons: [
        'csv', 'excel',{
            extend : "pdf",
            text: 'PDF',
            orientation : 'landscape',
        }
    ],

and then proceed to make an ajax call and draw my various columns etc etc

So, the question is: How do i hide a specific column only from the exports OR how to specify manually the data to show in a column?

jLinux

You might have to use the customize option, which is supported by the pdfHtml5 and pdf, but not the pdfFlash, I believe its basically a wrapper around the PDFMake functionality (Per Datatables Button Doc for both of those buttons)

If that doesn't work out for you, I would first recommend using DataTables Edit, that has multi select, and its obviously going to work perfectly fine with all the export options.

If for some reason you cant use that, then you could go another route. I also have multiple select fields in my tables, but they arent normal input fields, I use jQuery x-editable..
Screenshots: http://d.pr/i/11bVr & http://d.pr/i/11Vtb
It might not be exactly what you are looking for, but I would recommend it, or something like it. I dont see an easy/other way for DataTables to be able to export the selected element in an input, it exports what it sees in the DOM.

Honestly, your best bet is to use the customize option, like I said first, you can edit anything in the DOM, so you can use a loop to loop through the rows, then in the correct column, get the select elements selected value, and replace the whole select input with that value...

Best of luck

jLinux

P.S. you just helped me notice an issue in my own page, thanks! lol

mjaf

Previously, when there were the TableTools, you could postprocess the export data.
For unknown reasons, this does not seem to be possible anymore, and it is very limiting.
I have similar issues, when there is e.g. a link within a cell. When exporting, it will strip everything of that link, leaving me with an empty cell.

I tried quite some things to get around this, but in the end I had to patch the datatables.js to implement a new export config option, called "dataPostProcess". It basically hands over the prepared data to a callback you can define, where you process the data and send it back for export.

I tried to make a patch, but I actually never did this before and the Git somehow does not match what I have in my project.

datatables.js
line: ~92997
function: var _exportData = function ( dt, inOpts )

The line probably won't help, as this is a combined file, including the extensions and such.
There are multiple functions _exportData, but only one with the params "dt", and "inOpts", which is the one that needs to be adjusted.

I removed the "return" at the end of the function and replaced it with this:

var returnData = {
    header: header,
    footer: footer,
    body:   body
};

if ( config.dataPostProcess )  {
    console.log("Post-Processing export data");
    returnData = config.dataPostProcess(dt, returnData);
}
return returnData;

In my dataTable config I added this to the button config:

{
extend  : 'excel',
title       :   'test.xlsx',
exportOptions : {
    stripHtml : false,
    dataPostProcess : function(dt, str)  {
        console.log('preparing data');
        if (str.body.length)
            str.body = prepareTableData(str.body);
        if (str.header.length)
            str.header = prepareTableData(str.header);
        return str;
    }
}

prepareTableData is then a custom function that replaces stuff within the data that is then going to be exported.

Hacking the library is far from being optimal, of course. If anyone has a better idea or can tell me where to submit a change request... :)

Best
mjaf

diaxis

From what i see, the best option for me would be mjaf' suggestion because i do have a lot of different data types in my tables (checkboxes, dropdown, links). its a feature that we want to offer to our power users but we do not expect a big ROI on this function and I am scared of the bugs this might cause and the time to debug if I modify the source code. I will try it out in a branch and see how it goes.

jLinux

Im sure you can ask @allan to add this as a new feature, or he can tell you a better work around (that doesnt involve messing with the datatables.js files)

allan

Previously, when there were the TableTools, you could postprocess the export data.

At the moment, the option to preprocess the data is to use DataTables orthogonal data capabilities. I'll be creating an example of that soon.

It appears that this isn't going to be enough though, and I'm going to add a customize option to all the export types for the next release do you can modify the data to be exported directly. The PDF button already has this option, so it makes sense to extend it to all others.

Allan