Alignment of text in the excel export

Learn2excel

Hello All,

I am new to development. I am using the below set of code for export to excel from datatable. The export works fine, but the alignment of the values in the first column are not matching. The column header is aligned to the left while the values are aligned to the right. Can you let me know what additional piece of function or code is required so that I can get the desired alignment in the exported excel.

buttons: [
{
extend: 'excelHtml5',
text: '<i class="btn_excel"></i> <span class=btn_text_align>Export to Excel</span>',
className: 'btn-sm btn_size',
titleAttr: 'Export to Excel',
exportOptions: {
columns: [0, 1, 2, 3],
orthogonal: null,
}

Thank you in advance.

ranjan16031999

Hello @Learn2excel ,

Thank you for providing the code snippet. To address the alignment issue in the exported Excel file, you can use the following modified code:

buttons: [
{
extend: 'excelHtml5',
text: '<i class="btn_excel"></i> <span class=btn_text_align>Export to Excel</span>',
className: 'btn-sm btn_size',
titleAttr: 'Export to Excel',
exportOptions: {
columns: [0, 1, 2, 3],
orthogonal: null,
},
customize: function (xlsx) {
var sheet = xlsx.xl.worksheets['sheet1.xml'];

  // Set the alignment for all cells in the first column (column A)
  $('row c[r^="A"]', sheet).each(function() {
    $(this).attr('s', '50'); // '50' is for left-aligned text and 52 for -right , 51 - center
  });
}

}
]

Learn2excel

Hi Ranjan

Thank you very much for providing the solution. Yes it's working. There is one additional thing also to be corrected. The first row displays the table title and spans across all the columns. This text is getting aligned to the left, but needs to be centre aligned. The column headers start in the next row after this. In the first column, yes now the header and values are both aligned to the left so that's working as you mentioned, but I want the column header to appear in bold text as the other columns are like that.

Thank you again for your prompt response.

allan

Exactly the same as before applies. You need to apply a suitable style to the cells that you want to modify. There are a number of built in styles that you can use. If they don't suit your needs, then you need to modify the generated XML to add the styles you want and then use them.

It is a PITA, I fully acknowledge that. My Excel exporter is really meant for simple use cases only - a full exporter is a whole project in its own right, and there is SheetJS for that.

Allan

ranjan16031999

Hello @Learn2excel ,

I'm glad to hear that the initial solution resolved the alignment issue for the first column. To address the additional requirements you mentioned, you can modify the customize function in the code. Here's the updated code:

buttons: [
  {
    extend: 'excelHtml5',
    text: '<i class="btn_excel"></i> <span class=btn_text_align>Export to Excel</span>',
    className: 'btn-sm btn_size',
    titleAttr: 'Export to Excel',
    exportOptions: {
      columns: [0, 1, 2, 3],
      orthogonal: null,
    },
    customize: function (xlsx) {
      var sheet = xlsx.xl.worksheets['sheet1.xml'];

      // Set the alignment for all cells in the first column (column A)
      $('row c[r^="A"]', sheet).each(function() {
        $(this).attr('s', '52'); // '52' is for right-aligned text
      });

      // Set the alignment for the first row (table title)
      $('row:first c', sheet).attr('s', '51'); // '51' is for center-aligned text

      // Set bold text for column headers
      $('row:nth-child(2) c', sheet).attr('s', '2'); // '2' is for bold text
    }
  }
]

Edited by Colin - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

Learn2excel

Hi Ranjan

Thank you for your prompt help. Yes, it's now working as expected.

Thank you

Learn2excel

@ranjan16031999

Also, if you could check my other question posted with title 'Using Exp to excel from tables with different number of columns' and provide your suggestions.

Thank you

allan

My answer there wasn't right?