R语言导出Excel时为表格分配对应标题的技术问询

Let's break down the problem first: your original code was creating a vector of 4 labels for each dataframe instead of a single label, which caused writeData to output all 4 labels at once—leading to misaligned tables and repetitions. Here's how to fix it:

Key Changes Made: #

1. Use the index of each dataframe in its list to pick the correct gender term from your x vector.
2. Generate a single label string per dataframe instead of a vector.
3. Ensure worksheet creation works even if your original dataframe lists have no names.
4. Correct table numbering to match your desired format.

Corrected Code #

library(openxlsx) # Make sure this package is loaded

# Your original data
listofdfs <- list(data.frame("y"=c(2009,2010,2011),"b"=c(35,30,20)), 
                  data.frame("y"=c(2009,2010,2011), "b"=c(6,21,40)), 
                  data.frame("y"=c(2009,2010,2011), "a"=c(13,30,5), "b"=c(40,18,25)), 
                  data.frame("y"=c(2009,2010,2011), "a"=c(8,36,7), "b"=c(32,9,17)) )
listofdfs_2 <- list(data.frame("y"=c(2009,2010,2011),"b"=c(14,36,8)), 
                    data.frame("y"=c(2009,2010,2011), "b"=c(36,27,9)), 
                    data.frame("y"=c(2009,2010,2011), "a"=c(9,15,58), "b"=c(7,11,20)), 
                    data.frame("y"=c(2009,2010,2011), "a"=c(48,3,67), "b"=c(2,28,37)) )

# Define your gender term vector
x <- c("", "", "females", "males")

# Create workbook
mywb <- createWorkbook()

# Handle sheet names (use defaults if lists have no names)
all_sheet_names <- unique(c(names(listofdfs), names(listofdfs_2)))
if(length(all_sheet_names) == 0){
  all_sheet_names <- paste0("Sheet", 1:4)
  names(listofdfs) <- all_sheet_names
  names(listofdfs_2) <- all_sheet_names
}

# Add worksheets to the workbook
for(sheetName in all_sheet_names){
  addWorksheet(mywb, sheetName)
}

# Get all dataframe lists
l_listOfDF <- mget(ls(pattern="listofdf"))

# Initialize starting row for each sheet
startR <- rep(1, length(all_sheet_names)) 
names(startR) <- all_sheet_names

# Loop through each list of dataframes
for(N_myListOfDF in seq(l_listOfDF)){
  current_list <- l_listOfDF[[N_myListOfDF]]
  
  # Loop through each dataframe with its index (1-4)
  for(idx in seq_along(current_list)){
    pageName <- names(current_list)[idx]
    
    # Calculate table number: 
    # Use idx for per-list numbering (1-4 per list)
    # Or (N_myListOfDF-1)*4 + idx for overall numbering (1-8 total)
    table_num <- idx
    
    # Pick the correct gender term from x
    gender_term <- x[idx]
    
    # Build the label string
    if(gender_term == ""){
      label_2 <- paste0("Table ", table_num, ". Number of ", pageName, " users between 2010-2019")
    } else {
      label_2 <- paste0("Table ", table_num, ". Number of ", gender_term, " ", pageName, " users between 2010-2019")
    }
    
    # Write label and dataframe to the sheet
    writeData(mywb, sheet=pageName, startRow=startR[pageName], label_2)
    writeData(mywb, sheet=pageName, startRow=startR[pageName]+1, current_list[[idx]])
    
    # Update starting row for the next table in the sheet
    startR[pageName] <- startR[pageName] + nrow(current_list[[idx]]) + 3
  }
}

# Save the workbook
saveWorkbook(mywb , "diroutput\\pippo.xlsx", overwrite = TRUE)

How It Works: #

• For each dataframe in your lists, we use its index (idx) to grab the matching term from x (empty for first two, "females"/"males" for third/fourth).
• We generate one label per dataframe instead of a vector, so writeData only writes one header per table.
• Sheet names are handled gracefully—if your original lists have no names, we use default "Sheet1" to "Sheet4".
• Table numbering can be adjusted to either per-list (1-4) or overall (1-8) by modifying the table_num line.

内容的提问来源于stack exchange，提问作者jeff