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

Description

The Workbook is the main object exposed by the libxlsxwriter library. It represents the entire spreadsheet as you see it in Excel and internally it represents the Excel file as it is written on disk.

#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);
}
workbook01.png

Functions

lxw_workbookworkbook_new (const char *filename)
 Create a new workbook object. More...
 
lxw_workbookworkbook_new_opt (const char *filename, lxw_workbook_options *options)
 Create a new workbook object, and set the workbook options. More...
 
lxw_worksheetworkbook_add_worksheet (lxw_workbook *workbook, const char *sheetname)
 Add a new worksheet to a workbook. More...
 
lxw_formatworkbook_add_format (lxw_workbook *workbook)
 Create a new Format object to formats cells in worksheets. More...
 
lxw_chartworkbook_add_chart (lxw_workbook *workbook, uint8_t chart_type)
 Create a new chart to be added to a worksheet: More...
 
lxw_error workbook_close (lxw_workbook *workbook)
 Close the Workbook object and write the XLSX file. More...
 
lxw_error workbook_set_properties (lxw_workbook *workbook, lxw_doc_properties *properties)
 Set the document properties such as Title, Author etc. More...
 
lxw_error workbook_set_custom_property_string (lxw_workbook *workbook, const char *name, const char *value)
 Set a custom document text property. More...
 
lxw_error workbook_set_custom_property_number (lxw_workbook *workbook, const char *name, double value)
 Set a custom document number property. More...
 
lxw_error workbook_set_custom_property_boolean (lxw_workbook *workbook, const char *name, uint8_t value)
 Set a custom document boolean property. More...
 
lxw_error workbook_set_custom_property_datetime (lxw_workbook *workbook, const char *name, lxw_datetime *datetime)
 Set a custom document date or time property. More...
 
lxw_error workbook_define_name (lxw_workbook *workbook, const char *name, const char *formula)
 Create a defined name in the workbook to use as a variable. More...
 
lxw_worksheetworkbook_get_worksheet_by_name (lxw_workbook *workbook, const char *name)
 Get a worksheet object from its name. More...
 
lxw_error workbook_validate_worksheet_name (lxw_workbook *workbook, const char *sheetname)
 Validate a worksheet name. More...
 

Function Documentation

lxw_workbook* workbook_new ( const char *  filename)
Parameters
filenameThe name of the new Excel file to create.
Returns
A lxw_workbook instance.

The workbook_new() constructor is used to create a new Excel workbook with a given filename:

lxw_workbook *workbook = workbook_new("filename.xlsx");

When specifying a filename it is recommended that you use an .xlsx extension or Excel will generate a warning when opening the file.

Examples:
anatomy.c, array_formula.c, autofilter.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, hello.c, hide_row_col.c, hide_sheet.c, hyperlinks.c, images.c, merge_range.c, panes.c, tab_colors.c, tutorial1.c, tutorial2.c, tutorial3.c, utf8.c, and worksheet_protection.c.
lxw_workbook* workbook_new_opt ( const char *  filename,
lxw_workbook_options options 
)
Parameters
filenameThe name of the new Excel file to create.
optionsWorkbook options.
Returns
A lxw_workbook instance.

This function is the same as the workbook_new() constructor but allows additional options to be set.

.tmpdir = "C:\\Temp"};
lxw_workbook *workbook = workbook_new_opt("filename.xlsx", &options);

The options that can be set via lxw_workbook_options are:

  • constant_memory: Reduces the amount of data stored in memory so that large files can be written efficiently.

    Note
    In this mode a row of data is written and then discarded when a cell in a new row is added via one of the worksheet_write_*() functions. Therefore, once this option is active, data should be written in sequential row order. For this reason the worksheet_merge_range() doesn't work in this mode. See also Constant Memory Mode.
  • tmpdir: libxlsxwriter stores workbook data in temporary files prior to assembling the final XLSX file. The temporary files are created in the system's temp directory. If the default temporary directory isn't accessible to your application, or doesn't contain enough space, you can specify an alternative location using the tempdir option.*

See Working with Memory and Performance for more details.

Examples:
constant_memory.c.
lxw_worksheet* workbook_add_worksheet ( lxw_workbook workbook,
const char *  sheetname 
)
Parameters
workbookPointer to a lxw_workbook instance.
sheetnameOptional worksheet name, defaults to Sheet1, etc.
Returns
A lxw_worksheet object.

