Export to excel with Custom styles causes excel to show error when opening

Dzyann

I am trying to add custom styles to my excel export but no matter what I do I get errors.
Following this post I tried to add a custom style like this:

 dataTableConfig.buttons = [
    {
        extend: 'excel',
        text: '<span class="glyphicon glyphicon-download-alt"></span> Excel',
        titleAttr: 'Excel',
        className: 'btn btn-default',
        exportOptions: {
            orthogonal: 'export',
            modifier: {
                page: 'current'
            },
            columns: columnsToExport,
        },
        customize: function (xlsx) {
            var sheet = xlsx.xl.worksheets['sheet1.xml'];
            var styles = xlsx.xl["styles.xml"];
            var fillsCount = styles.childNodes[0].childNodes[1].childNodes.length;
            var fills = styles.childNodes[0].childNodes[1];
            var xfs = styles.childNodes[0].childNodes[4];
            var newFill = '<fill><patternFill patternType="solid"><fgColor rgb="ffa779c3"/><bgColor indexed="64"/></patternFill></fill>';
            fills.innerHTML = fills.innerHTML + newFill;    
            var newXf = '<xf borderId="0" fillId="' + fillsCount + '" fontId="1" numFmtId="0" applyBorder="1" applyFill="1" applyFont="1" xfId="0"/>';
            xfs.innerHTML = xfs.innerHTML + newXf;
                    
            var xfsLength = xfs.childNodes.length;
            $(xfs).attr('count', xfsLength + 1);
            $('row c[r="A1"]', sheet).attr('s', xfsLength);
            $('row c[r="C3"]', sheet).attr('s', '42');
        }
    }
];

But that doesn't seem to work, I also tried manipulating the styles with xml nodes like this:

customize: function (xlsx) {
    var sheet = xlsx.xl.worksheets['sheet1.xml'];
    var styles = xlsx.xl["styles.xml"];
    var fillsCount = styles.childNodes[0].childNodes[1].childNodes.length;
    var fills = styles.childNodes[0].childNodes[1];
    var xfs = styles.childNodes[0].childNodes[4];
    var newFill = '<fill><patternFill patternType="solid"><fgColor rgb="ffa779c3"/><bgColor indexed="64"/></patternFill></fill>';
    var newFillXml = $($.parseXML(newFill)).find("fill");
    fills.appendChild(newFillXml[0]);
    $(fills).attr('count', fillsCount + 1);

    var newXf = '<xf borderId="0" fillId="' + fillsCount + '" fontId="1" numFmtId="0" applyBorder="1" applyFill="1" applyFont="1" xfId="0"/>';
    var newXfXml = $($.parseXML(newXf)).find("xf");
    xfs.appendChild(newXfXml[0]);
    
    var xfsLength = xfs.childNodes.length;
    $(xfs).attr('count', xfsLength + 1);
    $('row c[r="A1"]', sheet).attr('s', xfsLength);
    $('row c[r="C3"]', sheet).attr('s', '42');  
}

No matter what I always get:
We found a problem with some content in 'File'. Do you want us to try to recover us much as we can? If you trust the source of this workbook, click Yes.
I don't understand what I am doing wrong? I updated by hand an excel file with this fill and it works fine. Then indexes were different but that's all.
Thanks!

allan

xfs.innerHTML = xfs.innerHTML + newXf;

The problem with using HTML is that it isn't always valid XML. Are you certain that it is creating valid XML?

I'd very much encourage you to use DOM node methods rather than string manipulation such as innerHTML.

It would also be work changing the created xlsx file to a zip file, unzip it and check the XML that has been created. You could do a diff between that and your manual version to see what the difference is.

Allan

Dzyann

Allan,
I tried the innerHTML because it is what I saw in this site and seemed to be working.
I was mostly using the $.parseXML (like in the second method I posted) but I couldn't make it work.
The XML seems sound, IE opens it without issue in both cases. I don't know what else could be the problem.

Thanks!

allan

Without being able to see it I am afraid I can say. Did you try doing the diff? That is the approach I would take myself.

Allan

Dzyann

Hi Allan,
What would you like me to do in order for you to see it? Maybe attach a sample excel with the issue?

Thanks!

tangerine

Did you try doing the diff?

allan

Yup - do the diff and see what the difference is. That will show what is cause Excel to reject the file. My guess is empty xmlns name space attributes.

Allan