Working with Formats

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

Creating and using a Format object

Formats are created by calling the workbook_add_format() method and properties as set using the various functions shown below:

Once a Format object has been created and its properties have been set it can be passed as an argument to the worksheet_write*() methods as follows:

worksheet_write_string(worksheet, 0, 0, "Hello", format);
worksheet_write_number(worksheet, 1, 0, 123.456, format);

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

worksheet_set_row (worksheet, 2, format, NULL);
worksheet_set_column(worksheet, 0, 20, format, NULL);

Format methods and Format properties

The following table shows the Excel format categories and the equivalent libxlsxwriter Format function:

Category Description Method Name
Font Font type format_set_font_name()
Font size format_set_font_size()
Font color format_set_font_color()
Bold format_set_bold()
Italic format_set_italic()
Underline format_set_underline()
Strikeout format_set_font_strikeout()
Super/Subscript format_set_font_script()
Number Numeric format format_set_num_format()
Protection Unlock cells format_set_unlocked()
Hide formulas format_set_hidden()
Alignment Horizontal align format_set_align()
Vertical align format_set_align()
Rotation format_set_rotation()
Text wrap format_set_text_wrap()
Indentation format_set_indent()
Shrink to fit format_set_shrink()
Pattern Cell pattern format_set_pattern()
Background color format_set_bg_color()
Foreground color format_set_fg_color()
Border Cell border format_set_border()
Bottom border format_set_bottom()
Top border format_set_top()
Left border format_set_left()
Right border format_set_right()
Border color format_set_border_color()
Bottom color format_set_bottom_color()
Top color format_set_top_color()
Left color format_set_left_color()
Right color format_set_right_color()

Format Colors

Format property colors are specified using a Html style RGB integer value or a limited number of defined colors:

See 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 function call without an argument will turn a property on, for example:

lxw_format *format = workbook_add_format(workbook);

Modifying and Reusing Formats

Once a format has been created it can be used and reused in worksheet_write*() functions across any number of worksheets:

lxw_format *myformat1 = workbook_add_format(workbook);
worksheet_write_string(worksheet1, 1, 4, "Some text", myformat1);
worksheet_write_string(worksheet2, 1, 4, "Some text", myformat1);

However, each unique cell format in an libxlsxwriter spreadsheet must have a corresponding Format object. It isn't possible to use a Format with a worksheet_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:

lxw_format *format = workbook_add_format(workbook);
worksheet_write_string(worksheet, 0, 0, "Hello", format);
worksheet_write_string(worksheet, 1, 0, "World", format);

Cell (0, 0) is assigned a format which with the font set to bold. However, the italic property is subsequently and used in cell (1, 0). This has the effect of adding italic to any previous uses of format. The result in this case is that "Hello" and "World" will both appear as bold and italic.

Number Format Categories

The format_set_num_format() function shown below, is used to set the number format for numbers used with worksheet_write_number():

#include "xlsxwriter.h"
int main() {
lxw_workbook *workbook = workbook_new("currency_format.xlsx");
lxw_worksheet *worksheet = workbook_add_worksheet(workbook, NULL);
lxw_format *currency_format = workbook_add_format(workbook);
format_set_num_format(currency_format, "$#,##0.00");
worksheet_write_number(worksheet, 0, 0, 1234.56, currency_format);
return 0;

If the number format you use is the same as one of Excel's built in number formats then it will have a number category such as "General", "Number", "Currency", "Accounting", "Date", "Time", "Percentage", "Fraction", "Scientific", "Text", "Special or "Custom". In the case of the example above the formatted output shows up as a Number category:

If we wanted it to have a different category, such as Currency, then we would have to match the number format string with the number format used by Excel. The easiest way to do this is to open the Number Formatting dialog in Excel and set the format that you want:

Then, while still in the dialog, change to Custom. The format displayed is the format used by Excel.

If we put the format that we found ("[$$-409]#,##0.00") into our previous example and rerun it we will get a number format in the Currency category:

include "xlsxwriter.h"
int main() {
lxw_workbook *workbook = workbook_new("currency_format.xlsx");
lxw_worksheet *worksheet = workbook_add_worksheet(workbook, NULL);
lxw_format *currency_format = workbook_add_format(workbook);
format_set_num_format(currency_format, "[$$-409]#,##0.00");
worksheet_write_number(worksheet, 0, 0, 1234.56, currency_format);
return 0;

Here is the output:

The same process can be used to find format strings for "Date" or "Accountancy" formats.

Number Formats in different locales

As shown in the previous section the format_set_num_format() method is used to set the number format for libxlsxwriter formats. A common use case is to set a number format with a "grouping/thousands" separator and a "decimal" point:

include "xlsxwriter.h"
int main() {
lxw_workbook *workbook = workbook_new("number_format.xlsx");
lxw_worksheet *worksheet = workbook_add_worksheet(workbook, NULL);
lxw_format *number_format = workbook_add_format(workbook);
format_set_num_format(number_format, "#,##0.00");
worksheet_write_number(worksheet, 0, 0, 1234.56, number_format);
return 0;

In the US locale (and some others) where the number "grouping/thousands" separator is "," and the "decimal" point is "." this would be shown in Excel as:

In other locales these values may be reversed or different. They are generally set in the "Region" settings of Windows or Mac OS. Excel handles this by storing the number format in the file format in the US locale, in this case #,##0.00, but renders it according to the regional settings of the host OS. For example, here is the same, unmodified, output file shown above in a German locale:

And here is the same file in a Russian locale. Note the use of a space as the "grouping/thousands" separator:

In order to replicate Excel's behavior all XlsxWriter programs should use US locale formatting which will then be rendered in the settings of your host OS.

Next: Working with Colors

lxw_error workbook_close(lxw_workbook *workbook)
Close the Workbook object and write the XLSX file.
void format_set_italic(lxw_format *format)
Turn on italic for the format font.
lxw_workbook * workbook_new(const char *filename)
Create a new workbook object.
void format_set_bold(lxw_format *format)
Turn on bold for the format font.
void format_set_num_format(lxw_format *format, const char *num_format)
Set the number format for a cell.
Struct to represent an Excel worksheet.
Definition: worksheet.h:2107
Struct to represent the formatting properties of an Excel format.
Definition: format.h:358
Struct to represent an Excel workbook.
Definition: workbook.h:292
lxw_error worksheet_write_string(lxw_worksheet *worksheet, lxw_row_t row, lxw_col_t col, const char *string, lxw_format *format)
Write a string to a worksheet cell.
Definition: format.h:217
lxw_error worksheet_set_row(lxw_worksheet *worksheet, lxw_row_t row, double height, lxw_format *format)
Set the properties for a row of cells.
lxw_error worksheet_write_number(lxw_worksheet *worksheet, lxw_row_t row, lxw_col_t col, double number, lxw_format *format)
Write a number to a worksheet cell.
void format_set_font_color(lxw_format *format, lxw_color_t color)
Set the color of the font used in the cell.
lxw_error worksheet_set_column(lxw_worksheet *worksheet, lxw_col_t first_col, lxw_col_t last_col, double width, lxw_format *format)
Set the properties for one or more columns of cells.
lxw_worksheet * workbook_add_worksheet(lxw_workbook *workbook, const char *sheetname)
Add a new worksheet to a workbook.
lxw_format * workbook_add_format(lxw_workbook *workbook)
Create a new Format object to formats cells in worksheets.