How to convert button in to custom text when Export to Excelhtml5

Shivani Vyas

Hello, I have Buttons as column's data in my datatable which are rendered columns. When we click on buttons its redirect to another page. Now when I am exporting to excel.. I am getting 'Submit' text in columns where ever there is a Submit button. Instead of that I would like to display 'NS'. How do I replace Submit buttons (with url) to custom text - 'NS' instead text 'Submit' ?

var table = $("#assyntCx_Table").DataTable({

    dom: "lBfrtip",
    serverSide: true,
    //processing: true,

    ajax: {
      url:  "../ajax/at/comms_plans.php",
      type: "POST",
      deferRender: true
    },

    //TABLE WINDOW
    scrollY:        "65vh",
    scrollX:        true,
    scrollCollapse: true,
    paging:         true,
    fixedColumns:   {
      left: 3
    },

    order: [[0, 'asc']],
    columns: [


        { data: "CTSC_RcvdStat", class: "dt-nowrap",
            render: function (data, type, row) {
                if(row.reviewer_chkbx !== null){
                    var reviewer = row.reviewer_chkbx;
                    if(reviewer.indexOf("1") >= 0){
                        if(data !== null){
                            return data + ' ' + '(' +'<a href="../submissions/planSub.php?tx='+row.CTSC_TxNo+'">'+row.CTSC_TxNo+'</a>' + ')';
                        }else{
                            return '<a href="../submissions/newPlanSubmit.php?pid='+row['id']+'&rid=1"><button>Submit</button></a>';
                        }

                    }
                    else
                    {
                        return "N/A";
                    }
                }
                else
                {
                    return "N/A";
                }
            }
        },


    ],

Here is the button

table.button().add(
      null, { 
        extend: 'excelHtml5',
        text: 'Excel',
        title: 'Commissioning Plans',
        exportOptions: {
            orthogonal: 'export',
             format: {
                body: function (data, row, column, node) {

                    return column === 9 ?
                    data.replace( /Submit/ig, 'NS' ) :
                    data;

                }
             }
            // format: {
            //     body: function ( data, row, column, node ) {

            //         if( column == 9) {

            //             return 'NS';

            //         }
            //         //return data.replace( /Submit/ig, "NS" );
            //         // return column === 9 ?
            //         //     data.replace( /Submit/ig, 'NS' ) :
            //         //     data;
            //     }
            // }
        },
        customize: function (xlsx) {

        //copy _createNode function from source
       function _createNode(doc, nodeName, opts) { 
           var tempNode = doc.createElement(nodeName);

           if (opts) {
               if (opts.attr) {
                   $(tempNode).attr(opts.attr);
               }

               if (opts.children) {
                   $.each(opts.children, function (key, value) {
                       tempNode.appendChild(value);
                   });
               }

               if (opts.text !== null && opts.text !== undefined) {
                   tempNode.appendChild(doc.createTextNode(opts.text));
               }
           }

           return tempNode;
       }

       var sheet = xlsx.xl.worksheets['sheet1.xml'];
       var mergeCells = $('mergeCells', sheet);
       mergeCells[0].children[0].remove(); // remove merge cell 1st row

       var rows = $('row', sheet);
       rows[0].children[0].remove(); // clear header cell

       // create new cell
       rows[0].appendChild(_createNode(sheet, 'c', {
           attr: {
               t: 'inlineStr',
               r: 'J1', //address of new cell
               s: 51 // center style - https://www.datatables.net/reference/button/excelHtml5
           },
           children: {
               row: _createNode(sheet, 'is', {
                   children: {
                       row: _createNode(sheet, 't', {
                           text: 'Submission'
                       })
                   }
               })
           }
       }));


       // set new cell merged
       mergeCells[0].appendChild(_createNode(sheet, 'mergeCell', {
           attr: {
               ref: 'J1:P1' // merge address
           }
       }));

       mergeCells.attr('count', mergeCells.attr('count') + 1);

    }

    },
 );

The above button code giving me url link in Excel .

Instead of this urls.. I want result as below.. when export to excel
Where ever there is Submit text... I want 'NS' which stand for Not Submitted.

kthorngren

I think using orthogonal data, like this example, will do what you want.

Kevin

Shivani Vyas

@kthorngren Thanks a lot Kevin.