使用KableExtra生成表格时列名重复及PDF输出异常求助

问题根源是你的Excel工作表存在重复列名（比如旱季、雨季区域都使用了station 1这类名称），read_excel读取时会自动给重复列名添加后缀（如station 1....2），同时在knit PDF过程中输出"new names"提示信息。下面提供两种实用解决方法：

方法一：读取时直接指定自定义列名 #

提前整理好所有列的目标名称，读取时通过col_names参数传入，从根源避免自动重命名：

library(kableExtra)
library(readxl)
options(knitr.kable.NA = '')

# 按Excel列顺序编写自定义列名，确保旱季、雨季的station名称正确显示
custom_colnames <- c("物种", "station 1", "station 2", "station 3", "station 4", "...",  # 补充完整剩余列名
                     "station 1", "station 2", "station 3", "station 4", "...")

# 读取数据时指定列名，若Excel原第一行是重复表头，需用skip=1跳过该行
LAGOS_SHIP_CHANNEL <- read_excel("C:/Users/missw/Desktop/903 -/LAGOS SHIP CHANNEL.xlsx", 
    sheet = "dry _wet seasn",
    col_names = custom_colnames,
    skip = 1)  

dt <- LAGOS_SHIP_CHANNEL
kbl(dt, caption = "Monthly composition of zooplankton per ml at Lagos Ship Channel, Southwest Nigeria (Nov. 2021- Aug. 2022)", booktabs = T) %>%
row_spec(row = 1:13, italic = TRUE) %>%
row_spec(18:19, bold = T)  %>%
kable_styling(latex_options = c("HOLD_position")) %>%
kable_classic() %>%
add_header_above(c(" " = 1,"Dry season" = 7, "Wet season" = 7))

方法二：读取后手动修正列名并隐藏提示 #

如果不想提前编写全量列名，可以读取后替换自动生成的后缀，同时用suppressMessages隐藏提示：

library(kableExtra)
library(readxl)
options(knitr.kable.NA = '')

# 包裹读取代码消除"new names"提示
LAGOS_SHIP_CHANNEL <- suppressMessages(read_excel("C:/Users/missw/Desktop/903 -/LAGOS SHIP CHANNEL.xlsx", 
    sheet = "dry _wet seasn"))

dt <- LAGOS_SHIP_CHANNEL
# 移除列名末尾的....2、....3等自动添加的后缀
colnames(dt) <- gsub("\\.\\.\\.\\d+$", "", colnames(dt))

kbl(dt, caption = "Monthly composition of zooplankton per ml at Lagos Ship Channel, Southwest Nigeria (Nov. 2021- Aug. 2022)", booktabs = T) %>%
row_spec(row = 1:13, italic = TRUE) %>%
row_spec(18:19, bold = T)  %>%
kable_styling(latex_options = c("HOLD_position")) %>%
kable_classic() %>%
add_header_above(c(" " = 1,"Dry season" = 7, "Wet season" = 7))

注意：方法二中的正则表达式\\.\\.\\.\\d+$会匹配所有以....加数字结尾的后缀，若你的原始列名本身包含类似格式，需要调整正则规则适配。

内容的提问来源于stack exchange，提问作者Kay Awo