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

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

Attribute	Description	Type	Default
cells	The cell or cell range that the style is being applied to.	String or Array of (Cell References)
rowref	Enables smart row references if set to "smart"	Enum ( false \| "smart" )	false
style	The style definition	Style Object
template	A template name	String
index	Built-in style index number	Integer
merge	Merge this style with the existing cell style	Boolean	true
width	Set the column width	Double
height	Set the row height	Double
condition	Condition to match for conditional formatting	Condition Object

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.

Attribute	Description	Type
font	To style the font used in a cell	Font Object
border	The border of the cell	Border Object
fill	To style the cell fill (ie. the cell background color and pattern)	Fill Object
numFmt	Apply a number format (eg. define currency display, decimal places, etc.)	NumFmt String
alignment	Horizontal and vertical alignment of the cell content	Alignment 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

Attribute	Meaning	Type	Example	Aliases
b	Bold	Boolean	`bold: true`	strong bold
color	Color	String (RGB or ARGB) or Color Object	`color: "FF0000"` `color: { rgb: "FF0000", tint: 0.54 }`
family	Font family	Integer	`family: 1`
i	Italic	Boolean	`i: true`	italic
name	Font name	String	`name: "Arial"`
strike	Strike through	Boolean	`strike: true`
sz	Font size (pt)	Double	`sz: 14`	size
u	Underline	Boolean or String	`u: true` (single underline) `u: "singleAccounting"` `u: "double"` `u: "doubleAccounting"`	underline
vertAlign	Subscript Superscript	String	`vertAlign: "subscript"` `vertAlign: "superscript"`

Color Object

Attribute	Meaning	Type	Example	Default
rgb	Hex RGB or ARGB color value	String	`rgb: "0C96FD"` `rgb: "800C96FD"`
tint	The tint value applied to the color	Double (-1.0 to 1.0)	`tint: -0.3`	0.0

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

Attribute	Meaning	Type	Example
top bottom left right diagonal	Border position	String (Border Style) Border Style Object	`top: "thin"` `bottom: { style: "dashed", color: "A9D08E" }`

Border Style Object

Attribute	Meaning	Type	Example
style	The style of the border	Enum (Border Styles String)	`style: "medium"`
color	The border color	String or Color Object	`color: "FF0000"` `color: { rgb: "FF0000", tint: 0.54 }`

Border Styles String

Value	Meaning
dashDot	Dash Dot Pattern
dashDotDot	Dash Dot Dot Pattern
dashed	Dashed Pattern
dotted	Dotted Pattern
double	Double Line Border
hair	Hairline Border
medium	Medium Weight Border
mediumDashDot	Medium Weight Dash Dot Pattern
mediumDashDotDot	Medium Weight Dash Dot Dot Pattern
mediumDashed	Medium Weight Dashed Pattern
slantDashDot	Slant Dash Dot Pattern
thick	Thick Weight Border
thin	Thin Weight Border

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

Attribute	Meaning	Type	Aliases
pattern	Pattern Fill	Pattern Object	patternFill
gradient	Gradient Fill	Gradient Object	gradientFill

Pattern Object

Attribute	Meaning	Type	Example	Aliases
type	Type of pattern	Pattern Type String	`type: "lightUp"` Default: `"solid"`
fgColor	Foreground color	String or Color Object	`fgColor: "FF0000"` `fgColor: { rgb: "FF0000", tint: 0.54 }`	color
bgColor	Background color	String or Color Object	`bgColor: "FF0000"` `bgColor: { rgb: "FF0000", tint: 0.54 }`

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.

Value	Meaning
darkDown	Dark Down
darkGray	Dark Gray
darkGrid	Dark Grid
darkHorizontal	Dark Horizontal
darkTrellis	Dark Trellis
darkUp	Dark Up
darkVertical	Dark Vertical
gray0625	Gray 0.0625
gray125	Gray 0.125
lightDown	Light Down
lightGray	Light Gray
lightGrid	Light Grid
lightHorizontal	Light Horizontal
lightTrellis	Light Trellis
lightUp	Light Up
lightVertical	Light Vertical
mediumGray	Medium Gray
solid	Solid

