Styling your spreadsheet

For styling your spreadsheet, there are two functions managing the styles for your Excel document. The styles are given names that can be referenced when you insert values in the cells to style the cell in a certain way.

The JSON style object: Use the function JSON to create the object. The JSON function takes a comma-separated list of key/value pairs, where all odd parameter numbers are the keys, and the even parameter numbers are the values. For the JSON parameters, the datatype of the parameter must be JSON, not a string containing a JSON.

Example: 

Excel_AddStyle ( wb, "ColumnTitles", JSON ("Font", "Arial", "FontSize", 14, "Decoration", "Bold"));

The table below describes the available options.

Style Options

Option names in the table below are case sensitive.

Option Name Type Description Example
Font string Specifies the font name for the style. "Arial"
FontSize int Specifies the font size. 12
Decoration string Specifies text decorations, such as Bold or Italic. Multiple decorations can be comma-separated. "Bold, Italic"
Color string Font color in RGB or ARGB format. Defaults to 000000 (black) or FFFFFF (white) depending on FillColor. It can also use color names from this list:
black=000000, white=FFFFFF, red=FF0000, green=00FF00, blue=0000FF, yellow=FFFF00, cyan=00FFFF, magenta=FF00FF, gray=808080, grey=808080, silver=C0C0C0, maroon=800000, olive=808000, lime=00FF00, teal=008080, navy=000080, purple=800080, orange=FFA500		 "FF0000FF"
FillColor string Background fill color in ARGB format. Adjusts Color automatically for contrast if not specified. "FFFFE0"
FillPattern string Specifies the fill pattern. The default is "solid". "solid"
NumFormat string Specifies the number format for the cell. "### ##0.00"
NumFormatCode int Specifies the number format for the cell, according to the Excel internal number format code table described on this page 7
Alignment string Specifies horizontal and/or vertical alignment. Supported values: Left, Center, Right, Top, Middle, Bottom. Comma-separated for combined alignment. Case-insensitive. "Center, Top"
TextWrap bool or int Enables or disables text wrapping. Accepts true, false, 1, or 0. Defaults to true if invalid. true
Borders object Specifies border styles and colors. Can include Top, Bottom, Left, Right, Diagonal, and their corresponding colors (TopColor, etc.). See detailed example below.

Constants for Line Types in ACF Language

The values for linetype are case-sensitive. To make it easier, we have made constants for them that are not case sensitive. Using those constants below instead of the translated value works with syntax-coloring in TextMate too.

Constant Name Translated Value Description
linetype_hair hair Smallest thickness.
linetype_dotted dotted Dotted line.
linetype_dashdot dashDot Alternating dash and dot.
linetype_dashdotdot dashDotDot Dash followed by two dots.
linetype_dashed dashed Dashed line.
linetype_double double Double line.
linetype_medium medium Medium thickness.
linetype_mediumdashed mediumDashed Medium thickness, dashed.
linetype_mediumdashdot mediumDashDot Medium thickness, dash-dot.
linetype_mediumdashdotdot mediumDashDotDot Medium thickness, dash-dot-dot.
linetype_slantdashdot slantDashDot Slanted dash-dot.
linetype_thick thick Thick line.
linetype_thin thin Thin line (default for many cases).

Example Usage in ACF

When using these constants in the ACF language, they are referenced without quotes. The compiler translates them to the appropriate string values.

Example ACF Script:

Excel_AddStyle(x, "headerStyle", JSON(
    "Borders", JSON(
        "Top", linetype_thick,
        "Bottom", linetype_double,
        "Left", linetype_dashdot,
        "Right", linetype_dotted
    )
));

Resulting JSON for the headerStyle:

{
    "Borders": {
        "Top": "thick",
        "Bottom": "double",
        "Left": "dashDot",
        "Right": "dotted"
    }
}

Colors, color names, RGB and ARGB

The color parameters accept both color names, RGB hexadecimal values, and ARGB hexadecimal values. RGB is either a 3 or 6 digit hex value specified as a string, using "#" in front. The ARGB includes an alpha value, making it 8-digit. The valid color names are:

Color RGB (hex)
black 000000
blue 0000FF
cyan 00FFFF
gray 808080
green 00FF00
grey 808080
lime 00FF00
magenta FF00FF
maroon 800000
navy 000080
olive 808000
orange FFA500
purple 800080
red FF0000
silver C0C0C0
teal 008080
white FFFFFF
yellow FFFF00

Border Configuration Example

The Borders option is a nested JSON object. Here’s an example configuration:

JSON myBorders = 
'{
    "Borders": {
        "Top": "thin",
        "TopColor": "0000FF",
        "Bottom": "thick",
        "Left": "dotted",
        "Right": "dashed",
        "Diagonal": "double",
        "DiagonalColor": "FF0000"
    }
}';

Corresponding Updates:

Intersecting borders

If your spreadsheet contains intersecting borders, you don't need to define a separate style for the intersecting cell. Let's say you have a column with data values, and it has a left border. Then below the table, you have a sum line with top and bottom borders, bold text, etc. Let's say the sum line has the style "SumLine", and the table values have "LeftLine". Using "SumLine,LeftLine" for the intersecting cell will produce a new style cloned from the first and add missing borders from the second.

It is only the border elements that are merged in this way, as we assume that the first style has the other elements already in place.

Alignment Example

If you want both horizontal and vertical alignment:

{
    "Alignment": "Left, Top"
}

This will set:

Notes: