libxlsxwriter
 All Data Structures Files Functions Variables Typedefs Enumerations Enumerator Macros Pages
Functions | Data Structures | Typedefs | Enumerations | Macros
worksheet.h File Reference

Description

The Worksheet object represents an Excel worksheet. It handles operations such as writing data to cells or formatting worksheet layout.

A Worksheet object isn't created directly. Instead a worksheet is created by calling the workbook_add_worksheet() function from a Workbook object:

#include "xlsxwriter.h"
int main() {
lxw_workbook *workbook = workbook_new("filename.xlsx");
lxw_worksheet *worksheet = workbook_add_worksheet(workbook, NULL);
worksheet_write_string(worksheet, 0, 0, "Hello Excel", NULL);
return workbook_close(workbook);
}

Functions

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. More...
 
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. More...
 
lxw_error worksheet_write_formula (lxw_worksheet *worksheet, lxw_row_t row, lxw_col_t col, const char *formula, lxw_format *format)
 Write a formula to a worksheet cell. More...
 
lxw_error worksheet_write_array_formula (lxw_worksheet *worksheet, lxw_row_t first_row, lxw_col_t first_col, lxw_row_t last_row, lxw_col_t last_col, const char *formula, lxw_format *format)
 Write an array formula to a worksheet cell. More...
 
lxw_error worksheet_write_datetime (lxw_worksheet *worksheet, lxw_row_t row, lxw_col_t col, lxw_datetime *datetime, lxw_format *format)
 Write a date or time to a worksheet cell. More...
 
lxw_error worksheet_write_url (lxw_worksheet *worksheet, lxw_row_t row, lxw_col_t col, const char *url, lxw_format *format)
 
lxw_error worksheet_write_boolean (lxw_worksheet *worksheet, lxw_row_t row, lxw_col_t col, int value, lxw_format *format)
 Write a formatted boolean worksheet cell. More...
 
lxw_error worksheet_write_blank (lxw_worksheet *worksheet, lxw_row_t row, lxw_col_t col, lxw_format *format)
 Write a formatted blank worksheet cell. More...
 
lxw_error worksheet_write_formula_num (lxw_worksheet *worksheet, lxw_row_t row, lxw_col_t col, const char *formula, lxw_format *format, double result)
 Write a formula to a worksheet cell with a user defined result. More...
 
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. More...
 
lxw_error worksheet_set_row_opt (lxw_worksheet *worksheet, lxw_row_t row, double height, lxw_format *format, lxw_row_col_options *options)
 Set the properties for a row of cells. More...
 
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. More...
 
lxw_error worksheet_set_column_opt (lxw_worksheet *worksheet, lxw_col_t first_col, lxw_col_t last_col, double width, lxw_format *format, lxw_row_col_options *options)
 Set the properties for one or more columns of cells with options. More...
 
lxw_error worksheet_insert_image (lxw_worksheet *worksheet, lxw_row_t row, lxw_col_t col, const char *filename)
 Insert an image in a worksheet cell. More...
 
lxw_error worksheet_insert_image_opt (lxw_worksheet *worksheet, lxw_row_t row, lxw_col_t col, const char *filename, lxw_image_options *options)
 Insert an image in a worksheet cell, with options. More...
 
lxw_error worksheet_insert_chart (lxw_worksheet *worksheet, lxw_row_t row, lxw_col_t col, lxw_chart *chart)
 Insert a chart object into a worksheet. More...
 
lxw_error worksheet_insert_chart_opt (lxw_worksheet *worksheet, lxw_row_t row, lxw_col_t col, lxw_chart *chart, lxw_image_options *user_options)
 Insert a chart object into a worksheet, with options. More...
 
lxw_error worksheet_merge_range (lxw_worksheet *worksheet, lxw_row_t first_row, lxw_col_t first_col, lxw_row_t last_row, lxw_col_t last_col, const char *string, lxw_format *format)
 Merge a range of cells. More...
 
lxw_error worksheet_autofilter (lxw_worksheet *worksheet, lxw_row_t first_row, lxw_col_t first_col, lxw_row_t last_row, lxw_col_t last_col)
 Set the autofilter area in the worksheet. More...
 
void worksheet_activate (lxw_worksheet *worksheet)
 Make a worksheet the active, i.e., visible worksheet. More...
 
void worksheet_select (lxw_worksheet *worksheet)
 Set a worksheet tab as selected. More...
 
void worksheet_hide (lxw_worksheet *worksheet)
 Hide the current worksheet. More...
 
void worksheet_set_first_sheet (lxw_worksheet *worksheet)
 Set current worksheet as the first visible sheet tab. More...
 
void worksheet_freeze_panes (lxw_worksheet *worksheet, lxw_row_t row, lxw_col_t col)
 Split and freeze a worksheet into panes. More...
 
void worksheet_split_panes (lxw_worksheet *worksheet, double vertical, double horizontal)
 Split a worksheet into panes. More...
 
void worksheet_set_selection (lxw_worksheet *worksheet, lxw_row_t first_row, lxw_col_t first_col, lxw_row_t last_row, lxw_col_t last_col)
 Set the selected cell or cells in a worksheet: More...
 
void worksheet_set_landscape (lxw_worksheet *worksheet)
 Set the page orientation as landscape. More...
 
void worksheet_set_portrait (lxw_worksheet *worksheet)
 Set the page orientation as portrait. More...
 
void worksheet_set_page_view (lxw_worksheet *worksheet)
 Set the page layout to page view mode. More...
 
void worksheet_set_paper (lxw_worksheet *worksheet, uint8_t paper_type)
 Set the paper type for printing. More...
 
void worksheet_set_margins (lxw_worksheet *worksheet, double left, double right, double top, double bottom)
 Set the worksheet margins for the printed page. More...
 
lxw_error worksheet_set_header (lxw_worksheet *worksheet, const char *string)
 Set the printed page header caption. More...
 
lxw_error worksheet_set_footer (lxw_worksheet *worksheet, const char *string)
 Set the printed page footer caption. More...
 
lxw_error worksheet_set_header_opt (lxw_worksheet *worksheet, const char *string, lxw_header_footer_options *options)
 Set the printed page header caption with additional options. More...
 
lxw_error worksheet_set_footer_opt (lxw_worksheet *worksheet, const char *string, lxw_header_footer_options *options)
 Set the printed page footer caption with additional options. More...
 
lxw_error worksheet_set_h_pagebreaks (lxw_worksheet *worksheet, lxw_row_t breaks[])
 Set the horizontal page breaks on a worksheet. More...
 
lxw_error worksheet_set_v_pagebreaks (lxw_worksheet *worksheet, lxw_col_t breaks[])
 Set the vertical page breaks on a worksheet. More...
 
void worksheet_print_across (lxw_worksheet *worksheet)
 Set the order in which pages are printed. More...
 
void worksheet_set_zoom (lxw_worksheet *worksheet, uint16_t scale)
 Set the worksheet zoom factor. More...
 
void worksheet_gridlines (lxw_worksheet *worksheet, uint8_t option)
 Set the option to display or hide gridlines on the screen and the printed page. More...
 
void worksheet_center_horizontally (lxw_worksheet *worksheet)
 Center the printed page horizontally. More...
 
void worksheet_center_vertically (lxw_worksheet *worksheet)
 Center the printed page vertically. More...
 
void worksheet_print_row_col_headers (lxw_worksheet *worksheet)
 Set the option to print the row and column headers on the printed page. More...
 
lxw_error worksheet_repeat_rows (lxw_worksheet *worksheet, lxw_row_t first_row, lxw_row_t last_row)
 Set the number of rows to repeat at the top of each printed page. More...
 
lxw_error worksheet_repeat_columns (lxw_worksheet *worksheet, lxw_col_t first_col, lxw_col_t last_col)
 Set the number of columns to repeat at the top of each printed page. More...
 
lxw_error worksheet_print_area (lxw_worksheet *worksheet, lxw_row_t first_row, lxw_col_t first_col, lxw_row_t last_row, lxw_col_t last_col)
 Set the print area for a worksheet. More...
 
void worksheet_fit_to_pages (lxw_worksheet *worksheet, uint16_t width, uint16_t height)
 Fit the printed area to a specific number of pages both vertically and horizontally. More...
 
void worksheet_set_start_page (lxw_worksheet *worksheet, uint16_t start_page)
 Set the start page number when printing. More...
 
void worksheet_set_print_scale (lxw_worksheet *worksheet, uint16_t scale)
 Set the scale factor for the printed page. More...
 
void worksheet_right_to_left (lxw_worksheet *worksheet)
 Display the worksheet cells from right to left for some versions of Excel. More...
 
void worksheet_hide_zero (lxw_worksheet *worksheet)
 Hide zero values in worksheet cells. More...
 
void worksheet_set_tab_color (lxw_worksheet *worksheet, lxw_color_t color)
 Set the color of the worksheet tab. More...
 
void worksheet_protect (lxw_worksheet *worksheet, const char *password, lxw_protection *options)
 Protect elements of a worksheet from modification. More...
 
void worksheet_set_default_row (lxw_worksheet *worksheet, double height, uint8_t hide_unused_rows)
 Set the default row properties. More...
 

Function Documentation

lxw_error worksheet_write_number ( lxw_worksheet worksheet,
lxw_row_t  row,
lxw_col_t  col,
double  number,
lxw_format format 
)
Parameters
worksheetpointer to a lxw_worksheet instance to be updated.
rowThe zero indexed row number.
colThe zero indexed column number.
numberThe number to write to the cell.
formatA pointer to a Format instance or NULL.
Returns
A lxw_error code.

The worksheet_write_number() function writes numeric types to the cell specified by row and column:

worksheet_write_number(worksheet, 0, 0, 123456, NULL);
worksheet_write_number(worksheet, 1, 0, 2.3451, NULL);
write_number01.png

The native data type for all numbers in Excel is a IEEE-754 64-bit double-precision floating point, which is also the default type used by worksheet_write_number.

The format parameter is used to apply formatting to the cell. This parameter can be NULL to indicate no formatting or it can be a Format object.

lxw_format *format = workbook_add_format(workbook);
format_set_num_format(format, "$#,##0.00");
worksheet_write_number(worksheet, 0, 0, 1234.567, format);
write_number02.png
Examples:
anatomy.c, array_formula.c, autofilter.c, chart.c, chart_area.c, chart_bar.c, chart_clustered.c, chart_column.c, chart_data_table.c, chart_data_tools.c, chart_doughnut.c, chart_fonts.c, chart_line.c, chart_pattern.c, chart_pie.c, chart_radar.c, chart_scatter.c, chart_styles.c, constant_memory.c, dates_and_times01.c, demo.c, format_num_format.c, hello.c, panes.c, tutorial1.c, tutorial2.c, and tutorial3.c.
lxw_error worksheet_write_string ( lxw_worksheet worksheet,
lxw_row_t  row,
lxw_col_t  col,
const char *  string,
lxw_format format 
)
Parameters
worksheetpointer to a lxw_worksheet instance to be updated.
rowThe zero indexed row number.
colThe zero indexed column number.
stringString to write to cell.
formatA pointer to a Format instance or NULL.
Returns
A lxw_error code.

The worksheet_write_string() function writes a string to the cell specified by row and column:

worksheet_write_string(worksheet, 0, 0, "This phrase is English!", NULL);
write_string01.png

The format parameter is used to apply formatting to the cell. This parameter can be NULL to indicate no formatting or it can be a Format object:

lxw_format *format = workbook_add_format(workbook);
worksheet_write_string(worksheet, 0, 0, "This phrase is Bold!", format);
write_string02.png

Unicode strings are supported in UTF-8 encoding. This generally requires that your source file is UTF-8 encoded or that the data has been read from a UTF-8 source:

worksheet_write_string(worksheet, 0, 0, "Это фраза на русском!", NULL);
write_string03.png
Examples:
anatomy.c, autofilter.c, chart_area.c, chart_bar.c, chart_clustered.c, chart_column.c, chart_data_table.c, chart_data_tools.c, chart_doughnut.c, chart_line.c, chart_pattern.c, chart_pie.c, chart_radar.c, chart_scatter.c, dates_and_times03.c, defined_name.c, demo.c, doc_custom_properties.c, doc_properties.c, format_font.c, headers_footers.c, hello.c, hide_row_col.c, hide_sheet.c, hyperlinks.c, images.c, panes.c, tab_colors.c, tutorial1.c, tutorial2.c, tutorial3.c, utf8.c, and worksheet_protection.c.
lxw_error worksheet_write_formula ( lxw_worksheet worksheet,
lxw_row_t  row,
lxw_col_t  col,
const char *  formula,
lxw_format format 
)
Parameters
worksheetpointer to a lxw_worksheet instance to be updated.
rowThe zero indexed row number.
colThe zero indexed column number.
formulaFormula string to write to cell.
formatA pointer to a Format instance or NULL.
Returns
A lxw_error code.

The worksheet_write_formula() function writes a formula or function to the cell specified by row and column:

worksheet_write_formula(worksheet, 0, 0, "=B3 + 6", NULL);
worksheet_write_formula(worksheet, 1, 0, "=SIN(PI()/4)", NULL);
worksheet_write_formula(worksheet, 2, 0, "=SUM(A1:A2)", NULL);
worksheet_write_formula(worksheet, 3, 0, "=IF(A3>1,\"Yes\", \"No\")", NULL);
worksheet_write_formula(worksheet, 4, 0, "=AVERAGE(1, 2, 3, 4)", NULL);
worksheet_write_formula(worksheet, 5, 0, "=DATEVALUE(\"1-Jan-2013\")", NULL);
write_formula01.png

The format parameter is used to apply formatting to the cell. This parameter can be NULL to indicate no formatting or it can be a Format object.

Libxlsxwriter doesn't calculate the value of a formula and instead stores a default value of 0. The correct formula result is displayed in Excel, as shown in the example above, since it recalculates the formulas when it loads the file. For cases where this is an issue see the worksheet_write_formula_num() function and the discussion in that section.

Formulas must be written with the US style separator/range operator which is a comma (not semi-colon). Therefore a formula with multiple values should be written as follows:

// OK.
worksheet_write_formula(worksheet, 0, 0, "=SUM(1, 2, 3)", NULL);
// NO. Error on load.
worksheet_write_formula(worksheet, 1, 0, "=SUM(1; 2; 3)", NULL);
Examples:
defined_name.c, tutorial1.c, tutorial2.c, tutorial3.c, and worksheet_protection.c.
lxw_error worksheet_write_array_formula ( lxw_worksheet worksheet,
lxw_row_t  first_row,
lxw_col_t  first_col,
lxw_row_t  last_row,
lxw_col_t  last_col,
const char *  formula,
lxw_format format 
)
Parameters
worksheetpointer to a lxw_worksheet instance to be updated.
first_rowThe first row of the range. (All zero indexed.)
first_colThe first column of the range.
last_rowThe last row of the range.
last_colThe last col of the range.
formulaArray formula to write to cell.
formatA pointer to a Format instance or NULL.
Returns
A lxw_error code.

The worksheet_write_array_formula() function writes an array formula to a cell range. In Excel an array formula is a formula that performs a calculation on a set of values.

In Excel an array formula is indicated by a pair of braces around the formula: {=SUM(A1:B1*A2:B2)}.

Array formulas can return a single value or a range or values. For array formulas that return a range of values you must specify the range that the return values will be written to. This is why this function has first_ and last_ row/column parameters. The RANGE() macro can also be used to specify the range:

worksheet_write_array_formula(worksheet, 4, 0, 6, 0, "{=TREND(C5:C7,B5:B7)}", NULL);
// Same as above using the RANGE() macro.
worksheet_write_array_formula(worksheet, RANGE("A5:A7"), "{=TREND(C5:C7,B5:B7)}", NULL);

If the array formula returns a single value then the first_ and last_ parameters should be the same:

worksheet_write_array_formula(worksheet, 1, 0, 1, 0, "{=SUM(B1:C1*B2:C2)}", NULL);
worksheet_write_array_formula(worksheet, RANGE("A2:A2"), "{=SUM(B1:C1*B2:C2)}", NULL);
Examples:
array_formula.c.
lxw_error worksheet_write_datetime ( lxw_worksheet worksheet,
lxw_row_t  row,
lxw_col_t  col,
lxw_datetime datetime,
lxw_format format 
)
Parameters
worksheetpointer to a lxw_worksheet instance to be updated.
rowThe zero indexed row number.
colThe zero indexed column number.
datetimeThe datetime to write to the cell.
formatA pointer to a Format instance or NULL.
Returns
A lxw_error code.

The worksheet_write_datetime() function can be used to write a date or time to the cell specified by row and column:

#include "xlsxwriter.h"
int main() {
/* A datetime to display. */
lxw_datetime datetime = {2013, 2, 28, 12, 0, 0.0};
/* Create a new workbook and add a worksheet. */
lxw_workbook *workbook = workbook_new("date_and_times02.xlsx");
lxw_worksheet *worksheet = workbook_add_worksheet(workbook, NULL);
/* Add a format with date formatting. */
lxw_format *format = workbook_add_format(workbook);
format_set_num_format(format, "mmm d yyyy hh:mm AM/PM");
worksheet_write_datetime(worksheet, 1, 0, &datetime, format); // Feb 28 2013 12:00 PM
return workbook_close(workbook);
}

The format parameter should be used to apply formatting to the cell using a Format object as shown above. Without a date format the datetime will appear as a number only.

See Working with Dates and Times for more information about handling dates and times in libxlsxwriter.

Examples:
dates_and_times02.c, dates_and_times03.c, and tutorial3.c.
lxw_error worksheet_write_url ( lxw_worksheet worksheet,
lxw_row_t  row,
lxw_col_t  col,
const char *  url,
lxw_format format 
)
Parameters
worksheetpointer to a lxw_worksheet instance to be updated.
rowThe zero indexed row number.
colThe zero indexed column number.
urlThe url to write to the cell.
formatA pointer to a Format instance or NULL.
Returns
A lxw_error code.

The worksheet_write_url() function is used to write a URL/hyperlink to a worksheet cell specified by row and column.

worksheet_write_url(worksheet, 0, 0, "http://libxlsxwriter.github.io", url_format);
hyperlinks_short.png

The format parameter is used to apply formatting to the cell. This parameter can be NULL to indicate no formatting or it can be a Format object. The typical worksheet format for a hyperlink is a blue underline:

The usual web style URI's are supported: http://, https://, ftp:// and mailto: :

worksheet_write_url(worksheet, 0, 0, "ftp://www.python.org/", url_format);
worksheet_write_url(worksheet, 1, 0, "http://www.python.org/", url_format);
worksheet_write_url(worksheet, 2, 0, "https://www.python.org/", url_format);
worksheet_write_url(worksheet, 3, 0, "mailto:jmcnamara@cpan.org", url_format);

An Excel hyperlink is comprised of two elements: the displayed string and the non-displayed link. By default the displayed string is the same as the link. However, it is possible to overwrite it with any other libxlsxwriter type using the appropriate worksheet_write_*() function. The most common case is to overwrite the displayed link text with another string:

// Write a hyperlink but overwrite the displayed string.
worksheet_write_url (worksheet, 2, 0, "http://libxlsxwriter.github.io", url_format);
worksheet_write_string(worksheet, 2, 0, "Read the documentation.", url_format);
hyperlinks_short2.png

Two local URIs are supported: internal: and external:. These are used for hyperlinks to internal worksheet references or external workbook and worksheet references:

worksheet_write_url(worksheet, 0, 0, "internal:Sheet2!A1", url_format);
worksheet_write_url(worksheet, 1, 0, "internal:Sheet2!B2", url_format);
worksheet_write_url(worksheet, 2, 0, "internal:Sheet2!A1:B2", url_format);
worksheet_write_url(worksheet, 3, 0, "internal:'Sales Data'!A1", url_format);
worksheet_write_url(worksheet, 4, 0, "external:c:\\temp\\foo.xlsx", url_format);
worksheet_write_url(worksheet, 5, 0, "external:c:\\foo.xlsx#Sheet2!A1", url_format);
worksheet_write_url(worksheet, 6, 0, "external:..\\foo.xlsx", url_format);
worksheet_write_url(worksheet, 7, 0, "external:..\\foo.xlsx#Sheet2!A1", url_format);
worksheet_write_url(worksheet, 8, 0, "external:\\\\NET\\share\\foo.xlsx", url_format);

Worksheet references are typically of the form Sheet1!A1. You can also link to a worksheet range using the standard Excel notation: Sheet1!A1:B2.

In external links the workbook and worksheet name must be separated by the # character:

worksheet_write_url(worksheet, 0, 0, "external:c:\\foo.xlsx#Sheet2!A1", url_format);

You can also link to a named range in the target worksheet: For example say you have a named range called my_name in the workbook c:\temp\foo.xlsx you could link to it as follows:

worksheet_write_url(worksheet, 0, 0, "external:c:\\temp\\foo.xlsx#my_name", url_format);

Excel requires that worksheet names containing spaces or non alphanumeric characters are single quoted as follows:

worksheet_write_url(worksheet, 0, 0, "internal:'Sales Data'!A1", url_format);

Links to network files are also supported. Network files normally begin with two back slashes as follows \\NETWORK\etc. In order to represent this in a C string literal the backslashes should be escaped:

worksheet_write_url(worksheet, 0, 0, "external:\\\\NET\\share\\foo.xlsx", url_format);

Alternatively, you can use Windows style forward slashes. These are translated internally to backslashes:

worksheet_write_url(worksheet, 0, 0, "external:c:/temp/foo.xlsx", url_format);
worksheet_write_url(worksheet, 1, 0, "external://NET/share/foo.xlsx", url_format);

Note:

libxlsxwriter will escape the following characters in URLs as required by Excel: \s " < > \ [ ] ^ { } unless the URL already contains %xx style escapes. In which case it is assumed that the URL was escaped correctly by the user and will by passed directly to Excel.

Examples:
hyperlinks.c.
lxw_error worksheet_write_boolean ( lxw_worksheet worksheet,
lxw_row_t  row,
lxw_col_t  col,
int  value,
lxw_format format 
)
Parameters
worksheetpointer to a lxw_worksheet instance to be updated.
rowThe zero indexed row number.
colThe zero indexed column number.
valueThe boolean value to write to the cell.
formatA pointer to a Format instance or NULL.
Returns
A lxw_error code.

Write an Excel boolean to the cell specified by row and column:

worksheet_write_boolean(worksheet, 2, 2, 0, my_format);
lxw_error worksheet_write_blank ( lxw_worksheet worksheet,
lxw_row_t  row,
lxw_col_t  col,
lxw_format format 
)
Parameters
worksheetpointer to a lxw_worksheet instance to be updated.
rowThe zero indexed row number.
colThe zero indexed column number.
formatA pointer to a Format instance or NULL.
Returns
A lxw_error code.

Write a blank cell specified by row and column:

worksheet_write_blank(worksheet, 1, 1, border_format);

This function is used to add formatting to a cell which doesn't contain a string or number value.

Excel differentiates between an "Empty" cell and a "Blank" cell. An Empty cell is a cell which doesn't contain data or formatting whilst a Blank cell doesn't contain data but does contain formatting. Excel stores Blank cells but ignores Empty cells.

As such, if you write an empty cell without formatting it is ignored.

lxw_error worksheet_write_formula_num ( lxw_worksheet worksheet,
lxw_row_t  row,
lxw_col_t  col,
const char *  formula,
lxw_format format,
double  result 
)
Parameters
worksheetpointer to a lxw_worksheet instance to be updated.
rowThe zero indexed row number.
colThe zero indexed column number.
formulaFormula string to write to cell.
formatA pointer to a Format instance or NULL.
resultA user defined result for a formula.
Returns
A lxw_error code.

The worksheet_write_formula_num() function writes a formula or Excel function to the cell specified by row and column with a user defined result:

// Required as a workaround only.
worksheet_write_formula_num(worksheet, 0, 0, "=1 + 2", NULL, 3);

Libxlsxwriter doesn't calculate the value of a formula and instead stores the value 0 as the formula result. It then sets a global flag in the XLSX file to say that all formulas and functions should be recalculated when the file is opened.

This is the method recommended in the Excel documentation and in general it works fine with spreadsheet applications.

However, applications that don't have a facility to calculate formulas, such as Excel Viewer, or some mobile applications will only display the 0 results.

If required, the worksheet_write_formula_num() function can be used to specify a formula and its result.

This function is rarely required and is only provided for compatibility with some third party applications. For most applications the worksheet_write_formula() function is the recommended way of writing formulas.

lxw_error worksheet_set_row ( lxw_worksheet worksheet,
lxw_row_t  row,
double  height,
lxw_format format 
)
Parameters
worksheetPointer to a lxw_worksheet instance to be updated.
rowThe zero indexed row number.
heightThe row height.
formatA pointer to a Format instance or NULL.

The worksheet_set_row() function is used to change the default properties of a row. The most common use for this function is to change the height of a row:

// Set the height of Row 1 to 20.
worksheet_set_row(worksheet, 0, 20, NULL);

The other common use for worksheet_set_row() is to set the a Format for all cells in the row:

lxw_format *bold = workbook_add_format(workbook);
// Set the header row to bold.
worksheet_set_row(worksheet, 0, 15, bold);

If you wish to set the format of a row without changing the height you can pass the default row height of LXW_DEF_ROW_HEIGHT = 15:

worksheet_set_row(worksheet, 0, LXW_DEF_ROW_HEIGHT, format);
worksheet_set_row(worksheet, 0, 15, format); // Same as above.

The format parameter will be applied to any cells in the row that don't have a format. As with Excel the row format is overridden by an explicit cell format. For example:

// Row 1 has format1.
worksheet_set_row(worksheet, 0, 15, format1);
// Cell A1 in Row 1 defaults to format1.
worksheet_write_string(worksheet, 0, 0, "Hello", NULL);
// Cell B1 in Row 1 keeps format2.
worksheet_write_string(worksheet, 0, 1, "Hello", format2);
Examples:
hide_row_col.c, merge_range.c, and panes.c.
lxw_error worksheet_set_row_opt ( lxw_worksheet worksheet,
lxw_row_t  row,
double  height,
lxw_format format,
lxw_row_col_options options 
)
Parameters
worksheetPointer to a lxw_worksheet instance to be updated.
rowThe zero indexed row number.
heightThe row height.
formatA pointer to a Format instance or NULL.
optionsOptional row parameters: hidden, level, collapsed.

The worksheet_set_row_opt() function is the same as worksheet_set_row() with an additional options parameter.

The options parameter is a lxw_row_col_options struct. It has the following members but currently only the hidden property is supported:

  • hidden
  • level
  • collapsed

The "hidden" option is used to hide a row. This can be used, for example, to hide intermediary steps in a complicated calculation:

lxw_row_col_options options = {.hidden = 1, .level = 0, .collapsed = 0};
// Hide the fourth row.
worksheet_set_row(worksheet, 3, 20, NULL, &options);
lxw_error worksheet_set_column ( lxw_worksheet worksheet,
lxw_col_t  first_col,
lxw_col_t  last_col,
double  width,
lxw_format format 
)
Parameters
worksheetPointer to a lxw_worksheet instance to be updated.
first_colThe zero indexed first column.
last_colThe zero indexed last column.
widthThe width of the column(s).
formatA pointer to a Format instance or NULL.

The worksheet_set_column() function can be used to change the default properties of a single column or a range of columns:

// Width of columns B:D set to 30.
worksheet_set_column(worksheet, 1, 3, 30, NULL);

If worksheet_set_column() is applied to a single column the value of first_col and last_col should be the same:

// Width of column B set to 30.
worksheet_set_column(worksheet, 1, 1, 30, NULL);

It is also possible, and generally clearer, to specify a column range using the form of COLS() macro:

worksheet_set_column(worksheet, 4, 4, 20, NULL);
worksheet_set_column(worksheet, 5, 8, 30, NULL);
// Same as the examples above but clearer.
worksheet_set_column(worksheet, COLS("E:E"), 20, NULL);
worksheet_set_column(worksheet, COLS("F:H"), 30, NULL);

The width parameter sets the column width in the same units used by Excel which is: the number of characters in the default font. The default width is 8.43 in the default font of Calibri 11. The actual relationship between a string width and a column width in Excel is complex. See the following explanation of column widths from the Microsoft support documentation for more details.

There is no way to specify "AutoFit" for a column in the Excel file format. This feature is only available at runtime from within Excel. It is possible to simulate "AutoFit" in your application by tracking the maximum width of the data in the column as your write it and then adjusting the column width at the end.

As usual the format.h format parameter is optional. If you wish to set the format without changing the width you can pass a default column width of LXW_DEF_COL_WIDTH = 8.43:

lxw_format *bold = workbook_add_format(workbook);
// Set the first column to bold.
worksheet_set_column(worksheet, 0, 0, LXW_DEF_COL_HEIGHT, bold);

The format parameter will be applied to any cells in the column that don't have a format. For example:

// Column 1 has format1.
worksheet_set_column(worksheet, COLS("A:A"), 8.43, format1);
// Cell A1 in column 1 defaults to format1.
worksheet_write_string(worksheet, 0, 0, "Hello", NULL);
// Cell A2 in column 1 keeps format2.
worksheet_write_string(worksheet, 1, 0, "Hello", format2);

As in Excel a row format takes precedence over a default column format:

// Row 1 has format1.
worksheet_set_row(worksheet, 0, 15, format1);
// Col 1 has format2.
worksheet_set_column(worksheet, COLS("A:A"), 8.43, format2);
// Cell A1 defaults to format1, the row format.
worksheet_write_string(worksheet, 0, 0, "Hello", NULL);
// Cell A2 keeps format2, the column format.
worksheet_write_string(worksheet, 1, 0, "Hello", NULL);
Examples:
anatomy.c, dates_and_times01.c, dates_and_times02.c, dates_and_times03.c, defined_name.c, demo.c, doc_custom_properties.c, doc_properties.c, format_font.c, format_num_format.c, headers_footers.c, hide_sheet.c, hyperlinks.c, images.c, merge_range.c, panes.c, tutorial3.c, and worksheet_protection.c.
lxw_error worksheet_set_column_opt ( lxw_worksheet worksheet,
lxw_col_t  first_col,
lxw_col_t  last_col,
double  width,
lxw_format format,
lxw_row_col_options options 
)
Parameters
worksheetPointer to a lxw_worksheet instance to be updated.
first_colThe zero indexed first column.
last_colThe zero indexed last column.
widthThe width of the column(s).
formatA pointer to a Format instance or NULL.
optionsOptional row parameters: hidden, level, collapsed.

The worksheet_set_column_opt() function is the same as worksheet_set_column() with an additional options parameter.

The options parameter is a lxw_row_col_options struct. It has the following members but currently only the hidden property is supported:

  • hidden
  • level
  • collapsed

The "hidden" option is used to hide a column. This can be used, for example, to hide intermediary steps in a complicated calculation:

lxw_row_col_options options = {.hidden = 1, .level = 0, .collapsed = 0};
worksheet_set_column_opt(worksheet, COLS("A:A"), 8.43, NULL, &options);
Examples:
hide_row_col.c.
lxw_error worksheet_insert_image ( lxw_worksheet worksheet,
lxw_row_t  row,
lxw_col_t  col,
const char *  filename 
)
Parameters
worksheetPointer to a lxw_worksheet instance to be updated.
rowThe zero indexed row number.
colThe zero indexed column number.
filenameThe image filename, with path if required.
Returns
A lxw_error code.

This function can be used to insert a image into a worksheet. The image can be in PNG, JPEG or BMP format:

worksheet_insert_image(worksheet, 2, 1, "logo.png");
insert_image.png

The worksheet_insert_image_opt() function takes additional optional parameters to position and scale the image, see below.

Note: The scaling of a image may be affected if is crosses a row that has its default height changed due to a font that is larger than the default font size or that has text wrapping turned on. To avoid this you should explicitly set the height of the row using worksheet_set_row() if it crosses an inserted image.

BMP images are only supported for backward compatibility. In general it is best to avoid BMP images since they aren't compressed. If used, BMP images must be 24 bit, true color, bitmaps.

Examples:
demo.c, and images.c.
lxw_error worksheet_insert_image_opt ( lxw_worksheet worksheet,
lxw_row_t  row,
lxw_col_t  col,
const char *  filename,
lxw_image_options options 
)
Parameters
worksheetPointer to a lxw_worksheet instance to be updated.
rowThe zero indexed row number.
colThe zero indexed column number.
filenameThe image filename, with path if required.
optionsOptional image parameters.
Returns
A lxw_error code.

The worksheet_insert_image_opt() function is like worksheet_insert_image() function except that it takes an optional lxw_image_options struct to scale and position the image:

lxw_image_options options = {.x_offset = 30, .y_offset = 10,
.x_scale = 0.5, .y_scale = 0.5};
worksheet_insert_image_opt(worksheet, 2, 1, "logo.png", &options);
insert_image_opt.png
Note
See the notes about row scaling and BMP images in worksheet_insert_image() above.
Examples:
images.c.
lxw_error worksheet_insert_chart ( lxw_worksheet worksheet,
lxw_row_t  row,
lxw_col_t  col,
lxw_chart chart 
)
Parameters
worksheetPointer to a lxw_worksheet instance to be updated.
rowThe zero indexed row number.
colThe zero indexed column number.
chartA lxw_chart object created via workbook_add_chart().
Returns
A lxw_error code.

The worksheet_insert_chart() can be used to insert a chart into a worksheet. The chart object must be created first using the workbook_add_chart() function and configured using the chart.h functions.

// Create a chart object.
// Add a data series to the chart.
chart_add_series(chart, NULL, "=Sheet1!$A$1:$A$6");
// Insert the chart into the worksheet
worksheet_insert_chart(worksheet, 0, 2, chart);
chart_working.png

Note:

A chart may only be inserted into a worksheet once. If several similar charts are required then each one must be created separately with worksheet_insert_chart().

Examples:
chart.c, chart_area.c, chart_bar.c, chart_clustered.c, chart_column.c, chart_data_table.c, chart_data_tools.c, chart_doughnut.c, chart_fonts.c, chart_line.c, chart_pattern.c, chart_pie.c, chart_radar.c, chart_scatter.c, and chart_styles.c.
lxw_error worksheet_insert_chart_opt ( lxw_worksheet worksheet,
lxw_row_t  row,
lxw_col_t  col,
lxw_chart chart,
lxw_image_options user_options 
)
Parameters
worksheetPointer to a lxw_worksheet instance to be updated.
rowThe zero indexed row number.
colThe zero indexed column number.
chartA lxw_chart object created via workbook_add_chart().
user_optionsOptional chart parameters.
Returns
A lxw_error code.

The worksheet_insert_chart_opt() function is like worksheet_insert_chart() function except that it takes an optional lxw_image_options struct to scale and position the image of the chart:

lxw_image_options options = {.x_offset = 30, .y_offset = 10,
.x_scale = 0.5, .y_scale = 0.75};
worksheet_insert_chart_opt(worksheet, 0, 2, chart, &options);
chart_line_opt.png

The lxw_image_options struct is the same struct used in worksheet_insert_image_opt() to position and scale images.

lxw_error worksheet_merge_range ( lxw_worksheet worksheet,
lxw_row_t  first_row,
lxw_col_t  first_col,
lxw_row_t  last_row,
lxw_col_t  last_col,
const char *  string,
lxw_format format 
)
Parameters
worksheetPointer to a lxw_worksheet instance to be updated.
first_rowThe first row of the range. (All zero indexed.)
first_colThe first column of the range.
last_rowThe last row of the range.
last_colThe last col of the range.
stringString to write to the merged range.
formatA pointer to a Format instance or NULL.
Returns
A lxw_error code.

The worksheet_merge_range() function allows cells to be merged together so that they act as a single area.

Excel generally merges and centers cells at same time. To get similar behavior with libxlsxwriter you need to apply a Format object with the appropriate alignment:

lxw_format *merge_format = workbook_add_format(workbook);
worksheet_merge_range(worksheet, 1, 1, 1, 3, "Merged Range", merge_format);

It is possible to apply other formatting to the merged cells as well:

format_set_bold (merge_format);
format_set_bg_color(merge_format, 0xD7E4BC);
worksheet_merge_range(worksheet, 2, 1, 3, 3, "Merged Range", merge_format);
merge.png

The worksheet_merge_range() function writes a char* string using worksheet_write_string(). In order to write other data types, such as a number or a formula, you can overwrite the first cell with a call to one of the other write functions. The same Format should be used as was used in the merged range.

// First write a range with a blank string.
worksheet_merge_range (worksheet, 1, 1, 1, 3, "", format);
// Then overwrite the first cell with a number.
worksheet_write_number(worksheet, 1, 1, 123, format);
Note
Merged ranges generally don’t work in libxlsxwriter when the Workbook lxw_workbook_options constant_memory mode is enabled.
Examples:
merge_range.c.
lxw_error worksheet_autofilter ( lxw_worksheet worksheet,
lxw_row_t  first_row,
lxw_col_t  first_col,
lxw_row_t  last_row,
lxw_col_t  last_col 
)
Parameters
worksheetPointer to a lxw_worksheet instance to be updated.
first_rowThe first row of the range. (All zero indexed.)
first_colThe first column of the range.
last_rowThe last row of the range.
last_colThe last col of the range.
Returns
A lxw_error code.

The worksheet_autofilter() function allows an autofilter to be added to a worksheet.

An autofilter is a way of adding drop down lists to the headers of a 2D range of worksheet data. This allows users to filter the data based on simple criteria so that some data is shown and some is hidden.

autofilter.png

To add an autofilter to a worksheet:

worksheet_autofilter(worksheet, 0, 0, 50, 3);
// Same as above using the RANGE() macro.
worksheet_autofilter(worksheet, RANGE("A1:D51"));

Note: it isn't currently possible to apply filter conditions to the autofilter.

Examples:
autofilter.c.
void worksheet_activate ( lxw_worksheet worksheet)
Parameters
worksheetPointer to a lxw_worksheet instance to be updated.

The worksheet_activate() function is used to specify which worksheet is initially visible in a multi-sheet workbook:

lxw_worksheet *worksheet1 = workbook_add_worksheet(workbook, NULL);
lxw_worksheet *worksheet2 = workbook_add_worksheet(workbook, NULL);
lxw_worksheet *worksheet3 = workbook_add_worksheet(workbook, NULL);
worksheet_activate(worksheet3);
worksheet_activate.png

More than one worksheet can be selected via the worksheet_select() function, see below, however only one worksheet can be active.

The default active worksheet is the first worksheet.

void worksheet_select ( lxw_worksheet worksheet)
Parameters
worksheetPointer to a lxw_worksheet instance to be updated.

The worksheet_select() function is used to indicate that a worksheet is selected in a multi-sheet workbook:

worksheet_activate(worksheet1);
worksheet_select(worksheet2);
worksheet_select(worksheet3);

A selected worksheet has its tab highlighted. Selecting worksheets is a way of grouping them together so that, for example, several worksheets could be printed in one go. A worksheet that has been activated via the worksheet_activate() function will also appear as selected.

void worksheet_hide ( lxw_worksheet worksheet)
Parameters
worksheetPointer to a lxw_worksheet instance to be updated.

The worksheet_hide() function is used to hide a worksheet:

worksheet_hide(worksheet2);

You may wish to hide a worksheet in order to avoid confusing a user with intermediate data or calculations.

hide_sheet.png

A hidden worksheet can not be activated or selected so this function is mutually exclusive with the worksheet_activate() and worksheet_select() functions. In addition, since the first worksheet will default to being the active worksheet, you cannot hide the first worksheet without activating another sheet:

worksheet_activate(worksheet2);
worksheet_hide(worksheet1);
Examples:
hide_sheet.c.
void worksheet_set_first_sheet ( lxw_worksheet worksheet)
Parameters
worksheetPointer to a lxw_worksheet instance to be updated.

The worksheet_activate() function determines which worksheet is initially selected. However, if there are a large number of worksheets the selected worksheet may not appear on the screen. To avoid this you can select the leftmost visible worksheet tab using worksheet_set_first_sheet():

worksheet_set_first_sheet(worksheet19); // First visible worksheet tab.
worksheet_activate(worksheet20); // First visible worksheet.

This function is not required very often. The default value is the first worksheet.

void worksheet_freeze_panes ( lxw_worksheet worksheet,
lxw_row_t  row,
lxw_col_t  col 
)
Parameters
worksheetPointer to a lxw_worksheet instance to be updated.
rowThe cell row (zero indexed).
colThe cell column (zero indexed).

The worksheet_freeze_panes() function can be used to divide a worksheet into horizontal or vertical regions known as panes and to "freeze" these panes so that the splitter bars are not visible.

The parameters row and col are used to specify the location of the split. It should be noted that the split is specified at the top or left of a cell and that the function uses zero based indexing. Therefore to freeze the first row of a worksheet it is necessary to specify the split at row 2 (which is 1 as the zero-based index).

You can set one of the row and col parameters as zero if you do not want either a vertical or horizontal split.

Examples:

worksheet_freeze_panes(worksheet1, 1, 0); // Freeze the first row.
worksheet_freeze_panes(worksheet2, 0, 1); // Freeze the first column.
worksheet_freeze_panes(worksheet3, 1, 1); // Freeze first row/column.
Examples:
panes.c.
void worksheet_split_panes ( lxw_worksheet worksheet,
double  vertical,
double  horizontal 
)
Parameters
worksheetPointer to a lxw_worksheet instance to be updated.
verticalThe position for the vertical split.
horizontalThe position for the horizontal split.

The worksheet_split_panes() function can be used to divide a worksheet into horizontal or vertical regions known as panes. This function is different from the worksheet_freeze_panes() function in that the splits between the panes will be visible to the user and each pane will have its own scroll bars.

The parameters vertical and horizontal are used to specify the vertical and horizontal position of the split. The units for vertical and horizontal are the same as those used by Excel to specify row height and column width. However, the vertical and horizontal units are different from each other. Therefore you must specify the vertical and horizontal parameters in terms of the row heights and column widths that you have set or the default values which are 15 for a row and 8.43 for a column.

Examples:

