problem apply multiple style to table cell export to excel
problem apply multiple style to table cell export to excel
arcanisgk
Posts: 61Questions: 15Answers: 0
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 ....
This question has an accepted answers - jump to answer
Answers
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
@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...
I might be wrong but I don't believe you can apply multiple styles like this.
Line 4 in your first code snippet:
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.
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
@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
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 ...
zorry i have missing some return data XD i have update my example on it: http://live.datatables.net/rehabiqe/1/edit
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
customizecallback. 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
@allan I honestly don't know what I have to do with what you shared with me... or how that solves the problem.
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:
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: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
@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:
seriously I don't see the number you are going to use, this is not clear to me...
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:
intead of all append etc...
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.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 ofoxfto 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
@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...
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
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