line break when exporting to excel

Eufragio · January 2019

I have my project in this way : https://jsfiddle.net/Eufragio/u342qgoz/1/

My problem is when exporting to excel, that the results bring me this way:

The result that I hope is this:

kthorngren · January 2019

Please don't duplicate your posts.

This is not a simple thing to do with the Excel export. You will need to use the format object of the exportOptions as shown in this example.

I have a discussion in this thread that shows how to replace newline characters to something that Excel will use to show line breaks. In your case you have <br> as your new line character. You will need to apply the same technique but to your footer instead of the body in my example.

Kevin

Eufragio · January 2019

Sorry, I still can not solve my problem.

since the beginning was to export the group, since that is not possible. for less I want to export the results of those groupings

You could make a demonstration of how it would be in my project: https://jsfiddle.net/Eufragio/u342qgoz/1/

Eufragio · January 2019

@kthorngren Can you help me with this, since your result does not stay clear, the aim is to publicize the results of each group

you think that it would be possible to export it in this way :

or do you recommend to solve the problems

Thank you for your time

kthorngren · January 2019

Like I said its not a simple solution. Did you try the code provided in the thread I linked to above?

Kevin

Eufragio · January 2019

I understand that this is the code that would help me, but I do not understand how to attach it to my project

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

kthorngren · January 2019

It took some time but I updated your example with the code for newlines in Excel:
https://jsfiddle.net/m26nx9yg/

For some reason char(13) didn't work so I changed to use char(10).

When using footer: function ( data, row, column, node ) the column parameter is actually the node, not the column number. Make sense since this isn't a normal Datatables row. Also changed the if in line 5 to look for a class ('newline') assigned to the footer column to only format the desired columns.

Changed the row selectors in the customize function to row:last to only affect the last row which is the footer. Also added the code to set the row height for the last row.

This should get you started.

Kevin

Eufragio · January 2019

Thank you brother, you have saved me life, I hope that someone more I will serve this example

itajackass · December 2019

@kthorngren I'm trying to edit your code to fit my needs:

some cel has p tags, some uses br, some div.... to get newline into html web app.

Now for export in excel:

How can I edit the code to fit all case in the simplest way?

My fiddle: https://jsfiddle.net/jx9hom27/

my example now only tried on column A. How to loop trought all columns without hardcode A,B,C,....?

kthorngren · December 2019

@itajackass Sorry, it would take a bit of time to write up an example.

some cel has p tags, some uses br, some div.... to get newline into html web app.

That example is pretty simplistic. There are lots of ways to tackle this. If you want to continue with the string split solution then the first thing is to determine the type of element you have to determine what to use to split on, for example a p might be split on </p><p>. You will also need to remove any leading and trailing element tags. I would try a regex replace using a string to remove HTML after the for loop before this statement data = 'CONCATENATE(' + data + ')';

my example now only tried on column A. How to loop trought all columns without hardcode A,B,C,....?

Haven't tried it but maybe the sample to convert the column index to Excel letter, in this SO thread, would work to programmatically set the Excel column letter.

Maybe you can give it a shot and if you need help along the way we can provide guidance.

Another way that might work is to modify the example I posted in this thread. It loops through all the rows and appends the corresponding child rows. You can omit that part and just loop through all the rows making the appropriate updates to convert the linefeeds.

Kevin

itajackass · December 2019

@kthorngren thanks to your examples, I get this nice result: https://jsfiddle.net/jx9hom27/2/

If you have suggestion to improve the code let me know.

Next step will be adapt height of row to fit the heighest one in the same row

itajackass · December 2019

@kthorngren to set height of the maximum height of a row: maybe, for each row, save the size of the array of splitted string (so...numbers of lines). get only the biggest one.
multiply the size for a costant in pixel (the height of a cell with standard text size in excel), and assign the result to customized option for excel? Any suggest?

Tried your code:

    $(this).attr('ht', 60);
    $(this).attr('customHeight', 1);

but it seem no changes.......

itajackass · December 2019

@kthorngren ok I've correct the styling for row height:

https://jsfiddle.net/jx9hom27/3/

It remaing how to autoadapt the value of height with the heighest to adapt all row

kthorngren · December 2019

@itajackass That looks fantastic. Great job! I'm sure it will be helpful for others. Thanks for posting your solution.

Kevin

itajackass · December 2019

@kthorngren updated code:

for each row establish the height based on num of lines of cells

https://jsfiddle.net/jx9hom27/5/

next step to do: align on top and keep wrapped text? I know there are some costum style to create...but i don't undestand how. can you help my starting from my code?

itajackass · December 2019

done align to top with wrapped text:

https://jsfiddle.net/17w3trky/

itajackass · January 2020

Hi i'm still here:
in my table when I have a string with "&" inside it, i get this error during export in XML using CONCANATE method here: https://jsfiddle.net/17w3trky/

error:

Errore interpretazione XML: non well-formed
Indirizzo:
Riga numero 1, colonna 29:

Any suggest to prevent this problem? thanks

itajackass · January 2020

done with:
data = data.replace( /&/g, "& amp;" );

https://jsfiddle.net/051h9wLa/

itajackass · January 2020

Ok the script is good now for few rows. but with a lot of rows (in my app about 2000 row with 13 cols), if I click export I get every few seconds a browser alert tell me "browser is slowed down by a script....what do you like to do? BLOCK or WAIT"... any way to simplify the script? or prevent browser to show this alert?

itajackass · January 2020

Example of the slowdown: https://jsfiddle.net/me05aLxv/2/

itajackass · January 2020

Example with blockui https://jsfiddle.net/j2fug3ax/

line break when exporting to excel

line break when exporting to excel

Answers

Howdy, Stranger!

Categories

DataTables

line break when exporting to excel

line break when exporting to excel

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables