如何用R实现Excel指定数据范围跨工作簿复制？

Absolutely feasible! This is a common task, and we can use two reliable R packages—readxl for reading specific ranges, and openxlsx for writing values back without carrying over formatting. Here's a complete, commented script to get this done smoothly:

Step 1: Install & Load Required Packages #

First, make sure you have the necessary packages installed (run this once if you haven't already):

# Install packages if they're missing
if (!require("readxl")) install.packages("readxl")
if (!require("openxlsx")) install.packages("openxlsx")

# Load the packages into your R session
library(readxl)
library(openxlsx)

Step 2: Read the Target Range from WorkbookX #

We'll pull only the raw values from WorksheetX, range A5:X1000—no formatting will be included here:

# Read the exact range we need
source_data <- read_excel(
  path = "WorkbookX.xlsx",
  sheet = "WorksheetX",
  range = "A5:X1000"
)

Step 3: Write Values to WorkbookY's Target Range #

Next, we'll open WorkbookY, navigate to WorksheetY, and paste the values into A5:X1000. This will overwrite any existing data in that range but leave all other sheets and content in the workbook untouched:

# Load the existing WorkbookY
target_workbook <- loadWorkbook("WorkbookY.xlsx")

# Write the raw values to the specified range
writeData(
  wb = target_workbook,
  sheet = "WorksheetY",
  x = source_data,
  startCol = 1,  # Column A corresponds to column index 1
  startRow = 5,  # Start pasting at row 5
  keepNA = FALSE  # Optional: replaces NA values with blank cells (adjust if needed)
)

# Save the modified WorkbookY (overwrites the original file)
saveWorkbook(target_workbook, "WorkbookY.xlsx", overwrite = TRUE)

Quick Tips to Avoid Headaches #

• Values Only Guarantee: Both packages handle raw values by default—no cell colors, fonts, or formulas will be copied over, which is exactly what you requested.
• Preserve Other Data: Using loadWorkbook() ensures we don't wipe out any other sheets or content in WorkbookY; we only modify the A5:X1000 range in WorksheetY.
• Empty Cells: If A5:X1000 in WorkbookX has empty rows/columns, the script will still read the full range and fill empty spots with blanks (or NAs if you set keepNA = TRUE).

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