Importing multiple .xlsx files with multiple sheets in R-CodePudding

I have problem when importin multiple .xlsx files with multiple sheets in R. I have 6 Excel-files with each 5 different worksheets (They have all the same length). I would like to have them imported in R as a following list form:

[[1]][[1]]
[[2]][[1]]
[[3]][[1]]
[[4]][[1]]
[[5]][[1]]
[[6]][[1]]
[[2]][[1]]
[[2]][[2]]
[[2]][[3]]
.
.
.
[[6]][[5]]

where the first list corresponds the specific Excel-file and second the worksheet. So, first [[1]][[1]] is the first Excel-files first worksheet.

I have written the following code

path_ <- "~/Desktop/my_folder/"

#This is suppose to read all the Excel-files
file.list <- list.files(path = paste(path_), pattern='*.xlsx', full.names = TRUE)
df.list <- lapply(file.list, function (x)  read_xlsx(x))

But it only returns the first worksheet from each of the six Excel-files.

[[1]]
[[2]]
[[3]]
[[4]]
[[5]]
[[6]]

I can't figure out how to get this to work. Can someone help me with this?

CodePudding user response：

Let's have 2 files with two worksheets each:

library(tidyverse)
library(readxl)

list.files("~/data", full.names = TRUE)
#> [1] "/home/rstudio/data/data.xlsx"  "/home/rstudio/data/data2.xlsx"
read_excel("/home/rstudio/data/data.xlsx", sheet = 1)
#> # A tibble: 2 x 2
#>   a       `1`
#>   <chr> <dbl>
#> 1 b         2
#> 2 c        NA
read_excel("/home/rstudio/data/data.xlsx", sheet = 2)
#> # A tibble: 2 x 2
#>   d       `4`
#>   <chr> <dbl>
#> 1 e         5
#> 2 f         6


expand_grid(
  file = list.files("~/data", full.names = TRUE),
  sheet = seq(2)
) %>%
  transmute(data = file %>% map2(sheet, ~ read_excel(path = .x, sheet = .y))) %>%
  pull(data)
#> [[1]]
#> # A tibble: 2 x 2
#>   a       `1`
#>   <chr> <dbl>
#> 1 b         2
#> 2 c        NA
#> 
#> [[2]]
#> # A tibble: 2 x 2
#>   d       `4`
#>   <chr> <dbl>
#> 1 e         5
#> 2 f         6
#> 
#> [[3]]
#> # A tibble: 2 x 2
#>   a       `1`
#>   <chr> <dbl>
#> 1 b         2
#> 2 c        NA
#> 
#> [[4]]
#> # A tibble: 2 x 2
#>   d       `4`
#>   <chr> <dbl>
#> 1 e         5
#> 2 f         6

^{Created on 2021-11-11 by the reprex package (v2.0.1)}