The workbook_add_worksheet() function adds a new worksheet to a workbook:

At least one worksheet should be added to a new workbook: The Worksheet object is used to write data and configure a worksheet in the workbook.

The sheetname parameter is optional. If it is NULL the default Excel convention will be followed, i.e. Sheet1, Sheet2, etc.:

worksheet = workbook_add_worksheet(workbook, NULL ); // Sheet1
worksheet = workbook_add_worksheet(workbook, "Foglio2"); // Foglio2
worksheet = workbook_add_worksheet(workbook, "Data"); // Data
worksheet = workbook_add_worksheet(workbook, NULL ); // Sheet4
workbook02.png

The worksheet name must be a valid Excel worksheet name, i.e. it must be less than 32 character and it cannot contain any of the characters:

/ \ [ ] : * ?

In addition, you cannot use the same, case insensitive, sheetname for more than one worksheet.

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, 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, hello.c, hide_row_col.c, hide_sheet.c, hyperlinks.c, images.c, merge_range.c, panes.c, tab_colors.c, tutorial1.c, tutorial2.c, tutorial3.c, utf8.c, and worksheet_protection.c.
lxw_format* workbook_add_format ( lxw_workbook workbook)
Parameters
workbookPointer to a lxw_workbook instance.
Returns
A lxw_format instance.

The workbook_add_format() function can be used to create new Format objects which are used to apply formatting to a cell.

// Create the Format.
lxw_format *format = workbook_add_format(workbook);
// Set some of the format properties.
// Use the format to change the text format in a cell.
worksheet_write_string(worksheet, 0, 0, "Hello", format);

See the Format object and Working with Formats sections for more details about Format properties and how to set them.

Examples:
anatomy.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_times01.c, dates_and_times02.c, dates_and_times03.c, demo.c, format_font.c, format_num_format.c, hyperlinks.c, merge_range.c, panes.c, tutorial2.c, tutorial3.c, and worksheet_protection.c.
lxw_chart* workbook_add_chart ( lxw_workbook workbook,
uint8_t  chart_type 
)
Parameters
workbookPointer to a lxw_workbook instance.
chart_typeThe type of chart to be created. See lxw_chart_type.
Returns
A lxw_chart object.

The workbook_add_chart() function creates a new chart object that can be added to a worksheet:

// Create a chart object.
// Add data series to the chart.
chart_add_series(chart, NULL, "Sheet1!$A$1:$A$5");
chart_add_series(chart, NULL, "Sheet1!$B$1:$B$5");
chart_add_series(chart, NULL, "Sheet1!$C$1:$C$5");
// Insert the chart into the worksheet
worksheet_insert_chart(worksheet, CELL("B7"), chart);

The available chart types are defined in lxw_chart_type. The types of charts that are supported are:

Chart type Description
LXW_CHART_AREA Area chart.
LXW_CHART_AREA_STACKED Area chart - stacked.
LXW_CHART_AREA_STACKED_PERCENT Area chart - percentage stacked.
LXW_CHART_BAR Bar chart.
LXW_CHART_BAR_STACKED Bar chart - stacked.
LXW_CHART_BAR_STACKED_PERCENT Bar chart - percentage stacked.
LXW_CHART_COLUMN Column chart.
LXW_CHART_COLUMN_STACKED Column chart - stacked.
LXW_CHART_COLUMN_STACKED_PERCENT Column chart - percentage stacked.
LXW_CHART_DOUGHNUT Doughnut chart.
LXW_CHART_LINE Line chart.
LXW_CHART_PIE Pie chart.
LXW_CHART_SCATTER Scatter chart.
LXW_CHART_SCATTER_STRAIGHT Scatter chart - straight.
LXW_CHART_SCATTER_STRAIGHT_WITH_MARKERS Scatter chart - straight with markers.
LXW_CHART_SCATTER_SMOOTH Scatter chart - smooth.
LXW_CHART_SCATTER_SMOOTH_WITH_MARKERS Scatter chart - smooth with markers.
LXW_CHART_RADAR Radar chart.
LXW_CHART_RADAR_WITH_MARKERS Radar chart - with markers.
LXW_CHART_RADAR_FILLED Radar chart - filled.

See chart.h for details.

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 workbook_close ( lxw_workbook workbook)
Parameters
workbookPointer to a lxw_workbook instance.
Returns
A lxw_error.

The workbook_close() function closes a Workbook object, writes the Excel file to disk, frees any memory allocated internally to the Workbook and frees the object itself.

