Working with Formats

The methods and properties used to add formatting to a cell are shown in The Format Class. This section provides some additional information about working with formats.

Creating and using a Format object

Cell formatting is defined through a Format object. Format objects are created by calling the workbook add_format() method as follows:

format1 = workbook:add_format()      -- Set properties later.
format2 = workbook:add_format(props) -- Set properties at creation.

Once a Format object has been constructed and its properties have been set it can be passed as an argument to the worksheet write methods as follows:

worksheet:write       (0, 0, "Foo", format)
worksheet:write_string(1, 0, "Bar", format)
worksheet:write_number(2, 0, 3,     format)
worksheet:write_blank (3, 0, "",    format)

Formats can also be passed to the worksheet set_row() and set_column() methods to define the default formatting properties for a row or column:

worksheet:set_row(0, 18, format)
worksheet:set_column("A:D", 20, format)

Format methods and Format properties

The following table shows the Excel format categories, the formatting properties that can be applied and the equivalent object method:

Category Description Property Method Name
Font Font type font_name set_font_name()
  Font size font_size set_font_size()
  Font color font_color set_font_color()
  Bold bold set_bold()
  Italic italic set_italic()
  Underline underline set_underline()
  Strikeout font_strikeout set_font_strikeout()
  Super/Subscript font_script set_font_script()
Number Numeric format num_format set_num_format()
Protection Lock cells locked set_locked()
  Hide formulas hidden set_hidden()
Alignment Horizontal align align set_align()
  Vertical align valign set_align()
  Rotation rotation set_rotation()
  Text wrap text_wrap set_text_wrap()
  Justify last text_justlast set_text_justlast()
  Center across center_across set_center_across()
  Indentation indent set_indent()
  Shrink to fit shrink set_shrink()
Pattern Cell pattern pattern set_pattern()
  Background color bg_color set_bg_color()
  Foreground color fg_color set_fg_color()
Border Cell border border set_border()
  Bottom border bottom set_bottom()
  Top border top set_top()
  Left border left set_left()
  Right border right set_right()
  Border color border_color set_border_color()
  Bottom color bottom_color set_bottom_color()
  Top color top_color set_top_color()
  Left color left_color set_left_color()
  Right color right_color set_right_color()

There are two ways of setting Format properties: by using the object interface or by setting the property as a table of key/value pairs in the constructor. For example, a typical use of the object interface would be as follows:

format = workbook:add_format()

By comparison 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 object method interface is mainly provided for backward compatibility. The key/value interface has proved to be more flexible in real world programs and is the recommended method for setting format properties.

It is also possible, as with any Lua function that takes a table as its only parameter to use the following shorthand syntax:

format = workbook:add_format{bold = true, font_color = “red”}

Format Colors

Format property colors are specified using a Html sytle #RRGGBB value or a imited number of named colors:


See Working with Colors for more details.

Format Defaults

The default Excel 2007+ cell format is Calibri 11 with all other properties off.

In general a format method call without an argument will turn a property on, for example:

format = workbook:add_format()

format:set_bold()  -- Turns bold on.

Modifying Formats

Each unique cell format in an xlsxwriter spreadsheet must have a corresponding Format object. It isn’t possible to use a Format with a write() method and then redefine it for use at a later stage. This is because a Format is applied to a cell not in its current state but in its final state. Consider the following example:

format = workbook:add_format({bold - true, font_color = "red"})
worksheet:write("A1", "Cell A1", format)

-- Later...
worksheet:write("B1", "Cell B1", format)

Cell A1 is assigned a format which is initially has the font set to the colour red. However, the colour is subsequently set to green. When Excel displays Cell A1 it will display the final state of the Format which in this case will be the colour green.