Export to excel and put color in specific case when a string is present

tidyverse
1

Hi community

Im want to export an excel file but put color in some columns but depend a string in other column

So, when CHECK_LATITUD, CHECK_LONGITUD,CHECK_DEPARTAMENTO,CHECK_MUNICIPIO,CHECK_UBICACION,CHECK_CIUDAD_CERCANA is different put a color in this cell but if correspond to latitude, longitude,DEPARTAMENTO,MUNICIPIO ,UBICACION.

datos<-structure(list(ACCESION = c("BRA1000", "BRA1005", "BRA1006", 
"BRA1007", "BRA1010", "BRA1011", "BRA1012", "BRA1015", "BRA1017", 
"BRA1018", "BRA1019", "BRA1020", "BRA1031", "BRA1033", "BRA1035"
), latitude = c(-4.12, 0, -19.38, -18.7, -18.38, -13.52, -17.3, 
-9.75, -10.98, -10.98, -10.98, -10.98, -3.1, -12.8, -3.72), longitude = c(-44.12, 
0, -40.07, -39.85, -40.28, -39.18, -39.22, -36.65, -37.3, -37.3, 
-37.3, -37.3, -60.02, -41.32, -40.98), PAIS_ORIGEN = c("BRA", 
"BRA", "BRA", "BRA", "BRA", "BRA", "BRA", "BRA", "BRA", "BRA", 
"BRA", "BRA", "BRA", "BRA", "BRA"), DEPARTAMENTO = c("MARANHOA", 
"ACRE", "ESPIRITU SANTO", "ESPIRITU SANTO", "ESPIRITU SANTO", 
"BAHIA", "BAHIA", "ALAGOAS", "SERGIPE", "SERGIPE", "SERGIPE", 
"SERGIPE", "AMAZONAS", "BAHIA", "CEARA"), MUNICIPIO = c("COROATA", 
NA, "LINHARES", "SAO MATEUS", "BOA ESPERANCA", "ALCOBACA", "PRADO", 
"ARAPIRACA", "ITAPORANGA D'AJUDA", "ITAPORANGA D'AJUDA", "ITAPORANGA DA AJUDA", 
"ITAPORANGA DA AJUDA", "MANAUS", "ANDARAI", "TIANGUA"), UBICACION = c(NA, 
NA, "H.LAGOA DOURADA", "BOA VISTA", NA, NA, NA, "SITIO VATINGA", 
"LOC: CHA DO SAPE", "LOC: CHA DO SAPE", "LOC: CHA DO SAPE", "LOC: CHA DO SAPE", 
"KM 32 DA ROD-AM", "KM 14 DA BR 242", "KM 05 BR 222"), CHECK_LATITUD = c("same", 
NA, "same", "same", "different", "different", "same", "same", 
"same", "same", "same", "same", "same", "same", "same"), CHECK_LONGITUD = c("same", 
NA, "same", "same", "same", "different", "different", "same", 
"same", "same", "same", "same", "same", "same", "same"), CHECK_DEPARTAMENTO = c("different", 
"different", "same", "same", "same", "same", "same", "same", 
"same", "same", "same", "same", "same", "same", "same"), CHECK_MUNICIPIO = c("same", 
NA, "same", "same", "same", "same", "same", "same", "same", "same", 
"different", "different", "same", "same", "same"), CHECK_UBICACION = c(NA, 
NA, "different", "different", NA, NA, NA, "different", "different", 
"different", "different", "different", "different", "different", 
"different"), CHECK_CIUDAD_CERCANA = c(NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA)), row.names = c(NA, -15L), class = c("tbl_df", 
"tbl", "data.frame"))

#---#
#Im find this example but only run for a column. When put two dont run well.

library(openxlsx)

df <- data.frame(Columna1 = c("A", "B", "C", "D"),
                 Columna2 = c("different", "same", "different", "same"))

wb <- createWorkbook()
addWorksheet(wb, "Hoja1")
writeData(wb, "Hoja1", df)
yellowStyle <- createStyle(fgFill = "yellow")

for (i in 1:nrow(df)) {
  if (df$Columna2[i] == "different") {
    addStyle(wb, "Hoja1", yellowStyle, rows = i + 1, cols = 1, gridExpand = FALSE)
  }
}

