如何将.xlsx中CV工作表的单元格底纹复制到对应非CV工作表？

Here are two practical approaches to achieve your goal using the openxlsx package in R:

Approach 1: Copy Existing Cell Background Fills #

This method replicates the static background colors already applied to cells in CV sheets to their corresponding non-CV sheets.

Step-by-Step Code #

library(openxlsx)

# Get all sheet names from your workbook
all_sheets <- getSheetNames(new_workbook)

# Filter sheets ending with " CV"
cv_sheets <- grep(" CV$", all_sheets, value = TRUE)

# Loop through each CV sheet
for (cv_sheet in cv_sheets) {
  # Derive matching non-CV sheet name
  non_cv_sheet <- sub(" CV$", "", cv_sheet)
  
  # Skip if non-CV sheet doesn't exist
  if (!non_cv_sheet %in% all_sheets) {
    message(paste("Skipping:", cv_sheet, "-", non_cv_sheet, "not found"))
    next
  }
  
  # Reuse your existing row range calculation
  template_data <- AllWorkbooks[AllWorkbooks$Template == wb, ]
  start_row <- 10
  end_row <- 10 + as.numeric(template_data[[14]]) - as.numeric(template_data[[13]])
  target_rows <- start_row:end_row
  target_cols <- 3:17
  
  # Copy background fill for each target cell
  for (row in target_rows) {
    for (col in target_cols) {
      # Get style from CV sheet cell
      cell_style <- getStyle(new_workbook, sheet = cv_sheet, row = row, col = col)
      
      # Apply background fill to non-CV sheet if present
      if (!is.null(cell_style$bgFill)) {
        new_style <- createStyle(bgFill = cell_style$bgFill, fontSize = 12)
        addStyle(new_workbook, sheet = non_cv_sheet, style = new_style, rows = row, cols = col, gridExpand = FALSE)
      }
    }
  }
  
  message(paste("Copied styles from", cv_sheet, "to", non_cv_sheet))
}

# Save modified workbook
saveWorkbook(new_workbook, "PASHE_updated.xlsx", overwrite = TRUE)

Approach 2: Apply Identical Conditional Formatting Rules #

Use this method if you want non-CV sheets to automatically update formatting when cell values change, mirroring the dynamic rules used for CV sheets.

Step-by-Step Code #

library(openxlsx)

# Reuse your existing style definitions
cvFive <- createStyle(bgFill = "#5bc0de", fontSize = 12)
cvTen <- createStyle(bgFill = "#428bca", fontSize = 12)

# Get all sheet names
all_sheets <- getSheetNames(new_workbook)

# Filter CV sheets to find non-CV counterparts
cv_sheets <- grep(" CV$", all_sheets, value = TRUE)

for (cv_sheet in cv_sheets) {
  non_cv_sheet <- sub(" CV$", "", cv_sheet)
  
  if (!non_cv_sheet %in% all_sheets) {
    message(paste("Skipping:", cv_sheet, "-", non_cv_sheet, "not found"))
    next
  }
  
  # Reuse row range calculation
  template_data <- AllWorkbooks[AllWorkbooks$Template == wb, ]
  start_row <- 10
  end_row <- 10 + as.numeric(template_data[[14]]) - as.numeric(template_data[[13]])
  target_rows <- start_row:end_row
  target_cols <- 3:17
  
  # Apply conditional formatting to non-CV sheet
  conditionalFormatting(new_workbook,
                        sheet = non_cv_sheet,
                        rows = target_rows,
                        cols = target_cols,
                        type = "between",
                        rule = c(5.001, 10),
                        style = cvFive)
  
  conditionalFormatting(new_workbook,
                        sheet = non_cv_sheet,
                        rows = target_rows,
                        cols = target_cols,
                        type = "between",
                        rule = c(10.001, 9996),
                        style = cvTen)
  
  message(paste("Applied conditional formatting to", non_cv_sheet))
}

# Save workbook
saveWorkbook(new_workbook, "PASHE_updated.xlsx", overwrite = TRUE)

Notes #

• Replace new_workbook with your actual workbook object if needed.
• Ensure you're using a recent version of openxlsx for the getStyle function to work correctly.
• Approach 1 is ideal for static replication of current formatting; Approach 2 maintains dynamic formatting tied to cell values.

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