如果始终有3个空白行分隔表，并且始终以表名开头，然后是列名，则应执行以下操作。
在这里，我们首先读入原始数据，然后解析它来找到每个表的位置（使用3个空行规则）。这是通过为3个NA值的字符串找到match来完成的，这就是read_excel()读取空行的方法。
对于宽度，这个解决方案利用了这样一个事实，即如果你指定了起始行和结束行，read_excel()就可以猜到宽度。因此，只要表格中没有其他东西会混淆它，你就不必弄清楚宽度。
出于演示的目的，我制作了一个最小的示例文件，其中只有前3行mtcars和iris，但它应该可以缩放到任意大小。

library(tidyverse)
library(readxl)
library(writexl) # for creating test input

# sample data
d <- structure(list(...1 = c("MTCARS", "mpg", "21", "21", "22.8", NA, NA, NA, "IRIS", "Sepal.Length", "5.0999999999999996", "4.9000000000000004", "4.7"), ...2 = c(NA, "cyl", "6", "6", "4", NA, NA, NA, NA, "Sepal.Width", "3.5", "3", "3.2"), ...3 = c(NA, "disp", "160", "160", "108", NA, NA, NA, NA, "Petal.Length", "1.4", "1.4", "1.3"), ...4 = c(NA, "hp", "110", "110", "93", NA, NA, NA, NA, "Petal.Width", "0.2", "0.2", "0.2"), ...5 = c(NA, "drat", "3.9", "3.9", "3.85", NA, NA, NA, NA, "Species", "setosa", "setosa", "setosa"), ...6 = c(NA, "wt", "2.62", "2.875", "2.3199999999999998", NA, NA, NA, NA, NA, NA, NA, NA), ...7 = c(NA, "qsec", "16.46", "17.02", "18.61", NA, NA, NA, NA, NA, NA, NA, NA), ...8 = c(NA, "vs", "0", "0", "1", NA, NA, NA, NA, NA, NA, NA, NA), ...9 = c(NA, "am", "1", "1", "1", NA, NA, NA, NA, NA, NA, NA, NA), ...10 = c(NA, "gear", "4", "4", "4", NA, NA, NA, NA, NA, NA, NA, NA), ...11 = c(NA, "carb", "4", "4", "1", NA, NA, NA, NA, NA, NA, NA, NA)), class = c("tbl_df", "tbl", "data.frame"), row.names = c(NA, -13L))
d %>% write_xlsx("test.xlsx", format_headers = F, col_names = F)

# read in raw data
raw_input <- read_excel("test.xlsx", col_names = F)

# take first column to find each table
first_col <- raw_input %>% pull(1)

# find positions for each table
table_start_rows <- c(1, which(first_col %in% c(NA,NA,NA))[c(T,F,F)] + 3) + 1
table_end_rows <- c(which(first_col %in% c(NA,NA,NA))[c(T,F,F)] - 1, length(first_col))
table_lengths <- table_end_rows - table_start_rows
# extract table names
table_names <- first_col[table_start_rows - 1]

# read in each table and capture into list
l <- map2(.x = (table_start_rows - 1),
     .y = table_lengths,
     .f = ~read_excel("test.xlsx", skip = .x, n_max = .y))

# add table names
names(l) <- table_names

# inspect output
l
#> $MTCARS
#> # A tibble: 3 × 11
#>   mpg   cyl   disp  hp    drat  wt                 qsec  vs    am    gear  carb 
#>   <chr> <chr> <chr> <chr> <chr> <chr>              <chr> <chr> <chr> <chr> <chr>
#> 1 21    6     160   110   3.9   2.62               16.46 0     1     4     4    
#> 2 21    6     160   110   3.9   2.875              17.02 0     1     4     4    
#> 3 22.8  4     108   93    3.85  2.3199999999999998 18.61 1     1     4     1    
#> 
#> $IRIS
#> # A tibble: 3 × 5
#>   Sepal.Length       Sepal.Width Petal.Length Petal.Width Species
#>   <chr>              <chr>       <chr>        <chr>       <chr>  
#> 1 5.0999999999999996 3.5         1.4          0.2         setosa 
#> 2 4.9000000000000004 3           1.4          0.2         setosa 
#> 3 4.7                3.2         1.3          0.2         setosa

创建于2022年11月17日，使用reprex v2.0.2