datatables-buttons-excel-styles v1.2.0

DataTables Buttons Excel Styling

Add beautifully styled Excel output to your DataTables

• Style the font, border, background, number-format of your table
• Target cells, columns, rows, headers, footers, title, message and messageBottom
• Target cell ranges using familiar Excel cell references
• Conditional formatting
• Insert or replace cells, columns and rows
• Set the default page printing options using a pageStyle config option
• Easy JSON configuration options
• Simple templates built in for fast styling
• Currency formatting, fix the standard US$ display
• Smart targeting of rows useful for styling a spreadsheet that sometimes has message, header, etc. turned off

DataTables is an amazing tool to display your tables in a user friendly way, and the Buttons extension makes downloading those tables a breeze.

Now you can easily style the Excel download of your DataTable without having to learn the intricacies of Office Open XML using either:

• Custom Styles - Your own custom defined font, border, background, number format and alignment styles, or
• Pre-defined Templates - A selection of templates to apply to your entire table or selected cells

Table of Contents

Demo

View the live Excel style demo containing lots of examples of how to style your Excel sheet

Installing

1. If you don't already have the 'Excel' download button running on your DataTable, make sure you add the Buttons Extension and JSZip to your page. Download from DataTables.net

2. Include the javascript files for this plugin from the following cdn, or download from git or npm and add the scripts in the 'js/' folder to your page.

<script src="https://cdn.jsdelivr.net/npm/datatables-buttons-excel-styles@1.2.0/js/buttons.html5.styles.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/datatables-buttons-excel-styles@1.2.0/js/buttons.html5.styles.templates.min.js"></script>

Usage

This plugin adds a new option named excelStyles to the DataTables Buttons configuration.

This option will contain your style which consists of either a single Excel Style Object or an array of Excel Style Objects to be applied to your table.

Style Example

With a custom Style Object you can customize your spreadsheet to look exactly as you'd like it to. Either use familiar Excel cell references or take advantage of the Cell Reference definitions available, to target specific parts of your worksheet.

See this example live

$("#myTable").DataTable({
    dom: "Bfrtip",
    buttons: [
        {
            extend: "excel",                    // Extend the excel button
            excelStyles: {                      // Add an excelStyles definition
                cells: "2",                     // to row 2
                style: {                        // The style block
                    font: {                     // Style the font
                        name: "Arial",          // Font name
                        size: "14",             // Font size
                        color: "FFFFFF",        // Font Color
                        b: false,               // Remove bolding from header row
                    },
                    fill: {                     // Style the cell fill (background)
                        pattern: {              // Type of fill (pattern or gradient)
                            color: "457B9D",    // Fill color
                        }
                    }
                }
            },
        },
    ],
});

Template Example

Pre-defined templates are a quick option for a nice output.

See this example live

$("#myTable").DataTable({
    dom: "Bfrtip",
    buttons: [
        {
            extend: "excel",              // Extend the excel button
            excelStyles: {                // Add an excelStyles definition
                template: "blue_medium",  // Apply the 'blue_medium' template
            },
        },
    ],
});

Styles and Templates Combined

You can easily combine the two. Start with a nice design and then make it yours!

See this example live

$("#myTable").DataTable({
    dom: "Bfrtip",
    buttons: [
        {
            extend: "excel",                    // Extend the excel button
            excelStyles: [                      // Add an excelStyles definition
                {                 
                    template: "green_medium",   // Apply the "green_medium" template
                },
                {
                    cells: "sh",                // Use Smart References (s) to target the header row (h)
                    style: {                    // The style definition
                        font: {                 // Style the font
                            size: 14,           // Size 14
                            b: false,           // Turn off the default bolding of the header row
                        },
                        fill: {                 // Style the cell fill
                            pattern: {          // Add a pattern (default is solid)
                                color: "1C3144" // Define the fill color
                            }
                        }
                    }
                }
            ]           
        },
    ],
});

Built-in Styles

Built-in styles can also be used. See the DataTables built-in style reference for pre-defined style definitions.

