DataTables Buttons excelHTML5 vertical cell alignment

iecwebmast

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!

allan

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

iecwebmast

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?

allan

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

iecwebmast

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/

allan

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

iecwebmast

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.

allan

Inside the customize callback of the excelHtm5 button type.

Allan

F12Magic

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.

kthorngren

Close @F12Magic

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

F12Magic

@kthorngren Cool

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

Dirk.

earlrodosn

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>

colin

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

itajackass

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

itajackass

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?

itajackass

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

https://jsfiddle.net/17w3trky/