R - xlsx package - how to add cell color without changing border

Question

I'm trying to export a table to Excel with a layout. I found a lot of information about the xlsx package and how to use it but there's still still something wrong with my script.

I don't know how to fill a cell with color, without modifying the previously added borders.

As an example, I created a table (Test.txt) and I want to color the cells of column "Mass1" with a value higher than 30.

Here is the script I wrote:

library(xlsx)

Test<-read.table("Test.txt",sep="\t", dec=".", header = TRUE)

wb<-createWorkbook()
sheet <- createSheet(wb, "Sheet 1")

cs1 <- CellStyle(wb) + Alignment(horizontal="ALIGN_CENTER", vertical="VERTICAL_CENTER") + Border(color="black", position=c("TOP", "RIGHT" , "LEFT","BOTTOM"),pen=c("BORDER_MEDIUM","BORDER_MEDIUM","BORDER_MEDIUM","BORDER_MEDIUM"))
cs2 <- CellStyle(wb) + Border(color="black", position=c("LEFT","RIGHT","TOP", "BOTTOM"),pen=c("BORDER_THIN","BORDER_THIN","BORDER_THIN","BORDER_THIN"))

addDataFrame(Test, sheet, row.names = F, colnamesStyle=cs1, colStyle=list(`1`=cs2, `2`=cs2, `3`=cs2))

for(i in 1:nrow(Test) ){ 
  if(Test[i,2]>30){
    Row<-getRows(sheet, rowIndex=(i+1))
    Cell<-getCells(Row,colIndex = 2)
    cs3<- CellStyle(wb) + Fill(foregroundColor="lightblue", backgroundColor="lightblue", pattern="SOLID_FOREGROUND")    
    setCellStyle(Cell[[1]], cs3)
  }
}

saveWorkbook(wb, "Test.xlsx")

My trouble is that the cells are correctly colored but the Bottom border disappears. I know that I can add borders in my cs3 style but in my real script, the bottom borders of my colored cells is not always the same (some are Thin and others are Medium).

How do I take into account the previously created borders to add a Fill color without modifying these borders ? I guess that the getCellStyle function could help but when I apply this fonction, yhe result is a "Formal Class jobjref" and not a "List of 8" as is my cs3 style...

Answer 1

I found out how to do it! You need to get the border style for your cell using getBorderLeft and three other functions, then you can restore the border when you set the cell style.

Here is an example, where test.xlsx has a cell with some borders, of thickness 1, in location B2, and we want to colour it yellow without removing the borders.

library(xlsx)

# load the workbook and get the sheet
wb <- loadWorkbook(file="test.xlsx")
SheetList <- getSheets(wb)
sheet <- SheetList[[1]]

# find the cell
row <- getRows(sheet, 2)
cells <- getCells(row, 2)
cell <- cells[[1]]

# get current borders
style <- getCellStyle(cell)
border <- c(style$getBorderLeft(), 
            style$getBorderTop(), 
            style$getBorderRight(),
            style$getBorderBottom()
           )

# construct new cell style; also works if there were no borders
new_style <- CellStyle(wb) + 
           Fill(backgroundColor = "yellow", 
                foregroundColor = "yellow",
                pattern         = "SOLID_FOREGROUND") +
           Border(color    = "black", 
                  position = c("LEFT", 
                               "TOP", 
                               "RIGHT", 
                               "BOTTOM"
                              )[border > 0])

# apply new cell style
setCellStyle(cell, new_style)
saveWorkbook(wb, "test2.xlsx")

Border thicknesses can be dealt with in a similar way. Tip: in RStudio, typing $ after the name of a style object (style in this example) shows a list of all the available methods, which is very convenient since so little is documented in the xlsx package.