How can I export a sanitized XML string? - excelHTML5

santa_cruz09

I'm trying to export a table with children rows. My logic to shift the parent rows works as expected, but my XML fails validation as I have an ampersand (&) in my text. I've tried sanitizing the special characters, but I'm unable to write $amp; to the file.

let row = $(this.outerHTML);

                    let parentRow = '<row r="' + rowCount + '">'

                    row.children().each(function () {
                        let cell = $(this);
                        // Get Letter of Column
                        let rc = cell.attr('r').replace(/[0-9]/g,"");

                        //check if numeric column
                        if(rc == 'A'){
                            parentRow += '<c r="' + rc + rowCount + '"><v>' + Number(cell.text()) + '</v></c>'
                            return true;
                        }

                        if(rc == 'G'){
                            parentRow += '<c r="' + rc + rowCount + '"><v>' + Number(cell.text()).toLocaleFixed(2) + '</v></c>'
                            return true;
                        }
                        parentRow += '<c t="inlineStr" r="' + rc + rowCount + '"><is><t>' + String(cell.text()).toSanitizedExcel() + '</t></is></c>'
                    });

                    parentRow += '</row>';
                    console.log("Parent Row:" + parentRow);
                    $('sheetData', sheet).append(parentRow);

Object.assign(String.prototype, {
    toSanitizedExcel() {
        if(this == null){
            return '';
        }

        return this.replace(/&/g, "&")
            .replace(/</g, "&lt;")
            .replace(/>/g, "&gt;")
            .replace(/"/g, "&quot;")
            .replace(/'/g, "&#39;");

    }
});

Log Statement

<row r="5"><c r="A5"><v>201088</v></c><c t="inlineStr" r="B5"><is><t>Align DLR-L Oracle P.2&amp;3 CAC</t></is></c><c r="G5"><v>408.13</v></c></row>

Excel Sheet1.xml

<row r="5">
<c r="A5">
<v>201088</v>
</c>
<c t="inlineStr" r="B5">
<is>
<t>Align DLR-L Oracle P.2&3 CAC</t>
</is>
</c>
<c r="G5">
<v>408.13</v>
</c>
</row>

allan

this.outerHTML

Don't do that. Avoid string manipulation of the DOM entirely. Use only the DOM methods to manipulate the DOM. It is a total pita I know, but it is the only way to be certain of getting things right.

Allan