How to align text to Top in first column in excel when exported from Datatables

Question

here i want to align text of column A top aligned in excel sheet when i export it from data tables. iam new programmer any help is appreciated.
this what iam getting

the result i want is the column A in excel should be Top aligned when i export it this is the result i want.as you can see Column A texts is top aligned

this Top align feature in MS excel as show in pic below .but i want it in datatable code

here is my code used to export so far `

    {
              extend: 'excelHtml5',
              footer: true,
              text: 'Save as Excel',
              pageSize: 'A4',
              title:'shop',
              filename:'shop',
              
              
              customize: function (xlsx) {
    
                
                            var sheet = xlsx.xl.worksheets['sheet1.xml'];
                            var style = xlsx.xl['styles.xml'];
                       
              
                           var tagName = style.getElementsByTagName('sz');
                          
                           $('row c[r^="A"]', sheet).attr( 's', '2' );
                            $('row c[r^="B"]', sheet).attr( 's', '55' );
                            $('row[r=2] c', sheet).attr( 's', '32' );
                            $('row[r=1] c', sheet).attr( 's', '51' );
    
                            $('xf', style).find("alignment[horizontal='center']").attr("wrapText", "1");
          
                            $('row', sheet).first().attr('ht', '40').attr('customHeight', "1");
                            
                            
                            
                            
                          
                         
    
                            var col = $('col', sheet);
                            $(col[0]).attr('width', 8);
                            $(col[1]).attr('width', 25);
                            $(col[2]).attr('width', 8);
                            $(col[3]).attr('width', 9);
                            $(col[4]).attr('width', 7);
                            $(col[5]).attr('width', 6);
                            $(col[6]).attr('width', 7);
                            $(col[7]).attr('width', 8);
                            $(col[8]).attr('width', 8);
                            $('row* ', sheet).each(function (index) {
                                if (index > 0) {
                                    $(this).attr('ht', 32);
                                    $(this).attr('customHeight', 1);
                                  
                                }
                            });
    
                            
    
                            var ranges = buildRanges(sheet);
                            ranges.push( "A1:I1" );
                            
                            // build the HTML string:
                            var mergeCellsHtml = '<mergeCells count="' + ranges.length + '">';
                            ranges.forEach(function(range) {
                                mergeCellsHtml = mergeCellsHtml + '<mergeCell ref="' + range + '"/>';
                            })
                            mergeCellsHtml = mergeCellsHtml + '</mergeCells>';
    
                            $( 'sheetData', sheet ).after( mergeCellsHtml );
                            // don't know why, but Excel auto-adds an extra mergeCells tag, so remove it:
                            $( 'mergeCells', sheet ).last().remove(); 
                            
    
    
    
                        },
              exportOptions: {
                columns: [1, 2, 3, 4, 5, 6, 7, 8, 9],
                
                
                rows: function (idx, data, node) {
                  return data[6] + data[7] > 0 ?
                    true : false;
                    
                }
              }
            }`
 function buildRanges(sheet) {

let prevCat = ''; // previous category
let currCat = ''; // current category
let currCellRef = ''; // current cell reference
let rows = $('row', sheet);
let startRange = '';
let endRange = '';
let ranges = [];

rows.each(function (i) {
  if (i > 0 && i < rows.length) { // skip first (headings) row
    let cols = $('c', $(this));
    cols.each(function (j) {
      if (j == 0) { // the "Category" column
        currCat = $(this).text(); // current row's category
        currCellRef = $(this).attr('r'); // e.g. "B3"
        if (currCat !== prevCat) {
          if (i == 0) {
            // start of first range
            startRange = currCellRef;
            endRange = currCellRef;
            prevCat = currCat;
          } else {
            // end of previous range
            if (endRange !== startRange) {
              // capture the range:
              ranges.push( startRange + ':' + endRange );
              
            }
             // start of a new range
            startRange = currCellRef;
            endRange = currCellRef;
            prevCat = currCat;
          }
        } else {
          // extend the current range end:
          endRange = currCellRef;
        }
        //console.log( $(this).attr('r') );
      }
    });
    if (i == rows.length -1 && endRange !== startRange) {
      // capture the final range:
      ranges.push( startRange + ':' + endRange );
     
    }
  }
});
return ranges;
}

Answer 1

You need to create a new style in your Excel file, and then apply that style to column A of your exported worksheet.

In Excel files, styles for top-aligned text are defined using the following XML fragment:

<xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0" applyAlignment="1">
    <alignment vertical="top"/>
</xf>

The important parts are applyAlignment="1" - meaning "use the alignment instruction; and <alignment vertical="top"/> - which is the type of alignment you want.

This style data is stored in a separate XML file in your Excel workbook: styles.xml. So, first you need to create a new style and add it to the existing list of styles in that XML file.

An Excel file exported from DataTables comes with a set of pre-defined styles, which are listed here. The style you want is not one of those styles - which is why we need to create our own.

---

The approach is very similar to this other question: JQuery Datatable Excel export number format which creates a custom number format.

But in this case, our logic is a bit simpler than the number format example.

The customize function:

customize: function( xlsx ) {
  addCustomFormat(xlsx);
  formatTargetColumn(xlsx, 'A'); // Excel column A
}

The two supporting functions used above:

function addCustomFormat(xlsx) {
  // add a new "cellXfs" cell formatter, which uses the next available format index (numFmt 176):
  var celXfsElement = xlsx.xl['styles.xml'].getElementsByTagName('cellXfs');
  var cellStyle = '<xf numFmtId="176" fontId="0" fillId="0" borderId="0" xfId="0" applyAlignment="1"' 
      + ' applyFont="1" applyFill="1" applyBorder="1"><alignment vertical="top"/></xf>';
  $( celXfsElement ).append( cellStyle );
  $( celXfsElement ).attr("count", "69"); // increment the count
}

and:

function formatTargetColumn(xlsx, col) {
  var sheet = xlsx.xl.worksheets['sheet1.xml'];
  // select all the cells whose addresses start with the letter prvoided
  // in 'col', and add a style (s) attribute for style number 68:
  $( 'row c[r^="' + col + '"]', sheet ).attr( 's', '68' );  
}

---

This logic will cause every cell in column A to use top-aligned text. You may want to refine that logic by making some changes to the above formatTargetColumn() function, in case you only want to apply this to rows 3 and lower. Otherwise, your headings may not look the way you want them to look.

I leave that exercise to you.

---

If you want to see for yourself how these XML fragments are used by Excel, you can simply take your Excel file and unzip it. You will see a folder structure containing all the different XML files which make up your Excel file, such as sheet1.xml, styles.xml and so on.

That is a great way for you to see for yourself how different formats are stored by Excel.