line break when exporting to excel

line break when exporting to excel

EufragioEufragio Posts: 20Questions: 2Answers: 0

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:

This question has an accepted answers - jump to answer

Answers

  • kthorngrenkthorngren Posts: 20,139Questions: 26Answers: 4,735

    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

  • EufragioEufragio Posts: 20Questions: 2Answers: 0

    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/

  • EufragioEufragio Posts: 20Questions: 2Answers: 0

    @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

  • kthorngrenkthorngren Posts: 20,139Questions: 26Answers: 4,735

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

    Kevin

  • EufragioEufragio Posts: 20Questions: 2Answers: 0
    edited 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;
                }
            }
        }
    },
    
  • kthorngrenkthorngren Posts: 20,139Questions: 26Answers: 4,735
    Answer ✓

    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

  • EufragioEufragio Posts: 20Questions: 2Answers: 0

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

  • itajackassitajackass Posts: 121Questions: 37Answers: 3
    edited 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,....?

  • kthorngrenkthorngren Posts: 20,139Questions: 26Answers: 4,735
    edited 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

  • itajackassitajackass Posts: 121Questions: 37Answers: 3
    edited 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 :/

  • itajackassitajackass Posts: 121Questions: 37Answers: 3
    edited 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.......

  • itajackassitajackass Posts: 121Questions: 37Answers: 3

    @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

  • kthorngrenkthorngren Posts: 20,139Questions: 26Answers: 4,735

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

    Kevin

  • itajackassitajackass Posts: 121Questions: 37Answers: 3
    edited 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?

  • itajackassitajackass Posts: 121Questions: 37Answers: 3

    done align to top with wrapped text:

    https://jsfiddle.net/17w3trky/

  • itajackassitajackass Posts: 121Questions: 37Answers: 3

    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

  • itajackassitajackass Posts: 121Questions: 37Answers: 3
    edited January 2020

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

    https://jsfiddle.net/051h9wLa/

  • itajackassitajackass Posts: 121Questions: 37Answers: 3

    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?

  • itajackassitajackass Posts: 121Questions: 37Answers: 3

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

  • itajackassitajackass Posts: 121Questions: 37Answers: 3

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

This discussion has been closed.