r - 如何自动计算具有或不具有行名的表的行数和列数

Question

我尝试将多个表写入一个 excel 文件。这是一种我可以获得每个表的计数然后定义我可以插入下一个表的行的方法吗？

示例表是：

tbl1 <- structure(list(`Event Count A` = c(NA, 
"72", "71 (98.61%)", "1 (1.39%)", "NA (NA%)"), `Event Count B` = c("22", 
"22", "0 (0.00%)", "0 (0.00%)", "12 (16.67%)"), `Patient Count A` = c(13L, 
13L, 1L, 12L, 8L), `Patient Count B` = c(8L, 
0L, 0L, NA, 72L)), row.names = c("Total number of patients", 
"Total number of AEs", "Non-Serious AE", 
"Serious AE", "Missing SAE Indicator"), class = "data.frame")

tbl2 <-structure(list(`Age Group` = c("Foetus (0 yr)", "Neonate (0.001 - 0.082 yr)", 
"Infant (0.083-1.999 yrs.)", "Child (2-12.999 yrs.)", "Adolescent (13-17.999 yrs.)", 
"Adult (18-64.999 yrs.)", "Elderly (65-199 yrs.)", "Total"), 
    `Patient Count A` = c(0L, 0L, 0L, 
    0L, 0L, 3L, 10L, 13L), `Patient Count B` = c(0L, 
    0L, 0L, 0L, 0L, 1L, 7L, 8L)), row.names = c(NA, -8L), class = "data.frame")

tbl3 <- structure(list(`Event Count A` = c(NA, 
"72", "71 (98.61%)", "1 (1.39%)", "NA (NA%)"), `Event Count B` = c("22", 
"22", "0 (0.00%)", "0 (0.00%)", "12 (16.67%)"), `Patient Count A` = c(13L, 
13L, 1L, 12L, 8L), `Patient Count B` = c(8L, 
0L, 0L, NA, 72L)), row.names = c("Total number of patients", 
"Total number of AEs", "Non-Serious AE", 
"Serious AE", "Missing SAE Indicator"), class = "data.frame")

tbl4 <-structure(list(`Age Group` = c("Foetus (0 yr)", "Neonate (0.001 - 0.082 yr)", 
"Infant (0.083-1.999 yrs.)", "Child (2-12.999 yrs.)", "Adolescent (13-17.999 yrs.)", 
"Adult (18-64.999 yrs.)", "Elderly (65-199 yrs.)", "Total"), 
    `Patient Count A` = c(0L, 0L, 0L, 
    0L, 0L, 3L, 10L, 13L), `Patient Count B` = c(0L, 
    0L, 0L, 0L, 0L, 1L, 7L, 8L)), row.names = c(NA, -8L), class = "data.frame")

将它们写入 Excel 的部分代码是：

###### Create a new excel workbook #################
wb <- createWorkbook()
addWorksheet(wb, sheetName = "Sample")

# Table 1: 
writeData(wb,sheet = "Sample", x = "Table 1",startRow = 1)
writeData(wb,sheet = "Sample", x = tbl1, colNames = TRUE,rowNames = TRUE,startRow = 2,  borders = "all")
addStyle(wb,sheet = "Sample", style = tableheaderStyle, rows = 2, cols = 1:Y (Y=5 for table 1), stack = TRUE)

# Table 2:
writeData(wb,sheet = "Sample", x = "Table 2",startRow = X)
writeData(wb,sheet = "Sample", x = tbl2,colNames = TRUE,startRow = X+1?, borders = "all")
addStyle(wb,sheet = "Sample", style = tableheaderStyle, rows = 2, cols = 1:Y (Y=3 for table 2, stack = TRUE)

我想知道是否可以根据每个插入表的行和列自动更改 x 和 y？在每个表之间留一个空行。一个挑战是有些表有行名，有些没有。手动计数太慢而且容易出错。

非常感谢。

标签： r

Answer 1

这接近一个答案：

library(openxlsx)

tbls <- list(tbl1, tbl2, tbl3, tbl4)         
rows <- cumsum(sapply(tbls, nrow))
cols <- sapply(tbls, ncol)

wb <- createWorkbook()
addWorksheet(wb, sheetName = "Sample")

writeData(wb,sheet = "Sample", x = "All tables",startRow = 1)
          
for(i in 1:length(rows)) {
  writeData(wb, sheet = "Sample", tbls[[i]], colNames = TRUE, startRow = rows[i], borders = "all")
  addStyle(wb,sheet = "Sample", style = headerStyle, rows = rows[i], cols = 1:cols[i], stack = TRUE)
}
         
saveWorkbook(wb, "writeDate.xlsx", overwrite = TRUE)

对于“一个挑战是有些表有行名，有些没有。”，它们的区别是什么=“年龄组”的存在。