New lines are not being exported to excel

tvirelli

I have datatables running with buttons extension. I am trying to export data to excel. My table loads it's data via ajax. I am using php to insert new lines "\n" where I want a new line to be output.

My buttons settings are as follows:

"buttons": [{
    extend: 'excelHtml5',
    text: '<i class="fa fa-download"></i> <strong>EXPORT TO EXCEL</strong>',
    className: 'btn btn-success btn-sm',
    title: exportName+' ('+exportDate+') Report Export',
    exportOptions: {
        columns: '.export',
        stripNewlines: false
    }
}

However, even after this, the text just outputs in one line line. No new lines are created in the cell. I also tried "\r\n" but that didn't work either.

allan

Can you link to a test case showing the issue so I can debug it please.

Allan

tvirelli

Does this help?
http://debug.datatables.net/afezuv

allan

I think in this case I would probably need a working page showing the issue to be honest. I'll try to create a local test case for it, but it will be a few days before I get time to do so.

Allan

tvirelli

@allan, here is an example I threw together:
https://codepen.io/anon/pen/pgrgbM

I want the export versions of columns "Type A" & "Type B" to have new lines. If you look at the JSON source you will see the new lines.

allan

Super - thanks! It turns out there are quite a number of issues here unfortunately. Two easy to resolve, one not.

1. Because your newline characters are at the start of the string, it also needs to have trim: false (since otherwise they are trimmed off)
2. There is an error in Buttons whereby, even if they were present, they would be stripped on Excel file creation. I've committed a fix
3. But the real killer is that there doesn't appear to be a way in Excel to mark a string as wrapped (which it appears is necessary in order to show it as multi-line) without using a formatting. Indeed, in my tests I can't actually make a multi-line inline string work at all, it needs to be a shared string.

That, as I'm sure you'll be able to imagine, is not going to be trivial to do. I've opened a bug about this so I can track it and ensure that it is implemented, but I'm afraid there doesn't appear to be a short term way of doing this.

Allan

tvirelli

@allan,

Thank you for your help. I have fixed the 2 "fixable" items and will follow the bug report for updates on the wrapping issue. Yes, I completely understand this isn't a trivial thing.

Thank you again for your help!

tvirelli

@allan,

I implemented the 2 fixes you suggested. I was then assuming that I could later format the column to wrap text in excel and it would work. Is that a correct assumption or will that not currently work either? I ask because in my testing it didn't do as I expected.

Thanks

tvirelli

As a side note, if I copy the cell and post it into textmate, it does show the new lines, so the export is exporting the new lines correctly now.

tvirelli

LOL, sorry for post after post after post, but just trying to help. OK, so I can't right click and select format cell and select wrap text. That doesn't work, but if I select the column then in the "HOME" table select wrap text it does work. This is an okay work around for now. I can explain to my users this has to be done.

Followed this: https://support.office.com/en-us/article/Wrap-text-in-a-cell-3b3317b4-dd97-4cd3-9e5e-bd7ee8c0e483#bmwraptext

allan

Is that a correct assumption or will that not currently work either? I ask because in my testing it didn't do as I expected.

I assumed that as well, but it didn't work either. The only difference I could see in my test files was that it was an inline string, which might just be enough for Excel to not allow wrapping - I'm not sure. I didn't dig into that too much, as I'm fairly sure it will need the string not to be inline in order to get the formatting to work anyway.

Good to hear you have a workaround for now.

Allan