libxlsxwriter
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);
format_set_bold(myformat1);
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);
workbook_close(workbook);
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);
workbook_close(workbook);
return 0;
}

Here is the output:

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

Next: Working with Colors

workbook_close
lxw_error workbook_close(lxw_workbook *workbook)
Close the Workbook object and write the XLSX file.
format_set_italic
void format_set_italic(lxw_format *format)
Turn on italic for the format font.
workbook_new
lxw_workbook * workbook_new(const char *filename)
Create a new workbook object.
format_set_bold
void format_set_bold(lxw_format *format)
Turn on bold for the format font.
format_set_num_format
void format_set_num_format(lxw_format *format, const char *num_format)
Set the number format for a cell.
lxw_worksheet
Struct to represent an Excel worksheet.
Definition: worksheet.h:1603
lxw_format
Struct to represent the formatting properties of an Excel format.
Definition: format.h:358
lxw_workbook
Struct to represent an Excel workbook.
Definition: workbook.h:273
worksheet_write_string
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.
LXW_COLOR_RED
@ LXW_COLOR_RED
Definition: format.h:217
worksheet_set_row
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.
worksheet_write_number
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.
format_set_font_color
void format_set_font_color(lxw_format *format, lxw_color_t color)
Set the color of the font used in the cell.
worksheet_set_column
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.
workbook_add_worksheet
lxw_worksheet * workbook_add_worksheet(lxw_workbook *workbook, const char *sheetname)
Add a new worksheet to a workbook.
workbook_add_format
lxw_format * workbook_add_format(lxw_workbook *workbook)
Create a new Format object to formats cells in worksheets.