在R中使用XLConnect::readWorksheet保留原始列名的方法

Great question! I’ve run into this exact quirk with XLConnect before—it’s super frustrating when your carefully formatted Excel column headers get automatically altered without warning. Let’s break down how to keep your original column names intact, just like data.table does.

1. Disable Automatic Column Name Cleaning #

The root of the issue is XLConnect’s default behavior: the readWorksheet() function uses check.names = TRUE by default, which converts any non-R-friendly characters (like spaces, slashes, or special symbols) into dots to create valid variable names. To turn this off, simply set check.names = FALSE when reading your worksheet:

library(XLConnect)
# Load your workbook
wb <- loadWorkbook("your_data_file.xlsx")
# Read the sheet with original column names preserved
your_data <- readWorksheet(wb, sheet = "Sheet1", check.names = FALSE)

After running this, your columns will retain their original names: "First Name" and "Sex/Gender" instead of "First.Name" and "Sex.Gender".

2. Referencing Columns with Special Characters #

Just like in data.table, when your column names have spaces, slashes, or other non-standard characters, you’ll need to wrap them in backticks (`) to reference them in R. Here’s how to do it:

# Using $ notation with backticks
your_data$`First Name`

# Using bracket notation (works without backticks too, but backticks are consistent)
your_data[["Sex/Gender"]]

# If you convert to data.table, the same syntax applies
library(data.table)
dt <- as.data.table(your_data)
dt[, `First Name`]

This keeps your column names true to their original Excel format while still letting you work with them seamlessly in R.

Quick Extra Tip #

If you accidentally read the data with default settings and need to revert to original column names (assuming you have a reference), you can manually overwrite the column names later:

# Replace with your actual original column names
colnames(your_data) <- c("First Name", "Sex/Gender", "Other Column")

But setting check.names = FALSE upfront is the cleanest solution.

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