yellowStyle <- createStyle(fgFill = "yellow")
saveWorkbook(wb, "ArchivoExportado.xlsx", overwrite = TRUE)

The output is something like that:

image
image1618×250 12.8 KB

Tnks!

2

Try using conditionsFormatting()

datos<-structure(list(ACCESION = c("BRA1000", "BRA1005", "BRA1006", 
                                   "BRA1007", "BRA1010", "BRA1011", "BRA1012", "BRA1015", "BRA1017", 
                                   "BRA1018", "BRA1019", "BRA1020", "BRA1031", "BRA1033", "BRA1035"
), latitude = c(-4.12, 0, -19.38, -18.7, -18.38, -13.52, -17.3, 
                -9.75, -10.98, -10.98, -10.98, -10.98, -3.1, -12.8, -3.72), 
longitude = c(-44.12, 0, -40.07, -39.85, -40.28, -39.18, -39.22, -36.65, -37.3, -37.3, -37.3, -37.3, -60.02, -41.32, -40.98), 
PAIS_ORIGEN = c("BRA", "BRA", "BRA", "BRA", "BRA", "BRA", "BRA", "BRA", "BRA", "BRA", "BRA", "BRA", "BRA", "BRA", "BRA"), 
DEPARTAMENTO = c("MARANHOA", "ACRE", "ESPIRITU SANTO", "ESPIRITU SANTO", "ESPIRITU SANTO", "BAHIA", "BAHIA", "ALAGOAS", "SERGIPE", "SERGIPE", "SERGIPE", "SERGIPE", "AMAZONAS", "BAHIA", "CEARA"), 
MUNICIPIO = c("COROATA", NA, "LINHARES", "SAO MATEUS", "BOA ESPERANCA", "ALCOBACA", "PRADO", "ARAPIRACA", "ITAPORANGA D'AJUDA", "ITAPORANGA D'AJUDA", "ITAPORANGA DA AJUDA", "ITAPORANGA DA AJUDA", "MANAUS", "ANDARAI", "TIANGUA"), 
UBICACION = c(NA, NA, "H.LAGOA DOURADA", "BOA VISTA", NA, NA, NA, "SITIO VATINGA", "LOC: CHA DO SAPE", "LOC: CHA DO SAPE", "LOC: CHA DO SAPE", "LOC: CHA DO SAPE", "KM 32 DA ROD-AM", "KM 14 DA BR 242", "KM 05 BR 222"), 
CHECK_LATITUD = c("same", NA, "same", "same", "different", "different", "same", "same", "same", "same", "same", "same", "same", "same", "same"), 
CHECK_LONGITUD = c("same", NA, "same", "same", "same", "different", "different", "same", "same", "same", "same", "same", "same", "same", "same"), 
CHECK_DEPARTAMENTO = c("different", "different", "same", "same", "same", "same", "same", "same", "same", "same", "same", "same", "same", "same", "same"), 
CHECK_MUNICIPIO = c("same", NA, "same", "same", "same", "same", "same", "same", "same", "same", "different", "different", "same", "same", "same"), 
CHECK_UBICACION = c(NA, NA, "different", "different", NA, NA, NA, "different", "different", "different", "different", "different", "different", "different", "different"), 
CHECK_CIUDAD_CERCANA = c(NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA)), row.names = c(NA, -15L), class = c("tbl_df", 
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             "tbl", "data.frame"))

library(openxlsx)
wb <- createWorkbook()
addWorksheet(wb, "Hoja1")
writeData(wb, "Hoja1", datos)
yellowStyle <- createStyle(bgFill = "yellow")
conditionalFormatting(wb, "Hoja1",
                      cols = 2:3,
                      rows = 2:16, rule = 'H2="different"', style = yellowStyle
)
conditionalFormatting(wb, "Hoja1",
                      cols = 5:7,
                      rows = 2:16, rule = 'J2="different"', style = yellowStyle
)
saveWorkbook(wb, "CondFormat.xlsx")
1 Like
3

Hi @FJCC , yeah was the solution.
Im going to test with all data base but is excellent. Tnks!

4

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.