How to format an Excel export from a queue?

The parameters  "Table Excel export customize" and "Table Excel export options" allow you to customize the format of the columns of the queue when the data is exported in Excel format. You can access these parameters from the administration of the queue.

By means of the "Table Excel export customize" field, we can establish rules for displaying and applying styles following the different types offered by the datatable.js component:

https://datatables.net/reference/button/excelHtml5

An example of this field is shown below, where the style with ordinal 59 is established in column "G".

• 59- Euro currency values (automatically detected and used by buttons - since 1.2.3)

var sheet = xlsx.xl.worksheets['sheet1.xml'];
$('row:gt(1) c[r^="G"]', sheet).attr('s', 59);

In this way, the exported data will be shown in currency format with the Euro symbol.

By means of the field "Table Excel export option" the possibility is offered to set changes on the data of fields on which the style will be applied. In this way, we can homogenize the exported data.

Below is an example of how to make the exported data unified to show a monetary value with the symbol Euro. To do this, the decimal separator is always set to "." and the Euro character is removed to avoid displaying it twice.

format: {
    body: function(data, row, column, node) {
        data = $('<p>' + data + '</p>').text();
        if (data.includes('€')) data = data.replace('€', '');
        if ($.isNumeric(data.replace(',', '.'))) data = data.replace(',', '.');
        return data;
    }
}

We would have the configuration as follows:

We show below how we would have the output of the original Excel and the output after the configuration of these two fields. 

Queue:

Original output without configuration:

Output after configuration:

Extended settings

A more extended configuration of styles can be applied to the Excel export, applying font colors, background colors, or even borders. For this, the necessary styles will be defined in each case and then added using the addCustomExcelStyles method provided by Athento.

After including the new styles, they can be applied using jQuery selectors on the elements of the exported table according to the XML Spreadsheet format.

In addition, fixed column width can be applied by specifying the column position and width.

An example of the extended format is shown below.

NOTE: It is important to note the position information of the styles that are created because they will be used in the inclusion of the desired rows or cells.

var sheet = xlsx.xl.worksheets['sheet1.xml'];

var myFontColors = [
    '000000', // idx 0 - black
    'ffffff' // idx 1 - white
];

var myFillColors = [
    'ccffff', // idx 0 - cyan
    'ffc000', // idx 1 - dark green
    '0066cc' // idx 2 - blue
];

var myBorders = [
    { style: 'medium', color: '9999ff' }
];

var myCellStyles = [
    { fontIdx: 0, fillIdx: 2, vAlign: 'center', hAlign: 'left' }, // idx 0 - black on blue
    { fontIdx: 1, fillIdx: 2, vAlign: 'center', hAlign: 'left' }, // idx 1 - white on blue
    { fontIdx: 0, fillIdx: 0, vAlign: 'top', hAlign: 'left' } // idx 2 - black on cyan
];

// Add custom styles for Excel exportation
var stylesCount = addCustomExcelStyles(xlsx, myFontColors, myFillColors, myBorders, myCellStyles);

// Apply style with idx 0 (Black on Blue) to row number 1
$('row:eq(1) c', sheet).attr('s', getExcelStyle(stylesCount, 0));

// Apply style with idx 2 (Black on Cyan) to rows after position 1
$('row:lt(1) c', sheet).attr('s', getExcelStyle(stylesCount, 2));

// Apply width: 25 to first column (position 0) and width: 40 to second column (position 1)
$('col', sheet)[0].attr('width', 25);
$('col', sheet)[1].attr('width', 40);