Can I rotate the table while exporting to excel .

rashad

i want transpose rows and columns placement.

rf1234

Well you can do anything but it is complex and you will be more or less on your own. You will need to develop a basic understanding of Open Office XML as well. No fun ...
https://datatables.net/reference/button/excelHtml5
Search for "Customisation" on that page.

Here is an example for an excel export in which I also move cells for certain columns etc. In addition I define my own fonts and apply them etc.

Would I do it again? Probably not client side, but do it with something like PHP Spreadsheet on the server. PHP Spreadsheet allows you to use your own templates as well.

//custom button for cashflow excel generation
$.fn.dataTable.ext.buttons.excelCashFlow = {
    extend: 'excel', filename: 'cashFlow', sheetName: 'cashflow1', title: cashFlowTitle,
    customize: function( xlsx ) {
// see built in styles here: https://datatables.net/reference/button/excelHtml5
// take a look at "buttons.html5.js", search for "xl/styles.xml"
//styleSheet.childNodes[0].childNodes[0] ==> number formats  <numFmts count="6"> </numFmts>
//styleSheet.childNodes[0].childNodes[1] ==> fonts           <fonts count="5" x14ac:knownFonts="1"> </fonts>
//styleSheet.childNodes[0].childNodes[2] ==> fills           <fills count="6"> </fills>
//styleSheet.childNodes[0].childNodes[3] ==> borders         <borders count="2"> </borders>
//styleSheet.childNodes[0].childNodes[4] ==> cell style xfs  <cellStyleXfs count="1"> </cellStyleXfs>
//styleSheet.childNodes[0].childNodes[5] ==> cell xfs        <cellXfs count="67"> </cellXfs>
//on the last line we have the 67 currently built in styles (0 - 66), see link above

        var sSh = xlsx.xl['styles.xml'];
        var lastXfIndex = $('cellXfs xf', sSh).length - 1;            
        var lastFontIndex = $('fonts font', sSh).length - 1; 
        var f1 = //bold and underlined font
        '<font>'+
                '<sz val="11" />'+
                '<name val="Calibri" />'+
                '<b />'+'<u />'+
        '</font>'

        var i; var y;
//n1, n2 ... are number formats; s1, s2, ... are styles
        var n1 = '<numFmt formatCode="##0.0000%" numFmtId="300"/>';
        var s1 = '<xf numFmtId="300" fontId="0" fillId="0" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyNumberFormat="1"/>';
        //define the style with the new font (number passed in as a variable)
        var s2 = '<xf numFmtId="0" fontId="'+(lastFontIndex+1)+'" fillId="2" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyAlignment="1">'+
                    '<alignment horizontal="center"/></xf>';         
//s3 is a combination of built in fonts 64 (2 dec places which has numFmtId="4") AND 2 (bold)
//just copied the xf of "two decimal places" and and changed the fontId based on "bold"  
        var s3 = '<xf numFmtId="4" fontId="2" fillId="0" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyNumberFormat="1"/>'
        var s4 = '<xf numFmtId="0" fontId="2" fillId="2" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyAlignment="1">'+
                    '<alignment horizontal="center" wrapText="1"/></xf>'
        sSh.childNodes[0].childNodes[0].innerHTML += n1;  //new number format
        sSh.childNodes[0].childNodes[1].innerHTML += f1; //new font
        sSh.childNodes[0].childNodes[5].innerHTML += s1 + s2 + s3 + s4; //new styles

        var fourDecPlaces    = lastXfIndex + 1;
        var greyBoldCentered = lastXfIndex + 2;
        var twoDecPlacesBold = lastXfIndex + 3;
        var greyBoldWrapText = lastXfIndex + 4;

        var sheet = xlsx.xl.worksheets['sheet1.xml'];
    //create array of all columns (0 - N)
        var cols = $('col', sheet);
    //set lenght of some columns: col number: length (excl. first column)
        var colsLength = ['01:12', '02:12', '03:16', '04:10', '05:12', '06:16',
                          '07:16', '08:16', '09:16', '10:16', '11:16', '12:16',
                          '13:16', '14:16', '15:16'];
        for ( i=0; i < colsLength.length; i++ ) {
            $( cols [ parseInt( colsLength[i].substr(0,2) ) ] )
                    .attr('width', parseInt( colsLength[i].substr(3) ) );                
        }

//two decimal places columns          
        var twoDecPlacesCols = ['D', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P'];            
        for ( i=0; i < twoDecPlacesCols.length; i++ ) {
            $('row c[r^='+twoDecPlacesCols[i]+']', sheet).attr( 's', '64' );
        }
        $('row c[r^="E"]', sheet).attr( 's', fourDecPlaces );  //% 4 decimal places, as added above
//                $('row c', sheet).attr( 's', '25' ); //for all rows
        $('row:eq(0) c', sheet).attr( 's', greyBoldCentered );  //grey background bold and centered, as added above
        $('row:eq(1) c', sheet).attr( 's', greyBoldWrapText );  //grey background bold, text wrapped
        $('row:last c', sheet).attr( 's', '2' );  //bold

//move text from column B to column A and empty columns B through F of rows with totals
        var rows = $('row c', sheet);  //create array of all rows (0 - N)      
        var copyPaste; var dateRefRate; var newDate;
        var emptyCellCols = ['B', 'C', 'D', 'E', 'F'];
        var twoDecPlacesBoldCols = ['G', 'H', 'I', 'J', 'L', 'M', 'O', 'P'];
        var boldCols = ['A'];
        for ( i=2; i < rows.length; i++ ) {                  
            copyPaste = $('row:eq('+i+') c[r^="B"] t', sheet).text();
            if ( copyPaste == 'Summen per Laufzeitende' ||
                 copyPaste == 'Totals'                     ) {
                $('row:eq('+i+') c[r^="A"] t', sheet).text(copyPaste);                    
                for ( y=0; y < emptyCellCols.length; y++ ) {
                    $('row:eq('+i+') c[r^='+emptyCellCols[y]+']', sheet).text('');
                }
                for ( y=0; y < twoDecPlacesBoldCols.length; y++ ) {
                    $('row:eq('+i+') c[r^='+twoDecPlacesBoldCols[y]+']', sheet).attr( 's', twoDecPlacesBold );
                }
                for ( y=0; y < boldCols.length; y++ ) {
                    $('row:eq('+i+') c[r^='+boldCols[y]+']', sheet).attr( 's', '2' );  //bold
                }
            }           
        }            
    },
    exportOptions: {
        columns: function(column, data, node) {
            if (column > 15) {
                return false;
            }
            return true;
        },
        //passing "undefined" also works but only with quotes
        modifier: { selected: null }, //make sure all records show up
        format: {
            body: function ( data, row, column, node ) {
                if (typeof data !== 'undefined') {
                    if (data != null) {  
                        if (column === 3 || column === 4 || column > 5) {
                            if (lang == 'de') { //this time we use the English formatting
                                //data contain only one comma we need to split there
                                var arr = data.split(',');
                                //subsequently replace all the periods with spaces
                                arr[0] = arr[0].toString().replace( /[\.]/g, "" );
                                //join the pieces together with a period if not empty
                                if (arr[0] > ''  || arr[1] > '') {
                                    data = arr[0] + '.' + arr[1];
                                } else {
                                    return '';
                                }
                            } else {
                                data = data.toString().replace( /[\,]/g, "" );
                            }
                            //result a number still as a string with decimal . and
                            //no thousand separators
                            //replace everything except numbers, decimal point and minus
                            data = data.toString().replace( /[^\d.-]/g, "" ); 
                            //percent must be adjusted to fraction to work ok
                            if (column == 4) {
                                if (data !== '') {
                                    data = data / 100;
                                }
                            }
                            return data;                                
                        }
                    }
                }
                return data;
            },
            header: function ( data, column ) {
                return data;
            }
        }
    }
};

rashad

        $(function() {
            grid = {
                dt: null,
                init: function() {
                    window.dt = $('#gridEmployeeFullReport').DataTable({
                        autoFill: true,
                        dom: 'Bfrtip',
                        buttons: [{
                            text: '<img src="@Url.Content("~/Content/Images/icons/object/Printer_32x32.png")" data-toggle="tooltip" data-placement="top" title="@SimilarResources.PrintButton">',
                            filename: '@EmployeeFullReportResources.HeaderEmployeeFullReport',
                            sheetName: 'EmployeeFullReport',
                            orientation: 'landscape',
                            autoFilter: false,
                            charset: 'utf-8',
                            extend: 'excel',
                            titleAttr: null,
                            title: null
                        }],
                        "serverSide": true,
                        "processing": true,
                        "responsive": false,
                        "searching": false,
                        "rowId": "Id",
                        "language": @Html.Raw(DataTableResources.Language),
                        "lengthMenu": [[10, 25, 50, 100], [10, 25, 50, 100]],
                        "ajax": {
                            "url": '@Url.Action("GetEmployeeFullReport")',
                            "type": "POST"
                        },
                        "columns": columns
                    });
                }
            }
            grid.init();
            $('#gridEmployeeFullReport').wrap("<div class='scrolled-table'></div>");

        });

rf1234

ok, some code. You are not doing any customization of the excel export in that code. So this is not quite in line with "i want transpose rows and columns placement."
Good luck!