2  Excel Stuff

{readxl} is the best package to read excel (xlsx) files.

library(readxl)
df <- read_xlsx("path_to_xlsx")

{writexl} is the best package to write excel (xlsx) files.

library(writexl)
write_xlsx(mtcars, "car_data.xlsx")

2.1 Miscellaneous Tips and Tricks

You can also pass a named list of data frames to write_xlsx, and it will write each data frame to a separate sheet in the workbook.

l <- list(
  "Car data" = mtcars,
  "Flower data" = iris
)

write_xlsx(l, "my_data.xlsx")

With the {purrr} package you can look through a directory of identically structured spreadsheets and bind them together:

library(purrr)
dir("path_to_directory", full.names = TRUE) %>% 
  map_dfr(\(file) read_csv(file)) # or map(\(file) read_csv(file)) %>% list_rbind()

With the {kapow} package package you can loop through each sheet in an xlsx workbook and assign the table to its sheet name in your global environment:

library(readxl)
library(purrr)
library(kapow) # remotes::install_github("daranzolin/kapow)

xlsx_path <- "path_to_xlsx"
sheet_names <- excel_sheets(xlsx_path)
sheet_names %>% 
  map(\(sheet) read_excel(xlsx_path, sheet = sheet)) %>% 
  set_names(sheet_names) %>% 
  kapow()

To apply special formatting to an Excel workbook in R you can use the {openxlsx} package. Here’s an example of how OEWD writes to xlsx:

library(openxlsx)
write_oewd_xlsx <- function(data, sheet_name, file, dateFormat = "yyyy/mm/dd", overwrite = FALSE) {
  wb <- createWorkbook()
  addWorksheet(wb, sheet_name)
  style <- createStyle(halign = "LEFT", valign = "CENTER")
  setColWidths(wb, sheet = 1, cols = 1:ncol(data), widths = "auto")
  addStyle(wb, 1,
           cols = 1:(ncol(data) + 1),
           rows = 1:(nrow(data) + 1),
           style = style,
           gridExpand = TRUE)
  headerStyle <- createStyle(
    halign = "LEFT",
    textDecoration = "Bold"
  )
  writeData(wb, 1, data, headerStyle = headerStyle)
  options("openxlsx.dateFormat" = dateFormat)
  saveWorkbook(wb, file, overwrite = overwrite)
}

write_oewd_xlsx(mtcars, "Car data", "car_data.xlsx")