Customize excel export

Customize excel export

har94har94 Posts: 5Questions: 1Answers: 0

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

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 61,452Questions: 1Answers: 10,055 Site admin

    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

  • har94har94 Posts: 5Questions: 1Answers: 0
    "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.

  • allanallan Posts: 61,452Questions: 1Answers: 10,055 Site admin
    Answer ✓

    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

  • har94har94 Posts: 5Questions: 1Answers: 0

    Thanks you allan

  • j.cuej.cue Posts: 7Questions: 1Answers: 0

    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

  • allanallan Posts: 61,452Questions: 1Answers: 10,055 Site admin

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

    Allan

This discussion has been closed.