worksheet_split_panes(worksheet1, 15, 0); // First row.
worksheet_split_panes(worksheet2, 0, 8.43); // First column.
worksheet_split_panes(worksheet3, 15, 8.43); // First row and column.
Examples:
panes.c.
void worksheet_set_selection ( lxw_worksheet worksheet,
lxw_row_t  first_row,
lxw_col_t  first_col,
lxw_row_t  last_row,
lxw_col_t  last_col 
)
Parameters
worksheetPointer to a lxw_worksheet instance to be updated.
first_rowThe first row of the range. (All zero indexed.)
first_colThe first column of the range.
last_rowThe last row of the range.
last_colThe last col of the range.

The worksheet_set_selection() function can be used to specify which cell or range of cells is selected in a worksheet: The most common requirement is to select a single cell, in which case the first_ and last_ parameters should be the same.

The active cell within a selected range is determined by the order in which first_ and last_ are specified.

Examples:

worksheet_set_selection(worksheet1, 3, 3, 3, 3); // Cell D4.
worksheet_set_selection(worksheet2, 3, 3, 6, 6); // Cells D4 to G7.
worksheet_set_selection(worksheet3, 6, 6, 3, 3); // Cells G7 to D4.
worksheet_set_selection(worksheet5, RANGE("D4:G7")); // Using the RANGE macro.
Examples:
panes.c.
void worksheet_set_landscape ( lxw_worksheet worksheet)
Parameters
worksheetPointer to a lxw_worksheet instance to be updated.

This function is used to set the orientation of a worksheet's printed page to landscape:

void worksheet_set_portrait ( lxw_worksheet worksheet)
Parameters
worksheetPointer to a lxw_worksheet instance to be updated.

This function is used to set the orientation of a worksheet's printed page to portrait. The default worksheet orientation is portrait, so this function isn't generally required:

void worksheet_set_page_view ( lxw_worksheet worksheet)
Parameters
worksheetPointer to a lxw_worksheet instance to be updated.

This function is used to display the worksheet in "Page View/Layout" mode:

void worksheet_set_paper ( lxw_worksheet worksheet,
uint8_t  paper_type 
)
Parameters
worksheetPointer to a lxw_worksheet instance to be updated.
paper_typeThe Excel paper format type.

This function is used to set the paper format for the printed output of a worksheet. The following paper styles are available:

Index Paper format Paper size
0 Printer default Printer default
1 Letter 8 1/2 x 11 in
2 Letter Small 8 1/2 x 11 in
3 Tabloid 11 x 17 in
4 Ledger 17 x 11 in
5 Legal 8 1/2 x 14 in
6 Statement 5 1/2 x 8 1/2 in
7 Executive 7 1/4 x 10 1/2 in
8 A3 297 x 420 mm
9 A4 210 x 297 mm
10 A4 Small 210 x 297 mm
11 A5 148 x 210 mm
12 B4 250 x 354 mm
13 B5 182 x 257 mm
14 Folio 8 1/2 x 13 in
15 Quarto 215 x 275 mm
16 10x14 in
17 11x17 in
18 Note 8 1/2 x 11 in
19 Envelope 9 3 7/8 x 8 7/8
20 Envelope 10 4 1/8 x 9 1/2
21 Envelope 11 4 1/2 x 10 3/8
22 Envelope 12 4 3/4 x 11
23 Envelope 14 5 x 11 1/2
24 C size sheet
25 D size sheet
26 E size sheet
27 Envelope DL 110 x 220 mm
28 Envelope C3 324 x 458 mm
29 Envelope C4 229 x 324 mm
30 Envelope C5 162 x 229 mm
31 Envelope C6 114 x 162 mm
32 Envelope C65 114 x 229 mm
33 Envelope B4 250 x 353 mm
34 Envelope B5 176 x 250 mm
35 Envelope B6 176 x 125 mm
36 Envelope 110 x 230 mm
37 Monarch 3.875 x 7.5 in
38 Envelope 3 5/8 x 6 1/2 in
39 Fanfold 14 7/8 x 11 in
40 German Std Fanfold 8 1/2 x 12 in
41 German Legal Fanfold 8 1/2 x 13 in

Note, it is likely that not all of these paper types will be available to the end user since it will depend on the paper formats that the user's printer supports. Therefore, it is best to stick to standard paper types:

worksheet_set_paper(worksheet1, 1); // US Letter
worksheet_set_paper(worksheet2, 9); // A4

If you do not specify a paper type the worksheet will print using the printer's default paper style.

void worksheet_set_margins ( lxw_worksheet worksheet,
double  left,
double  right,
double  top,
double  bottom 
)
Parameters
worksheetPointer to a lxw_worksheet instance to be updated.
leftLeft margin in inches. Excel default is 0.7.
rightRight margin in inches. Excel default is 0.7.
topTop margin in inches. Excel default is 0.75.
bottomBottom margin in inches. Excel default is 0.75.

The worksheet_set_margins() function is used to set the margins of the worksheet when it is printed. The units are in inches. Specifying -1 for any parameter will give the default Excel value as shown above.

worksheet_set_margins(worksheet, 1.3, 1.2, -1, -1);
lxw_error worksheet_set_header ( lxw_worksheet worksheet,
const char *  string 
)
Parameters
worksheetPointer to a lxw_worksheet instance to be updated.
stringThe header string.
Returns
A lxw_error code.

Headers and footers are generated using a string which is a combination of plain text and control characters.

The available control character are:

Control Category Description
&L Justification Left
&C Center
&R Right
&P Information Page number
&N Total number of pages
&D Date
&T Time
&F File name
&A Worksheet name
&Z Workbook path
&fontsize Font Font size
&"font,style" Font name and style
&U Single underline
&E Double underline
&S Strikethrough
&X Superscript
&Y Subscript

Text in headers and footers can be justified (aligned) to the left, center and right by prefixing the text with the control characters &L, &C and &R.

For example (with ASCII art representation of the results):

worksheet_set_header(worksheet, "&LHello");
---------------------------------------------------------------
| |
| Hello |
| |
worksheet_set_header(worksheet, "&CHello");
---------------------------------------------------------------
| |
| Hello |
| |
worksheet_set_header(worksheet, "&RHello");
---------------------------------------------------------------
| |
| Hello |
| |

For simple text, if you do not specify any justification the text will be centered. However, you must prefix the text with &C if you specify a font name or any other formatting:

worksheet_set_header(worksheet, "Hello");
---------------------------------------------------------------
| |
| Hello |
| |

You can have text in each of the justification regions:

worksheet_set_header(worksheet, "&LCiao&CBello&RCielo");
---------------------------------------------------------------
| |
| Ciao Bello Cielo |
| |

The information control characters act as variables that Excel will update as the workbook or worksheet changes. Times and dates are in the users default format:

worksheet_set_header(worksheet, "&CPage &P of &N");
---------------------------------------------------------------
| |
| Page 1 of 6 |
| |
worksheet_set_header(worksheet, "&CUpdated at &T");
---------------------------------------------------------------
| |
| Updated at 12:30 PM |
| |

You can specify the font size of a section of the text by prefixing it with the control character &n where n is the font size:

worksheet_set_header(worksheet1, "&C&30Hello Big");
worksheet_set_header(worksheet2, "&C&10Hello Small");

You can specify the font of a section of the text by prefixing it with the control sequence &"font,style" where fontname is a font name such as Windows font descriptions: "Regular", "Italic", "Bold" or "Bold Italic": "Courier New" or "Times New Roman" and style is one of the standard

worksheet_set_header(worksheet1, "&C&\"Courier New,Italic\"Hello");
worksheet_set_header(worksheet2, "&C&\"Courier New,Bold Italic\"Hello");
worksheet_set_header(worksheet3, "&C&\"Times New Roman,Regular\"Hello");

It is possible to combine all of these features together to create sophisticated headers and footers. As an aid to setting up complicated headers and footers you can record a page set-up as a macro in Excel and look at the format strings that VBA produces. Remember however that VBA uses two double quotes "" to indicate a single double quote. For the last example above the equivalent VBA code looks like this:

.LeftHeader = ""
.CenterHeader = "&""Times New Roman,Regular""Hello"
.RightHeader = ""

Alternatively you can inspect the header and footer strings in an Excel file by unzipping it and grepping the XML sub-files. The following shows how to do that using libxml's xmllint to format the XML for clarity:

$ unzip myfile.xlsm -d myfile
$ xmllint --format `find myfile -name "*.xml" | xargs` | egrep "Header|Footer"
<headerFooter scaleWithDoc="0">
<oddHeader>&amp;L&amp;P</oddHeader>
</headerFooter>

Note that in this case you need to unescape the Html. In the above example the header string would be &L&P.

