How to add a custom row at the bottom of excel export

jasontt33

I need a way to insert a row at the end of my data set in the excel export. I am using the excelHtml5 buttons and can insert a row at the top before my column headers and data, but need to add a row at the bottom. Any ideas?

therul

You can do this, but you will need to run the buttons.html5.js locally and modify it..

here is my code where I set the width of the cells, I should probably submit it sometime.
But you would want to add your code after the broken out area

   //
    // Excel (xlsx) export
    //
    DataTable.ext.buttons.excelHtml5 = {

        className: 'buttons-excel buttons-html5',

        available: function () {
            return window.FileReader !== undefined && window.JSZip !== undefined && !_isSafari();
        },

        text: function (dt) {
            return dt.i18n('buttons.excel', 'Excel');
        },

        action: function (e, dt, button, config) {
            // Set the text
            var cellLength = [];
            var xml = '';
            var data = dt.buttons.exportData(config.exportOptions);
            var addRow = function (row) {
                var cells = [];

                for (var i = 0, ien = row.length ; i < ien ; i++) {
                    if (row[i] === null || row[i] === undefined) {
                        row[i] = '';
                    }

                    // Don't match numbers with leading zeros or a negative anywhere
                    // but the start
                    cells.push(typeof row[i] === 'number' || (row[i].match && row[i].match(/^-?[0-9\.]+$/) && row[i].charAt(0) !== '0') ?
                        '<c t="n"><v>' + row[i] + '</v></c>' :
                        '<c t="inlineStr"><is><t>' + (
                            !row[i].replace ?
                                row[i] :
                                row[i]
                                    .replace(/&(?!amp;)/g, '&amp;')
                                    .replace(/[\x00-\x1F\x7F-\x9F]/g, '')) + // remove control characters
                        '</t></is></c>'                                    // they are not valid in XML
                    );
                    if (cellLength.length == i) {
                        cellLength[i] = 0;
                    }
                    var myLength = getWidthOfText(row[i]);
                    cellLength[i] = cellLength[i] > myLength ? cellLength[i] : myLength;
                }

                return '<row>' + cells.join('') + '</row>';
            };
            //Define the columns
            
            if (config.header) {
                xml += addRow(data.header);
            }

            for (var i = 0, ien = data.body.length ; i < ien ; i++) {
                xml += addRow(data.body[i]);
            }

            if (config.footer) {
                xml += addRow(data.footer);
            }
            var lenXML = '';
            for (var i = 1; i <= cellLength.length; i++) {
                //lenXML += '<col min="' + i + '" max="' + i + '" width="' + cellLength[i-1].toString() + '" customWidth="1"/>';
                if(cellLength[i-1] != undefined){
                    lenXML += '<col min="' + i + '" max="' + i + '" width="' + cellLength[i - 1].toString() + '" bestFit="1" customWidth="1"/>'
                }
            }
            lenXML = lenXML ;
            var zip = new window.JSZip();
            var _rels = zip.folder("_rels");
            var xl = zip.folder("xl");
            var xl_rels = zip.folder("xl/_rels");
            var xl_worksheets = zip.folder("xl/worksheets");

            zip.file('[Content_Types].xml', excelStrings['[Content_Types].xml']);
            _rels.file('.rels', excelStrings['_rels/.rels']);
            xl.file('workbook.xml', excelStrings['xl/workbook.xml']);
            xl_rels.file('workbook.xml.rels', excelStrings['xl/_rels/workbook.xml.rels']);
            excelStrings['xl/worksheets/sheet1.xml'] = excelStrings['xl/worksheets/sheet1.xml'].replace('_colWidth_',lenXML);
            xl_worksheets.file('sheet1.xml', excelStrings['xl/worksheets/sheet1.xml'].replace('__DATA__', xml));

            _saveAs(
                zip.generate({ type: "blob" }),
                _filename(config)
            );
        },

        filename: '*',

        extension: '.xlsx',

        exportOptions: {},

        header: true,

        footer: false
    };
    function getWidthOfText(txt) {

        return txt.length ;
    }