Package index

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 thewbWorkbook object, except forget_ 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. Seevignette("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. Seevignette("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 awbWorkbook 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_get_bookview()wb_remove_bookview()wb_set_bookview(): Get and 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 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 thewb_data attribute to a data frame in a worksheet
wb_dims(): Helper to specify thedims argument
create_border(): Create border format
create_cell_style(): Create 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 spreadsheet hyperlink string
create_numfmt(): Create number format
create_shape(): Helper to create a shape
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_set_page_setup()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 spreadsheet column
col2int(): Convert spreadsheet 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 spreadsheet 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 withdims
clean_worksheet_name(): Clean worksheet name
styles_on_sheet(): Get all styles on a sheet

Package introduction

openxlsx2openxlsx2-package: xlsx reading, writing and editing.

Package options

openxlsx2_options: Options consulted by openxlsx2

Deprecated

openxlsx2-deprecated: Deprecated functions in packageopenxlsx2

Movatterモバイル変換