how to customize add background color column wise in excel export

abduls

light red for Column A (Payer Name, Address ,Phone ) Column B (Payer License Numbers ) and light green for Column C (Payee Name, Address ,Phone ) Column D (Payee License Numbers ) and brighter red for Column F(Amount) transactions $10,000.00 or more.

colin

These examples here and here show how to change the colour of cells - you should be able to use that as a template,

Colin

pjjonesnz

You might like to check out the Excel Styles plugin that Allan has recently linked at the bottom of the front page of the Datatables website. It adds a new option when exporting excel spreadsheets to allow you to easily style them.

It also has easy conditional cell formatting available.

After including the plugin js on your site, add the following excelStyles object to your button to do what you have asked.

Note: I've added this example to the demo site - the very last example on the dropdown list. (with the only difference being the targeting of numbers over 100,000.)

https://www.pauljones.co.nz/github/buttons-html5-styles/examples/simple_table_style.html

excelStyles: [
    {
        cells: "sA:B",  // Smart select Columns A and B
        style: {
            fill: {
                pattern: {
                    color: 'FCDCDC', // Light red color
                }
            }
        }
    },
    {
        cells: "sC:D",  // Smart select Columns C and D
        style: {
            fill: {
                pattern: {
                    color: 'E2EFDA', // Light green color
                }
            }
        }
    },
    {
        cells: "sF",   // Smart select Column F
        condition: {                         // Add conditional formatting
            type: "cellIs",                  // The value of the cell is
            operator: "greaterThanOrEqual",  // greater than or equal to
            formula: 10000,                 // this amount
        },
        style: {
            fill: {
                pattern: {
                    bgColor: "F78989" // Brighter red (Note the Excel gotcha - requires bgColor for conditional formatting)
                }
            }
        }
    },
]

chbocca

@pjjonesnz. Thank you! Love the new plug in.

When targeting columns, I see styling options for single columns or a contiguous range of columns, but can you target multiple columns throughout the spreadsheet?

Something like this, targeting say columns D, G, and T:

                    "excelStyles": [
                        {
                            "cells": "sD,sG,sT",
                            "condition": {
                                "type": "cellIs",
                                "operator": "greaterThan",
                                "formula": 100000
                            },
                            "style": {
                                "fill": {
                                    "pattern": {
                                        "bgColor": "CCFFCC"
                                    }
                                }
                            }
                        }
                    ]

chbocca

@colin. I'm getting the 404 error on your example links above ...

https://live.datatables.net/jijumeji/1/edit

https://live.datatables.net/golezopo/1/edit

Not sure if this is deliberate ... or, just users that have closed accounts/moved files.

kthorngren

They work. I just tried both. I’ve seen other posts recently about 404 errors. I think it was either a browser or firewall issue. Try clearing your browser cache or a different browser.

Kevin

chbocca

@kthorngren.

Thank you Kevin!

Here's demo. I can get the <b>C:D</b> columns styled, but not <b>C & H</b> using the syntax above.

Any suggestions?

Also, I did try clearing cache, but the 404 errors remained for Colin's links in this page. But, when I used Opera instead of Firefox, success. Thank you!

kthorngren

Looks like you are using the plugin posted above by pjjonesnz. It would be best to contact the developer of the plugin directly for help.

Kevin

chbocca

Thank you Kevin. Will do! c

pjjonesnz

Hey @chbocca
You can apply a style to different ranges by using an array instead of a string.

So instead of

"cells": "sD,sG,sT",

use

"cells": ["sD","sG","sT"],

Hope that helps

chbocca

Beautiful! Love this new plugin!!!! Thank you Paul!

Makes the world a better place.

Have updated the demo.

c

pjjonesnz

Excellent - so pleased it worked for you.

Just for reference, you can also mix the two ways of selecting cells with your two columns in your demo being contiguous, like this:

"cells": ["sC:D","sH"],

In this case it will give exactly the same result, but just so you know you can do this.

Here is a link to the demo site - you can also edit the examples there to test things out.

chbocca

Lovely!