datatable excel export customize

stackoverflow.com › questions › 73765025 › datatables-excel-export-with-styles

UPDATE

The below process may be suitable for your needs, but you may prefer to avoid making manual changes, by using a different approach:

jQuery datatable export to excel with cell() background color

This approach does not require manual file editing.

ORIGINAL ANSWER

Looking at your comment:

"how to add new styles by modifying "xl/styles.xml" section in “buttons.html5.js” file, tried with many ways but no success"

Here is a step-by-step guide to how I edited my local copy of the buttons.html5.js file to add a new custom style.

I first went to the DataTables download page and downloaded the files I need.
I used the related script tags in the <head> section of the page - for example:

<script type="text/javascript" src="buttons/Buttons-2.2.3/js/buttons.html5.js"></script>

And I made sure my files were in the expected location relative to my HTML file containing my DataTable.

I edited my buttons.html5.js file as follows... You need to get this exactly right...

What I will do in the next steps is:

add one new font style to define a new font color (a shade of green).
add one new fill style to define a new cell background color (a shade of yellow).
add one new cell formatting style which uses the above colors.

I added one new font to the <fonts> section, at the end:

            '<fonts count="6" x14ac:knownFonts="1">'+
                '<font>'+
                    '<sz val="11" />'+
                    '<name val="Calibri" />'+
                '</font>'+
                '<font>'+
                    '<sz val="11" />'+
                    '<name val="Calibri" />'+
                    '<color rgb="FFFFFFFF" />'+
                '</font>'+
                '<font>'+
                    '<sz val="11" />'+
                    '<name val="Calibri" />'+
                    '<b />'+
                '</font>'+
                '<font>'+
                    '<sz val="11" />'+
                    '<name val="Calibri" />'+
                    '<i />'+
                '</font>'+
                '<font>'+
                    '<sz val="11" />'+
                    '<name val="Calibri" />'+
                    '<u />'+
                '</font>'+
                
                // added font ID 5:
                '<font>'+
                    '<sz val="11" />'+
                    '<name val="Calibri" />'+
                    '<color rgb="FF548235" />'+
                '</font>'+
                
            '</fonts>'+

I incremented the related count (at the top of that section) from count="5" to count="6".

That RGB string FF548235 represents the color 548235, which is a shade of green, with an alpha channel of FF (meaning opaque). Note how the FF of the alpha channel comes before the RGB code.

I repeated the process for the <fills> section, adding one new fill color:

            '<fills count="7">'+
                '<fill>'+
                    '<patternFill patternType="none" />'+
                '</fill>'+
                '<fill>'+ // Excel appears to use this as a dotted background regardless of values but
                    '<patternFill patternType="none" />'+ // to be valid to the schema, use a patternFill
                '</fill>'+
                '<fill>'+
                    '<patternFill patternType="solid">'+
                        '<fgColor rgb="FFD9D9D9" />'+
                        '<bgColor indexed="64" />'+
                    '</patternFill>'+
                '</fill>'+
                '<fill>'+
                    '<patternFill patternType="solid">'+
                        '<fgColor rgb="FFD99795" />'+
                        '<bgColor indexed="64" />'+
                    '</patternFill>'+
                '</fill>'+
                '<fill>'+
                    '<patternFill patternType="solid">'+
                        '<fgColor rgb="ffc6efce" />'+
                        '<bgColor indexed="64" />'+
                    '</patternFill>'+
                '</fill>'+
                '<fill>'+
                    '<patternFill patternType="solid">'+
                        '<fgColor rgb="ffc6cfef" />'+
                        '<bgColor indexed="64" />'+
                    '</patternFill>'+
                '</fill>'+
                
                // added fill ID 6:
                '<fill>'+
                    '<patternFill patternType="solid">'+
                        '<fgColor rgb="ffffc000" />'+
                        '<bgColor indexed="64" />'+
                    '</patternFill>'+
                '</fill>'+
                
            '</fills>'+

The fill color is ffffc000, which is ff for the alpha channel, followed by the RGB code of ffc000 - which is a shade of darker green.

As with the fonts section, I incremented the related count (at the top of that section). This time, it changed from count="6" to count="7".

NOTE that the font ID and fill ID are 1 less than the total count, because the IDs start at zero - so that is why the 7th <fill> has an ID of 6.

You do not provide these IDs in the XML. Excel determines the IDs based on the position of the font (or fill) within the overall fonts (or fills) list.

But you need to know the correct ID numbers so you can use them in the next step...

