problem apply multiple style to table cell export to excel

arcanisgk

in the customization of export to excel i have added this to fill the entire cell when have multiple lines:

var value = $('is t', this).text();
var count = (value.match(/\n/g) || []).length;
if (count == 0) {
    $(this).attr('s', styleIndex - 2);
} else {
    $(this).attr('s', '55');
}

the problem is that this cell content with line brake is vertical align to button instead of top.

if i try with $(this).attr('s', ['55','50']); to add the top align the wrap stop working ....

kthorngren

See my answer in your other thread. If this doesn't help then please provie a test case showing what you are trying to do.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin

arcanisgk

@kthorngren the two questions are not related, they are different things... in this question I have already obtained the line break for excel but I cannot upload a test case, since the error is visible when the excel is opened and all cells with 2 or more lines with style 55; they apply a text wrap that allows the full text to be seen if I remove it or combine it with another style, excel stops respecting/rendering on view the line breaks and even if they exists... I must go to each cell one by one, click on it as if I were going to edit the text remove the focus () from the cell so that it respects the line break again...

kthorngren

if i try with $(this).attr('s', ['55','50']); to add the top align the wrap stop working

I might be wrong but I don't believe you can apply multiple styles like this.

Line 4 in your first code snippet:

$(this).attr('s', styleIndex - 2);

Did you also add the code, to your customize function, to add the style for top aligning the text? In your other thread I poted a link to a thread that references this thread for vertical alignment.

I cannot upload a test case

The test case just needs example data and the buttons code you are using for the Excel button. Use the Download builder to generate the proper set of files. I started a test case for you:
http://live.datatables.net/jacuhohu/1/edit

Just update it with the excel code you. are using and maybe update some of the data if need for the problem demonstration.

Kevin

arcanisgk

@kthorngren i have try to recreate my own example of my minimal javascript runing and cant make it work correctly for exporting file:

http://live.datatables.net/lehulipe/1/edit

arcanisgk

it is my current script that have 2 diferent error:
1. on export to excel dont work with http://live.datatables.net/ but work in my local.
2. i have trying to prevent search use last 2 column but not work ...

arcanisgk

zorry i have missing some return data XD i have update my example on it: http://live.datatables.net/rehabiqe/1/edit

allan

I might be wrong but I don't believe you can apply multiple styles like this.

Kevin is correct. What he diplomatically left out is that styles in Excel are a massive PITA. You'd need to modify the XML here to add the style you want and then set the cell style to the index of that style element you've added.

That can be done is the customize callback. It is a bit messy though.

Personally, if the Excel export we provide doesn't do what you need out of the box, I would suggest using a library such as SheetJS or similar instead. That is actually designed as an Excel library - ours (and I say this having been the one who wrote it) is intended as a basic export only.

Allan

arcanisgk

@allan I honestly don't know what I have to do with what you shared with me... or how that solves the problem.

kthorngren

I was hoping for a simpler test case to look at. I grabbed your excel export code, modified it a bit and put into this test case:
http://live.datatables.net/jurunozo/1/edit

Its been awhile since I looked at customizing the styles I put some comments in the example to explain a bit about what is happening. To apply multiple styles you will need to create a new style and append to the XML that Allan linked to above. Your test case is doing this:

styles.append('<xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1">' + '<alignment vertical="top" wrapText="1" />' + '</xf>');

Its combining two styles vertical="top" wrapText="1". You are trying to combine two like this which won't work because it is expecting one number:

$(this).attr('s', [55, 50]);

Looks like you want to combine these styles horizontal="left" wrapText="1". I added this to my example.

I learned something from this. I don't need to do all that crazy concatenation stuff in my old code from 5 years ago

Kevin

arcanisgk

@kthorngren Ok, thank you very much, could you explain me something more:

How is the style number related to the added style ??? If I see the code this line does not have any number:

// Append vertical="top" style
          styles.append('<xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1">' + '<alignment vertical="top" />' + '</xf>');

          // Append two styles horizontal="left" (excel style 50) and text wrap (excel style 55)
          styles.append('<xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1">' + '<alignment horizontal="left" wrapText="1" />' + '</xf>');

          var sheet = xlsx.xl.worksheets['sheet1.xml'];
          var oxf = $('xf', xlsx.xl['styles.xml']);

seriously I don't see the number you are going to use, this is not clear to me...

          /*
          oxf starts with a default format of:
          <xf numFmtId="0" fontId="0" fillId="0" borderId="0"/>
          
          DataTables creates it's styles here:
          https://github.com/DataTables/Buttons/blob/c25ea69f9241501b36193afffb91196928aab18f/js/buttons.html5.js#L645
          
          oxf.length = oxf default (1) + datatables styles + additional styles appended above
          
          For example 1 + 68 + 1 = 70
          
          To choose the appended style above 1 must be subtracted for the default 
          and 1 subtracted for 0 index, thus styleIndex - 2 to access the last appended style.
          
          */

I don't know about you, but that xml style template with a certain style number is a bit cumbersome... if it's okay to implement a default style, it seems to me that the setting should be in real time, like:

styles.setNew({70:{top:true,left:true,wraptext:1}});

intead of all append etc...

kthorngren

I don't know about you, but that xml style template with a certain style number is a bit cumbersome

Yes, its very cumbersome. You can read all about it in the Open XML documentation.

Unfortunately you can't just use styles.setNew({70:{top:true,left:true,wraptext:1}}); to add a new style. What if there are 75 styles? You need to append to what is there.

How is the style number related to the added style ???

Looking at the Datatables XML styles you will see there are currently 68. You add one then there are 69. So you will want to to use style number 68 - its a zero based number like an array.

However, Allan may add some styles so you don't want to hardcode anything. One way to calculate the index is to get the length of var oxf = $('xf', xlsx.xl['styles.xml']);. If you want to see the details you can do some debugging of oxf to see that it has one more style than what was added by Datatables. Looking at oxf[0] looks like a default style. Looking at oxf[1] looks like the first style Datatables has. So basically the first style you append is oxf.length - 2. 1 is for the default and the other is to use 0 based indexes.

TL;DR: subtract 2 from the length of oxf to get the index of the last style you appended.

Kevin

arcanisgk

@kthorngren well there is also the problem with the search filter... where I don't want it to search for data in the last two columns... but they keep doing it...

kthorngren

Please don't ask questions then post a new thread with the same question. It becomes confusing for those helping. Just create the new thread. See my answer in your other thread.

Kevin

allan

Yup - styling is a total pig in Open XML! See why I said to use something like SheetJS if you want to do anything beyond what our library does out of the box .

Allan