Creating Excel Workbooks
Generally, when doing anything in R I typically work with .csv files, their fast and straightforward to use. However, I find times, where I need to create a bunch of them to output and having to go and open each one individually, can be a pain for anyone. In this case, it’s much better to create a workbook where each of the .csv files you would have created will now be a separate sheet.
Below is a simple script I use frequently that gets the job done. Also included is the initial process of creating dummy data to outline the process.
EXAMPLE CODE:
Libraries used
library(tidyverse) library(openxlsx)
Creating example files to work with
products <- c("Monitor", "Laptop", "Keyboards", "Mice") Stock <- c(20,10,25,50) Computer_Supplies <- cbind(products,Stock) products <- c("Packs of Paper", "Staples") Stock <- c(100,35) Office_Supplies <- cbind(products,Stock)
Take these new data frames and save them using the write.csv function.
write.csv(“Data”, “Where you want to store the data.csv”, row.names = FALSE)
write.csv(Computer_Supplies, "Data/ComputerSupplies.csv", row.names = FALSE) write.csv(Office_Supplies, "Data/OfficeSupplies.csv", row.names = FALSE)
Point to directory your files are located in (.csv here) and read each in as a list
# Get the file name read in as a column read_filename <- function(fname) { read_csv(fname, col_names = TRUE) %>% mutate(filename = fname) } tbl <- list.files(path = "Data/", pattern ="*.csv", full.names = TRUE) %>% map_df(~read_filename(.))
Removing path from the file names
*Note: Max length of a Workbook’s name is 31 characters
You may need to edit the filenames, even if the names aren’t too long I will still do the code below. This will remove the path before the filename and .csv on the end.
tbl$filename <- gsub("Data/", "", tbl$filename) tbl$filename <- gsub(".csv", "", tbl$filename)
Split the “tbl” object into individual lists
mylist <- tbl %>% split(.$filename) names(mylist) ## [1] "/ComputerSupplies" "/OfficeSupplies"
Creating an Excel workbook and having each CSV file be a separate sheet
wb <- createWorkbook() lapply(seq_along(mylist), function(i){ addWorksheet(wb=wb, sheetName = names(mylist[i])) writeData(wb, sheet = i, mylist[[i]][-length(mylist[[i]])]) }) #Save Workbook saveWorkbook(wb, "test.xlsx", overwrite = TRUE
Reading in sheets from an Excel file
(The one we just created)
df_ComputerSupplies <- read.xlsx("test.xlsx", sheet = 1)
Loading and adding a new sheet to an already existing Excel workbook
wb <- loadWorkbook("test.xlsx") names(wb) ## [1] "/ComputerSupplies" "/OfficeSupplies" addWorksheet(wb, "News Sheet Name") names(wb) ## [1] "/ComputerSupplies" "/OfficeSupplies" "News Sheet Name"
thank you for sharing this. such a helpful post