Answers

• ranjan16031999 Posts: 4Questions: 0Answers: 2

  January 10 Answer ✓

  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 Posts: 33Questions: 5Answers: 1

  January 10

  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 Posts: 63,542Questions: 1Answers: 10,476 Site admin

  January 10

  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 Posts: 4Questions: 0Answers: 2

  January 10 edited January 11 Answer ✓

  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 Posts: 33Questions: 5Answers: 1

  January 10

  Hi Ranjan

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

  Thank you
• Learn2excel Posts: 33Questions: 5Answers: 1

  January 11

  @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 Posts: 63,542Questions: 1Answers: 10,476 Site admin

  January 11

  My answer there wasn't right?