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;
}
Advertisement
Answer
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.