Function reference • openxlsx2

Read tabular data

Read data out of an xlsx file as an R object

wb_to_df() read_xlsx() wb_read(): Create a data frame from a Workbook

Write tabular data

Write tabular data frame(s) to file with options

write_xlsx(): Write data to an xlsx file

Load a workbook

Load and interact with an existing workbook

wb_load(): Load an existing .xlsx, .xlsm or .xlsb file

wbWorkbook: Workbook class

Create a workbook

Add content to a help you interact to a new or existing workbook.

wb_workbook(): Create a new Workbook object

Add content to a workbook

Add data or images to a new or existing workbook

Add a sheet to a workbook

wb_add_worksheet(): Add a worksheet to a workbook

Add to a worksheet

These functions help you write in worksheets. They invisibly return the wbWorkbook object, except for get_ functions, who return a character vector, unless specified otherwise.

wb_set_col_widths() wb_remove_col_widths(): Modify column widths of a worksheet

wb_add_filter() wb_remove_filter(): Add/remove column filters in a worksheet

wb_group_cols() wb_ungroup_cols() wb_group_rows() wb_ungroup_rows(): Group rows and columns in a worksheet

wb_add_named_region() wb_remove_named_region() wb_get_named_regions(): Modify named regions in a worksheet

wb_set_row_heights() wb_remove_row_heights(): Modify row heights of a worksheet

wb_add_conditional_formatting(): Add conditional formatting to cells in a worksheet

wb_add_data(): Add data to a worksheet

wb_add_data_table(): Add a data table to a worksheet

wb_add_formula(): Add a formula to a cell range in a worksheet

wb_add_pivot_table(): Add a pivot table to a worksheet

wb_add_slicer(): Add a slicer to a pivot table

wb_add_thread() wb_get_thread(): Add threaded comments to a cell in a worksheet

wb_freeze_pane(): Freeze pane of a worksheet

wb_merge_cells() wb_unmerge_cells(): Merge cells within a worksheet

Add images and charts to a worksheet

Add images or Excel charts to a worksheet with the mschart package. See vignette("openxlsx2_charts_manual").

wb_add_image(): Insert an image into a worksheet

wb_add_plot(): Insert the current plot into a worksheet

wb_add_chartsheet(): Add a chartsheet to a workbook

wb_add_mschart(): Add mschart object to a worksheet

Style a workbook

Style a cell region, a worksheet or the entire workbook. See vignette("openxlsx2_style_manual").

Worksheet styling

Add styling to a cell region in a worksheet

wb_add_border(): Modify borders in a cell region of a worksheet

wb_add_cell_style(): Modify the style in a cell region

wb_add_fill(): Modify the background fill color in a cell region

wb_add_font(): Modify font in a cell region

wb_add_named_style(): Apply styling to a cell region with a named style

wb_add_numfmt(): Modify number formatting in a cell region

wb_get_cell_style() wb_set_cell_style() wb_set_cell_style_across(): Apply styling to a cell region

Workbook styling

These styling functions apply formatting to the workbook globally.

wb_set_base_font() wb_get_base_font(): Set the default font in a workbook

wb_add_dxfs_style(): Set a dxfs styling for the workbook

wb_add_style(): Set the default style in a workbook

wb_set_base_colors() wb_get_base_colors(): Set the default colors in a workbook

View and save a workbook

Visualize the content of a workbook in a spreadsheet software and save it

wb_open(): Preview a workbook in a spreadsheet software

wb_save(): Save a workbook to file

xl_open(): Open an xlsx file or a wbWorkbook object

Edit workbook defaults

These functions are used to modify setting of a workbook as a whole.

wb_get_properties() wb_set_properties(): Modify workbook properties

wb_set_last_modified_by(): Modify author in the metadata of a workbook

wb_add_creators() wb_set_creators() wb_remove_creators() wb_get_creators(): Modify creators of a workbook

wb_get_order() wb_set_order(): Order worksheets in a workbook

wb_set_sheet_names() wb_get_sheet_names(): Get / Set worksheet names for a workbook

wb_remove_worksheet(): Remove a worksheet from a workbook

