MS Excel Compatibility

MS Excel Compatibility

NicodebNicodeb Posts: 4Questions: 1Answers: 0
edited May 20 in Free community support

Link to test case: Removed
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);
                            }
                        });
                    }
                },

Answers

  • NicodebNicodeb Posts: 4Questions: 1Answers: 0
    edited May 13

    It's the last loop of the customize function that is breaking the file for MS Excel, if i comment it the export works fine but I don't have the customize cells size and line returns that I need.

    The loop :

    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);
         }
    });
    
  • allanallan Posts: 63,498Questions: 1Answers: 10,471 Site admin

    Thanks for the follow up and confirming that it isn't an error in DataTables / Buttons.

    I really need to get around to writing an abstraction layer for this sort of thing sometime. Customing the XML is quite difficult and error prone.

    I'm not entierly sure why Excel doesn't like that XML I'm afraid. What I normally do with this sort of thing is to create the file in Excel and save it. Then rename as .zip and unzip it to see what the XML you want is. Then you can dump the XML you are creating / modifying to see what the difference is.

    Allan

  • NicodebNicodeb Posts: 4Questions: 1Answers: 0

    Hi Allan, thanks for your answer and the zip tip :)
    I'll try that out.

  • NicodebNicodeb Posts: 4Questions: 1Answers: 0
    edited May 13

    It was just a matter of adding <r> tag with <t xml:space="preserve"> to finally get it worked !

    $(this).attr('t', 'str');
    $(this).html('<r><t xml:space="preserve">' + $(this).text() + '</t></r>');
    
  • allanallan Posts: 63,498Questions: 1Answers: 10,471 Site admin

    Nice one! Thanks for posting back.

    Allan

Sign In or Register to comment.