Customize excel export

har94

Hi,
Great tool, I want to export an excel like attached copy.
is it possible in datatables?

allan

Yes, but you'd need to use the customize callback of the excelHtml5 button type to modify the XML of the XLSX file to match that. There isn't a simple easy to use API for modifying the created spreadsheet.

Allan

har94

"buttons": [{
                "extend": 'excelHtml5',
                "text": 'Excel',
                "footer": true,
                "exportOptions": _tableExportOptions,
                "customize": _customizeExcelOptions
            }]


  var _customizeExcelOptions = function (xlsx) {
                var sheet = xlsx.xl.worksheets['sheet1.xml'];
                var numrows = 5;
                var clR = $('row', sheet);

                //update Row
                clR.each(function () {
                    var attr = $(this).attr('r');
                    var ind = parseInt(attr);
                    ind = ind + numrows;
                    $(this).attr("r", ind);
                });

                // Create row before data
                $('row c ', sheet).each(function () {
                    var attr = $(this).attr('r');
                    var pre = attr.substring(0, 1);
                    var ind = parseInt(attr.substring(1, attr.length));
                    ind = ind + numrows;
                    $(this).attr("r", pre + ind);
                });

                function Addrow(index, data) {
                  var msg = '<row r="' + index + '">'
                    for (var i = 0; i < data.length; i++) {
                        var key = data[i].key;
                        var value = data[i].value;
                        msg += '<c t="inlineStr" r="' + key + index + '">';
                        msg += '<is>';
                        msg += '<t>' + value + '</t>';
                        msg += '</is>';
                        msg += '</c>';
                    }
                    msg += '</row>';
                    return msg;
                }


                //insert
                var r1 = Addrow(1, [{ key: 'E', value: reportType }]);
                var r2 = Addrow(2, [ { key: 'E', value: pdfHeaderDateRange }]);
                var r3 = Addrow(3, [{ key: 'B', value: pdfMessage }]);

                sheet.childNodes[0].childNodes[1].innerHTML = r1 + r2 + r3 + sheet.childNodes[0].childNodes[1].innerHTML;


                $('row c[r^="B6"]', sheet).attr('s', '48');
                $('row c[r^="A6"]', sheet).attr('s', '48');

                $('row c[r^="B3"]', sheet).attr('s', '48');
                $('row c[r^="E1"]', sheet).attr('s', '48');
                $('row c[r^="E2"]', sheet).attr('s', '48');

            }

This is what I had written. But I can't able to set height and width of the row.

allan

The hight aspect is beyond my knowledge of the XLSX format that I'm afraid. This is how Buttons sets the width of columns in the file. You'd need to override that col elements that Buttons has created.

Allan

har94

Thanks you allan

j.cue

Hello,

I know this has been solved, but I am curious as to how you were able to change the font size in your Excel file. From your picture "Daily Branch Report" is a larger size than 11. Can you show your code where you edited the XML to do that? I would appreciate it.

Joe

allan

This is where Buttons defines the size. You could change that in the code, or using the customize callback.

Allan