wb_get_active_sheet() wb_set_active_sheet() wb_get_selected() wb_set_selected(): Modify the state of active and selected sheets in a workbook

wb_get_sheet_visibility() wb_set_sheet_visibility(): Get/set worksheet visible state in a workbook

wb_set_bookview(): Set the workbook position, size and filter

wb_add_person() wb_get_person(): Helper for adding threaded comments

wb_protect(): Protect a workbook from modifications

wb_get_tables(): List Excel tables in a worksheet

wb_remove_tables(): Remove a data table from a worksheet

Workbook editing helpers

These functions are helpers that create an intermediate object. They are helpful with editing a workbook. They are useful when you add content, styling or you want to modify certain elements.

wb_color(): Helper to create a color

wb_comment(): Helper to create a comment object

wb_data() `[`(<wb_data>): Add the wb_data attribute to a data frame in a worksheet

wb_dims(): Helper to specify the dims argument

create_border(): Helper to create a border

create_cell_style(): Helper to create a cell style

create_colors_xml(): Create custom color xml schemes

create_dxfs_style(): Create a custom formatting style

create_fill(): Create fill pattern

create_font(): Create font format

create_hyperlink(): Create Excel hyperlink string

create_numfmt(): Create number format

create_sparklines(): Create sparklines object

create_tablestyle() create_pivottablestyle(): Create custom (pivot) table styles

Other functions

These functions can be used to achieve more specialized operations.

Other tools to interact with worksheets

wb_add_comment() wb_get_comment() wb_remove_comment(): Add comment to worksheet

wb_add_sparklines(): Add sparklines to a worksheet

wb_add_chart_xml(): Add a chart XML to a worksheet

wb_add_drawing(): Add drawings to a worksheet

wb_add_data_validation(): Add data validation to cells in a worksheet

wb_add_form_control(): Add a checkbox, radio button or drop menu to a cell in a worksheet

wb_add_ignore_error(): Ignore error types on worksheet

wb_add_page_break(): Add a page break to a worksheet

wb_clean_sheet(): Remove all values in a worksheet

wb_clone_sheet_style(): Apply styling from a sheet to another within a workbook

wb_clone_worksheet(): Create copies of a worksheet within a workbook

wb_copy_cells(): Copy cells around within a worksheet

wb_page_setup(): Set page margins, orientation and print scaling of a worksheet

wb_protect_worksheet(): Protect a worksheet from modifications

wb_set_grid_lines() wb_grid_lines(): Modify grid lines visibility in a worksheet

wb_set_header_footer(): Set headers and footers of a worksheet

wb_set_sheetview(): Modify the default view of a worksheet

wb_update_table(): Update a data table position in a worksheet

XML functions

A curated list of XML functions used in openxlsx2.

read_xml(): read xml file

xml_node() xml_node_name() xml_value() xml_attr(): xml_node

xml_add_child(): append xml child to node

xml_attr_mod(): adds or updates attribute(s) in existing xml node

xml_node_create(): create xml_node from R objects

xml_rm_child(): remove xml child to node

as_xml(): loads character string to pugixml and returns an externalptr

print(<pugi_xml>): print pugi_xml

Other helpers

int2col(): Convert integer to Excel column

col2int(): Convert Excel column to integer

fmt_txt() `+`(<fmt_txt>) as.character(<fmt_txt>) print(<fmt_txt>): format strings independent of the cell style.

convert_date() convert_datetime() convert_hms(): Convert from Excel date, datetime or hms number to R Date type

convert_to_excel_date(): convert back to an Excel Date

temp_xlsx(): helper function to create temporary directory for testing purpose

current_sheet() next_sheet() na_strings(): openxlsx2 waivers

dims_to_rowcol() rowcol_to_dims(): Helper functions to work with dims

clean_worksheet_name(): Clean worksheet name

styles_on_sheet(): Get all styles on a sheet

Package introduction

openxlsx2 openxlsx2-package: xlsx reading, writing and editing.

Package options

openxlsx2_options: Options consulted by openxlsx2

Deprecated

openxlsx2-deprecated: Deprecated functions in package openxlsx2