时间戳

首页 > 解决方案 > 如何在R中突出显示excel单元格

r - 如何在R中突出显示excel单元格

问题描述

所以我有一堆数据正在查看。过去，我使用openxlsx突出显示整行。我想加强一点并突出显示特定的单元格。这是我正在使用的数据格式的示例

df <- structure(list(Name = c("ENSCAFG00000000019","ENSCAFG00000000052", "ENSCAFG00000000094","ENSCAFG00000000210"), baseMean = c(692.430970065448, 391.533849079888, 1223.74083601928, 280.477417588943), log2FoldChange = c("0.0819834415495699", 
"-2.6249568393179099", "6.15181461329998", "0.23483770613468"
), lfcSE = c("0.247177913269579", "0.65059275393549898", "0.33371763683349598", "0.353449339778654"), stat = c("4.3773467751931898", "-4.0347157625707997", 
"3.4514646101088902", "3.4936766522410099"), pvalue = c("1.20132758621478E-5", "5.4668435006169397E-5", "5.5755287106466398E-4", "4.7641767052765697E-4"), padj = c("9.8372077245438908E-4", "0.00004", "0.000006", "1.47480018315951E-2"), symbol = c("ZNF516", "CDH19", "LMAN1", "NA"), entrez = c("483930", "483948", "476186", "NA")), .Names = c("Names", "baseMean", "log2FoldChange", "lfcSE", "stat", "pvalue", "padj", "symbol", "entrez"), row.names = c(NA, -4L), class = c("tbl_df", "tbl", "data.frame"))

所以我想要做的是突出显示log2FoldChange<= -1 或 >= 1 的单元格并突出显示 <= 0.05 的单元格。这是可以做到的吗？我已经阅读了很多关于突出显示行而不是具有条件的特定单元格的内容。

在此处输入图像描述

这就是我希望我能得到数据的样子。和不需要像上面的例子那样组成log2Foldchange。padj

提前致谢

标签： r

解决方案

这是一个例子。但是请注意，列中的所有单元格的padj值都低于 0.05。

library(openxlsx)

# note that some columns of df look numeric, but are character
df <- data.frame(
    Name = c("ENSCAFG00000000019","ENSCAFG00000000052", "ENSCAFG00000000094","ENSCAFG00000000210"), 
    baseMean = c(692.430970065448, 391.533849079888, 1223.74083601928, 280.477417588943), 
    log2FoldChange = c(0.0819834415495699, -2.6249568393179099, 6.15181461329998, 0.23483770613468), 
    lfcSE = c(0.247177913269579, 0.65059275393549898, 0.33371763683349598, 0.353449339778654), 
    stat = c(4.3773467751931898, -4.0347157625707997, 3.4514646101088902, 3.4936766522410099), 
    pvalue = c(1.20132758621478E-5, 5.4668435006169397E-5, 5.5755287106466398E-4, 4.7641767052765697E-4), 
    padj = c(9.8372077245438908E-4, 0.00004, 0.000006, 1.47480018315951E-2), 
    symbol = c("ZNF516", "CDH19", "LMAN1", "NA"), entrez = c("483930", "483948", "476186", "NA"),
    stringsAsFactors=FALSE
)

# write dataset
wb <- createWorkbook()
addWorksheet(wb, sheetName="df")
writeData(wb, sheet="df", x=df)

# define style
yellow_style <- createStyle(fgFill="#FFFF00")

# log2FoldChange
y <- which(colnames(df)=="log2FoldChange")
x <- which(abs(df$log2FoldChange)>=1)
addStyle(wb, sheet="df", style=yellow_style, rows=x+1, cols=y, gridExpand=TRUE) # +1 for header line

# padj
y <- which(colnames(df)=="padj")
x <- which(abs(df$padj)<=0.05)
addStyle(wb, sheet="df", style=yellow_style, rows=x+1, cols=y, gridExpand=TRUE) # +1 for header line

# write result
saveWorkbook(wb, "yellow.xlsx", overwrite=TRUE)

您可能还想看看BERT。

推荐阅读