I added a new style at the end of the <cellXfs> section:

                // added xf ID 68:
                '<xf numFmtId="0" fontId="5" fillId="6" borderId="0" applyFont="1" applyFill="1" applyBorder="1"/>'+

This is where I use the font and fill IDs: fontId="5" fillId="6".

I also needed to increment the overall style count from count="68" to count="69".

After saving all the above changes, I can now use that new style - which is style ID 68.

I used the code from your question for this:

var table = $('#example').DataTable( {
  dom: "Brftip",
  buttons: [{
    extend: 'excelHtml5',
    customize: function(xlsx) {
        var sheet = xlsx.xl.worksheets['sheet1.xml'];
 
        $('row c', sheet).each( function () {
            // Get the value
            if ( $('is t', this).text() == 'New York' ) {
                $(this).attr( 's', '68' );
            }
        });
    }
  }]
} );

Note the attr( 's', '68' ) above.

Using my test data, this generates the following:

The big downside with this approach is that you have manually edited the DataTables JavaScript - and those changes will be lost when you upgrade DataTables to a newer version. You will have to repeat the above edits.

You can avoid this by automating these edits in your DataTable's JavaScript code using customize: function( xlsx ).

That function gives you access to the Excel object (xlsx) inside which all the spreadsheet's data and styles are stored.

That is a larger, more complicated programming task.

But if you are interested, you can see an example showing a similar approach here: JQuery Datatable Excel export number format and also in various other questions on Stack Overflow.

Answer from andrewJames on Stack Overflow

DataTables

datatables.net › extensions › buttons › examples › initialisation › export.html

DataTables example - File export

new DataTable('#example', { layout: { topStart: { buttons: ['copy', 'csv', 'excel', 'pdf', 'print'] } } });

DataTables

datatables.net › extensions › buttons › examples › html5 › excelBorder.html

DataTables example - Excel - Customise borders

Comprehensive editing library for DataTables. ... The Excel export button saves to an XLSX file and the data can be customised before exporting the file using the customize method of the excelHtml5 button type.

Discussions

Customize excel export — DataTables forums

Hi, Great tool, I want to export an excel like attached copy. is it possible in datatables? More on datatables.net

datatables.net

December 22, 2016

css - DataTables Excel export with styles - Stack Overflow

You can avoid this by automating ... using customize: function( xlsx ). That function gives you access to the Excel object (xlsx) inside which all the spreadsheet's data and styles are stored. That is a larger, more complicated programming task. But if you are interested, you can see an example showing a similar approach here: JQuery Datatable Excel export number format ... More on stackoverflow.com

stackoverflow.com

Excel Export Column Customize Format — DataTables forums

Hi, I'm Transferring to Excel as follows, Number 5 Digit Character L is on Column, Here I want to do formatting as follows, 5,2167 Must be 4 characters after the comma, How Can I Convert This Format ? More on datatables.net

datatables.net

custom button for excel export — DataTables forums

I'm using the solution from @you2525 for excel export with multiple sheets Is there also away to define a Custom button which exports a specific table to… More on datatables.net

datatables.net

Videos

youtube.com

How to Export DataTable to Excel, PDF & CSV Using Custom Button ...

October 7, 2023

05:58

YouTube

Data Table Export Table Data in PDF, Excel, CSV | DataTable JS ...

January 22, 2022

12:09

YouTube

DataTabels JS | How to export table in Excel, CSV, PDF with ...

August 15, 2020

youtube.com

Export jQuery Datatable Data into CSV,PDF and Excel Using ...

youtube.com

Datatables - Exportar a Excel y personalizar impresión

View all

DataTables

datatables.net › forums › discussion › 78202 › modifying-datatable-function-to-use-export-to-excel-functionality

Modifying datatable function to use export to excel functionality — DataTables forums

February 17, 2024 - I tried adding the columnDefs to the datatable definition and also the export options. The excel sheet does hide the first column, so that part is working.

DataTables

datatables.net › forums › discussion › 39605 › customize-excel-export

Customize excel export — DataTables forums