workbook_close(workbook);

The workbook_close() function returns any lxw_error error codes encountered when creating the Excel file. The error code can be returned from the program main or the calling function:

return workbook_close(workbook);
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, 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, hello.c, hide_row_col.c, hide_sheet.c, hyperlinks.c, images.c, merge_range.c, panes.c, tab_colors.c, tutorial1.c, tutorial2.c, tutorial3.c, utf8.c, and worksheet_protection.c.
lxw_error workbook_set_properties ( lxw_workbook workbook,
lxw_doc_properties properties 
)
Parameters
workbookPointer to a lxw_workbook instance.
propertiesDocument properties to set.
Returns
A lxw_error.

The workbook_set_properties function can be used to set the document properties of the Excel file created by libxlsxwriter. These properties are visible when you use the Office Button -> Prepare -> Properties option in Excel and are also available to external applications that read or index windows files.

The properties that can be set are:

  • title
  • subject
  • author
  • manager
  • company
  • category
  • keywords
  • comments
  • hyperlink_base

The properties are specified via a lxw_doc_properties struct. All the members are char * and they are all optional. An example of how to create and pass the properties is:

// Create a properties structure and set some of the fields.
lxw_doc_properties properties = {
.title = "This is an example spreadsheet",
.subject = "With document properties",
.author = "John McNamara",
.manager = "Dr. Heinz Doofenshmirtz",
.company = "of Wolves",
.category = "Example spreadsheets",
.keywords = "Sample, Example, Properties",
.comments = "Created with libxlsxwriter",
.status = "Quo",
};
// Set the properties in the workbook.
workbook_set_properties(workbook, &properties);
doc_properties.png
Examples:
doc_properties.c.
lxw_error workbook_set_custom_property_string ( lxw_workbook workbook,
const char *  name,
const char *  value 
)
Parameters
workbookPointer to a lxw_workbook instance.
nameThe name of the custom property.
valueThe value of the custom property.
Returns
A lxw_error.

The workbook_set_custom_property_string() function can be used to set one or more custom document text properties not covered by the standard properties in the workbook_set_properties() function above.

For example:

workbook_set_custom_property_string(workbook, "Checked by", "Eve");
custom_properties.png

There are 4 workbook_set_custom_property_string_*() functions for each of the custom property types supported by Excel:

Note: the name and value parameters are limited to 255 characters by Excel.

Examples:
doc_custom_properties.c.
lxw_error workbook_set_custom_property_number ( lxw_workbook workbook,
const char *  name,
double  value 
)
Parameters
workbookPointer to a lxw_workbook instance.
nameThe name of the custom property.
valueThe value of the custom property.
Returns
A lxw_error.

Set a custom document number property. See workbook_set_custom_property_string() above for details.

workbook_set_custom_property_number(workbook, "Document number", 12345);
Examples:
doc_custom_properties.c.
lxw_error workbook_set_custom_property_boolean ( lxw_workbook workbook,
const char *  name,
uint8_t  value 
)
Parameters
workbookPointer to a lxw_workbook instance.
nameThe name of the custom property.
valueThe value of the custom property.
Returns
A lxw_error.

Set a custom document boolean property. See workbook_set_custom_property_string() above for details.

workbook_set_custom_property_boolean(workbook, "Has Review", 1);
Examples:
doc_custom_properties.c.
lxw_error workbook_set_custom_property_datetime ( lxw_workbook workbook,
const char *  name,
lxw_datetime datetime 
)
Parameters
workbookPointer to a lxw_workbook instance.
nameThe name of the custom property.
datetimeThe value of the custom property.
Returns
A lxw_error.

Set a custom date or time number property. See workbook_set_custom_property_string() above for details.

lxw_datetime datetime = {2016, 12, 1, 11, 55, 0.0};
workbook_set_custom_property_datetime(workbook, "Date completed", &datetime);
Examples:
doc_custom_properties.c.
lxw_error workbook_define_name ( lxw_workbook workbook,
const char *  name,
const char *  formula 
)
Parameters
workbookPointer to a lxw_workbook instance.
nameThe defined name.
formulaThe cell or range that the defined name refers to.
Returns
A lxw_error.

This function is used to defined a name that can be used to represent a value, a single cell or a range of cells in a workbook: These defined names can then be used in formulas:

