DataTables Buttons excelHTML5 vertical cell alignment

DataTables Buttons excelHTML5 vertical cell alignment

iecwebmastiecwebmast Posts: 66Questions: 9Answers: 1

Hello,
I am using the Buttons excelHTML5 export with a table which contains one column with a large amount of text. I was able to implement the customize function to wraptext. Unfortunately all of the cells by default are bottom aligned, which makes the table difficult to read due to the row height being increased to accommodate the wrapped column with a large amount of text. Is there a way to specify that the cell content should be top aligned?

$('#myTable').DataTable( {
    buttons: [
        {
            extend: 'excelHtml5',
            text: 'Save as Excel',
            customize: function( xlsx ) {
                var sheet = xlsx.xl.worksheets['sheet1.xml'];
                $('row:first c', sheet).attr( 's', '55' );  //<-- wrapped text
            }
        }
    ]
} );

Thank you in advance for your help!

Answers

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

    You'd need to add a style to the output file that would change the alignment to the top. What exactly that is, I don't know - I'd need to defer to the open spreadsheet documentation.

    I should say that styling in the Excel XML is a massive pain. If you need advanced styling, I would very much encourage you to create the file server-side with a more comprehensive API. I only want Buttons to be simple.

    Allan

  • iecwebmastiecwebmast Posts: 66Questions: 9Answers: 1

    Hi Alan,
    I took a look at how the other excelHtml5 styling options were configured in:
    /Buttons-1.2.2/js/buttons.html5.js

    It looks to me like adding a line in the section should work:
    var excelStrings = {
    in the part:
    "xl/styles.xml":
    at the end of:
    '<cellXfs count="56">'+

    This line seems logical, following the pattern of the others:
    '<xf numFmtId="0" fontId="0" fillId="0" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyAlignment="1">'+ '<alignment vertical="top"/>'+ '</xf>'+

    But I am wondering about the number to assign? Increasing the '<cellXfs count="56">'+ to 57 seems logical, but the last number in this section corresponds to "55" for wrapped text... and there are numbers 57 to 64 which are assigned to other styles. As shown in the documentation page:
    https://datatables.net/reference/button/excelHtml5

    Could you help me to figure this out?

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

    The way I'd recommend doing this in a forwards compatible manner is to use the customize method of the excelHtml5 button type. With that you have access to the styles file (and all the other XML files that make up the XSLX file).

    Use jQuery to add the extra xf tag into the document with a simple $().append(). Then count the number of elements and you'll know the index of your new tag (or you could use $().index()) and use that to assign the style. That way it will always work regardless of any extra styles being used in future.

    Allan

  • iecwebmastiecwebmast Posts: 66Questions: 9Answers: 1

    Hi Allan,
    Yes, forward compatible sounds like the way to go. I'm not sure though how to use the $().append(). Would this be appending onto <cellXfs? I've made a jsfiddle, if you could give me hand with this I'd really appreciate your help.
    https://jsfiddle.net/lbriquet/0n9j52jx/

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

    Yes - appending a new xf tag to the cellXfs element would be the way to do it.

    $('cellXfs', xlsx.xl['styles.xml']).append( '<xf ... />' );
    

    should do it.

    Allan

  • iecwebmastiecwebmast Posts: 66Questions: 9Answers: 1

    Thank you Allan,
    I've prepared the append line, but I am not sure where it should go in my datatables call?
    $('cellXfs', xlsx.xl['styles.xml']).append('<xf numFmtId='0' fontId='0' fillId='0' borderId='0' applyFont='1' applyFill='1' applyBorder='1' xfId='0' applyAlignment='1'>' + '<alignment vertical='top'/>');

    I've tried inserting it in several places, but it breaks my table.

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

    Inside the customize callback of the excelHtm5 button type.

    Allan

  • F12MagicF12Magic Posts: 109Questions: 0Answers: 28

    Should be something like the updated fiddle : https://jsfiddle.net/0n9j52jx/1/
    I'm afraid it's not working. Inserting xml tags with append also insert the famous xmls="" attribute. And that will make the excel corrupted. :(

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736
    edited April 2017

    Close @F12Magic :smile:

    And that will make the excel corrupted

    The corruption was due to this line being out of range:
    $('row c[r^="B"]', sheet).attr( 's', styleIndex ); //<-- Top Align

    I made a couple small changes here:
    https://jsfiddle.net/0n9j52jx/5/

    I changed this line to subtract 2 from the length (styleIndex):
    $('row c[r^="B"]', sheet).attr( 's', styleIndex - 2 ); //<-- Top Align

    Even though it seems to work without this change I updated the count attribute:
    ocellXfs.attr('count', ocellXfs.attr('count') +1 );

    Since it's what I need I added wrapText to the alignment.

    Thanks for all your pointers. I think one of your other posts helped me a great deal too.

    Kevin

  • F12MagicF12Magic Posts: 109Questions: 0Answers: 28
    edited April 2017

    @kthorngren Cool :smile:

    Always nice to have better examples. Thx for fiddle Kevin.

    Dirk.

  • earlrodosnearlrodosn Posts: 1Questions: 0Answers: 0

    guys got errors when doing export with given sample.

    https://jsfiddle.net/0n9j52jx/5/

    any help for

    <xf numFmtId="0" fontId="2" fillId="2" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyAlignment="1">'+
    '<alignment vertical="top" wrapText="1" /></xf>

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    Hi @earlrodosn ,

    I didn't see an error when I tried. Can you give steps on how to reproduce, please, and what you expect to happen.

    Cheers,

    Colin

  • itajackassitajackass Posts: 121Questions: 37Answers: 3

    @colin I get the error too. column B is not aligned to the top....

  • itajackassitajackass Posts: 121Questions: 37Answers: 3

    It seems, without adding custom style:

        console.log(ocellXfs);
        console.log(oxf);
    

    have different size!!

    ocellXfs indexed from 0 - 55
    oxf indexed from 0 to 56..... maybe this is the problem?

  • itajackassitajackass Posts: 121Questions: 37Answers: 3

    here is a example working based on others info found in the forum:

    https://jsfiddle.net/17w3trky/

This discussion has been closed.