Styling your spreadsheet

For styling your spreadsheet there is 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 value. 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 describe the available options.

Style Options

Option Names in the table below is 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 colour in RGB or ARGB format. Defaults to 00000 (black) or FFFFFF (white) depending on FillColor. It can also use colour 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 colour 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 boolor int Enables or disables text wrapping. Accepts true, false, 1, or 0. Defaults to trueif 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 is 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"
    }
}

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 dont need to define a separate style for the intersecting cell. Lets 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 border, bold text, etc. Lets say the sum line has the style "SumLine", and the table values has "LeftLine". Using "Sumline,LeftLine" to the intersecting cell will produce a new style cloned from the first, and added missing borders from the second.

It is only the border elements that is merged in this way, as we asume that the first style have the other elements allready in place.

Alignment Example

If you want both horizontal and vertical alignment:

{
    "Alignment": "Left, Top"
}

This will set:

Notes: