Is it possible to split one cell into new rows in excelHtml5 export?

sebas146

Hi all, i'm trying to export in excel but i need to format like this:
1. I have some cells with inside table in it
1.1. I need those cells to convert in new rows in the excel (Formatting </tr> as \n\r, see image 1.

This is what i have done so far (For the record, I already make it possible to wrap text in one cell, but what i need is to create new rows instead wrapping them inside the cell):

'buttons' => [
                            [
                                'extend' => 'excelHtml5',
                                'text' => '<i class="far fa-file-excel processing" style="font-size:18px"></i>',
                                'className' => 'btn btn-outline-excel btn-sm',
                                'messageTop' => $messageTop,
                                'exportOptions' => [                                
                                    'columns' => ':visible',
                                    'format' => [
                                        'body' => 'function ( data, row, column, node ) {
                                            data = data.replace(/<br\s*[\/]?>/gi, " ");
                                            data = data.replace(/<tr\s*[\/]?>/gi, "\n");
                                            data = data.replace(/(&nbsp;|<([^>]+)>)/ig, "");

                                            //This doesnt work
                                            /*if (column === 3) {
                                                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 += "; \n\r ; ";
                                                    }
                                                }
                                                //Add concat function
                                                data = "CONCAT(" + data + ")";
                                                console.log(data);

                                                return data;
                                            }*/
                                            //This doesnt work

                                            return data;
                                        }'
                                    ],
                                ],
                                '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", sheet).each(function(x) {
                                        if (x > 2) {
                                            //Atributo s 55 significa estilo -wrapped text- (https://datatables.net/reference/button/excelHtml5)
                                            $("row:nth-child("+(x+1)+") c", sheet).attr("s", "55");

                                            //This doesnt work
                                            /*if ($("is t", this).text()) {
                                                console.log("aqui");
                                                //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();
                                            }*/
                                            //This doesnt work
                                        }

                                    });
                                }'
                            ],
                        ]

Can anyone knows a way to achieve that?

Thanks in advance.

kthorngren

Its possible but not something built into Datatables. You might be able to adapt this example from this thread to do what you want. Look at the last two comments.

You would add code to the customizeRowExport() function to preprocess the table data to build the rows the way you want. Sounds like you want to loop through all the rows and if column 3 has multiple lines then split the lines and append new rows for each line.

Kevin