using R, how can I go through multiples tabs on an Excel file to extract some data and sent to dataf-CodePudding

I have an Excel file which has multiple tabs, I would like to have a function where I can go tab by tab and extract the same column (it is always in the same position of every tab) and sent them to a dataframe, any idea about how to reach this?

#Excel #R

This is an example of the template that I have: excel file

CodePudding user response：

Here is a way with packages readxl and cellranger.
Get the sheet names in sheets then loop through them reading column 5, from row 7 to 31.
Change the path value ("~/Temp") to the path where your files are.

library(readxl)

path <- "~/Temp"
fl <- list.files(path = path, pattern = "\\.xlsx", full.names = TRUE)

sheets <- excel_sheets(fl)
cells <- cellranger::cell_limits(ul = c(7, 5), lr = c(31, 5))

df1 <- lapply(sheets, \(s) read_excel(path = fl, sheet = s, range = cells))
df1 <- do.call(cbind, df1)

CodePudding user response：

Use this:

library(openxlsx)
filenamerv<-file.choose() # File name
sheetsrv <- getSheetNames(filenamerv) #get all sheets
SheetListrv <- lapply(sheetsrv,read.xlsx,xlsxFile=filenamerv) #get all data in sheets and store in a list
names(SheetListrv) <- sheetsrv 
names(SheetListrv)