Skip to contents

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() wb_remove_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_hyperlink() wb_remove_hyperlink()
wb_add_hyperlink
wb_add_pivot_table()
Add a pivot table to a worksheet
wb_add_slicer() wb_remove_slicer() wb_add_timeline() wb_remove_timeline()
Add a slicer/timeline 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_add_mips() wb_get_mips()
wb get and apply MIP section
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