Gradient Object

Attribute	Meaning	Type	Example
type	Gradient fill type	Enum ( `linear` \| `path` )	`type: "linear"` `type: "path"`
degree	Angle of the gradient for linear gradients	Integer	`degree: "270"`
left right top bottom	Edge position percentage of the inner rectangle for path gradients	Double (0.0 - 1.0)	`left: "0.3"`
stop	Array of two or more gradient stops	Array of Stop Objects	`stop: [{ position: "0", color: "#FF0000"}, ..., ...]`

Stop Object

Attribute	Meaning	Type	Example
position	Position percentage	Double (0.0 to 1.0)	`position: "0"` `position: "1"`
color	Color	String or Color Object	`fgColor: "FF0000"` `fgColor: { rgb: "FF0000", tint: 0.54 }`

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:

Open Excel
Modify a cell to format the number in the way you would like it
View the 'Format cells...' dialog
Select the 'Number' tab
Click 'Custom' in the category list
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

Attribute	Meaning	Type	Info
horizontal	Horizontal Alignment	Horizontal Alignment Enum
indent	Indent	Integer	value is multiplied by 3 text spaces
readingOrder	Reading Order	Integer	0 - Context Dependent 1 - Left-to-Right 2 - Right-to-Left
shrinkToFit	Shrink To Fit	Boolean	Should text be shrunk to fit cell width
textRotation	Text Rotation	Unsigned Integer (0 - 180)	Degrees to rotate text
vertical	Vertical Alignment	Vertical Alignment Enum
wrapText	Word Wrapping	Boolean

Horizontal Alignment Enum

Value	Meaning
center	Centered Horizontal Alignment
centerContinuous	Center Continuous Horizontal Alignment
distributed	Distributed Horizontal Alignment
fill	Fill
general	General Horizontal Alignment
justify	Justify
left	Left Horizontal Alignment
right	Right Horizontal Alignment

Vertical Alignment Enum

Value	Meaning
bottom	Aligned To Bottom
center	Centered Vertical Alignment
distributed	Distributed Vertical Alignment
justify	Justified Vertically
top	Align Top

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

Template Name	Description
black_medium	Complete table: black theme
blue_gray_medium	Complete table: blue-gray theme
blue_medium	Complete table: blue theme
cyan_medium	Complete table: cyan theme
gold_medium	Complete table: gold theme
gray_medium	Complete table: gray theme
green_medium	Complete table: green theme
light_gray_medium	Complete table: light-gray theme
orange_medium	Complete table: orange theme

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

Template Parts

Part Name	Description	Default Cell Reference
Basic Types
b	Bold	`s1:-0` All data rows
u	Underline	`s1:-0` All data rows
i	Italic	`s1:-0` All data rows

Headers and Footers
header_black	Header: black	`['sh', 'sf']` Header and footer
header_blue	Header: blue	`['sh', 'sf']` Header and footer
header_blue_gray	Header: blue_gray	`['sh', 'sf']` Header and footer
header_cyan	Header: cyan	`['sh', 'sf']` Header and footer
header_gold	Header: gold	`['sh', 'sf']` Header and footer
header_gray	Header: gray	`['sh', 'sf']` Header and footer
header_green	Header: green	`['sh', 'sf']` Header and footer
header_light_gray	Header: light_gray	`['sh', 'sf']` Header and footer
header_orange	Header: orange	`['sh', 'sf']` Header and footer

