Excel export and newlines

kthorngren

I found lots of posts on this but have not come up with a solution. My goal is to display the text with new lines. The closest I've come to making this work is to replace newlines like this: data.replace( /\n/g, '"&CHAR(10)&CHAR(13)&"' ). The problem is that the cell needs to be a formula. Simply prepending = to the line doesn't work. However if I edit that cell in Excel and replace the = by typing = its turned into a function and works. Although I need to also enable wrap text.

I'm returning something that looks like this:
="Line 1"&CHAR(10)&CHAR(13)&"Line 2"

Instead of returning this as a string I need to return it in a way that Excel interprets it as a function. Any ideas?

Bonus question.... How do I enable wrap text in the export function? Don't mind if I need to manually enable it in Excel.

I'm not tied to this solution if there are other working options.

Kevin

allan

To wrap the text you can use style 55 from the style options built into the excelHtml5 button type (this example shows how to apply styles).

If I recall correctly you can use 50 to work with new line characters. However, I've not tried using formulas at all in the exported files since they don't have any meaning to DataTables itself.

What to do is to create a seriously simply spreadsheet in Excel cell A1 as =(1+1) or something. Save it and then unzip the xlsx file. Have a look in the generated XML and there might be something "magic" about how it defines which cells contain formulas.

I'll take a pop at that in the next few days.

Allan

kthorngren

Thanks for the info, I will check it out and respond with the results.

Kevin

kthorngren

This was a fun puzzle. I was able to get it working. Below is how I did it. Please let me know if there is a better or more efficient way.

I looked Office XML information and found this:

The inline string Datatables uses looks like this:

<c r="C4" s="2" t="inlineStr">
  <is>
    <t>my string</t>
  </is>
</c>

A formula looks like this. Apparently the <v> element is used for numbers so not needed for my purposes.

<c r="B9" s="3" t="str">
  <f>SUM(B2:B8)</f>
  <v>2105</v>
</c>

First the data needs to be reformatted. Found that I can't use & as it results in an innerHTML error. Instead I need to build the string representing the Excel CONCATENATE function. Instead of returning "Line 1"&CHAR(13)&"Line 2" I need to return CONCATENATE("Line 1", CHAR(13), "Line 2"). The Mac renders CHAR(13) as a new line. Other OS's may need CHAR(10). Might be a good idea to include both. This data already has \n for newlines. If the data has <br> and <p></p> elements then the formatting function will need to change them to \n for the split method to work.

Also found that double quotes in the data need to be single quotes. Escaping them causes Excel file format errors. The double quotes around each line need to be escaped. I use the below to build the string.

exportOptions: {
    columns: [2, 3, 4, 5],
    format: {
        body: function ( data, row, column, node ) {
               if (column === 3) {
                    //need to change double quotes to single
                    data = data.replace( /"/g, "'" );
                    //split at each new line
                    splitData = data.split('\n');
                    data = '';
                    for (i=0; i < splitData.length; i++) {
                        //add escaped double quotes around each line
                        data += '\"' + splitData[i] + '\"';
                        //if its not the last line add CHAR(13)
                        if (i + 1 < splitData.length) {
                            data += ', CHAR(13), ';
                        }
                    }
                    //Add concat function
                    data = 'CONCATENATE(' + data + ')';
                    return data;
                }
                return data;
            }
        }
    }
},

The next step is to apply the wrap text attribute and change the cell type from inlineStr to str using the customize function. Also need to add the <f> element with the reformatted string and removal the <is> <t> elements.

customize: function( xlsx ) {
    var sheet = xlsx.xl.worksheets['sheet1.xml'];
    var col = $('col', sheet);
    //set the column width otherwise it will be the length of the line without the newlines
    $(col[3]).attr('width', 50);
    $('row c[r^="D"]', sheet).each(function() {
        if ($('is t', this).text()) {
            //wrap text
            $(this).attr('s', '55');
            //change the type to `str` which is a formula
            $(this).attr('t', 'str');
            //append the concat formula
            $(this).append('<f>' + $('is t', this).text() + '</f>');
            //remove the inlineStr
            $('is', this).remove();
        }
    })
},

