Export a data in R with changing rows colour

Question

I want to export a dataset similar to the one below, into an excel file with colouring the rows , and changing the colnames+rownames format to bold.

data("HairEyeColor")

mat<-table(HairEyeColor$Hair,HairEyeColor$Eye)
class(mat)

df<-as.data.frame(cbind(mat[,1],mat[,2],mat[,3],mat[,4]))

colnames(df)<-colnames(mat)

write.xlsx(df,"df.xlsx",col.names = TRUE,
           row.names = TRUE)

like this one :) ( I used those colours for the expl : #0070C0 #8DB4E2 #DAEEF3)

Check out the package [xslx](https://cran.r-project.org/web/packages/xlsx/xlsx.pdf), which has formatting options. There are also other SO questions about formatting with `xslx`, [for example this one](https://stackoverflow.com/questions/32185958/r-package-xlsx-formatting-single-cell). — Christopher Belanger, Sep 19 '21 at 19:58

Peter · Answer 1 · 2021-09-20T07:05:01.370

Try with openxlsx

This approach may seem a bit verbose, but you are building up the spreadsheet code line by code line, much in the same way you would build up the spreadsheet click by click.

openxlsx's one liner default for : write.xlsx(mtcars, "openxlsx_table_default.xlsx", asTable = TRUE) gets you part of the way there.

library(openxlsx)

df <- mtcars


# Create workbook
wb <- createWorkbook("df_eg")

# Add a worksheets
addWorksheet(wb, sheet = 1, gridLines = FALSE)

# write data
writeData(wb, sheet = 1, df, rowNames = TRUE)

# set column widths
setColWidths(wb, sheet = 1, cols = LETTERS[0:ncol(df)+1], widths = c(20, rep(10, ncol(df))))

# header style
header_style <- 
  createStyle(fontSize = 10, fontColour = "white", halign = "left", fgFill = "#0070C0", textDecoration = "bold")

addStyle(wb, sheet = 1, header_style, rows = 1, cols = 0:ncol(df)+1, gridExpand = TRUE)

# row names (style same as header)

addStyle(wb, sheet = 1, header_style, rows = 1:nrow(df)+1, cols = 1, gridExpand = TRUE)


# table body style
table_body_style1 <- 
  createStyle(fontSize = 10, fgFill = "#8DB4E2")

addStyle(wb, sheet = 1, table_body_style1, rows = seq(2, nrow(df)+1, by = 2), cols = 1:ncol(df)+1, gridExpand = TRUE)

table_body_style2 <- 
  createStyle(fontSize = 10, fgFill = "#DAEEF3")

addStyle(wb, sheet = 1, table_body_style2, rows = seq(3, nrow(df)+1, by = 2), cols = 1:ncol(df)+1, gridExpand = TRUE)

# save workbook
saveWorkbook(wb, "df.xlsx", overwrite = TRUE)

^{Created on 2021-09-19 by the reprex package (v2.0.0)}

Export a data in R with changing rows colour

1 Answers1