Formatting excel export column

Formatting excel export column

DevJoDevJo Posts: 2Questions: 1Answers: 0

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;
                                }
                            }
                        },
                    }
            
            
            ],

Answers

  • kthorngrenkthorngren Posts: 20,142Questions: 26Answers: 4,736

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

    Kevin

  • DevJoDevJo Posts: 2Questions: 1Answers: 0
    edited July 2022

    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>
    
  • kthorngrenkthorngren Posts: 20,142Questions: 26Answers: 4,736

    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

Sign In or Register to comment.