Row Stripes
stripes_black	Stripes: black	`s1:n,2` All columns, every second row of the data rows
stripes_blue	Stripes: blue	`s1:n,2` All columns, every second row of the data rows
stripes_blue_gray	Stripes: blue_gray	`s1:n,2` All columns, every second row of the data rows
stripes_cyan	Stripes: cyan	`s1:n,2` All columns, every second row of the data rows
stripes_gold	Stripes: gold	`s1:n,2` All columns, every second row of the data rows
stripes_gray	Stripes: gray	`s1:n,2` All columns, every second row of the data rows
stripes_green	Stripes: green	`s1:n,2` All columns, every second row of the data rows
stripes_light_gray	Stripes: light_gray	`s1:n,2` All columns, every second row of the data rows
stripes_orange	Stripes: orange	`s1:n,2` All columns, every second row of the data rows

Row Borders Top & Bottom
rowlines_black	Rowlines: black	`sh:f` All columns, every row from the header to the footer
rowlines_blue	Rowlines: blue	`sh:f` All columns, every row from the header to the footer
rowlines_blue_gray	Rowlines: blue_gray	`sh:f` All columns, every row from the header to the footer
rowlines_cyan	Rowlines: cyan	`sh:f` All columns, every row from the header to the footer
rowlines_gold	Rowlines: gold	`sh:f` All columns, every row from the header to the footer
rowlines_gray	Rowlines: gray	`sh:f` All columns, every row from the header to the footer
rowlines_green	Rowlines: green	`sh:f` All columns, every row from the header to the footer
rowlines_light_gray	Rowlines: light_gray	`sh:f` All columns, every row from the header to the footer
rowlines_orange	Rowlines: orange	`sh:f` All columns, every row from the header to the footer

Table Outline
outline_black	Outline: black
outline_blue	Outline: blue
outline_blue_gray	Outline: blue_gray
outline_cyan	Outline: cyan
outline_gold	Outline: gold
outline_gray	Outline: gray
outline_green	Outline: green
outline_light_gray	Outline: light_gray
outline_orange	Outline: orange

Currency Format
currency_us	US currency number format
currency_eu	Euro currency number format
currency_gb	GB Pound currency number format

Date Format
date_long	Date: Long format - eg. 24 September 1979
date_medium	Date: Medium format - eg. 4 Dec 1987

Number Format
int	Integer number format
decimal_1	Number format - one decimal place, negatives in brackets
decimal_2	Number format - two decimal places, negatives in brackets
decimal_3	Number format - three decimal places, negatives in brackets
decimal_4	Number format - four decimal places, negatives in brackets

Conditional Styles

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

Condition Object

Attribute	Description	Type	Default
type	The type of conditional formatting rule.	Type Enum
operator	When type is 'cellIs', sets the comparison type. Don't use with expression.	Operator Enum
formula	The data to compare	Number/String Array (for between or notBetween operators)
priority	The priority of this conditional formatting rule	Number	1

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

Value	Meaning
cellIs	Compares a cell value using an operator
expression	Evaluate a formula
colorScale	Creates a gradated color scale on the cells
dataBar	Displays a gradated data bar in the range of cells
iconSet	Applies icons to cells according to their values

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

Operator Enum

Value	Meaning
between	Value is between the two numbers in the formula array
equal	Value is equal to the number in the formula
greaterThan	Value is greater than the number in the formula
greaterThanOrEqual	Value is greater than or equal to the number in the formula
lessThan	Value is less than the number in the formula
lessThanOrEqual	Value is less than or equal to the number in the formula
notBetween	Value is NOT between the two numbers in the formula array
notEqual	Value is NOT equal to the number in the formula

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

Name		Name	Last commit message	Last commit date
Latest commit History 133 Commits
docs		docs
examples		examples
js		js
CHANGELOG.md		CHANGELOG.md
LICENSE.md		LICENSE.md
README.md		README.md
_config.yml		_config.yml
package.json		package.json

License

pjjonesnz/datatables-buttons-excel-styles

Folders and files

Latest commit

History

Repository files navigation

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

Type Enum

Operator Enum

License

Contributing

Thanks

Table of Contents

About

Resources

License

Stars

Watchers

Forks

Languages