December 22, 2016 - "buttons": [{ "extend": 'excelHtml5', "text": 'Excel', "footer": true, "exportOptions": _tableExportOptions, "customize": _customizeExcelOptions }] var _customizeExcelOptions = function (xlsx) { var sheet = xlsx.xl.worksheets['sheet1.xml']; var numrows = 5; var clR = $('row', sheet); //update Row clR.each(function () { var attr = $(this).attr('r'); var ind = parseInt(attr); ind = ind + numrows; $(this).attr("r", ind); }); // Create row before data $('row c ', sheet).each(function () { var attr = $(this).attr('r'); var pre = attr.substring(0, 1); var ind = parseInt(attr.substring(1, attr.length

DataTables

datatables.net › extensions › buttons › examples › html5 › excelCellShading.html

DataTables example - Excel - Cell background

Northcoder

northcoder.com › post › modifying-exported-datatables-data

Modifying Exported DataTables Data | northCoder

Another export data option, similar to the above example, but this one provides the data after all of it has been gathered and pre-processed by all other formatting options: ... The customizeData option is a bit of a legacy hack. It was put in place before the Excel export buttons had a customize callback and it was the only way to modify the output data.

GitHub

github.com › pjjonesnz › datatables-buttons-excel-styles

GitHub - pjjonesnz/datatables-buttons-excel-styles: Easy custom styling of the Excel export from DataTables jQuery plug-in · GitHub

Easy custom styling of the Excel export from DataTables jQuery plug-in - pjjonesnz/datatables-buttons-excel-styles

Starred by 41 users

Forked by 11 users

Languages JavaScript

Find elsewhere

Google Bing Mojeek

Stack Overflow

stackoverflow.com › questions › 73765025 › datatables-excel-export-with-styles

css - DataTables Excel export with styles - Stack Overflow

Top answer

1 of 1

UPDATE

The below process may be suitable for your needs, but you may prefer to avoid making manual changes, by using a different approach:

jQuery datatable export to excel with cell() background color

This approach does not require manual file editing.

ORIGINAL ANSWER

Looking at your comment:

"how to add new styles by modifying "xl/styles.xml" section in “buttons.html5.js” file, tried with many ways but no success"

Here is a step-by-step guide to how I edited my local copy of the buttons.html5.js file to add a new custom style.

I first went to the DataTables download page and downloaded the files I need.
I used the related script tags in the <head> section of the page - for example:

<script type="text/javascript" src="buttons/Buttons-2.2.3/js/buttons.html5.js"></script>

And I made sure my files were in the expected location relative to my HTML file containing my DataTable.

I edited my buttons.html5.js file as follows... You need to get this exactly right...

What I will do in the next steps is:

add one new font style to define a new font color (a shade of green).
add one new fill style to define a new cell background color (a shade of yellow).
add one new cell formatting style which uses the above colors.

I added one new font to the <fonts> section, at the end:

            '<fonts count="6" x14ac:knownFonts="1">'+
                '<font>'+
                    '<sz val="11" />'+
                    '<name val="Calibri" />'+
                '</font>'+
                '<font>'+
                    '<sz val="11" />'+
                    '<name val="Calibri" />'+
                    '<color rgb="FFFFFFFF" />'+
                '</font>'+
                '<font>'+
                    '<sz val="11" />'+
                    '<name val="Calibri" />'+
                    '<b />'+
                '</font>'+
                '<font>'+
                    '<sz val="11" />'+
                    '<name val="Calibri" />'+
                    '<i />'+
                '</font>'+
                '<font>'+
                    '<sz val="11" />'+
                    '<name val="Calibri" />'+
                    '<u />'+
                '</font>'+
                
                // added font ID 5:
                '<font>'+
                    '<sz val="11" />'+
                    '<name val="Calibri" />'+
                    '<color rgb="FF548235" />'+
                '</font>'+
                
            '</fonts>'+

I incremented the related count (at the top of that section) from count="5" to count="6".

I repeated the process for the <fills> section, adding one new fill color:

            '<fills count="7">'+
                '<fill>'+
                    '<patternFill patternType="none" />'+
                '</fill>'+
                '<fill>'+ // Excel appears to use this as a dotted background regardless of values but
                    '<patternFill patternType="none" />'+ // to be valid to the schema, use a patternFill
                '</fill>'+
                '<fill>'+
                    '<patternFill patternType="solid">'+
                        '<fgColor rgb="FFD9D9D9" />'+
                        '<bgColor indexed="64" />'+
                    '</patternFill>'+
                '</fill>'+
                '<fill>'+
                    '<patternFill patternType="solid">'+
                        '<fgColor rgb="FFD99795" />'+
                        '<bgColor indexed="64" />'+
                    '</patternFill>'+
                '</fill>'+
                '<fill>'+
                    '<patternFill patternType="solid">'+
                        '<fgColor rgb="ffc6efce" />'+
                        '<bgColor indexed="64" />'+
                    '</patternFill>'+
                '</fill>'+
                '<fill>'+
                    '<patternFill patternType="solid">'+
                        '<fgColor rgb="ffc6cfef" />'+
                        '<bgColor indexed="64" />'+
                    '</patternFill>'+
                '</fill>'+
                
                // added fill ID 6:
                '<fill>'+
                    '<patternFill patternType="solid">'+
                        '<fgColor rgb="ffffc000" />'+
                        '<bgColor indexed="64" />'+
                    '</patternFill>'+
                '</fill>'+
                
            '</fills>'+

The fill color is ffffc000, which is ff for the alpha channel, followed by the RGB code of ffc000 - which is a shade of darker green.

As with the fonts section, I incremented the related count (at the top of that section). This time, it changed from count="6" to count="7".

NOTE that the font ID and fill ID are 1 less than the total count, because the IDs start at zero - so that is why the 7th <fill> has an ID of 6.

You do not provide these IDs in the XML. Excel determines the IDs based on the position of the font (or fill) within the overall fonts (or fills) list.

But you need to know the correct ID numbers so you can use them in the next step...

I added a new style at the end of the <cellXfs> section:

                // added xf ID 68:
                '<xf numFmtId="0" fontId="5" fillId="6" borderId="0" applyFont="1" applyFill="1" applyBorder="1"/>'+

This is where I use the font and fill IDs: fontId="5" fillId="6".

I also needed to increment the overall style count from count="68" to count="69".

After saving all the above changes, I can now use that new style - which is style ID 68.

I used the code from your question for this:

var table = $('#example').DataTable( {
  dom: "Brftip",
  buttons: [{
    extend: 'excelHtml5',
    customize: function(xlsx) {
        var sheet = xlsx.xl.worksheets['sheet1.xml'];
 
        $('row c', sheet).each( function () {
            // Get the value
            if ( $('is t', this).text() == 'New York' ) {
                $(this).attr( 's', '68' );
            }
        });
    }
  }]
} );

Note the attr( 's', '68' ) above.

Using my test data, this generates the following:

You can avoid this by automating these edits in your DataTable's JavaScript code using customize: function( xlsx ).

That function gives you access to the Excel object (xlsx) inside which all the spreadsheet's data and styles are stored.

That is a larger, more complicated programming task.

But if you are interested, you can see an example showing a similar approach here: JQuery Datatable Excel export number format and also in various other questions on Stack Overflow.

DataTables

datatables.net › reference › button › excel

excel

This button can have the following options set in its configuration object to customise its actions and display, in addition to those options which are available for all buttons (e.g. buttons.buttons.text): ... Create and save a Excel file. ... The button's class name. See buttons.buttons.className for details. ... Option to instruct the Excel export to create empty cells.

DataTables

datatables.net › forums › discussion › 58378 › excel-export-column-customize-format

Excel Export Column Customize Format — DataTables forums

$(document).ready(function () { $('#CariHesapHareketleri').DataTable({ buttons: ['copy', 'csv', 'excel', 'print', { extend: 'excel', text: 'Testing Excel', customize: function (xlsx, row) { var sheet = xlsx.xl.worksheets['sheet1.xml']; $('row c[r^="L"]', sheet).attr('s', 0); } } ] }); });

Northcoder

northcoder.com › post › exporting-to-excel-from-datatables

Exporting to Excel from DataTables | northCoder

May 1, 2021 - The above is just a small sample of the possible configuration options supported by Excel. The following page lists the main options which can be attached to an Excep export button. ... customize: function( xlsx ) { ...

DataTables

datatables.net › forums › discussion › 61669 › custom-button-for-excel-export

custom button for excel export — DataTables forums

I generally know how to add a custom button. My problem is how to start the excel export for a specific table. Taking the example from @you2525 the export is initialized for table example and the sheet for table example2 is added. var table = $('#example').DataTable({ dom: 'Bftrip', buttons: [ { extend: 'excelHtml5', text: 'Excel', customize: function( xlsx ) { setSheetName(xlsx, 'Calls'); addSheet(xlsx, '#example2', 'My Sheet2', 'Summary2', '2'); } } ] });

DataTables

datatables.net › extensions › buttons › examples › html5 › outputFormat-function.html

DataTables example - Format output data - export options

Webix

docs.webix.com › datatable__exporttoexcel.html

Export to Excel of DataTable, Data Export Webix Docs

Related sample: Datatable: Hiding Cols/Rows during Export to Excel · including cell styles from Datatable header and body into export: ... exporting row heights. This setting is regulated by the heights option, which is set to false by default and can take the following values: true - to export only custom row heights (different from the default rowHeight);

Stack Overflow

stackoverflow.com › questions › 36352181 › how-to-customize-export-to-csv-excel-pdf-in-jquery-datatables

how to customize export to csv,excel,pdf in jquery datatables - Stack Overflow

Top answer

1 of 1

For the excel, this function will add a new rows at the top of document:

customize: function (xlsx) {
                var sheet = xlsx.xl.worksheets['sheet1.xml'];
                var numrows = 3;
                var clR = $('row', sheet);

                //update Row
                clR.each(function () {
                    var attr = $(this).attr('r');
                    var ind = parseInt(attr);
                    ind = ind + numrows;
                    $(this).attr("r",ind);
                });

                // Create row before data
                $('row c ', sheet).each(function () {
                    var attr = $(this).attr('r');
                    var pre = attr.substring(0, 1);
                    var ind = parseInt(attr.substring(1, attr.length));
                    ind = ind + numrows;
                    $(this).attr("r", pre + ind);
                });

                function Addrow(index,data) {
                    msg='<row r="'+index+'">'
                    for(i=0;i<data.length;i++){
                        var key=data[i].key;
                        var value=data[i].value;
                        msg += '<c t="inlineStr" r="' + key + index + '">';
                        msg += '<is>';
                        msg +=  '<t>'+value+'</t>';
                        msg+=  '</is>';
                        msg+='</c>';
                    }
                    msg += '</row>';
                    return msg;
                }


                //insert
                var r1 = Addrow(1, [{ key: 'A', value: '' }, { key: 'B', value: '' }]);
                var r2 = Addrow(2, [{ key: 'A', value: '' }, { key: 'B', value: '' }]);
                var r3 = Addrow(3, [{ key: 'A', value: '' }, { key: 'B', value: '' }]);

                sheet.childNodes[0].childNodes[1].innerHTML = r1 + r2+ r3+ sheet.childNodes[0].childNodes[1].innerHTML;
            }

For CSV:

customize: function(doc){
                return "Your text" + doc;
            }

For the others exports you will need to put a message parameter like this:

{
            extend: 'print',
            message: "Your text"
        },

DataTables

datatables.net › forums › discussion › 60532 › how-do-customize-excel-with-using-datatable-in-jquery

How do customize excel with using datatable in jQuery? — DataTables forums

My expected exported excel file is as below picture: However my output is as below picture, my title report and address is located at middle isn't on top of report: ... { extend: 'excelHtml5', title: 'Trace Report', messageTop: 'ABC company' + 'address', //message: "Any message for header inside the file. I am not able to put message in next row in excel file but you can use \n"+'modelID'+modelId， render: function (data, type, full, meta) { return '<a href="' + data + '">Download</a>'; //change the button text here }, customize: function (xlsx) {

DataTables

datatables.net › reference › button › excelHtml5

excelHtml5

For complete control over the generated file, a custom button could be constructed using the SheetJS library. If your table has a header or footer with multiple rows, these will all be included in the export. If the header or footer contains colspan or rowspan cells, they will automatically be migrated to the Excel document for export.

DataTables

datatables.net › forums › discussion › 72366 › excel-export

Excel Export — DataTables forums

But then the next problem emerged: How to do it, a) if the user wants only selected columns (selected with the colvis button), or b) the user wants wysiwyg: only what is displayed on the screen (not including child-rows hidden by the "responsive" extension) - and still get the export formatting done in order to prevent Excel from destroying part of the data. This was hard to develop and my code for this is ugly, I think. I would strongly encourage a better solution for this. If anyone is interested how I resolved this with my ugly solution I can share it. allan Posts: 64,847Questions: 1Answers: 10,733 Site admin ... Presumably the ugly bit is in a customize callback to determine which columns need to get the formatting applied to?

DataTables

datatables.net › extensions › buttons › examples › html5 › titleMessage.html

DataTables example - Export titles and messages

Please note that the CSV export button types do not support these options! Including this type of meta information in a CSV file would make it unreadable to a CSV viewer. This example demonstrates the use of these options in various configurations: copy uses all of the default (title and caption bottom in this case) excel shows a top message in addition to the title and caption ... var printCounter = 0; $('#example').DataTable({ caption: "A fictional company's staff table.", layout: { topStart: { buttons: [ 'copy', { extend: 'excel', messageTop: 'The information in this table is copyright to Sirius Cybernetics Corp.'

DataTables

datatables.net › forums › discussion › 44164 › excel-export-customize

Excel Export customize — DataTables forums

August 17, 2017 - 1) The example on the homepage of datatables (Excel - Cell background) https://datatables.net/extensions/buttons/examples/html5/excelCellShading.html doesn't work if I'm trying to export the excel file. No cell has a blue background. See attached file Datatables_request_1.PNG. 2) The customize function doesn't work if I'm exporting the excel with new added row.