$("#myTable").DataTable({
    dom: "Bfrtip",
    buttons: [
        {
            extend: "excel",    // Extend the excel button
            excelStyles: {      // Add an excelStyles definition
                cells: "sh",    // Use Smart References (s) to target the header row (h)
                index: 12,      // Apply the built-in style #12 which gives the cells a red background
            },
        },
    ],
});

Conditional Formatting

You can apply Conditional Styles to cells. Not all Excel conditional formatting is currently supported, but the most common conditional number formatting is as well as formulas which cover most other use cases. Note that you can only apply custom styles (ie. not templates or built-in styles).

ColorScale, DataBar and IconSets are also supported. See the test site for examples.

The major benefit to using this method (as opposed to writing your own customize method to add fixed styles to cells) is that the applied styles automatically update when you make changes to your data inside of Excel AFTER opening the sheet.

See this example live

$("#myTable").DataTable({
    dom: "Bfrtip",
    buttons: [
        {
            extend: "excel",                        // Extend the excel button
            excelStyles: {                          // Add an excelStyles definition
                cells: "sF",                        // (s) Smart row reference, All data rows in column F
                condition: {                        // Add this style conditionally
                    type: 'cellIs',                 // Use the 'cellIs' condition type
                    operator: 'between',            // Use the 'between' operator
                    formula: [150000,200000],   // Add the two numbers to match between
                },
                style: {                            // The style block
                    font: {
                        bold: true,
                    },
                    fill: {
                        pattern: {
                            bgColor: "457B9D",      // NOTE: An excel quirk is that conditional solid fills need 
                                                    // the bgColor set, not the fgColor as for normal fills. 
                        }
                    }
                }
            },
        },
    ],
});

Replace or insert cells, columns and rows

This isn't fully documented yet, but is very clear from the example below. Please see the demo site for further examples, but here are the basics.

Inserting rows demo

$("#myTable").DataTable({
    dom: "Bfrtip",
    buttons: [
        {
            extend: "excel",                // Extend the excel button
            insertCells: [                  // Add an insertCells config option 
                {
                    cells: 'sCh',               // Target the header with smart selection
                    content: 'New column C',    // New content for the cells
                    pushCol: true,              // pushCol causes the column to be inserted
                },
                {
                    cells: 'sC1:C-0',           // Target the data
                    content: '',                // Add empty content
                    pushCol: true               // push the columns to the right over one
                },
                {
                    cells: 's5:6',              // Target data row 5 and 6
                    content: '',                // Add empty content
                    pushRow: true               // push the rows down to insert the content
                },
                {
                    cells: 'B3',                // Target cell B3
                    content: 'THIS IS CELL B3', // without pushCol or pushRow defined, the cell
                                                // is overwritten
                }
            ],
            excelStyles: {
                template: 'cyan_medium',    // Add a template to the result
            }
        },
    ],
});

Printer defaults

Printer default settings can be defined using the pageStyle option. I haven't completed a full list of options yet, but it's pretty clear from the example below.

The 'repeatHeading' option can be set to true to repeat the heading at the top of each printed page, or can also be a cell reference - note that only the row part of the reference is used (eg. repeatHeading: 'st:h' will repeat the title and heading row on each printed page.) The 'repeatCol' option repeats columns when the table width spans multiple pages.

Printer defaults demo

$("#myTable").DataTable({
    dom: "Bfrtip",
    buttons: [
        {
            extend: "excel",                // Extend the excel button
            pageStyle: {
                sheetPr: {
                    pageSetUpPr: {
                        fitToPage: 1            // Fit the printing to the page
                    } 
                },
                printOptions: {
                    horizontalCentered: true,
                    verticalCentered: true,
                },
                pageSetup: {
                    orientation: "landscape",   // Orientation
                    paperSize: "9",             // Paper size (1 = Letter, 9 = A4)
                    fitToWidth: "1",            // Fit to page width
                    fitToHeight: "0",           // Fit to page height
                },
                pageMargins: {
                    left: "0.2",
                    right: "0.2",
                    top: "0.4",
                    bottom: "0.4",
                    header: "0",
                    footer: "0",
                },
                repeatHeading: true,    // Repeat the heading row at the top of each page
                repeatCol: 'A:A',       // Repeat column A (for pages wider than a single printed page)
            },
            excelStyles: {
                template: 'blue_gray_medium',    // Add a template style as well if you like
            }
        },
    ],
});

