Upgrade from openxlsx

Basic read and write functions

Welcome to the openxlsx2 update vignette. In this vignette we will take some common code examples from openxlsx and show you how similar results can be replicated in openxlsx2. Thank you for taking a look, and let’s get started. While previous openxlsx functions used the . in function calls, as well as camel-case, we have tried to switch to snake-case (this is still a work in progress, there are still function arguments that use camel-case).

Read xlsx or xlsm files

The basic read function changed from read.xlsx to read_xlsx. Using a default xlsx file included in the package:

xlsxFile <- system.file("extdata", "readTest.xlsx", package = "openxlsx2")

The old syntax looked like this:

# read in openxlsx
openxlsx::read.xlsx(xlsxFile)

This has changed to this:

# read in openxlsx2
openxlsx2::read_xlsx(xlsxFile)

##     Var1 Var2 NA  Var3  Var4       Var5         Var6    Var7
## 2   TRUE    1 NA     1     a 2015-02-07 3209324 This #DIV/0!
## 3   TRUE   NA NA #NUM!     b 2015-02-06         <NA>    <NA>
## 4   TRUE    2 NA  1.34     c 2015-02-05         <NA>   #NUM!
## 5  FALSE    2 NA  <NA> #NUM!       <NA>         <NA>    <NA>
## 6  FALSE    3 NA  1.56     e       <NA>         <NA>    <NA>
## 7  FALSE    1 NA   1.7     f 2015-02-02         <NA>    <NA>
## 8     NA   NA NA  <NA>  <NA> 2015-02-01         <NA>    <NA>
## 9  FALSE    2 NA    23     h 2015-01-31         <NA>    <NA>
## 10 FALSE    3 NA  67.3     i 2015-01-30         <NA>    <NA>
## 11    NA    1 NA   123  <NA> 2015-01-29         <NA>    <NA>

As you can see, we return the spreadsheet return codes (e.g., #NUM) in openxlsx2. Another thing to see above, we return the cell row as rowname for the data frame returned. openxlsx2 should return a data frame of the selected size, even if it empty. If you preferred readWorksheet() this has become wb_read(). All of these are wrappers for the newly introduced function wb_to_df() which provides the most options. read_xlsx() and wb_read() were created for backward comparability.

Write xlsx files

Basic writing in openxlsx behaves identical to openxlsx. Though be aware that overwrite is an optional parameter in openxlsx and just like in other functions like base::write.csv if you write onto an existing file name, this file will be replaced.

Setting the output to some temporary xlsx file

output <- temp_xlsx()

The previous write function looks like this:

# write in openxlsx
openxlsx::write.xlsx(iris, file = output, colNames = TRUE)

The new function looks quite similar:

# write in openxlsx2
openxlsx2::write_xlsx(iris, file = output, colNames = TRUE)

Basic workbook functions

Workbook functions have been renamed to begin with wb_ there are plenty of these in the package, therefore looking at the man pages seems to be the fastest way. Yet, it all begins with loading the workbook.

Loading a workbook

A major feature in openxlsx are workbooks. Obviously they remain a central piece in openxlsx2. Previous you would load them with:

wb <- loadWorkbook(xlsxFile)

In openxlsx2 loading was changed to:

wb <- wb_load(xlsxFile)

There are plenty of functions to interact with workbooks and we will not describe every single one here. A detailed list can be found over at our references

Styles

One of the biggest user facing change was the removal of the stylesObject. In the following section we use code from addStyle

## Create a new workbook
wb <- createWorkbook("My name here")
addWorksheet(wb, "Expenditure", gridLines = FALSE)
writeData(wb, sheet = 1, USPersonalExpenditure, rowNames = TRUE)

## style for body
bodyStyle <- createStyle(border = "TopBottom", borderColour = "#4F81BD")
addStyle(wb, sheet = 1, bodyStyle, rows = 2:6, cols = 1:6, gridExpand = TRUE)

## set column width for row names column
setColWidths(wb, 1, cols = 1, widths = 21)

In openxlsx2 the same code looks something like this:

border_color <- wb_colour(hex = "FF4F81BD")
wb <- wb_workbook("My name here")$
  add_worksheet("Expenditure", gridLines = FALSE)$
  add_data(x = USPersonalExpenditure, rowNames = TRUE)$
  add_border( # add the outer and inner border
    dims = "A1:F6",
    top_border = "thin", top_color = border_color,
    bottom_border = "thin", bottom_color = border_color,
    inner_hgrid = "thin", inner_hcolor = border_color,
    left_border = "", right_border = ""
  )$
  set_col_widths( # set column width
    cols = 1:6,
    widths = c("20", rep("10", 5))
  )$ # remove the value in A1
  add_data(dims = "A1", x = "")

The code above uses chaining. If you prefer piping, we provide the chained functions with the prefix wb_ so wb_add_worksheet(), wb_add_data(), wb_add_border() and wb_set_col_widths() would be the functions to use with pipes %>% or |>.

You can re-use styles with wb_get_cell_style() and wb_set_cell_style(). Abandoning stylesObject in openxlsx2 has the huge benefit that we can import and export a spreadsheet without changing any cell style. It is still possible to modify a cell style with wb_add_border(), wb_add_fill(), wb_add_font() and wb_add_numfmt().

Additional examples regarding styles can be found in the styles vignette.

Conditional formatting

Extended examples for conditional formatting can be found in the conditional formatting vignette. A minimal example is the following:

wb <- wb_workbook()$
  add_worksheet("a")$
  add_data(x = 1:4, colNames = FALSE)$
  add_conditional_formatting(cols = 1, rows = 1:4, rule = ">2")

Data validation

Similar data validation has been updated and improved. This openxlsx code for data validation

wb <- createWorkbook()
addWorksheet(wb, "Sheet 1")
writeDataTable(wb, 1, x = iris[1:30, ])
dataValidation(wb, 1,
  col = 1:3, rows = 2:31, type = "whole",
  operator = "between", value = c(1, 9)
)

looks in openxlsx2 something like this:

wb <- wb_workbook()$
  add_worksheet("Sheet 1")$
  add_data_table(1, x = iris[1:30, ])$
  add_data_validation(1,
    col = 1:3, rows = 2:31, type = "whole",
    operator = "between", value = c(1, 9)
  )

Saving

Saving has been switched from saveWorbook() to wb_save() and opening a workbook has been switched from openXL() to wb_open().