如何用R识别.xlsx格式Excel文件中的带批注单元格？

Absolutely! You can detect cells with comments in an Excel (.xlsx) file when working in R, and run conditional if else logic specifically on those cells. The most straightforward and reliable approach uses the openxlsx package—it’s lightweight, doesn’t require Java dependencies, and has built-in tools for handling Excel comments. Here’s a step-by-step breakdown:

First, make sure you have the package installed (if not) and loaded into your R environment:

# Install if you haven't already
install.packages("openxlsx")

# Load the package
library(openxlsx)

Instead of using read.xlsx (which only imports cell values), use loadWorkbook to load the entire workbook—this preserves comment metadata:

# Replace "your_file.xlsx" with your actual file path
wb <- loadWorkbook("your_file.xlsx")

Use getComments() to pull all comments from your target worksheet. This returns a list where each entry is a named vector (cell references as names, comment text as values):

# Extract comments from the first worksheet (replace 1 with sheet name if needed)
comments_list <- getComments(wb, sheet = 1)

# Convert the list to a data frame for easier manipulation
comment_df <- do.call(rbind, lapply(comments_list, function(x) {
  data.frame(
    cell_ref = names(x),
    comment_text = unlist(x),
    stringsAsFactors = FALSE
  )
}))

Next, you’ll need to translate Excel-style cell references (like "A1", "B3") into row/column indices that match your imported data. First, import the actual cell values:

# Import the data from the same worksheet
data <- read.xlsx(wb, sheet = 1)

Then, convert cell references to row/column positions using a simple helper function:

# Helper function to split cell reference into row and column
parse_cell_ref <- function(cell_ref) {
  # Extract column letters (all non-digit characters)
  col_letters <- gsub("[0-9]", "", cell_ref)
  # Extract row number (all digit characters)
  row_num <- as.integer(gsub("[^0-9]", "", cell_ref))
  # Convert column letters to column index (A=1, B=2, etc.)
  col_num <- openxlsx::convertFromExcelRef(col_letters)
  return(list(row = row_num, col = col_num))
}

# Apply the helper function to all commented cells
comment_positions <- lapply(comment_df$cell_ref, parse_cell_ref)

# Create a logical matrix matching your data's dimensions, marking commented cells
has_comment <- matrix(FALSE, nrow = nrow(data), ncol = ncol(data))
for (pos in comment_positions) {
  # Note: If your data has a header row, subtract 1 from row_num (adjust as needed)
  has_comment[pos$row, pos$col] <- TRUE
}

Now you can use the has_comment matrix to run your if else checks. For example, let’s modify values in commented cells to flag them:

# Loop through each cell to apply conditional logic
for (i in 1:nrow(data)) {
  for (j in 1:ncol(data)) {
    if (has_comment[i, j]) {
      # Your custom logic for commented cells
      data[i, j] <- paste("[Commented]", data[i, j])
    } else {
      # Your logic for non-commented cells (optional)
      data[i, j] <- paste("[No Comment]", data[i, j])
    }
  }
}

# View the modified data
print(data)

Alternative: Using the xlsx Package #

If you already work with the xlsx package (which requires Java), you can also extract comments using its getComments() function—though the workflow is slightly more verbose due to Java dependencies. openxlsx is generally preferred for its simplicity and lack of external requirements.

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