jquery datatable export to excel with style

buttons: [{
       //"colvis",
            extend: 'excelHtml5',
            text: 'Export to Excel',
            autoFilter: true,
            title: '',
            customize: function (xlsx) {
                var styles = xlsx.xl['styles.xml'];
            //custom font
           f1 = '<font><sz val="11" /><name val="Calibri" /><color rgb="FFFFFFFF" /><b /> 
                </font>';
           //custom colors
           //green 67
s1 = '<fill><patternFill patternType="solid"><fgColor rgb="C6E0B4" /><bgColor indexed="64" /></patternFill></fill>';
s2 = '<xf numFmtId="168" fontId="0" fillId="6" borderId="1" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyAlignment="1"><alignment horizontal="center"/></xf>';

styles.childNodes[0].childNodes[2].innerHTML = styles.childNodes[0].childNodes[2].innerHTML + s1;
styles.childNodes[0].childNodes[5].innerHTML = styles.childNodes[0].childNodes[5].innerHTML + s2;

 $('row c[r^="Z"]', sheet).attr('s', '67');
}]

Another reference can be found at this site: https://datatables.net/forums/discussion/45846/datatable-excel-export-how-can-we-apply-multiple-styles-to-same-cell

DataTables

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

DataTables example - Excel - Cell background

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. This example demonstrates how the created file can be customised by giving any cell in the Office column that has a value of 'New ...

Discussions

css - DataTables Excel export with styles - Stack Overflow

So anyone can help me with this, a trick or other way to apply specific styles on exported xlsx files. ... 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 More on stackoverflow.com

stackoverflow.com

Datatables export Excel - Stack Overflow

I'm exporting the datatables in csv. And when I open the file with excel, I've got problem with big numbers (around 20 digits). I also have problem with the special characters. I guess it's a forma... More on stackoverflow.com

stackoverflow.com

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

Hello All, I am new to development. I need help with the following. More on datatables.net

datatables.net

February 17, 2024

Table cells format when exporting jQuery Datatable to Excel

I have a datatable in my application that I need to export to Excel but when the datatable is exported in excel, the format type for each cell is 'General'. However, I want all my cells to be displ... More on stackoverflow.com

stackoverflow.com

Videos

26:45

YouTube

Add CSV Excel Export Button in Laravel Yajra Datatable - YouTube

May 29, 2020

10:03

YouTube

Export jQuery Datatables Data to Excel CSV PDF using PHP Ajax - ...

October 23, 2018

15:22

YouTube

jQuery datatables export to pdf - YouTube

Jquery Datatable Tutorial | How to add Copy | Excel | PDF Button ...

February 26, 2017

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

View all

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

var sheet = xlsx.xl.worksheets['sheet1.xml']; var numrows = 10; // add styles for the column header, these row will be moved down var clRow = $('row', sheet); //$(clRow[0]).find('c').attr('s', 32); //update Row clRow.each(function () { var attr = $(this).attr('r'); var ind = parseInt(attr); ind = ind + numrows; //ind is num of row +1 $(this).attr("r", ind); }); // Create row before data $('row c ', sheet).each(function (index) { var attr = $(this).attr('r'); var pre = attr.substring(0, 1); //pre=A,B,C..-F repeat 5 time var ind = parseInt(attr.substring(1, attr.length)); ind = ind + numrows; $(

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

DataTables

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

DataTables example - Excel - Customise borders

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. This example demonstrates how to manipulate the file using this method to add a styling attribute to a row in the XML used to create ...

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".

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.

Stack Overflow

stackoverflow.com › questions › 60528517 › datatables-export-excel

Datatables export Excel - Stack Overflow

Top answer

1 of 1

There is a self-contained example at the end of this answer, but here are your two problems:

Large Numbers

The best way to fix this is to use 'excel' instead of 'csv' here:

dom: 'Bfrtip',
"buttons": [
  'excel'
]

This will ensure the Excel cell format is "number" instead of "general".

I don't know of a way to automatically control the Excel cell format when using the CSV export option - unless you are prepared to save the CSV as a text file, then import into Excel and format it during the import (a manual process).

Accented Characters

There are various reasons why you could be having this issue - many of which are outside the scope of DataTables - so the following may not help you, but...

Make sure your HTML page contains this inside the head tag:

<meta charset="UTF-8">

This is sufficient for me to get my demo working (see below). For example:

However, like I say, there could be many other reasons - for example, see here.

Full Example

Paste the following HTML into a text file (use Notepad++ not Notepad, if you are on Windows). Assuming Notepad++, make sure the file is saved as UTF-8 - menu > Encoding > UTF-8. Then open the file in any browser.

You don't need all of those JS imports provided below (for example the PDF one); feel free to remove extra ones. (I have them for a fuller demo and was too lazy to remove them.)

<!doctype html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <title>Export to Excel</title>
  <script src="https://code.jquery.com/jquery-3.3.1.min.js"></script>
  <script src="https://cdn.datatables.net/1.10.20/js/jquery.dataTables.min.js"></script>
  <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.20/css/jquery.dataTables.min.css">
  <link rel="stylesheet" type="text/css" href="https://datatables.net/media/css/site-examples.css">

  <!-- buttons -->
  <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/buttons/1.6.1/css/buttons.dataTables.min.css">
  <script src="https://code.jquery.com/jquery-3.3.1.js"></script>
  <script src="https://cdn.datatables.net/1.10.20/js/jquery.dataTables.min.js"></script>
  <script src="https://cdn.datatables.net/buttons/1.6.1/js/dataTables.buttons.min.js"></script>
  <script src="https://cdn.datatables.net/buttons/1.6.1/js/buttons.flash.min.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/pdfmake.min.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/vfs_fonts.js"></script>
  <script src="https://cdn.datatables.net/buttons/1.6.1/js/buttons.html5.min.js"></script>
  <script src="https://cdn.datatables.net/buttons/1.6.1/js/buttons.print.min.js"></script>

</head>

<body>

<div style="margin: 20px;">

<table id="example" class="display nowrap dataTable cell-border" style="width:100%">
        <thead>
            <tr>
                <th>Name</th>
                <th>Position</th>
                <th>Office</th>
                <th>Age</th>
                <th>Start date</th>
                <th>Salary</th>
            </tr>
        </thead>
        <tbody>
            <tr>
                <td>Adélaïde Nixon</td>
                <td>System Architect</td>
                <td>Edinburgh</td>
                <td>6123456789012345</td>
                <td>2011/04/25</td>
                <td>$320,800</td>
            </tr>
        </tbody>
        <tfoot>
            <tr>
                <th>Name</th>
                <th>Position</th>
                <th>Office</th>
                <th>Age</th>
                <th>Start date</th>
                <th>Salary</th>
            </tr>
        </tfoot>
    </table>

</div>

<script type="text/javascript">

  $(document).ready(function() {
    $('#example').DataTable({

      dom: 'Bfrtip',
      "buttons": [
        'excel'
      ]
    });
  });

</script>

</body>

Note on the CSV Option

If you do use "csv" instead of "excel" in your button definition, and if you open the resulting file in a text editor, instead of Excel, you will see this data:

"Name","Position","Office","Age","Start date","Salary"
"Adélaïde Nixon","System Architect","Edinburgh","6123456789012345","2011/04/25","$320,800"

The data is the way you need it to be - it's just that Excel will make various assumptions about how to format the data when opening the csv file.

Find elsewhere

Google Bing Mojeek

JSFiddle

jsfiddle.net › opchggs3 › 3

jquery datatable export table to PDF/Excel demo - JSFiddle - Code Playground

JSFiddle - Test your JavaScript, CSS, HTML or CoffeeScript online with JSFiddle.

CodePen

codepen.io › RedJokingInn › pen › oYJbMP

DataTables example of styling Excel export

<div class="bg-success"> <button class="btn btn-success">Styled Excel</button> <span class="bt-text">Click button to replace the built-in cell styles with a new stylesheet, and export the table to Excel.</span> </div> <table id="example" ...

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 don't understand why you would want that when DataTables is already available as a jQuery plugin? ... I don't want to create any plugin. The function createDataTable is already there in the code as mentioned in my first post. Now I want to modify this main function createDataTable to take columns and adjust the logic accordingly on pages that uses it. Below is the code snippet for the export to excel button.

Stack Overflow

stackoverflow.com › questions › 51779689 › table-cells-format-when-exporting-jquery-datatable-to-excel

Table cells format when exporting jQuery Datatable to Excel

Top answer

1 of 1

You could apply the following code to accomplish your task:

$('#example').DataTable( {
    dom: 'Bfrtip',
    columns: [
        { data: 'name' },
        { data: 'surname' },
        { data: 'position' },
        { data: 'office' },
        { data: 'salary' }
    ],
    buttons: [
        {
            extend: 'excelHtml5',
            customize: function(xlsx) {
                //Get the built-in styles
                //refer buttons.html5.js "xl/styles.xml" for the XML structure
                var styles = xlsx.xl['styles.xml'];

                //Create our own style to use the "Text" number format with id: 49
                var style = '<xf numFmtId="49" fontId="0" fillId="0" borderId="0" xfId="0" applyFont="1" applyFill="1" applyBorder="1" applyNumberFormat="1"/>';
                // Add new node and update counter
                el = $('cellXfs', styles);
                el.append(style).attr('count', parseInt(el.attr('count'))+1);
                // Index of our new style
                var styleIdx = $('xf', el).length - 1;

                //Apply new style to the first (A) column
                var sheet = xlsx.xl.worksheets['sheet1.xml'];
                //Set new style default for the column (optional)
                $('col:eq(0)', sheet).attr('style', styleIdx);
                //Apply new style to the existing rows of the first column ('A')
                //Skipping the header row
                $('row:gt(0) c[r^="A"]', sheet).attr('s', styleIdx);
            },
            exportOptions: {
                format: {
                    body: function(data, row, column, node) {
                        return column === 0 ? "\0" + data : data;
                    }
                }
            },
        },
    ]
} );

Here is JSFiddle

In this example we work with column 0 ('A').
For your column 13, use this code:

                //Set new style default for the column (optional)
                $('col:eq(13)', sheet).attr('style', styleIdx);
                //Apply new style to the existing rows of the 13th column ('N')
                //Skipping the header row
                $('row:gt(0) c[r^="N"]', sheet).attr('s', styleIdx);

Update Apr 4th, 2019

Added an additional trick in the code above to ensure very large numbers are still being treated as text:

exportOptions: {
    format: {
        body: function(data, row, column, node) {
            return column === 0 ? "\0" + data : data;
        }
    }
},

JSFiddle was updated

CodePen

codepen.io › yashwant › pen › MbjvzJ

DataTable - Export To Excel

$(document).ready( function() { $('#example').DataTable( { dom: 'Bfrtip', buttons: [ { extend: 'excelHtml5', customize: function( xlsx ) { var sheet = xlsx.xl.worksheets['sheet1.xml']; $('row c[r^="C"]', sheet).attr( 's', '2' ); } } ] } ); } );

DataTables

datatables.net › forums › discussion › 64791 › jquery-datatable-export-to-excel-with-cell-background-color

jQuery datatable export to excel with cell() background color — DataTables forums

A loop is created to loop all the td cells in each row. If there is a background color it is set using the selector $('c[r^="' + excelMap[td] + '"]', row). excelMap[td] maps the column number to the Excel letter.

Stack Overflow

stackoverflow.com › questions › 64216415 › jquery-datatable-export-to-excel-with-celltd-background-color

jQuery datatable export to excel with cell(<td>) background color - Stack Overflow

This approach automates the creation of new Excel styles - one of which can be used to recreate HTML table cells with red backgrounds. ... In my case, I chose to make the Excel font white for those cells with a red background, just for demonstration purposes, but a small change would restore the original black. ... $(document).ready(function() { var table = $('#example').DataTable( { dom: "Brftip", buttons: [{ extend: 'excelHtml5', customize: function(xlsx) { // set up new styles: let styles = xlsx.xl['styles.xml']; let stylesCount = parseInt($( 'cellXfs', styles ).attr("count"), 10); addCellC

Stack Overflow

stackoverflow.com › questions › 61313581 › jquery-datatable-export-to-excel-customization-make-first-row-bold

jQuery datatable export to Excel customization - Make first row bold - Stack Overflow

Top answer

1 of 1

Assuming you have the buttons extension installed, then the following will print a custom title in the first row of the spreadsheet, and it will make that title bold:

$(document).ready(function() {

  $('#myTable').DataTable( {
    dom: 'Bfrtip',
    buttons: [
        {
          extend: 'excel',
          customize: function ( xlsx ) {
            var sheet = xlsx.xl.worksheets['sheet1.xml'];
            $('c[r=A1] t', sheet).text( 'Custom Heading in First Row' );
            $('row:first c', sheet).attr( 's', '2' ); // first row is bold
          }
        }
    ]
  } );

});

Example:

Regarding the pre-defined formats you mentioned, you can see a list of them here.

0 - Normal text
1 - White text
2 - Bold
3 - Italic
4 - Underline
etc...

The custom commands to manipulate the exported data are also summarized in the same page. But you would need to refer to SheetJS for more details, I think.

Stack Overflow

stackoverflow.com › questions › 50496386 › jquery-datatables-exporting-data-to-excel

jQuery datatables exporting data to Excel

Top answer

1 of 2

I think the best thing to do is to remove the paging, then do the export, then turn the paging back on once it's done.

I made a couple minor changes:

$(function () 
{
    var table = $('#example').DataTable();

    $("#btnExport").click(function(e) 
    {
        table.page.len( -1 ).draw();
        window.open('data:application/vnd.ms-excel,' + 
            encodeURIComponent($('#example').parent().html()));
      setTimeout(function(){
        table.page.len(10).draw();
      }, 1000)

    });
});

Updated fiddle: http://jsfiddle.net/jzdjdo3z/176/

Page Length docs: https://datatables.net/reference/api/page.len()

Paging option docs: https://datatables.net/reference/option/paging

I'm not sure why initializing with dataTables vs DataTables made a difference, but it did. So keep an eye out for that.

2 of 2

If you want download as .xlsx format, use following code. Here you can define the name of the file also. Scripts:

<script src="https://unpkg.com/xlsx/dist/shim.min.js"></script>
<script src="https://unpkg.com/xlsx/dist/xlsx.full.min.js"></script>
<script src="https://unpkg.com/[email protected]/Blob.js"></script>
<script src="https://unpkg.com/[email protected]/FileSaver.js"></script>

Javacript:

var table = $('#example').DataTable();
 $("#btnExport").click(function(e) 
{
   var fileName = "test";
   var fileType = "xlsx";
   var table_obj = document.getElementById("example");
   var wb = XLSX.utils.table_to_book(table_obj, {sheet: "Sheet JS"});
   return XLSX.writeFile(wb, null || fileName + "." + (fileType || "xlsx"));
});

But issue is, you can download first page only. If any one found to full table, update here. Thanks in Advance.

Northcoder

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

Exporting to Excel from DataTables | northCoder

May 1, 2021 - The styles.xml file contains information relating to fonts, borders, fills, alignment, and so on. These can then be re-used in different locations. ... 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.

Stack Overflow

stackoverflow.com › questions › 50105700 › jquery-datatable-export-excel-cannot-apply-built-in-style-to-empty-cell › 50145382

JQuery DataTable Export Excel cannot apply Built in style to empty cell - Stack Overflow

Top answer

1 of 2

Use "createEmptyCells" : true,

Example { extend: 'excelHtml5', text: 'Export to Excel', "createEmptyCells" : true, exportOptions: { title:null, orthogonal: 'export' } },

2 of 2

To style the third cell for each row excluding the header, you need this selector:

$('row[r!=1] c:nth-child(3)', sheet).attr('s', '20');

The styling will apply even the cell is empty. Check this example I created.

DataTables

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

DataTables example - Format output data - export options

npm

npmjs.com › package › datatables-buttons-excel-styles

datatables-buttons-excel-styles - npm

September 15, 2021 - Latest version: 1.2.0, last published: 4 years ago. Start using datatables-buttons-excel-styles in your project by running `npm i datatables-buttons-excel-styles`. There are no other projects in the npm registry using ...

      » npm install datatables-buttons-excel-styles

Published Sep 15, 2021

Version 1.2.0

Author Paul Jones

Repository https://github.com/pjjonesnz/datatables-buttons-excel-styles

Homepage https://github.com/pjjonesnz/datatables-buttons-excel-styles#readme