Show
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() { }
◆ worksheet_write_number()Parameters
The 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 The NaN , Inf or -Inf as a number value. If you are writing data that
contains these values then your application should convert them to a string or handle them in some other way. Examplesanatomy.c, array_formula.c, autofilter.c,
chart.c, chart_area.c, chart_bar.c, chart_clustered.c,
chart_column.c, chart_data_labels.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,
chartsheet.c, conditional_format1.c, conditional_format2.c, constant_memory.c,
data_validate.c, dates_and_times01.c, demo.c, dynamic_arrays.c,
format_num_format.c, hello.c, outline.c, outline_collapsed.c,
panes.c, tables.c, tutorial1.c, tutorial2.c, and
tutorial3.c.◆ worksheet_write_string()Parameters
The The 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: Examples anatomy.c, autofilter.c, chart_area.c, chart_bar.c, chart_clustered.c, chart_column.c, chart_data_labels.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, chartsheet.c, comments1.c, comments2.c, conditional_format2.c, data_validate.c, dates_and_times04.c, defined_name.c, demo.c, diagonal_border.c, doc_custom_properties.c, doc_properties.c, dynamic_arrays.c, format_font.c, headers_footers.c, hello.c, hide_row_col.c, hide_sheet.c, hyperlinks.c, ignore_errors.c, images.c, lambda.c, macro.c, outline.c, outline_collapsed.c, panes.c, tab_colors.c, tables.c, tutorial1.c, tutorial2.c, tutorial3.c, utf8.c, and worksheet_protection.c.◆ worksheet_write_formula()Parameters
The The Libxlsxwriter doesn't calculate the value of a formula and instead stores a default value of 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: See also Working with Formulas. Examplesdata_validate.c, defined_name.c, ignore_errors.c, outline.c, outline_collapsed.c, tutorial1.c, tutorial2.c, tutorial3.c, and worksheet_protection.c.◆ worksheet_write_array_formula()Parameters
The In Excel an array formula is indicated by a pair of braces around the formula: 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 If the array formula returns a single value then the ◆ worksheet_write_dynamic_array_formula()Parameters
The
Dynamic array formulas and their usage in libxlsxwriter is explained in detail Dynamic Array support. The following is a example usage: "=_xlfn._xlws.FILTER(A1:D17,C1:C17=K2)", NULL); This formula gives the results shown in the image below. The need for the ◆ worksheet_write_dynamic_formula()Parameters
The "=_xlfn._xlws.SORT(_xlfn.UNIQUE(B2:B17))", NULL); This formula gives the following result: The need for the ◆ worksheet_write_datetime()Parameters
The #include "xlsxwriter.h" int main() { lxw_datetime datetime = {2013, 2, 28, 12, 0, 0.0}; } The See Working with Dates and Times for more information about handling dates and times in libxlsxwriter. Examplesdates_and_times02.c, dates_and_times04.c, and tutorial3.c.◆ worksheet_write_unixtime()Parameters
The The The output from this code sample is: Unixtime is generally represented with a 32 bit See Working with Dates and Times for more information about handling dates and times in libxlsxwriter. Examplesdates_and_times03.c.◆ worksheet_write_url()Parameters
The The The usual web style URI's are supported: 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 Two local URIs are supported: worksheet_write_url(worksheet, 5, 0, "external:c:\\foo.xlsx#Sheet2!A1", NULL); worksheet_write_url(worksheet, 7, 0, "external:..\\foo.xlsx#Sheet2!A1", NULL); worksheet_write_url(worksheet, 8, 0, "external:\\\\NET\\share\\foo.xlsx", NULL); Worksheet references are typically of the form In external links the workbook and worksheet name must be separated by the You can also link to a named range
in the target worksheet: For example say you have a named range called Excel requires that worksheet names containing spaces or non alphanumeric characters are single quoted as follows: Links to network files are also supported. Network files normally begin with two back slashes as follows Alternatively, you can use Unix style forward slashes. These are translated internally to backslashes: Note: libxlsxwriter will escape the following characters in URLs as required by Excel: Note: The maximum allowable URL length in recent versions of Excel is 2079 characters. In older versions of Excel (and libxlsxwriter <= 0.8.8) the limit was 255 characters. Examples hyperlinks.c.◆ worksheet_write_boolean()Parameters
Write an Excel boolean to the cell specified by ◆ worksheet_write_blank()Parameters
Write a blank cell specified by 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. ◆ worksheet_write_formula_num()Parameters
The Libxlsxwriter doesn't calculate the value of a formula and instead stores the value 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 If required, the 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. See also Working with Formulas. ◆ worksheet_write_formula_str()Parameters
The The One place where the See the FAQ Q. Why do my formulas show a zero result in some, non-Excel applications?. See also Working with Formulas. ◆ worksheet_write_rich_string()Parameters
The &fragment13, &fragment14, NULL}; The basic rule is to break the string into fragments and put a lxw_format object before the fragment that you want to format. So if we look at the above example again: This is bold and this is italic The would be broken down into 4 fragments: default: |This is | bold: |bold| default: | and this is | italic: |italic| This in then converted to the lxw_rich_string_tuple fragments shown in the example above. For the default format we use The fragments are passed to &fragment13, &fragment14, NULL}; Note: Excel doesn't allow the use of
two consecutive formats in a rich string or an empty string fragment. For either of these conditions a warning is raised and the input to ◆ worksheet_write_comment()Parameters
The The following example shows how to add a comment to a cell: See also Working with Cell Comments Examplescomments1.c, and comments2.c.◆ worksheet_write_comment_opt()Parameters
The The following example shows how to add a comment to a cell with options: The following options are available in lxw_comment_options:
Comment options are explained in detail in the Setting Comment Properties section of the docs. Examplescomments2.c.◆ worksheet_set_row()Parameters
The The height is specified in character units. To specify the height in pixels use the The other common use for 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: The ◆ worksheet_set_row_opt()Parameters
The The
The The ◆ worksheet_set_row_pixels()Parameters
The If you wish to set the format of a row without changing the height you can pass the default row height in pixels: LXW_DEF_ROW_HEIGHT_PIXELS. ◆ worksheet_set_row_pixels_opt()Parameters
The ◆ worksheet_set_column()Parameters
The If It is also possible, and generally
clearer, to specify a column range using the form of The 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 The As in Excel a row format takes precedence over a default column format: Examplesanatomy.c, autofilter.c, comments2.c, data_validate.c, dates_and_times01.c, dates_and_times02.c, dates_and_times03.c, dates_and_times04.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, ignore_errors.c, images.c, macro.c, merge_range.c, outline.c, outline_collapsed.c, panes.c, rich_strings.c, tables.c, tutorial3.c, and worksheet_protection.c.◆ worksheet_set_column_opt()Parameters
The The
The The ◆ worksheet_set_column_pixels()Parameters
The If you wish to set the format of a column without changing the width you can pass the default column width in pixels: LXW_DEF_COL_WIDTH_PIXELS. Examplesdynamic_arrays.c.◆ worksheet_set_column_pixels_opt()Parameters
The ◆ worksheet_insert_image()Parameters
This function can be used to insert a image into a worksheet. The image can be in PNG, JPEG, GIF or BMP format: The 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 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. Examplesdemo.c, and images.c.◆ worksheet_insert_image_opt()Parameters
The
For example, to scale and position the image: .x_scale = 0.5, .y_scale = 0.5}; The The supported URL formats are the same as those supported by the The worksheet_insert_image() above. Examplesimages.c.◆ worksheet_insert_image_buffer()Parameters
This function can be used to insert a image into a worksheet from a memory buffer: The buffer should be a pointer to an array of unsigned char data with a specified size. See
◆ worksheet_insert_image_buffer_opt()Parameters
The
For example, to scale and position the image: .x_scale = 2, .y_scale = 1}; The buffer should be a pointer to an array of unsigned char data with a specified size. See ◆ worksheet_set_background()Parameters
The The Some people use this
method to add a watermark background to their document. However, Microsoft recommends using a header image to set a watermark. The choice of method depends on whether you want the watermark to be visible in normal viewing mode or just when the file is printed. In libxlsxwriter you can get the header watermark effect using ◆ worksheet_set_background_buffer()
Parameters
This function can be used to insert a background image into a worksheet from a memory buffer: The buffer should be a pointer to an array of unsigned char data with a specified size. See ◆ worksheet_insert_chart()ParametersReturnsA lxw_error code. The 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_opt()◆ worksheet_merge_range()Parameters
The 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: It is possible to apply other formatting to the merged cells as well: The constant_memory mode is enabled. Examplesmerge_range.c, and
merge_rich_string.c.◆ worksheet_autofilter()Parameters
The 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. To add an autofilter to a worksheet: In order to apply a filter condition it is necessary to add filter rules to the columns using either the
These functions are explained below. It isn't sufficient to just specify the filter condition. You must also hide any rows that don't match the filter condition. See Filtering data in an autofilter for more details. Examples autofilter.c.◆ worksheet_filter_column()◆ worksheet_filter_column2()Parameters ReturnsA lxw_error code. The .value_string = "East"}; .value_string = "South"}; The rules and criteria are explained in more detail in Setting a filter criteria for a column in Working with Autofilters. The The It isn't sufficient to just specify the filter condition. You must also hide any rows that don't match the filter condition. See Filtering data in an autofilter for more details. Examplesautofilter.c.◆ worksheet_filter_list()Parameters
The As an example, consider a column that contains data for the months of the year. The char* list[] = {"March", "April", "May", NULL}; Note, the array must be NULL terminated to indicate the end of the array of strings. To filter blanks as part of the list use char* list[] = {"March", "April", "May", "Blanks", NULL}; It isn't sufficient to just specify the filter condition. You must also hide any rows that don't match the filter condition. See Filtering data in an autofilter for more details. Examplesautofilter.c.◆ worksheet_data_validation_cell()Parameters
The Data validation and the various options of lxw_data_validation are described in more detail in Working with Data Validation. Examplesdata_validate.c.◆ worksheet_data_validation_range()Parameters
The Data validation and the various options of lxw_data_validation are described in more detail in Working with Data Validation. ◆ worksheet_conditional_format_cell()Parameters
The conditional_format->value = 50; conditional_format->format = format1; The conditional format parameters is specified in lxw_conditional_format. See Working with Conditional Formatting for full details. ◆ worksheet_conditional_format_range()Parameters
The conditional_format->value = 50; conditional_format->format = format1; conditional_format->value = 50; conditional_format->format = format2; Output: The conditional format parameters is specified in lxw_conditional_format. See Working with Conditional Formatting for full details. Examplesconditional_format1.c, and conditional_format2.c.◆ worksheet_insert_button()Parameters
The The button properties are set using the lxw_button_options struct. See also Working with VBA Macros Examplesmacro.c.◆ worksheet_add_table()Parameters
The Output: See Working with Worksheet Tables for more detailed usage information and also tables.c. Examplestables.c.◆ worksheet_activate()Parameters The More than one worksheet can be selected via the The default active worksheet is the first worksheet. ◆ worksheet_select()Parameters The 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_hide()Parameters The You may wish to hide a worksheet in order to avoid confusing a user with intermediate data or calculations. A hidden worksheet can not be activated or selected so this function is mutually exclusive with the ◆ worksheet_set_first_sheet()Parameters The This function is not required very often. The default value is the first worksheet. ◆ worksheet_freeze_panes()Parameters
The The parameters You can set one of the Examples: Examplespanes.c.◆ worksheet_split_panes()
Parameters
The The parameters Examples: Examplespanes.c.◆ worksheet_set_selection()Parameters
The The active cell within a selected range is determined by the order in which Examples: Examplespanes.c.◆ worksheet_set_top_left_cell()Parameters
The ◆ worksheet_set_landscape()Parameters This function is used to set the orientation of a worksheet's printed page to landscape: ◆ worksheet_set_portrait()Parameters 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: ◆ worksheet_set_page_view()Parameters This function is used to display the worksheet in "Page View/Layout" mode: ◆ worksheet_set_paper()
Parameters
This function is used to set the paper format for the printed output of a worksheet. The following paper styles are available:
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: If you do not specify a paper type the worksheet will print using the printer's default paper style. ◆ worksheet_set_margins()
Parameters
The ◆ worksheet_set_header()Parameters
Headers and footers are generated using a string which is a combination of plain text and control characters. The available control character are:
Note: inserting images requires the Text in headers and footers can be justified (aligned) to the left, center and right by prefixing the text with the control characters For example (with ASCII art representation of the results): For simple text, if you do not specify any justification the text will be centered. However, you must prefix the text with You can have text in each of the justification regions: 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: You can specify the font size of a section of the text by prefixing it with the control character You can specify the font of a section of the text by prefixing it with the control sequence 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 .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" | sed 's/&/\&/g' <headerFooter scaleWithDoc="0"> <oddHeader>&L&P</oddHeader> </headerFooter> To include a single literal ampersand ◆ worksheet_set_footer()◆ worksheet_set_header_opt()Parameters
The syntax of this function is the same as The lxw_header_footer_options options are:
Images can be inserted in the header by specifying the ◆ worksheet_set_footer_opt()◆ worksheet_set_h_pagebreaks()Parameters
The 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: 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: There is an Excel limitation of 1023 horizontal page breaks per worksheet. Note: If you specify the "fit to page" option via the ◆ worksheet_set_v_pagebreaks()Parameters
The 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: 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: There is an Excel limitation of 1023 vertical page breaks per worksheet. Note: If you specify the "fit to page" option via the ◆ worksheet_print_across()Parameters The 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 [1] [2] [3] [4] ◆ worksheet_set_zoom()
Parameters
Set the worksheet zoom factor in the range 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_gridlines()
Parameters
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. ◆ worksheet_center_horizontally()Parameters Center the worksheet data horizontally between the margins on the printed page: ◆ worksheet_center_vertically()Parameters Center the worksheet data vertically between the margins on the printed page: ◆ worksheet_print_row_col_headers()
Parameters 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: ◆ worksheet_repeat_rows()Parameters
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 ◆ worksheet_repeat_columns()Parameters
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 ◆ worksheet_print_area()Parameters
This function is used to specify the area of the worksheet that will be printed. The RANGE() macro is often convenient for this. In order to set a row or column range you must specify the entire range: ◆ worksheet_fit_to_pages()
Parameters
The The print area can be defined using the A common requirement is to fit the printed
output to Note:
◆ worksheet_set_start_page()
Parameters
The ◆ worksheet_set_print_scale()
Parameters
This function sets the scale factor of the printed page. The Scale factor must be in the range The default scale factor is 100. Note, Note that although it is valid to use both ◆ worksheet_print_black_and_white()
Parameters Set the option to print the worksheet in black and white: ◆ worksheet_right_to_left()Parameters The This is useful when creating Arabic, Hebrew or other near or far eastern worksheets that use right-to-left as the default direction. ◆ worksheet_hide_zero()Parameters The ◆ worksheet_set_tab_color()Parameters
The The color should be an RGB integer value, see Working with Colors. Examplestab_colors.c.◆ worksheet_protect()Parameters
The The Passing a 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 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: .format_cells = 1, .insert_hyperlinks = 1, .insert_rows = 1, .delete_rows = 1, .insert_columns = 1, .delete_columns = 1, }; See also the format_set_unlocked() and format_set_hidden() format functions. Note: Sheet 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 ◆ worksheet_outline_settings()
Parameters
The The The The The The default settings for all of these parameters in libxlsxwriter correspond to Excel's default parameters and are shown below: The worksheet parameters controlled by ◆ worksheet_set_default_row()
Parameters
The 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. Exampleshide_row_col.c.◆ worksheet_set_vba_name()Parameters
The In general Excel uses the worksheet name such as "Sheet1" as the VBA name. However, this can be changed in the VBA environment or if the the macro was extracted from a foreign language version of Excel. See also Working with VBA Macros ◆ worksheet_show_comments()◆ worksheet_set_comments_author()
◆ worksheet_ignore_errors()Parameters
The This causes Excel to display a small green triangle in the top left hand corner of the cell to indicate an error/warning: Sometimes these warnings are useful indicators that there is an issue in the spreadsheet but sometimes it is preferable to turn them off. Warnings can be turned off at the Excel level for all workbooks and worksheets by using the using "Excel options -> Formulas -> Error checking rules". Alternatively you can turn them off for individual cells in a worksheet, or ranges of cells,
using the The range can be a single cell, a range of cells, or multiple cells and ranges separated by spaces: NoteCallingworksheet_ignore_errors() more than once for the same
lxw_ignore_errors type will overwrite the previous range.You can turn off warnings for an entire column by specifying the range from the first cell in the column to the last cell in the column: Or for the entire worksheet by specifying the range from the first cell in the worksheet to the last cell in the worksheet: The worksheet errors/warnings that can be ignored are:
◆ lxw_row_col_options◆ lxw_conditional_formatThe fields/options in the the
lxw_conditional_format are used to define a worksheet conditional format. It is used in conjunction with ◆ lxw_table_column◆ lxw_table_options◆ lxw_filter_ruleOptions to define an autofilter rule. ◆ lxw_image_options◆ lxw_chart_options◆ lxw_comment_options◆ lxw_button_options◆ lxw_header_footer_options◆ lxw_rich_string_tupleArrays of this struct are used to define "rich" multi-format strings that are passed to ◆ lxw_worksheetThe members of the lxw_worksheet struct aren't modified directly. Instead the worksheet properties are set by calling the functions shown in worksheet.h. ◆ LXW_DEF_COL_WIDTH#define LXW_DEF_COL_WIDTH (double)8.43 Default Excel column width in character units. ◆ LXW_DEF_ROW_HEIGHT#define LXW_DEF_ROW_HEIGHT (double)15.0 ◆ LXW_DEF_COL_WIDTH_PIXELS#define LXW_DEF_COL_WIDTH_PIXELS 64 Default Excel column width in pixels. ◆ LXW_DEF_ROW_HEIGHT_PIXELS#define LXW_DEF_ROW_HEIGHT_PIXELS 20 Default Excel column height in pixels. ◆ lxw_gridlinesGridline options using in
◆ lxw_validation_booleanData validation property values.
◆ lxw_validation_typesData validation types.
◆ lxw_validation_criteriaData validation criteria uses to control the selection of data.
◆ lxw_validation_error_typesData validation error types for pop-up messages.
◆ lxw_comment_display_typesSet the display type for a cell comment. This is hidden by default but can be set to visible with the
◆ lxw_conditional_format_typesValues used to set the "type" field of conditional format.
◆ lxw_conditional_criteriaCriteria used to define how a conditional format works.
◆ lxw_conditional_format_rule_typesConditional format rule types that apply to Color Scale and Data Bars.
◆ lxw_conditional_format_bar_directionValues used to set the bar direction of a conditional format data bar.
◆ lxw_conditional_bar_axis_positionValues used to set the position of the axis in a conditional format data bar.
◆ lxw_conditional_icon_typesDefinitions of icon styles used with Icon Set conditional formats.
◆ lxw_table_style_typeThe type of table style (Light, Medium or Dark).
◆ lxw_table_total_functionsDefinitions for the standard Excel functions that are available via the dropdown in the total row of an Excel table.
◆ lxw_filter_criteriaCriteria used to define an autofilter rule condition.
◆ lxw_filter_operatorAnd/or operator conditions when using 2 filter rules with worksheet_filter_column2(). In general LXW_FILTER_OR is used with LXW_FILTER_CRITERIA_EQUAL_TO and LXW_FILTER_AND is used with the other filter criteria.
◆ lxw_object_positionOptions to control the positioning of worksheet objects such as images or charts. See Working with Object Positioning.
◆ lxw_ignore_errorsOptions for ignoring worksheet errors/warnings. See worksheet_ignore_errors().
Options for inserted buttons. Definition: worksheet.h:1900 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. lxw_error worksheet_set_v_pagebreaks(lxw_worksheet *worksheet, lxw_col_t breaks[]) Set the vertical page breaks on a worksheet. void worksheet_hide_zero(lxw_worksheet *worksheet) Hide zero values in worksheet cells. Options for rows and columns. Definition: worksheet.h:839 void worksheet_set_default_row(lxw_worksheet *worksheet, double height, uint8_t hide_unused_rows) Set the default row properties. lxw_error workbook_close(lxw_workbook *workbook) Close the Workbook object and write the XLSX file. uint8_t criteria Definition: worksheet.h:931 Struct to represent a rich string format/string pair. Definition: worksheet.h:2090 lxw_error worksheet_insert_button(lxw_worksheet *worksheet, lxw_row_t row, lxw_col_t col, lxw_button_options *options) Insert a button object into a worksheet. void worksheet_print_across(lxw_worksheet *worksheet) Set the order in which pages are printed. lxw_error worksheet_set_vba_name(lxw_worksheet *worksheet, const char *name) Set the VBA name for the worksheet. Options for inserted charts. Definition: worksheet.h:1746 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. lxw_error worksheet_write_unixtime(lxw_worksheet *worksheet, lxw_row_t row, lxw_col_t col, int64_t unixtime, lxw_format *format) Write a Unix datetime to a worksheet cell. Worksheet protection options. Definition: worksheet.h:2003 void format_set_italic(lxw_format *format) Turn on italic for the format font. void worksheet_set_top_left_cell(lxw_worksheet *worksheet, lxw_row_t row, lxw_col_t col) Set the first visible cell at the top left of a worksheet. @ LXW_BORDER_DOUBLE Definition: format.h:310 char * url Definition: worksheet.h:1733 @ LXW_ALIGN_VERTICAL_CENTER Definition: format.h:153 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. @ LXW_FALSE Definition: common.h:51 Worksheet data validation options. Definition: worksheet.h:920 void format_set_align(lxw_format *format, uint8_t alignment) Set the alignment for data in the cell. Options for autofilter rules. Definition: worksheet.h:1663 lxw_chart_series * chart_add_series(lxw_chart *chart, const char *categories, const char *values) Add a data series to a chart. #define LXW_DEF_ROW_HEIGHT Definition: worksheet.h:76 lxw_workbook * workbook_new(const char *filename) Create a new workbook object. 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. @ LXW_IGNORE_NUMBER_STORED_AS_TEXT Definition: worksheet.h:679 lxw_error worksheet_set_background(lxw_worksheet *worksheet, const char *filename) Set the background image for a worksheet. uint8_t criteria Definition: worksheet.h:1666 @ LXW_CONDITIONAL_CRITERIA_GREATER_THAN_OR_EQUAL_TO Definition: worksheet.h:320 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. lxw_error worksheet_filter_column(lxw_worksheet *worksheet, lxw_col_t col, lxw_filter_rule *rule) Write a filter rule to an autofilter column. void worksheet_gridlines(lxw_worksheet *worksheet, uint8_t option) Set the option to display or hide gridlines on the screen and the printed page. 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: lxw_error worksheet_write_url(lxw_worksheet *worksheet, lxw_row_t row, lxw_col_t col, const char *url, lxw_format *format) void format_set_border(lxw_format *format, uint8_t style) Set the cell border style. lxw_format * format Definition: worksheet.h:2094 void worksheet_set_print_scale(lxw_worksheet *worksheet, uint16_t scale) Set the scale factor for the printed page. void format_set_bold(lxw_format *format) Turn on bold for the format font. lxw_error worksheet_write_dynamic_formula(lxw_worksheet *worksheet, lxw_row_t row, lxw_col_t col, const char *formula, lxw_format *format) Write an Excel 365 dynamic array formula to a worksheet cell. void worksheet_set_zoom(lxw_worksheet *worksheet, uint16_t scale) Set the worksheet zoom factor. void format_set_num_format(lxw_format *format, const char *num_format) Set the number format for a cell. void worksheet_set_start_page(lxw_worksheet *worksheet, uint16_t start_page) Set the start/first page number when printing. 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. double minimum_number Definition: worksheet.h:1013 @ LXW_CHART_LINE Definition: chart.h:125 void worksheet_set_margins(lxw_worksheet *worksheet, double left, double right, double top, double bottom) Set the worksheet margins for the printed page. Struct to represent an Excel worksheet. Definition: worksheet.h:2107 void worksheet_hide(lxw_worksheet *worksheet) Hide the current worksheet. void worksheet_set_landscape(lxw_worksheet *worksheet) Set the page orientation as landscape. @ LXW_FILTER_OR Definition: worksheet.h:638 double maximum_number Definition: worksheet.h:1031 lxw_format * workbook_get_default_url_format(lxw_workbook *workbook) Get the default URL format used with worksheet_write_url(). @ LXW_IGNORE_EVAL_ERROR Definition: worksheet.h:683 void worksheet_print_black_and_white(lxw_worksheet *worksheet) Set the worksheet to print in black and white. Struct to represent the formatting properties of an Excel format. Definition: format.h:358 @ LXW_FILTER_CRITERIA_EQUAL_TO Definition: worksheet.h:601 lxw_error worksheet_set_h_pagebreaks(lxw_worksheet *worksheet, lxw_row_t breaks[]) Set the horizontal page breaks on a worksheet. #define RANGE(range) Convert an Excel A1:B2 range into a (first_row, first_col, last_row, last_col) sequence. Definition: utility.h:82 @ LXW_TRUE Definition: common.h:53 void worksheet_protect(lxw_worksheet *worksheet, const char *password, lxw_protection *options) Protect elements of a worksheet from modification. 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. lxw_error worksheet_conditional_format_cell(lxw_worksheet *worksheet, lxw_row_t row, lxw_col_t col, lxw_conditional_format *conditional_format) Add a conditional format to a worksheet cell. 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. @ LXW_HIDE_ALL_GRIDLINES Definition: worksheet.h:87 Struct to represent an Excel chart. Definition: chart.h:1090 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. #define COLS(cols) Convert an Excel A:B column range into a (col1, col2) pair. Definition: utility.h:63 void format_set_bg_color(lxw_format *format, lxw_color_t color) Set the pattern background color for a cell. int32_t x_offset Definition: worksheet.h:1704 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. @ LXW_ALIGN_CENTER Definition: format.h:129 @ LXW_COMMENT_DISPLAY_VISIBLE Definition: worksheet.h:224 lxw_error worksheet_set_row_pixels(lxw_worksheet *worksheet, lxw_row_t row, uint32_t pixels, lxw_format *format) Set the properties for a row of cells, with the height in pixels. lxw_error worksheet_write_dynamic_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 Excel 365 dynamic array formula to a worksheet range. void worksheet_show_comments(lxw_worksheet *worksheet) Make all comments in the worksheet visible. lxw_error worksheet_set_header(lxw_worksheet *worksheet, const char *string) Set the printed page header caption. uint8_t hidden Definition: worksheet.h:841 void worksheet_right_to_left(lxw_worksheet *worksheet) Display the worksheet cells from right to left for some versions of Excel. void worksheet_set_first_sheet(lxw_worksheet *worksheet) Set current worksheet as the first visible sheet tab. void worksheet_select(lxw_worksheet *worksheet) Set a worksheet tab as selected. @ LXW_UNDERLINE_SINGLE Definition: format.h:98 Struct to represent a date and time in Excel. Definition: common.h:155 Options for inserted images. Definition: worksheet.h:1701 @ LXW_CONDITIONAL_TYPE_CELL Definition: worksheet.h:237 lxw_error worksheet_write_comment_opt(lxw_worksheet *worksheet, lxw_row_t row, lxw_col_t col, const char *string, lxw_comment_options *options) Write a comment to a worksheet cell with options. @ LXW_COLOR_GREEN Definition: format.h:196 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 numeric result. void worksheet_set_comments_author(lxw_worksheet *worksheet, const char *author) Set the default author of the cell comments. void worksheet_set_page_view(lxw_worksheet *worksheet) Set the page layout to page view mode. @ LXW_VALIDATION_TYPE_INTEGER Definition: worksheet.h:116 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. uint8_t validate Definition: worksheet.h:925 void worksheet_outline_settings(lxw_worksheet *worksheet, uint8_t visible, uint8_t symbols_below, uint8_t symbols_right, uint8_t auto_style) Set the Outline and Grouping display properties. void worksheet_set_paper(lxw_worksheet *worksheet, uint8_t paper_type) Set the paper type for printing. Struct to represent an Excel workbook. Definition: workbook.h:280 uint32_t lxw_row_t Definition: common.h:40 lxw_error worksheet_insert_chart_opt(lxw_worksheet *worksheet, lxw_row_t row, lxw_col_t col, lxw_chart *chart, lxw_chart_options *user_options) Insert a chart object into a worksheet, with options. 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. 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. void worksheet_activate(lxw_worksheet *worksheet) Make a worksheet the active, i.e., visible worksheet. void worksheet_center_vertically(lxw_worksheet *worksheet) Center the printed page vertically. lxw_error worksheet_insert_image_buffer_opt(lxw_worksheet *worksheet, lxw_row_t row, lxw_col_t col, const unsigned char *image_buffer, size_t image_size, lxw_image_options *options) Insert an image in a worksheet cell, from a memory buffer. void worksheet_set_tab_color(lxw_worksheet *worksheet, lxw_color_t color) Set the color of the worksheet tab. 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. lxw_error worksheet_filter_list(lxw_worksheet *worksheet, lxw_col_t col, char **list) Write multiple string filters to an autofilter column. lxw_error workbook_set_vba_name(lxw_workbook *workbook, const char *name) Set the VBA name for the workbook. 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_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. lxw_error worksheet_add_table(lxw_worksheet *worksheet, lxw_row_t first_row, lxw_col_t first_col, lxw_row_t last_row, lxw_col_t last_col, lxw_table_options *options) Add an Excel table to a worksheet. @ LXW_COLOR_RED Definition: format.h:217 lxw_error worksheet_data_validation_range(lxw_worksheet *worksheet, lxw_row_t first_row, lxw_col_t first_col, lxw_row_t last_row, lxw_col_t last_col, lxw_data_validation *validation) Add a data validation to a range. lxw_error worksheet_set_row(lxw_worksheet *worksheet, lxw_row_t row, double height, lxw_format *format) Set the properties for a row of cells. lxw_error worksheet_write_number(lxw_worksheet *worksheet, lxw_row_t row, lxw_col_t col, double number, lxw_format *format) Write a number to a worksheet cell. void worksheet_print_row_col_headers(lxw_worksheet *worksheet) Set the option to print the row and column headers on the printed page. void format_set_font_color(lxw_format *format, lxw_color_t color) Set the color of the font used in the cell. lxw_error worksheet_data_validation_cell(lxw_worksheet *worksheet, lxw_row_t row, lxw_col_t col, lxw_data_validation *validation) Add a data validation to a cell. void worksheet_freeze_panes(lxw_worksheet *worksheet, lxw_row_t row, lxw_col_t col) Split and freeze a worksheet into panes. lxw_error worksheet_insert_image_buffer(lxw_worksheet *worksheet, lxw_row_t row, lxw_col_t col, const unsigned char *image_buffer, size_t image_size) Insert an image in a worksheet cell, from a memory buffer. @ LXW_SHOW_PRINT_GRIDLINES Definition: worksheet.h:93 lxw_error worksheet_write_rich_string(lxw_worksheet *worksheet, lxw_row_t row, lxw_col_t col, lxw_rich_string_tuple *rich_string[], lxw_format *format) Write a "Rich" multi-format string to a worksheet cell. lxw_error worksheet_filter_column2(lxw_worksheet *worksheet, lxw_col_t col, lxw_filter_rule *rule1, lxw_filter_rule *rule2, uint8_t and_or) Write two filter rules to an autofilter column. void format_set_underline(lxw_format *format, uint8_t style) Turn on underline for the format: void worksheet_split_panes(lxw_worksheet *worksheet, double vertical, double horizontal) Split a worksheet into panes. @ LXW_CONDITIONAL_CRITERIA_LESS_THAN Definition: worksheet.h:317 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. void worksheet_center_horizontally(lxw_worksheet *worksheet) Center the printed page horizontally. 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. lxw_error worksheet_conditional_format_range(lxw_worksheet *worksheet, lxw_row_t first_row, lxw_col_t first_col, lxw_row_t last_row, lxw_col_t last_col, lxw_conditional_format *conditional_format) Add a conditional format to a worksheet range. #define CELL(cell) Convert an Excel A1 cell string into a (row, col) pair. Definition: utility.h:45 lxw_worksheet * workbook_add_worksheet(lxw_workbook *workbook, const char *sheetname) Add a new worksheet to a workbook. lxw_chart * workbook_add_chart(lxw_workbook *workbook, uint8_t chart_type) Create a new chart to be added to a worksheet: void worksheet_set_portrait(lxw_worksheet *worksheet) Set the page orientation as portrait. lxw_error worksheet_ignore_errors(lxw_worksheet *worksheet, uint8_t type, const char *range) Ignore various Excel errors/warnings in a worksheet for user defined ranges. lxw_error worksheet_write_comment(lxw_worksheet *worksheet, lxw_row_t row, lxw_col_t col, const char *string) Write a comment to a worksheet cell. uint16_t lxw_col_t Definition: common.h:46 @ LXW_VALIDATION_CRITERIA_BETWEEN Definition: worksheet.h:177 lxw_format * workbook_add_format(lxw_workbook *workbook) Create a new Format object to formats cells in worksheets. 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. #define LXW_DEF_COL_WIDTH Definition: worksheet.h:73 lxw_error worksheet_write_formula_str(lxw_worksheet *worksheet, lxw_row_t row, lxw_col_t col, const char *formula, lxw_format *format, const char *result) Write a formula to a worksheet cell with a user defined string result. lxw_error worksheet_set_background_buffer(lxw_worksheet *worksheet, const unsigned char *image_buffer, size_t image_size) Set the background image for a worksheet, from a buffer. Can't Print more than one copy of your worksheet which tab would you go to?Set one or more print areas. On the worksheet, select the cells that you want to define as the print area. Tip: To set multiple print areas, hold down the Ctrl key and click the areas you want to print. ... . On the Page Layout tab, in the Page Setup group, click Print Area, and then click Set Print Area.. Which feature do you use to Print a worksheet at its actual size?On the Layout tab, under Print, select the Fit To check box. In the page(s) wide and page(s) tall boxes, enter the number of pages on which you want to print the sheet. On the File menu, click Print. Note: Excel ignores manual page breaks when you use the Fit To option.
Where can you see a preview of how your worksheet will look when printed including headers?When you select one or more sheets and then click File > Print, you'll see a preview of how the data will appear on the printout. Select the worksheet(s) you want to preview. Click File, and then click Print to display the Preview window and printing options. Keyboard shortcut You can also press Ctrl+F2.
Which of the following options can you set to make sure a worksheet will Print on one page?Shrink a worksheet to fit on one page. Click Page Layout. ... . Select the Page tab in the Page Setup dialog box.. Select Fit to under Scaling.. To fit your document to print on one page, choose 1 page(s) wide by 1 tall in the Fit to boxes. ... . Press OK at the bottom of the Page Setup dialog box.. |