Alignment of text in the excel export
Alignment of text in the excel export
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.
This question has accepted answers - jump to:
Answers
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'];
}
]
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.
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
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:
Hi Ranjan
Thank you for your prompt help. Yes, it's now working as expected.
Thank you
@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
My answer there wasn't right?