workbook_define_name(workbook, "Exchange_rate", "=0.96");
worksheet_write_formula(worksheet, 2, 1, "=Exchange_rate", NULL);
defined_name.png

As in Excel a name defined like this is "global" to the workbook and can be referred to from any worksheet:

// Global workbook name.
workbook_define_name(workbook, "Sales", "=Sheet1!$G$1:$H$10");

It is also possible to define a local/worksheet name by prefixing it with the sheet name using the syntax 'sheetname!definedname':

// Local worksheet name.
workbook_define_name(workbook, "Sheet2!Sales", "=Sheet2!$G$1:$G$10");

If the sheet name contains spaces or special characters you must follow the Excel convention and enclose it in single quotes:

workbook_define_name(workbook, "'New Data'!Sales", "=Sheet2!$G$1:$G$10");

The rules for names in Excel are explained in the Microsoft Office documentation.

Examples:
defined_name.c.
lxw_worksheet* workbook_get_worksheet_by_name ( lxw_workbook workbook,
const char *  name 
)
Parameters
workbookPointer to a lxw_workbook instance.
nameWorksheet name.
Returns
A lxw_worksheet object.

This function returns a lxw_worksheet object reference based on its name:

worksheet = workbook_get_worksheet_by_name(workbook, "Sheet1");
lxw_error workbook_validate_worksheet_name ( lxw_workbook workbook,
const char *  sheetname 
)
Parameters
workbookPointer to a lxw_workbook instance.
sheetnameWorksheet name to validate.
Returns
A lxw_error.

This function is used to validate a worksheet name according to the rules used by Excel:

  • The name is less than or equal to 31 UTF-8 characters.
  • The name doesn't contain any of the characters: [ ] : * ? / \
  • The name isn't already in use.
lxw_error err = workbook_validate_worksheet_name(workbook, "Foglio");

This function is called by workbook_add_worksheet() but it can be explicitly called by the user beforehand to ensure that the worksheet name is valid.

Typedef Documentation

Workbook document properties.

Optional parameters when creating a new Workbook object via workbook_new_opt().

The following properties are supported:

  • constant_memory: Reduces the amount of data stored in memory so that large files can be written efficiently.

    Note
    In this mode a row of data is written and then discarded when a cell in a new row is added via one of the worksheet_write_*() functions. Therefore, once this option is active, data should be written in sequential row order. For this reason the worksheet_merge_range() doesn't work in this mode. See also Constant Memory Mode.
  • tmpdir: libxlsxwriter stores workbook data in temporary files prior to assembling the final XLSX file. The temporary files are created in the system's temp directory. If the default temporary directory isn't accessible to your application, or doesn't contain enough space, you can specify an alternative location using the tempdir option.
typedef struct lxw_workbook lxw_workbook

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

Macro Definition Documentation

#define LXW_FOREACH_WORKSHEET (   worksheet,
  workbook 
)    STAILQ_FOREACH((worksheet), (workbook)->worksheets, list_pointers)

This macro allows you to loop over all the worksheets that have been added to a workbook. You must provide a lxw_worksheet pointer and a pointer to the lxw_workbook:

lxw_workbook *workbook = workbook_new("test.xlsx");
lxw_worksheet *worksheet; // Generic worksheet pointer.
// Worksheet objects used in the program.
lxw_worksheet *worksheet1 = workbook_add_worksheet(workbook, NULL);
lxw_worksheet *worksheet2 = workbook_add_worksheet(workbook, NULL);
lxw_worksheet *worksheet3 = workbook_add_worksheet(workbook, NULL);
// Iterate over the 3 worksheets and perform the same operation on each.
LXW_FOREACH_WORKSHEET(worksheet, workbook) {
worksheet_write_string(worksheet, 0, 0, "Hello", NULL);
}
Examples:
defined_name.c.

Data Structures

struct  lxw_doc_properties
 
struct  lxw_workbook_options
 Workbook options. More...
 
struct  lxw_workbook
 Struct to represent an Excel workbook. More...
 

Typedefs

typedef struct lxw_doc_properties lxw_doc_properties
 
typedef struct lxw_workbook_options lxw_workbook_options
 Workbook options. More...
 
typedef struct lxw_workbook lxw_workbook
 Struct to represent an Excel workbook. More...
 

Macros

#define LXW_FOREACH_WORKSHEET(worksheet, workbook)   STAILQ_FOREACH((worksheet), (workbook)->worksheets, list_pointers)
 Macro to loop over all the worksheets in a workbook. More...