CSV export with only one header line, when multiple are present (docs?)

CSV export with only one header line, when multiple are present (docs?)

kc5151kc5151 Posts: 47Questions: 4Answers: 1
edited September 28 in Free community support

Hey there.

Since this is a general question, I'm not including an example.

I found https://datatables.net/extensions/buttons/examples/html5/complex-header.html

This site is showing how multiple headers lines are being rendered in (CSV) exports. What I want to do is omitting my second header line from the export altogether.

As far as docs are concerned, I only found:

https://datatables.net/reference/button/csvHtml5

I assume I should be able to install a callback for "header", but I cannot find any documentation (googling it does turn some ancient stuff up, but I'd rather work with the current (>= 2) Datatables API.

Thanks a bunch in advance.

This question has an accepted answers - jump to answer

Answers

  • rf1234rf1234 Posts: 2,944Questions: 87Answers: 416
    edited September 28

    Just take a look at the export options that will have already seen under the second link above.

    Here is an abbreviated example from my own coding:

    exportOptions: {
        modifier: { selected: null }, //make sure all records show up
        format: {            
            header: function ( data, column ) {
                //replace html tags with one space
                return data.replace(/<[^>]*>/g, ' ');
            }
        }
    }
    

    I replace html tags in the header with something else. Just take a look at the "data" parameter in your debugger. You'll probably be able to manipulate that.

    Another option would be to use "customize". I use both options. If you want a full button definition from my own coding, just let me know. I can post it here if you like.

  • kc5151kc5151 Posts: 47Questions: 4Answers: 1
    edited September 28

    Sorry for being daft, but I don't quite get your solution. I have two header lines in my datatable. I want to omit the second header line altogether. I don't see how doing a regexp replace on one of those lines (or both?) would help. Also, I cannot seem to find the docs for the header callback, so maybe this is a site bug?

    Thanks again for your time :blush:

  • rf1234rf1234 Posts: 2,944Questions: 87Answers: 416
    edited September 28

    I don't see how doing a regexp replace on one of those lines (or both?) would help.

    Of course it doesn't help with that ... It is just a code example. As I said: Just take a look at the "data" parameter and manipulate it. I guess you will need to experiment somewhat.

    Also, I cannot seem find the docs for the header callback, so maybe this is a site bug?

    I don't know that. I am just a user like you who figured out how to do what I needed to do. I didn't find any specific documentation about the header callback either.

  • kc5151kc5151 Posts: 47Questions: 4Answers: 1

    You mentioned "customize" before. Are there any docs available for that? Thank you so much :smiley:.

  • rf1234rf1234 Posts: 2,944Questions: 87Answers: 416
    edited September 28

    I am just checking whether I can get it done myself. Experimenting a little. Will get back to you in a moment.

    First thing I did is to return space from export options. that completely empties the header line. It also deletes the formatting (I had a colored background; it is Excel). All I need to figure out now is how to delete that line completely. Maybe I can get that done with "customize". Not sure.

  • rf1234rf1234 Posts: 2,944Questions: 87Answers: 416
    edited September 28

    I just found out that I never used "customize" for csv. My users want Excel :-).

    The Excel docs say the following about "customize":

    I use it simply like this because I don't need the other parms. I do all kinds of manipulations of the xlsx file there.

    customize: function( xlsx ) {
    ...
    },
    

    Looks like it is the same for CSV.

    You could do something like this, I guess.

    customize: function( csv ) {
       return csv.substr(200);
    },
    

    200 being the header length - or whatever it really is in your case. It's all under the link you posted above.
    https://datatables.net/reference/button/csvHtml5

  • kc5151kc5151 Posts: 47Questions: 4Answers: 1
    edited September 28

    Thank you, I'll look into it next week. It really stumps me though that something that should be easy requires so much diving into :smile:. Maybe a suggestion for the "headers" api option: instead of "true"/"false" also accept an array with header lines that should be included. Like "[0]" for "first header line only". @allan :smile:

  • rf1234rf1234 Posts: 2,944Questions: 87Answers: 416
    edited September 28

    Well, manipulations of Excel exports etc. are complex. I am comparing this with what banks in this country offer: The poorest export you can imagine: Numbers that are formatted like text and cannot be used for calculations. I have never seen anything better from any bank. Apparently it is really hard to manipulate all of that stuff - so they don't do that for you. But I do it for my users using "customize" and that is really, really cumbersome.

    Just tested this - worked :smile:

    All you would need to do is determine the length of your header - and cut it off.

    //custom button for cashflow csv generation
    $.fn.dataTable.ext.buttons.csvCashFlow = {
        extend: 'csv', fieldSeparator: csvSep, charset: 'UTF-8', bom: true,
        filename: function () { return cfFilename; },
        customize: function( csv ) {
           return csv.substr(200);
        }
    };
    
  • kc5151kc5151 Posts: 47Questions: 4Answers: 1
    edited September 28

    I think I get it, but I'm kind of reluctant to install a callback for all possible export formats. Modifying the raw export data seems very messy. I currently have exports for copy+paste/csv/excel/pdf and this seems very cumbersome for what I actually want to achieve.

  • rf1234rf1234 Posts: 2,944Questions: 87Answers: 416

    Yes it is cumbersome! That's why most people don't do it.

    Here is a really complex example of an Excel export which makes sure that my users have nicely formatted texts and numbers that allow immediate calculations without the need to reformat everything manually.
    https://datatables.net/forums/discussion/comment/167672/#Comment_167672

  • kc5151kc5151 Posts: 47Questions: 4Answers: 1
    edited September 28

    Well, your use case is way more complex than mine. I just want to omit the second header line I have in my table, and in my opinion this should not require modifying the raw export data :smile:. Maybe allan has time to take a look, I highlighted him before and won't do so again :smile:.

  • rf1234rf1234 Posts: 2,944Questions: 87Answers: 416
    edited September 28

    Built something that cuts out the line specified as "rowToDelete". You could put this into a little helper function and pass "rowToDelete" as a parameter..

    This one deletes your 2nd header line:

    //custom button for cashflow csv generation
    $.fn.dataTable.ext.buttons.csvCashFlow = {
        extend: 'csv', fieldSeparator: csvSep, charset: 'UTF-8', bom: true,
        filename: function () { return cfFilename; },
        customize: function( csv ) {
            var rowToDelete = 1; //row number to delete starting with 0
            var matchArr = [...csv.matchAll(/\r\n/g)];
            if ( rowToDelete >= matchArr.length ) {
                return csv;
            }
            for ( var ix=0; ix < matchArr.length; ix++ ) {
                if ( ix === rowToDelete ) {
                    if ( ix === 0 ) {
                        csv = csv.substr(matchArr[ix].index + 3);
                    } else {
                        csv = csv.substr(0, matchArr[ix-1].index + 4) + csv.substr(matchArr[ix].index + 4);
                    }
                    return csv;
                } 
            }
        }, ....
    
  • allanallan Posts: 63,205Questions: 1Answers: 10,415 Site admin
    Answer ✓

    Hi,

    This example might help. Basically you use the customizeData callback to tweak the data structure that will be used to build the exported file (in this case removing a single row from the header).

    At some point I'll add a headerRowSelector (or something like that) which I think will be useful, but haven't yet got around to doing!

    Allan

  • kc5151kc5151 Posts: 47Questions: 4Answers: 1

    Thank you so much Allan, did not find that one :wink:

Sign In or Register to comment.