How to format this datatables export to excel?

Noodles12

I have a datatable where I am exporting the results in excel spreadsheet. Most of the rows have long text along with some formatting such as line breaks, lists.

Is there a way to do the following when exporting to excel?

1) Wrap text for all the cells
2) Preserve line breaks, p and ul tags.

Here is my example: https://live.datatables.net/yamifera/1/edit

Thanks.

Noodles12

ok, so I added the following code. It works for wrapping text but unbolds column headers. How can I avoid that?Thanks.

extend: 'excelHtml5',
customize: function( xlsx ) {
var sheet = xlsx.xl.worksheets['sheet1.xml'];

             $('row c[r^="B"]', sheet).attr( 's', '55' );
             $('row c[r^="C"]', sheet).attr( 's', '55' );
             $('row c[r^="A"]', sheet).attr( 's', '55' );
             $('row c[r^="D"]', sheet).attr( 's', '55' );
             $('row c[r^="E"]', sheet).attr( 's', '55' );
             $('row c[r^="F"]', sheet).attr( 's', '55' );                 

        },

allan

In Excel, if you want bold and wrap at the same time, you need to create a new style in the XML. This is the line in our default styles that does wrapped text (index 55). You could either:

1. Copy the file and host it locally and tweak to add a bold wrapped text style, or
2. Use the customize method to add that style dynamically and then use that style's index.

Yes, styles in Excel are a paint in the a**e. One day I want to write an abstraction layer for our exporter to make it easier. One day...

Allan

Noodles12

Thankyou. I will add a custom style for bold and wrap.

Is there a way to preserve line breaks, p and ul tags. Make the excel format look somewhat similar to HTML? If not all, atleast p tags or br tags will be very helpful.

Noodles12

I used stripNewlines: false and it works. However, I will have to Top align the cells in excel, Is there a style for it or can you point me to the style that I can change? Thanks

allan

I actually don't know what the XML for that is I'm afraid. I'd guess it is related to the alignment you can see in my link above, but I fear you'd need to refer to the Open Spreadsheet XML documentation for vertical alignment.

Allan

Noodles12

So I managed to update the code by reading another forum. It works fine on live.datatables.net but it gives me - Uncaught ReferenceError: styleIndex is not defined error. What am I missing? Where do I define styleIndex?

https://live.datatables.net/yamifera/2/edit

Noodles12

forgot to link the forum I got the code from - https://datatables.net/forums/discussion/39287/datatables-buttons-excelhtml5-vertical-cell-alignment

Noodles12

I figured out what I was missing. Thanks.

allan

Awesome - thanks for the update.

Allan