CODEWITHSUNDEEP
rexcelxlconnect
Sachin
Sachin

Reputation: 31

R XLConnect styling does not persist

I'm facing an issue while trying to format/style an existing excel file with data. I want to change the format of a numeric cell, add background color and a border.

 require(XLConnect)
 wb <- loadWorkbook("example.xlsx", create = FALSE)
 cs <- createCellStyle(wb)
 setDataFormat(cs, format = "###,##0")
 setFillBackgroundColor(cs, color = XLC$"COLOR.YELLOW")
 setBorder(cs, side = "all", type = XLC$"BORDER.THIN",
           color = XLC$"COLOR.BLACK")
 setCellStyle(wb, sheet = "PSNB", row = 24, col = 3, cellstyle = cs)
 saveWorkbook(wb)

After running the above code, the cell doesn't have the background color (Yellow) and the data format persisted. When i double click on the cell, i can see the background color changing to yellow and the commas(Ex: 100,000) appearing.

Any help would be greatly appreciated!

I'm using XLConnect 0.2-13

Upvotes: 0

Views: 525

Answers (1)

Martin Studer
Martin Studer

Reputation: 2321

I think you are rather looking to set the fill foreground color instead of the fill background color. Background colors are usually only needed in conjunction with non-solid fill patterns (see setFillPattern).

The following may do what you are looking for:

require(XLConnect)
wb <- loadWorkbook("example.xlsx", create = FALSE)
cs <- createCellStyle(wb)
setDataFormat(cs, format = "###,##0")
setFillForegroundColor(cs, color = XLC$"COLOR.YELLOW")
setFillPattern(cs, fill = XLC$FILL.SOLID_FOREGROUND)
setBorder(cs, side = "all", type = XLC$"BORDER.THIN", 
          color = XLC$"COLOR.BLACK")
setCellStyle(wb, sheet = "PSNB", row = 24,col = 3, cellstyle = cs)
saveWorkbook(wb)

Note the use of setFillForegroundColor and setFillPattern instead of setFillBackgroundColor.

Upvotes: 1

Related Questions