pageSetup options can be found here

Applying your Styles

In most cases your styles will be automatically applied by this plugin, but please consider the following.

Please Note: This plugin hooks in using the customize option in the DataTables Buttons configuration to automatically run when you click on the Excel button. If you are already using the customize method to apply other table formatting or modification, you can run this plugin by calling applyStyles from within your customize method as follows:

$("#myTable").DataTable({
    dom: "Bfrtip",
    buttons: [
        {
            extend: "excel",
            excelStyles: {
                // ... custom Excel Style Objects defined ...
            },
            customize: function(xlsx) {
                // ... your custom code here ...

                // Apply the excelStyles
                this.applyStyles(xlsx); 
            }
        }
    ]
});

Excel Style Object

The excelStyles DataTables Buttons option is added as a configuration item for the DataTables Buttons object. It contains either a single Excel Style Object or an array of Excel Style Objects.

Cell Reference

Use familiar Excel cell references to select a specific cell or cell range.

The Cell Reference can be a single string, or an array of references if you wish to apply the style to a range of cells in different locations (eg. applying the same style to the header and the footer)

View this page for a complete list of all cell reference options

Standard references

• A2 - Select cell A2
• C17 - Select cell C17
• B3:D20 - Select the range from cell B3 to cell D20

Extended references are used to select individual rows and columns, or row/column ranges:

• 4 - All cells in row 4
• B - All cells in column B
• 3:7 - All cells from (and including) row 3 to row 7
• 3: - All cells from row 3 to the end of the table
• > - The last column in the table
• -0 - The last row in the table
• -2 - The third to last row in the table
• and more...

Smart row references can select the various parts of the table (title, header, data, footer, etc.). These are enabled with a s prefix in the cell reference, or with the rowref: "smart" config option:

• sh - The header
• sf - The footer
• s1 - Becomes the first data row
• s-0 - Becomes the last data row
• sB3 - Column B, row 3 of the data rows
• and more...

For examples of using these cell selections please view the demo, or have a look at the templates in buttons.html5.styles.templates.js

Style Object

There are five main properties available within a Style Object.

Font Object

The font style is the simplest and consists of an object with the font attributes listed as key:value pairs inside.

$("#myTable").DataTable({
    dom: "Bfrtip",
    buttons: [
        {
            extend: "excel",
            excelStyles: {
                cells: "A2:",
                style: {

                    // Font Object
                    font: {
                        name: "Arial",
                        size: 18,
                        u: true,          // Single underline
                        color: "D75F41"
                    }
                }
            }
        }
    ]
});

Font Attributes

The commonly used font attributes are listed below. A full list can be found in the Office Open XML Spec

Color Object

Border Object

The border of a cell can be defined by a simple object

$("#myTable").DataTable({
    dom: "Bfrtip",
    buttons: [
        {
            extend: "excel",
            excelStyles: {
                cells: "A2:",
                style: {

                    // Border Object
                    border: {
                        top: "thin",            // Thin black border at top of cell/s
                        bottom: {               // At the bottom of the cell/s apply a
                            style: "thick",     // thick border with
                            color: "A9D08E",    // a lovely hue of green
                        },
                    }
                }
            }
        }
    ]
});

Border Attributes

Border Style Object

Border Styles String

Fill Object

The fill style can either be a pattern or a gradient. While these styles are fully supported by Excel on all devices, many of the advanced pattern and gradient options are not completely supported by other spreadsheet viewers (eg. the default ios viewer)

Solid background color

$("#myTable").DataTable({
    dom: "Bfrtip",
    buttons: [
        {
            extend: "excel",
            excelStyles: {
                cells: "A2:",
                style: {

                    // Solid Fill
                    fill: {
                        pattern: {
                            color: "457B9D",
                        }
                    }
                }
            }
        }
    ]
});

Patterned background

$("#myTable").DataTable({
    dom: "Bfrtip",
    buttons: [
        {
            extend: "excel",
            excelStyles: {
                cells: "A2:",
                style: {

                    // Patterned Fill
                    fill: {
                        pattern: {
                            type: "lightUp",
                            fgColor: "1C3144",
                            bgColor: "C3D898",
                        }
                    }
                }
            }
        }
    ]
});

