Link to test case: https://v3-uits.crowdaa.net/

Error messages shown: MS Excel Error : A problem has been found in the content of the file. Try to Repair : XML error on /xl/worksheets/sheet1.xml. Loading error. Line 2, column 0.

Description of problem: I had to make some customizations on the .xlsx to make readable data on the .xlsx export. The code works file when i open the exported file with LibreOffice but not with MS Excel. If I " save as " from LibreOffice, the result file works fine on MS Excel.

Here is the code :

function columnToLetter(column) {
        var temp, letter = '';
        while (column > 0)
        {
            temp = (column - 1) % 26;
            letter = String.fromCharCode(temp + 65) + letter;
            column = (column - temp - 1) / 26;
        }
        return letter;
    }
    // Enlève les balises html en préservant les sauts de lignes
    function remove_tags(html) {
        // Remplace les balises <br> par un placeholder
        html = html.replace(/<br>/g,"$br$"); 
        // Remplace les sauts de lignes par un placeholder
        html = html.replace(/(?:\r\n|\r|\n)/g, '$n$');
        // Créer une DIV temporaire
        var tmp = document.createElement("DIV");
        tmp.innerHTML = html;
        // Efface toutes les balises HTML
        html = tmp.textContent||tmp.innerText;
        // Remplace les placeholders par des balise <br>
        html = html.replace(/\$br\$/g,"<br>");  
        html = html.replace(/\$n\$/g,"<br>");
        
       return html;
    }

    $(document).ready(function() {
        var table = $('#home-table').DataTable({

            paging: true,
            autoWidth: true,
            dom: 'Bfrtip',
            buttons: [
                {
                    extend: 'excelHtml5',
                    text: 'Excel', // Texte personnalisé pour le bouton Excel
                    extension: '.xlsx',
                    filename: 'UITS - Export Prestataires',
                    exportOptions: {
                        format: {
                            body: function(data,row,column,node) {
                                // Change les double guillemets en simple
                                data = data.replace( /"/g, "'" );
                                
                                // Remplace le tiret par <br> et retire l'espace avant dans la colonne des procédés
                                if(column === 6) {
                                    data = data.replace(/([^\s])\s?-/g, "$1<br>-");
                                }
                                
                                // Enlève les balise html
                                data = remove_tags(data);
                                
                                // Sépare les lignes
                                splitData = data.split('<br>');
                                
                                // Enlève les lignes vides
                                splitData = splitData.filter(function(v){return v!==''});
                                
                                data = '';
                                for (i=0; i < splitData.length; i++) {
                                    // Ajoute des double guillements autour de chaque ligne
                                    data += '\"' + splitData[i] + '\"';
                                    // Ajoute CHAR(10) quand ce n'est pas la dernière ligne
                                    if (i + 1 < splitData.length) {
                                        data += ', CHAR(10), ';
                                    }
                                }
                                
                                return data;
                            }
                        },
                        columns: [1, 2, 3, 4, 5, 8, 9, 13, 17],
                    },
                    customize: function (xlsx) {
                        
                        var sSh = xlsx.xl['styles.xml'];
    
                        var styleSheet = sSh.childNodes[0];

                        cellXfs = styleSheet.childNodes[5];
                        
                        // Using this instead of "" (required for Excel 2007+, not for 2003)
                        var ns = 'http://schemas.openxmlformats.org/spreadsheetml/2006/main';

                        // Create a custom style
                        var lastStyleNum = $('cellXfs xf', sSh).length - 1;
                        var wrappedTopIndex = lastStyleNum + 1;
                        var newStyle = document.createElementNS(ns, "xf");
                        // Customize style
                        newStyle.setAttribute("numFmtId", 0);
                        newStyle.setAttribute("fontId", 0);
                        newStyle.setAttribute("fillId", 0);
                        newStyle.setAttribute("borderId", 0);
                        newStyle.setAttribute("applyFont", 1);
                        newStyle.setAttribute("applyFill", 1);
                        newStyle.setAttribute("applyBorder", 1);
                        newStyle.setAttribute("xfId", 0);
                        // Alignment (optional)
                        var align = document.createElementNS(ns, "alignment");
                        align.setAttribute("vertical", "top");
                        align.setAttribute("wrapText", 1);
                        newStyle.appendChild(align);
                        // Append the style next to the other ones
                        cellXfs.appendChild(newStyle);
                        
                        var sheet = xlsx.xl.worksheets['sheet1.xml'];

                        // Width de la colonne procédé
                        $('col', sheet).eq(6).attr('width', 100);

                        //Width colonne Secteur d'activité
                        $('col', sheet).eq(5).attr('width', 30);

                        var firstExcelRow = 3;
                        
                        table.rows({order: 'applied', search: 'applied'}).every( function ( rowIdx, tableLoop, rowLoop ) {

                            var node = this.node();
                            
                            var num_colonne = 9;
                            
                            // the cell with biggest number of line inside it determine the height of entire row
                            var maxCountLinesRow = 1;


                            for ( var indexCol = 1; indexCol <= num_colonne; indexCol++ ) {
                            
                                var letterExcel = columnToLetter(indexCol);
                        
                                $('c[r=' + letterExcel + ( firstExcelRow + rowLoop ) + ']', sheet).each(function(e) {
                                    // Ajoute l'attribut string à toutes les cellules
                                    $(this).attr('t', 'str');
                                
                                    // Compte le nombre de lignes
                                    var countLines = ($('is t', this).text().match(/\"/g) || []).length / 2;
                                    if ( countLines > maxCountLinesRow ) {
                                        maxCountLinesRow = countLines;
                                    }

                                    // Insertion du style alignement top
                                    $(this).attr('s', wrappedTopIndex );
                                    
                                    var cellValue = $(this).text();


                                    // Ajoute la formule et le concatener dans les cellules
                                    $(this).append('<f>CONCATENATE(' + cellValue + ')</f>');
                                });
                                
                                $('row:nth-child('+( firstExcelRow + rowLoop )+')', sheet).attr('ht', maxCountLinesRow * 15);
                                $('row:nth-child('+( firstExcelRow + rowLoop )+')', sheet).attr('customHeight', 1);
                            }
                        });
                    }
                },