In R, How to highlight values of a specific column and export to existing xlsx file-CodePudding

In iris tibble, How do I highlight the rows where Petal.Width < 1.0 and export the output as a worksheet/tab of an existing xlsx file

# tibble
library(dplyr)
library(tidyr)
library(broom)

set.seed(1234)

df <- iris[sample(nrow(iris), 20), ]
df

output

# tibble
 Sepal.Length Sepal.Width Petal.Length Petal.Width    Species
28           5.2         3.5          1.5         0.2     setosa
80           5.7         2.6          3.5         1.0 versicolor
101          6.3         3.3          6.0         2.5  virginica
111          6.5         3.2          5.1         2.0  virginica
137          6.3         3.4          5.6         2.4  virginica
133          6.4         2.8          5.6         2.2  virginica
144          6.8         3.2          5.9         2.3  virginica
132          7.9         3.8          6.4         2.0  virginica
98           6.2         2.9          4.3         1.3 versicolor
103          7.1         3.0          5.9         2.1  virginica
90           5.5         2.5          4.0         1.3 versicolor
70           5.6         2.5          3.9         1.1 versicolor
79           6.0         2.9          4.5         1.5 versicolor
116          6.4         3.2          5.3         2.3  virginica
14           4.3         3.0          1.1         0.1     setosa
126          7.2         3.2          6.0         1.8  virginica
62           5.9         3.0          4.2         1.5 versicolor
4            4.6         3.1          1.5         0.2     setosa
143          5.8         2.7          5.1         1.9  virginica
40           5.1         3.4          1.5         0.2     setosa

My attempt

please note: my attempt does not show highlighted rows in the xlsx file, so I am open to other approaches.

library(knitr)
library(kableExtra)
library(xlsx)

# with the below code, I am unable to export as an xlsx file because "kable" is not a dataframe
df_highlighted <- kable(df)%>%row_spec(which(df$`Petal.Width` < 1.0), color="red")
df_highlighted

# export the dataframe to an existing xlsx file
write.xlsx(df_highlighted, file="existing_file.xlsx", sheetName="df_highlighted", append=TRUE, row.names=FALSE)

Please comment and share your code. Thanx in advance

CodePudding user response：

You can do this with the openxlsx package. Here is an example:

library(openxlsx)

set.seed(1234)

df <- iris[sample(nrow(iris), 20), ]

wb <- createWorkbook()

addWorksheet(wb, "iris")

writeData(wb, "iris", df)

highlight <- createStyle(fontColour = "#000000",
                         bgFill = "#FFFF00")

conditionalFormatting(wb,
                      "iris",
                      cols = 1:length(df),
                      rows = 1:nrow(df),
                      rule = "$D1<1",
                      style = highlight)

saveWorkbook(wb, "example.xlsx", overwrite = TRUE)