library(readxl)
<- read_xlsx("path_to_xlsx") df
2 Excel Stuff
{readxl} is the best package to read excel (xlsx) files.
{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.
<- list(
l "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)
<- "path_to_xlsx"
xlsx_path <- excel_sheets(xlsx_path)
sheet_names %>%
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)
<- function(data, sheet_name, file, dateFormat = "yyyy/mm/dd", overwrite = FALSE) {
write_oewd_xlsx <- createWorkbook()
wb addWorksheet(wb, sheet_name)
<- createStyle(halign = "LEFT", valign = "CENTER")
style 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)
<- createStyle(
headerStyle 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")