To include a single literal ampersand & in a header or footer you should use a double ampersand &&:

worksheet_set_header(worksheet, "&CCuriouser && Curiouser - Attorneys at Law");

Note, the header or footer string must be less than 255 characters. Strings longer than this will not be written.

Examples:
headers_footers.c.
lxw_error worksheet_set_footer ( lxw_worksheet worksheet,
const char *  string 
)
Parameters
worksheetPointer to a lxw_worksheet instance to be updated.
stringThe footer string.
Returns
A lxw_error code.

The syntax of this function is the same as worksheet_set_header().

Examples:
headers_footers.c.
lxw_error worksheet_set_header_opt ( lxw_worksheet worksheet,
const char *  string,
lxw_header_footer_options options 
)
Parameters
worksheetPointer to a lxw_worksheet instance to be updated.
stringThe header string.
optionsHeader options.
Returns
A lxw_error code.

The syntax of this function is the same as worksheet_set_header() with an additional parameter to specify options for the header.

Currently, the only available option is the header margin:

lxw_header_footer_options header_options = { 0.2 };
worksheet_set_header_opt(worksheet, "Some text", &header_options);
lxw_error worksheet_set_footer_opt ( lxw_worksheet worksheet,
const char *  string,
lxw_header_footer_options options 
)
Parameters
worksheetPointer to a lxw_worksheet instance to be updated.
stringThe footer string.
optionsFooter options.
Returns
A lxw_error code.

The syntax of this function is the same as worksheet_set_header_opt().

lxw_error worksheet_set_h_pagebreaks ( lxw_worksheet worksheet,
lxw_row_t  breaks[] 
)
Parameters
worksheetPointer to a lxw_worksheet instance to be updated.
breaksArray of page breaks.
Returns
A lxw_error code.

The worksheet_set_h_pagebreaks() function adds horizontal page breaks to a worksheet. A page break causes all the data that follows it to be printed on the next page. Horizontal page breaks act between rows.

The function takes an array of one or more page breaks. The type of the array data is lxw_row_t and the last element of the array must be 0:

lxw_row_t breaks1[] = {20, 0}; // 1 page break. Zero indicates the end.
lxw_row_t breaks2[] = {20, 40, 60, 80, 0};
worksheet_set_h_pagebreaks(worksheet1, breaks1);
worksheet_set_h_pagebreaks(worksheet2, breaks2);

To create a page break between rows 20 and 21 you must specify the break at row 21. However in zero index notation this is actually row 20:

// Break between row 20 and 21.
lxw_row_t breaks[] = {20, 0};
worksheet_set_h_pagebreaks(worksheet, breaks);

There is an Excel limitation of 1023 horizontal page breaks per worksheet.

Note: If you specify the "fit to page" option via the worksheet_fit_to_pages() function it will override all manual page breaks.

Examples:
headers_footers.c.
lxw_error worksheet_set_v_pagebreaks ( lxw_worksheet worksheet,
lxw_col_t  breaks[] 
)
Parameters
worksheetPointer to a lxw_worksheet instance to be updated.
breaksArray of page breaks.
Returns
A lxw_error code.

The worksheet_set_v_pagebreaks() function adds vertical page breaks to a worksheet. A page break causes all the data that follows it to be printed on the next page. Vertical page breaks act between columns.

The function takes an array of one or more page breaks. The type of the array data is lxw_col_t and the last element of the array must be 0:

lxw_col_t breaks1[] = {20, 0}; // 1 page break. Zero indicates the end.
lxw_col_t breaks2[] = {20, 40, 60, 80, 0};
worksheet_set_v_pagebreaks(worksheet1, breaks1);
worksheet_set_v_pagebreaks(worksheet2, breaks2);

To create a page break between columns 20 and 21 you must specify the break at column 21. However in zero index notation this is actually column 20:

// Break between column 20 and 21.
lxw_col_t breaks[] = {20, 0};
worksheet_set_v_pagebreaks(worksheet, breaks);

There is an Excel limitation of 1023 vertical page breaks per worksheet.

Note: If you specify the "fit to page" option via the worksheet_fit_to_pages() function it will override all manual page breaks.

void worksheet_print_across ( lxw_worksheet worksheet)
Parameters
worksheetPointer to a lxw_worksheet instance to be updated.

The worksheet_print_across() function is used to change the default print direction. This is referred to by Excel as the sheet "page order":

The default page order is shown below for a worksheet that extends over 4 pages. The order is called "down then across":

[1] [3]
[2] [4]

However, by using the print_across function the print order will be changed to "across then down":

[1] [2]
[3] [4]
void worksheet_set_zoom ( lxw_worksheet worksheet,
uint16_t  scale 
)
Parameters
worksheetPointer to a lxw_worksheet instance to be updated.
scaleWorksheet zoom factor.

Set the worksheet zoom factor in the range 10 <= zoom <= 400:

worksheet_set_zoom(worksheet1, 50);
worksheet_set_zoom(worksheet2, 75);
worksheet_set_zoom(worksheet3, 300);
worksheet_set_zoom(worksheet4, 400);

The default zoom factor is 100. It isn't possible to set the zoom to "Selection" because it is calculated by Excel at run-time.

Note, worksheet_zoom() does not affect the scale of the printed page. For that you should use worksheet_set_print_scale().

Examples:
chart_styles.c.
void worksheet_gridlines ( lxw_worksheet worksheet,
uint8_t  option 
)
Parameters
worksheetPointer to a lxw_worksheet instance to be updated.
optionGridline option.

Display or hide screen and print gridlines using one of the values of lxw_gridlines.

The Excel default is that the screen gridlines are on and the printed worksheet is off.

void worksheet_center_horizontally ( lxw_worksheet worksheet)
Parameters
worksheetPointer to a lxw_worksheet instance to be updated.

Center the worksheet data horizontally between the margins on the printed page:

void worksheet_center_vertically ( lxw_worksheet worksheet)
Parameters
worksheetPointer to a lxw_worksheet instance to be updated.

Center the worksheet data vertically between the margins on the printed page:

void worksheet_print_row_col_headers ( lxw_worksheet worksheet)
Parameters
worksheetPointer to a lxw_worksheet instance to be updated.

When printing a worksheet from Excel the row and column headers (the row numbers on the left and the column letters at the top) aren't printed by default.

This function sets the printer option to print these headers:

lxw_error worksheet_repeat_rows ( lxw_worksheet worksheet,
lxw_row_t  first_row,
lxw_row_t  last_row 
)
Parameters
worksheetPointer to a lxw_worksheet instance to be updated.
first_rowFirst row of repeat range.
last_rowLast row of repeat range.
Returns
A lxw_error code.

For large Excel documents it is often desirable to have the first row or rows of the worksheet print out at the top of each page.

This can be achieved by using this function. The parameters first_row and last_row are zero based:

worksheet_repeat_rows(worksheet, 0, 0); // Repeat the first row.
worksheet_repeat_rows(worksheet, 0, 1); // Repeat the first two rows.
lxw_error worksheet_repeat_columns ( lxw_worksheet worksheet,
lxw_col_t  first_col,
lxw_col_t  last_col 
)
Parameters
worksheetPointer to a lxw_worksheet instance to be updated.
first_colFirst column of repeat range.
last_colLast column of repeat range.
Returns
A lxw_error code.

For large Excel documents it is often desirable to have the first column or columns of the worksheet print out at the left of each page.

This can be achieved by using this function. The parameters first_col and last_col are zero based:

worksheet_repeat_columns(worksheet, 0, 0); // Repeat the first col.
worksheet_repeat_columns(worksheet, 0, 1); // Repeat the first two cols.
lxw_error worksheet_print_area ( lxw_worksheet worksheet,
lxw_row_t  first_row,
lxw_col_t  first_col,
lxw_row_t  last_row,
lxw_col_t  last_col 
)
Parameters
worksheetPointer to a lxw_worksheet instance to be updated.
first_rowThe first row of the range. (All zero indexed.)
first_colThe first column of the range.
last_rowThe last row of the range.
last_colThe last col of the range.
Returns
A lxw_error code.

This function is used to specify the area of the worksheet that will be printed. The RANGE() macro is often convenient for this.

worksheet_print_area(worksheet, 0, 0, 41, 10); // A1:K42.
// Same as:
worksheet_print_area(worksheet, RANGE("A1:K42"));

In order to set a row or column range you must specify the entire range:

worksheet_print_area(worksheet, RANGE("A1:H1048576")); // Same as A:H.
void worksheet_fit_to_pages ( lxw_worksheet worksheet,
uint16_t  width,
uint16_t  height 
)
Parameters
worksheetPointer to a lxw_worksheet instance to be updated.
widthNumber of pages horizontally.
heightNumber of pages vertically.

