The Format Class

This section describes the methods and properties that are available for formatting cells in Excel.

The properties of a cell that can be formatted include: fonts, colours, patterns, borders, alignment and number formatting.

Format objects are created by calling the workbook add_format() method as follows:

 format = workbook:add_format()

Format properties can be set by calling any of the methods shown in this section:

 format = workbook:add_format()
 format:set_bold()
 format:set_font_color("red")

Alternatively the properties can be set by passing a table of properties to the add_format() constructor:

 format = workbook:add_format({bold = true, font_color = "red"})

The documentation below shows the property methods but the information is equally applicable when using them in the add_format() constructor.

format:set_font_name()

Specify the font used used in the cell format:

 cell_format:set_font_name("Times New Roman")

Excel can only display fonts that are installed on the system that it is running on. Therefore it is best to use the fonts that come as standard such as "Calibri", "Times New Roman" and "Courier New".

The default font for an unformatted cell in Excel 2007+ is "Calibri".

format:set_font_size()

Set the font size of the cell format:

 format = workbook:add_format()
 format:set_font_size(30)

Excel adjusts the height of a row to accommodate the largest font size in the row. You can also explicitly specify the height of a row using the set_row() worksheet method.

format:set_font_color()

Set the font colour:

 format = workbook:add_format()

 format:set_font_color("red")

 worksheet:write(0, 0, "wheelbarrow", format)

The color can be a Html style #RRGGBB string or a limited number of named colors, see colors.

Note: The set_font_color() method is used to set the colour of the font in a cell. To set the colour of a cell use the set_bg_color() and set_pattern() methods.

format:set_bold()

Set the bold property of the font:

 format:set_bold()

format:set_italic()

Set the italic property of the font:

 format:set_italic()

format:set_underline()

Set the underline property of the format:

 format:set_underline()

The available underline styles are:

• 1 = Single underline (the default)
• 2 = Double underline
• 33 = Single accounting underline
• 34 = Double accounting underline

format:set_font_strikeout()

format:set_font_script()

The available options are:

• 1 = Superscript
• 2 = Subscript

format:set_num_format()

This method is used to define the numerical format of a number in Excel. It controls whether a number is displayed as an integer, a floating point number, a date, a currency value or some other user defined format:

The numerical format of a cell can be specified by using a format string or an index to one of Excel's built-in formats:

 format1 = workbook:add_format()
 format2 = workbook:add_format()

 format1:set_num_format("d mmm yyyy")  -- Format string.
 format2:set_num_format(0x0F)          -- Format index.

Format strings can control any aspect of number formatting allowed by Excel:

 format01:set_num_format("0.000")
 worksheet:write(1, 0, 3.1415926, format01)       --> 3.142

 format02:set_num_format("#,##0")
 worksheet:write(2, 0, 1234.56, format02)         --> 1,235

 format03:set_num_format("#,##0.00")
 worksheet:write(3, 0, 1234.56, format03)         --> 1,234.56

 format04:set_num_format("0.00")
 worksheet:write(4, 0, 49.99, format04)           --> 49.99

 format05:set_num_format("mm/dd/yy")
 worksheet:write(5, 0, 36892.521, format05)       --> 01/01/01

 format06:set_num_format("mmm d yyyy")
 worksheet:write(6, 0, 36892.521, format06)       --> Jan 1 2001

 format07:set_num_format("d mmmm yyyy")
 worksheet:write(7, 0, 36892.521, format07)       --> 1 January 2001

 format08:set_num_format("dd/mm/yyyy hh:mm AM/PM")
 worksheet:write(8, 0, 36892.521, format08)       --> 01/01/2001 12:30 AM

 format09:set_num_format('0 "dollar and" .00 "cents"')
 worksheet:write(9, 0, 1.87, format09)            --> 1 dollar and .87 cents

 -- Conditional numerical formatting.
 format10:set_num_format("[Green]General;[Red]-General;General")
 worksheet:write(10, 0, 123, format10)  -- > 0 Green
 worksheet:write(11, 0, -45, format10)  -- < 0 Red
 worksheet:write(12, 0,   0, format10)  -- = 0 Default colour

 -- Zip code.
 format11:set_num_format("00000")
 worksheet:write(13, 0, 1209, format11)

The number system used for dates is described in working_with_dates_and_time.

The colour format should have one of the following values:

 [Black] [Blue] [Cyan] [Green] [Magenta] [Red] [White] [Yellow]

For more information refer to the Microsoft documentation on cell formats.

Excel's built-in formats are shown in the following table:

format:set_locked()

This property can be used to prevent modification of a cells contents. Following Excel's convention, cell locking is turned on by default. However, it only has an effect if the worksheet has been protected using the worksheet protect() method:

 locked = workbook:add_format()
 locked:set_locked(true)

 unlocked = workbook:add_format()
 locked:set_locked(false)

 -- Enable worksheet protection
 worksheet:protect()

 -- This cell cannot be edited.
 worksheet:write("A1", "=1+2", locked)

 -- This cell can be edited.
 worksheet:write("A2", "=1+2", unlocked)

format:set_hidden()

