Alignment and formatting issues in Jquery DataTable export to excel

Question

In the below example, I am having one jquery datatable and exporting it into excel.

 //myjsfile.js

function DisplayingData() {

  $('#mytable').DataTable({
   
    columnDefs: [{ type: 'currency', targets: [1] }, { targets: 1, className: "text-right" }],
    dom: 'lBfrtip',
    buttons: [
        {
            extend: 'excel',
            text: '<i class="fa fa-file-excel"></i> Excel',
            title: 'Balance',
            
            className: 'btn btn-success',
            exportOptions: {
                columns: ':not(.notexport)'
            }

        },
        
    ]   
});

---

// mypage.cshtml

<div id="mytable">
<table>
<thead>
            <tr>
                <th>
                    Balance
                </th>
             </tr>
    </thead>
<tbody>

  for (int i = 0; i < Model.CurrencyData.Count; i++)
   {
    @String.Format("{0:c}", Model.CurrencyData[i].Balance)

  }
</tbody>
</table>
</div>

The below picture is an example from the above code which i wrote here as same as my original code.

////After adding customization, highlighted by //*****
 function DisplayingData() {

  $('#mytable').DataTable({
   
    columnDefs: [{ type: 'currency', targets: [1] }, { targets: 1, className: "text-right" }],
    dom: 'lBfrtip',
    buttons: [
        {
            extend: 'excel',
            text: '<i class="fa fa-file-excel"></i> Excel',
            title: 'Balance',
             //*****
            customize: function (xlsx) {
            var sheet = xlsx.xl.worksheets['sheet1.xml'];
                 $('row c[r ^= "A"]', sheet).attr('s', '52');//right align*/
            //*****
            className: 'btn btn-success',
            exportOptions: {
                columns: ':not(.notexport)'
            }

        },
        
    ]   
});

Output below :

How can make this column right aligned in export to excel without loosing currency format ?

Answer 1

So I didn't find any short cut or easy way to solve this problem other than iterating each row/cells. The below code solved my problem.

      $('row c[r^="I"],c[r^="J"]', sheet).each(function () {
                    var cValue = $('is t', this).text(); 
                    if (cValue.length > 0) {
                       
                            $(this).attr('s', '57'); - first apply currency formatting
                            $(this).attr('s', '52'); - then set for right alignment
                    }
                });