Formatting excel export column

DevJo

After performing an export via excel bottom, the last column in the spreadsheet has the HTML tags, and I can remove the tags via regex and add a space between the data. I need the data to be on a new line every time there is a space between in excel.

table = $('#table_id').DataTable({
                "dom": 'Bfrtip',
                buttons: [
                    {
                        extend: 'excel',
                        exportOptions: {
                           
                            format: {
                                body: function(data, row, column) {
                                    // swap col and row
                                    return column === 7 ?
                                        data = data.replace(/<ul>|<li>|<\/li>|<\/ul>/g, " "):
                                     data;
                                }
                            }
                        },
                    }
            
            
            ],

kthorngren

See this thread about how I handled new lines with Excel.

Kevin

DevJo

Thanks for the fast reply. I came across your thread before posting my question, but I could not replicate the solution to my project.
Down below is an example of the data that I need to split up

<ul><li>204444 - IHateThisData</li><li>444444 -IHateThisData</li><li>22222 - IHateThisData</li></ul>

kthorngren

Its been awhile since looking at this but every place you want a new line you need to insert ', CHAR(13), '. I guess you will want something like this to insert new line for each list item:

data = data.replace(/<li><\/li>/g, ", CHAR(13), ")

You will also need to remove the leading <ul><li> and trailing </li></ul> with a separate replace statement.

Also you need to use the customize function to set the format.

If you need help with this please build a test case with an example of your data so we can take a look. You can update my example or create your own.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin