如何在R中读取并写入带有红色文本格式的Excel文件？

Absolutely! You can absolutely keep red text formatting intact when moving Excel data between your spreadsheet and R. The openxlsx package is perfect for this—it handles Excel formatting natively without requiring Java dependencies, which makes it way easier to work with than some alternatives.

Here's a step-by-step breakdown with code to make this happen:

Step 1: Install and Load the openxlsx Package #

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

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

# Load the package
library(openxlsx)

Step 2: Read the Excel File and Capture Red Text Locations #

Instead of just reading raw data with read.xlsx(), we'll load the entire workbook object so we can access cell formatting details. We'll then scan each cell to find which ones have red text, and store their positions:

# Load the workbook (replace "your_file.xlsx" with your actual file path)
wb <- loadWorkbook("your_file.xlsx")

# Get the first worksheet (adjust the sheet number/name if needed)
ws <- wb$sheets[[1]]

# Initialize a list to store cells with red text
red_cells <- list()

# Loop through each row and column to check font color
for (row in 1:ws$dimension[1]) {
  for (col in 1:ws$dimension[2]) {
    # Get the cell's style
    cell_style <- getCellStyle(wb, sheet = 1, rows = row, cols = col)
    
    # Check if the font color is standard red (hex code #FF0000)
    if (!is.null(cell_style$font$color) && cell_style$font$color == "#FF0000") {
      red_cells[[length(red_cells) + 1]] <- c(row, col)
    }
  }
}

# Now read the raw data into a data frame
df <- read.xlsx(wb, sheet = 1)

Step 3: Process Your Data (If Needed) #

You can manipulate the data frame df however you need—filter rows, add columns, clean values, etc.—the red cell positions we stored will still correspond to the correct cells in the data.

Step 4: Write the Data Back to Excel and Restore Red Text Formatting #

Create a new workbook, write your data, then apply the red font formatting to the cells we identified earlier:

# Create a new workbook
new_wb <- createWorkbook()

# Add a worksheet
addWorksheet(new_wb, "Formatted Data")

# Write the data frame to the worksheet
writeData(new_wb, sheet = 1, x = df)

# Create a red font style
red_font <- createStyle(fontColour = "#FF0000")

# Apply the red font to the stored cell positions
for (cell in red_cells) {
  addStyle(new_wb, sheet = 1, style = red_font, rows = cell[1], cols = cell[2])
}

# Save the new Excel file
saveWorkbook(new_wb, "formatted_output.xlsx", overwrite = TRUE)

Quick Notes #

• If your Excel uses a different shade of red, use Excel's format inspector to get the exact hex code and replace "#FF0000" with that value.
• For multiple worksheets, just adjust the sheet index/name and loop through each sheet to capture and apply formatting.
• This method works for other font styles too (bold, italic, other colors)—just tweak the style check and creation steps.

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