I do need to change the format function to skip the first row. Otherwise the heading is seen as a formula and shows as a name error in Excel.

Let me know if improvements can be made.

Kevin

allan

Wow - that's awesome analysis. I don't immediately see anything that I would change - it looks good to me!

Allan

kthorngren

I've seen some postings about setting the row height but no answers. I was able to use this in the customize function to set the row height:

                                        $('row* ', sheet).each(function(index) {
                                            if (index > 0) {
                                                $(this).attr('ht', 60);
                                                $(this).attr('customHeight', 1);
                                            }
                                        });

This shows five or so lines of my wrapped text starting in the middle. But I would like to set the vertical alignment to the top. I didn't see a builtin style for it in the excelHtml5 docs. Does anyone know how to set the vertical alignment?

I'll post a response if I find the answer.

Kevin

kthorngren

Got the vertical alignment working. One of the posts by @F12Magic pointed me in the right direction:
https://datatables.net/forums/discussion/comment/102413/#Comment_102413

Not sure this is worth all the hassle but was interesting to learn about

Kevin

allan

I think you are more knowledgable in the xlsx format than myself now. I'm not sure if you should be proud of that or not ;-).

I've thought a few times about spinning off the xlsx part of Buttons into its own library as that would be quite an interesting project. But also potentially a lot of work since Excel has so many edge cases.

Allan

itajackass

@kthorngren hi can you post an example working of a custom style with cell top aligned and wrapped text together? example from this post I get corrupted excel: https://datatables.net/forums/discussion/comment/102413/#Comment_102413

aneycheva

Here is what worked for me (no formula):

Note 1: depending on the OS use either '\n' or '\r' or both . I had to worry only about Windows, so using '\n'.

Note 2: I did not care about HTML, only export, so data was assigned as array of arrays

STEP 1: Format the data by replacing '\n' with some special character Excel export won't strip
if (str != null){ return str.replace(/(\r\n|\n|\r)/gm,"~"); } else { return null; }

STEP 2: In "customize: function( xlsx )" for "buttons:" in options of the table add special processing for column (can be also done for row or cell). In my case it was column "D"
$('row c[r^="D"]', sheet).each(WrapAndBreak);

Here is WrapAndBreak function:

WrapAndBreak = function(index) {
        if (index > 0){ //skip the header
            $(this).attr('s', '55'); //wrap
            
            var cellText = $('t', $(this));
            if (cellText.text() != null && cellText.text().indexOf('~') > 0){
                cellText.text(cellText.text().replace(/~/g,"\n")); //insert stripped line break
            }
        }
    }

Edited by Colin - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

asadnaeem

https://stackoverflow.com/questions/35761577/export-value-with-linebreaks-into-single-cell-in-excel-with-jquery-datatables-20

$( document ).ready(function() {

var fixNewLine = {
        exportOptions: {
            format: {
                body: function ( data, column, row ) {
                    // Strip $ from salary column to make it numeric
                    return column === 5 ?
// THIS WORKS:          data.replace(/test/ig, "blablabla"):
                        data.replace( /<br\s*\/?>/ig, "\n" ) :
                        data;
                }
            }
        }
    };


    $('#table2excel').DataTable({
        dom: 'Bfrtip',
        buttons:[
            $.extend( true, {}, fixNewLine, {
                extend: 'copyHtml5'
            } ),
            $.extend( true, {}, fixNewLine, {
                extend: 'excelHtml5'
            } ),
            $.extend( true, {}, fixNewLine, {
                extend: 'pdfHtml5'
            } )
        ]

    });
});

However, you need to press the "wrap text" button when opening the excel. If someone knows a way to have it wrapped automatically, please let me know.

Edited by Colin - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

kthorngren

Have you looked at the code provided in this thread? It shows how to format the cell for wrapped text.

Kevin