The worksheet_fit_to_pages() function is used to fit the printed area to a specific number of pages both vertically and horizontally. If the printed area exceeds the specified number of pages it will be scaled down to fit. This ensures that the printed area will always appear on the specified number of pages even if the page size or margins change:

worksheet_fit_to_pages(worksheet1, 1, 1); // Fit to 1x1 pages.
worksheet_fit_to_pages(worksheet2, 2, 1); // Fit to 2x1 pages.
worksheet_fit_to_pages(worksheet3, 1, 2); // Fit to 1x2 pages.

The print area can be defined using the worksheet_print_area() function as described above.

A common requirement is to fit the printed output to n pages wide but have the height be as long as necessary. To achieve this set the height to zero:

// 1 page wide and as long as necessary.
worksheet_fit_to_pages(worksheet, 1, 0);

Note:

  • Although it is valid to use both worksheet_fit_to_pages() and worksheet_set_print_scale() on the same worksheet Excel only allows one of these options to be active at a time. The last function call made will set the active option.
  • The worksheet_fit_to_pages() function will override any manual page breaks that are defined in the worksheet.
  • When using worksheet_fit_to_pages() it may also be required to set the printer paper size using worksheet_set_paper() or else Excel will default to "US Letter".
void worksheet_set_start_page ( lxw_worksheet worksheet,
uint16_t  start_page 
)
Parameters
worksheetPointer to a lxw_worksheet instance to be updated.
start_pageStarting page number.

The worksheet_set_start_page() function is used to set the number of the starting page when the worksheet is printed out:

// Start print from page 2.
void worksheet_set_print_scale ( lxw_worksheet worksheet,
uint16_t  scale 
)
Parameters
worksheetPointer to a lxw_worksheet instance to be updated.
scalePrint scale of worksheet to be printed.

This function sets the scale factor of the printed page. The Scale factor must be in the range 10 <= scale <= 400:

worksheet_set_print_scale(worksheet1, 75);
worksheet_set_print_scale(worksheet2, 400);

The default scale factor is 100. Note, worksheet_set_print_scale() does not affect the scale of the visible page in Excel. For that you should use worksheet_set_zoom().

Note that although it is valid to use both worksheet_fit_to_pages() and worksheet_set_print_scale() on the same worksheet Excel only allows one of these options to be active at a time. The last function call made will set the active option.

void worksheet_right_to_left ( lxw_worksheet worksheet)
Parameters
worksheetPointer to a lxw_worksheet instance to be updated.

The worksheet_right_to_left() function is used to change the default direction of the worksheet from left-to-right, with the A1 cell in the top left, to right-to-left, with the A1 cell in the top right.

This is useful when creating Arabic, Hebrew or other near or far eastern worksheets that use right-to-left as the default direction.

void worksheet_hide_zero ( lxw_worksheet worksheet)
Parameters
worksheetPointer to a lxw_worksheet instance to be updated.

The worksheet_hide_zero() function is used to hide any zero values that appear in cells:

worksheet_hide_zero(worksheet1);
void worksheet_set_tab_color ( lxw_worksheet worksheet,
lxw_color_t  color 
)
Parameters
worksheetPointer to a lxw_worksheet instance to be updated.
colorThe tab color.

The worksheet_set_tab_color() function is used to change the color of the worksheet tab:

worksheet_set_tab_color(worksheet3, 0xFF9900); // Orange.

The color should be an RGB integer value, see Working with Colors.

Examples:
tab_colors.c.
void worksheet_protect ( lxw_worksheet worksheet,
const char *  password,
lxw_protection options 
)
Parameters
worksheetPointer to a lxw_worksheet instance to be updated.
passwordA worksheet password.
optionsWorksheet elements to protect.

The worksheet_protect() function protects worksheet elements from modification:

worksheet_protect(worksheet, "Some Password", options);

The password and lxw_protection pointer are both optional:

worksheet_protect(worksheet1, NULL, NULL);
worksheet_protect(worksheet2, NULL, my_options);
worksheet_protect(worksheet3, "password", NULL);
worksheet_protect(worksheet4, "password", my_options);

Passing a NULL password is the same as turning on protection without a password. Passing a NULL password and NULL options, or any other combination has the effect of enabling a cell's locked and hidden properties if they have been set.

A locked cell cannot be edited and this property is on by default for all cells. A hidden cell will display the results of a formula but not the formula itself. These properties can be set using the format_set_unlocked() and format_set_hidden() format functions.

You can specify which worksheet elements you wish to protect by passing a lxw_protection pointer in the options argument with any or all of the following members set:

no_select_locked_cells
no_select_unlocked_cells
format_cells
format_columns
format_rows
insert_columns
insert_rows
insert_hyperlinks
delete_columns
delete_rows
sort
autofilter
pivot_tables
scenarios
objects

All parameters are off by default. Individual elements can be protected as follows:

lxw_protection options = {
.format_cells = 1,
.insert_hyperlinks = 1,
.insert_rows = 1,
.delete_rows = 1,
.insert_columns = 1,
.delete_columns = 1,
};
worksheet_protect(worksheet, NULL, &options);

See also the format_set_unlocked() and format_set_hidden() format functions.

Note: Worksheet level passwords in Excel offer very weak protection. They don't encrypt your data and are very easy to deactivate. Full workbook encryption is not supported by libxlsxwriter since it requires a completely different file format and would take several man months to implement.

Examples:
worksheet_protection.c.
void worksheet_set_default_row ( lxw_worksheet worksheet,
double  height,
uint8_t  hide_unused_rows 
)
Parameters
worksheetPointer to a lxw_worksheet instance to be updated.
heightDefault row height.
hide_unused_rowsHide unused cells.

The worksheet_set_default_row() function is used to set Excel default row properties such as the default height and the option to hide unused rows. These parameters are an optimization used by Excel to set row properties without generating a very large file with an entry for each row.

To set the default row height:

To hide unused rows:

Note, in the previous case we use the default height LXW_DEF_ROW_HEIGHT = 15 so the the height remains unchanged.

Examples:
hide_row_col.c.

Typedef Documentation

Options struct for the worksheet_set_column() and worksheet_set_row() functions.

It has the following members but currently only the hidden property is supported:

  • hidden
  • level
  • collapsed

Options for modifying images inserted via worksheet_insert_image_opt().

Optional parameters used in the worksheet_set_header_opt() and worksheet_set_footer_opt() functions.

typedef struct lxw_worksheet lxw_worksheet

The members of the lxw_worksheet struct aren't modified directly. Instead the worksheet properties are set by calling the functions shown in worksheet.h.

Macro Definition Documentation

#define LXW_DEF_COL_WIDTH   (double)8.43

Default column width in Excel

#define LXW_DEF_ROW_HEIGHT   (double)15.0

Default row height in Excel

Enumeration Type Documentation

Gridline options using in worksheet_gridlines().

Enumerator
LXW_HIDE_ALL_GRIDLINES 

Hide screen and print gridlines.

LXW_SHOW_SCREEN_GRIDLINES 

Show screen gridlines.

LXW_SHOW_PRINT_GRIDLINES 

Show print gridlines.

LXW_SHOW_ALL_GRIDLINES 

Show screen and print gridlines.

Data Structures

struct  lxw_row_col_options
 Options for rows and columns. More...
 
struct  lxw_image_options
 Options for inserted images. More...
 
struct  lxw_header_footer_options
 Header and footer options. More...
 
struct  lxw_protection
 Worksheet protection options. More...
 
struct  lxw_worksheet
 Struct to represent an Excel worksheet. More...
 

Typedefs

typedef struct lxw_row_col_options lxw_row_col_options
 Options for rows and columns. More...
 
typedef struct lxw_image_options lxw_image_options
 Options for inserted images. More...
 
typedef struct
lxw_header_footer_options 
lxw_header_footer_options
 Header and footer options. More...
 
typedef struct lxw_protection lxw_protection
 Worksheet protection options.
 
typedef struct lxw_worksheet lxw_worksheet
 Struct to represent an Excel worksheet. More...
 

Enumerations

enum  lxw_gridlines { LXW_HIDE_ALL_GRIDLINES = 0, LXW_SHOW_SCREEN_GRIDLINES, LXW_SHOW_PRINT_GRIDLINES, LXW_SHOW_ALL_GRIDLINES }
 

Macros

#define LXW_DEF_COL_WIDTH   (double)8.43
 
#define LXW_DEF_ROW_HEIGHT   (double)15.0