Gradient background

$("#myTable").DataTable({
    dom: "Bfrtip",
    buttons: [
        {
            extend: "excel",
            excelStyles: {
                cells: "A2:",
                style: {

                    // Gradient Fill
                    fill: {
                        gradient: {
                            degree: 90,
                            stop: [
                                {
                                    position: 0,
                                    color: "000000",
                                },
                                {
                                    position: 1,
                                    color: "CC0000",
                                }
                            ]
                        }
                    }
                }
            }
        }
    ]
});

Fill Attributes

Pattern Object

Pattern Type String

This list along with examples can be found here. Note that support for pattern types is good with Excel but limited with some other viewers.

Gradient Object

Stop Object

NumFmt String

The numFmt attribute is used to apply advanced formatting to cells containing numbers. It consists of a single string with the number formatting code.

$("#myTable").DataTable({
    dom: "Bfrtip",
    buttons: [
        {
            extend: "excel",
            excelStyles: {
                cells: "A2:",
                style: {

                    // NumFmt String
                    numFmt: "#,##0.0000;(#,##0.0000)"
                }
            }
        }
    ]
});

See Microsoft's guide for Number format codes

The easiest way to find a custom code using Excel is as follows:

1. Open Excel
2. Modify a cell to format the number in the way you would like it
3. View the 'Format cells...' dialog
4. Select the 'Number' tab
5. Click 'Custom' in the category list
6. Copy the code from the 'Type' input and use that as your NumFmt String

Alignment Object

The alignment object applies alignment to the content of your cells.

$("#myTable").DataTable({
    dom: "Bfrtip",
    buttons: [
        {
            extend: "excel",
            excelStyles: {
                cells: "A2:",
                style: {

                    // Alignment Object
                    alignment: {
                        vertical: "center",
                        horizontal: "left",
                        wrapText: true,
                    }

                }
            }
        }
    ]
});

Alignment Attributes

Horizontal Alignment Enum

Vertical Alignment Enum

Pre-defined templates

Predefined templates are located in the buttons.html5.styles.templates.min.js javascript file. This file must be included on your page to use the templates.

Templates are as simple to apply as this:

$("#myTable").DataTable({
    dom: "Bfrtip",
    buttons: [
        {
            extend: "excel",
            excelStyles: {                // Add an excelStyles definition
                template: "blue_medium",  // Apply the 'blue_medium' template
            }
        }
    ]
});

Complete templates are made up of template parts that can also be individually applied:

$("#myTable").DataTable({
    dom: "Bfrtip",
    buttons: [
        {
            extend: "excel",
            excelStyles: {                // Add an excelStyles definition
                template: "header_blue",  // Apply the 'header_blue' template part (white font on a blue background in the header/footer)
            }
        }
    ]
});

Multiple templates can be applied by using an array. If a second template target the same cell style as the preceding one, it will overwrite that style.

$("#myTable").DataTable({
    dom: "Bfrtip",
    buttons: [
        {
            extend: "excel",
            excelStyles: {              // Add an excelStyles definition
                template: [             // Apply multiple templates
                    "gold_medium",      // Apply the 'gold_medium' template to the entire table
                    "header_cyan"       // Overwrite the header with the 'header_cyan' style
                ],    
            }
        }
    ]
});
}

Some of the template parts are suitable to apply to a specific cell or cell range. If you don't define cells, the template will be applied to the default range of cells defined within the template itself. If no default range or cell reference is defined, then the template will be skipped.

$("#myTable").DataTable({
    dom: "Bfrtip",
    buttons: [
        {
            extend: "excel",
            excelStyles: {                  // Add an excelStyles definition
                cells: "sD3",               // Column D, row 3 of the data rows
                template: "currency_eu",    // Format the cells with a predefined numFmt 
            }                               // displaying the values as Euro currency
        }
    ]
});

For multiple templates targeting different cell ranges, use an array of Style Objects with the cells and template defined in each object