This property is used to hide a formula while still displaying its result. This is generally used to hide complex calculations from end users who are only interested in the result. It only has an effect if the worksheet has been protected using the worksheet protect() method:

 hidden = workbook:add_format()
 hidden:set_hidden()

 -- Enable worksheet protection
 worksheet:protect()

 -- The formula in this cell isn't visible
 worksheet:write("A1", "=1+2", hidden)

format:set_align()

This method is used to set the horizontal and vertical text alignment within a cell. The following are the available horizontal alignments:

The following are the available vertical alignments:

As in Excel, vertical and horizontal alignments can be combined:

 format = workbook:add_format()

 format:set_align("center")
 format:set_align("vcenter")

 worksheet:set_row(0, 30)
 worksheet:write(0, 0, "Some Text", format)

Text can be aligned across two or more adjacent cells using the "center_across" property. However, for genuine merged cells it is better to use the merge_range() worksheet method.

The "vjustify" (vertical justify) option can be used to provide automatic text wrapping in a cell. The height of the cell will be adjusted to accommodate the wrapped text. To specify where the text wraps use the set_text_wrap() method.

format:set_center_across()

Text can be aligned across two or more adjacent cells using the set_center_across() method. This is an alias for the set_align("center_across") method call.

Only one cell should contain the text, the other cells should be blank:

 format = workbook:add_format()
 format:set_center_across()

 worksheet:write(1, 1, "Center across selection", format)
 worksheet:write_blank(1, 2, format)

For actual merged cells it is better to use the merge_range() worksheet method.

format:set_text_wrap()

Turn text wrapping on for text in a cell:

 format = workbook:add_format()
 format:set_text_wrap()

 worksheet:write(0, 0, "Some long text to wrap in a cell", format)

If you wish to control where the text is wrapped you can add newline characters to the string:

 format = workbook:add_format()
 format:set_text_wrap()

 worksheet:write(0, 0, "It's\na bum\nwrap", format)

Excel will adjust the height of the row to accommodate the wrapped text. A similar effect can be obtained without newlines using the set_align("vjustify") method.

format:set_rotation()

Set the rotation of the text in a cell. The rotation can be any angle in the range -90 to 90 degrees:

 format = workbook:add_format()
 format:set_rotation(30)

 worksheet:write(0, 0, "This text is rotated", format)

The angle 270 is also supported. This indicates text where the letters run from top to bottom.

format:set_indent()

This method can be used to indent text in a cell. The argument, which should be an integer, is taken as the level of indentation:

 format1 = workbook:add_format()
 format2 = workbook:add_format()

 format1:set_indent(1)
 format2:set_indent(2)

 worksheet:write("A1", "This text is indented 1 level",  format1)
 worksheet:write("A2", "This text is indented 2 levels", format2)

Indentation is a horizontal alignment property. It will override any other horizontal properties but it can be used in conjunction with vertical properties.

format:set_shrink()

This method can be used to shrink text so that it fits in a cell:

 format = workbook:add_format()
 format:set_shrink()

 worksheet:write(0, 0, "Honey, I shrunk the text!", format)

format:set_text_justlast()

Only applies to Far Eastern versions of Excel.

format:set_pattern()

Set the background pattern of a cell.

The most common pattern is 1 which is a solid fill of the background color.

format:set_bg_color()

The set_bg_color() method can be used to set the background colour of a pattern. Patterns are defined via the set_pattern() method. If a pattern hasn't been defined then a solid fill pattern is used as the default.

Here is an example of how to set up a solid fill in a cell:

 format = workbook:add_format()

 format:set_pattern(1)  -- This is optional when using a solid fill.
 format:set_bg_color("green")

 worksheet:write("A1", "Ray", format)

The color can be a Html style #RRGGBB string or a limited number of named colors, see colors.

format:set_fg_color()

The set_fg_color() method can be used to set the foreground colour of a pattern.

The color can be a Html style #RRGGBB string or a limited number of named colors, see colors.

format:set_border()

Individual border elements can be configured using the following methods with the same parameters:

• set_bottom()
• set_top()
• set_left()
• set_right()

A cell border is comprised of a border on the bottom, top, left and right. These can be set to the same value using set_border() or individually using the relevant method calls shown above.

The following shows the border styles sorted by Excel index number:

The following shows the borders in the order shown in the Excel Dialog:

format:set_bottom()

Set the cell bottom border style. See set_border for details on the border styles.

format:set_top()

Set the cell top border style. See set_border for details on the border styles.

format:set_left()

Set the cell left border style. See set_border for details on the border styles.

format:set_right()

Set the cell right border style. See set_border for details on the border styles.

format:set_border_color()

Individual border elements can be configured using the following methods with the same parameters:

• set_bottom_color()
• set_top_color()
• set_left_color()
• set_right_color()

Set the colour of the cell borders. A cell border is comprised of a border on the bottom, top, left and right. These can be set to the same colour using set_border_color() or individually using the relevant method calls shown above.

The color can be a Html style #RRGGBB string or a limited number of named colors, see colors.

format:set_bottom_color()

See set_border_color for details on the border colors.

format:set_top_color()

See set_border_color for details on the border colors.

format:set_left_color()

See set_border_color for details on the border colors.

format:set_right_color()

See set_border_color for details on the border colors.

Last updated: 3 years ago | Edit on GitHub