$("#myTable").DataTable({
    dom: "Bfrtip",
    buttons: [
        {
            extend: "excel",
            excelStyles: [
                {                  
                    cells: "sD",
                    template: "currency_eu",
                },
                {                  
                    cells: "sE",
                    template: "date_long",
                }
            ]
        }
    ]
});

Template List

Full Table Templates

Each of the Full Table Templates above automatically applies the Header & Footer, Row Stripes, Row Borders & Table Outline templates.

Template Parts

Conditional Styles

Conditional formatting causes styles to only be applied to cells if the condition is met.

Condition Object

NOTE: As this feature is still not complete, not all conditional types have been tested.

Presently cellIs and expression have been tested with examples on the test site. The 'expression' type should cover most use cases. ColorScale, dataBar and iconSet formatting also work with examples on the test site.

Also

Example:

$("#myTable").DataTable({
    dom: "Bfrtip",
    buttons: [
        {
            extend: "excel",
            excelStyles: {
                cells: "sF",                    // Smart select Column F

                // Conditional formatting block
                condition: {
                    type: "cellIs",
                    operator: "greaterThan",
                    formula: 150000,
                },
                style: {
                    fill: {
                        pattern: {
                            bgColor: "F78989"   // Brighter red (Note the Excel gotcha requires
                        }                       // bgColor for conditional formatting)
                    }
                }
            }
        }
    ]
});

Using multiple values

$("#myTable").DataTable({
    dom: "Bfrtip",
    buttons: [
        {
            extend: "excel",
            excelStyles: {
                cells: "sF",                    // Smart select Column F
                condition: {
                    type: "cellIs",
                    operator: "notBetween",
                    formula: [150000,200000],
                },
                style: {
                    fill: {
                        pattern: {
                            bgColor: "F78989"   // Brighter red (Note the Excel gotcha requires
                        }                       // bgColor for conditional formatting)
                    }
                }
            }
        }
    ]
});

Using a formula

$("#myTable").DataTable({
    dom: "Bfrtip",
    buttons: [
        {
            extend: "excel",
            excelStyles: {
                cells: "s:",                    // Smart select all data rows - ie. apply to entire row
                condition: {
                    type: "expression",         // Use a forumula
                    formula: "AND($F3>150000,$B3=\"Software Engineer\")",   // The formula - make sure you escape strings and use WITHOUT leading = (equals) sign.  You also can't use smart row references in the formula.  Note the $ in front of the cell reference to lock it to the column.
                },
                style: {
                    fill: {
                        pattern: {
                            bgColor: "F78989"   // Brighter red (Note the Excel gotcha requires
                        }                       // bgColor for conditional formatting)
                    }
                }
            }
        }
    ]
});

Type Enum

Note: See the demo site for examples as colorScale, dataBar and iconSet need specific attributes set.

Operator Enum

License

This plugin is released under the MIT license. You are free to use, modify and distribute this software, as long as the copyright header is left intact.

Contributing

I hope this plugin helps you output beautiful spreadsheets from your DataTables.net enabled tables.

Please let me know if you have any helpful comments or code that you would like to contribute or if this plugin has been helpful.

Thanks

To the DataTables.net team for making jQuery table display so much easier and more beautiful!

To the C-Rex team for their excellent Office Open XML File Format Reference

Table of Contents

• DataTables Buttons Excel Styling
  • Demo
  • Installing
  • Usage
    • Style Example
    • Template Example
    • Styles and Templates Combined
    • Built-in Styles
    • Conditional Formatting
  • Replace or insert cells, columns and rows
  • Printer defaults
  • Applying your Styles
  • Excel Style Object
  • Cell Reference
  • Style Object
  • Font Object
    • Font Attributes
      • Color Object
  • Border Object
    • Border Attributes
      • Border Style Object
      • Border Styles String
  • Fill Object
    • Fill Attributes
      • Pattern Object
      • Pattern Type String
      • Gradient Object
      • Stop Object
  • NumFmt String
  • Alignment Object
    • Alignment Attributes
      • Horizontal Alignment Enum
      • Vertical Alignment Enum
  • Pre-defined templates
  • Template List
    • Full Table Templates
    • Template Parts
  • Conditional Styles
    • Condition Object
      • Operator Enum
  • License
  • Contributing
  • Thanks

Table of contents generated with markdown-toc