1

Trying to make simple changes to cells in a MS Excel sheet from Rvia XLConnect.

Specifically make the first row bold. What I see from quick search is that you can define a style within Excel and then import it to R but this option is not suitable for me since all the changes I make have to be reproducible by a R script.

Here is an example of an Excel file created with XLConnect:

require(XLConnect)

wb <- loadWorkbook("test.xlsx", create = TRUE)
createSheet(wb, name = "foo")
df <- data.frame(number  = 1:4,
                 species = c("dog", "cat"))
writeWorksheet(wb, df, sheet = "foo", startRow = 1, startCol = 1)
saveWorkbook(wb)

Perhaps this is easier] with the xlsx package? I already ran into technical problems with openxlsx.

EDIT: I know how to change background color of a cell and assume similar method can be used to change fonts to bold:

cs.tr <- createCellStyle(wb)
setFillForegroundColor(cs.tr, color = XLC$"COLOR.WHITE")
setCellStyle(wb, sheet = "foo", row = 1, col = 1:2, 
             cellstyle = cs.tr)
s_baldur
  • 29,441
  • 4
  • 36
  • 69
  • Turns out formatting text is currently not possible with XLConnect: see p. 16 in https://cran.r-project.org/web/packages/XLConnect/vignettes/XLConnect.pdf – s_baldur Mar 07 '17 at 22:13
  • Not sure whether I should delete question. – s_baldur Mar 07 '17 at 22:18
  • using r-xlsx https://stackoverflow.com/questions/38871931/formatting-an-entire-excel-workbook-efficiently-using-the-r-xlsx-package – M-- May 22 '19 at 19:21
  • http://www.sthda.com/english/wiki/print.php?id=151#step-25.-define-some-cell-styles-for-formating-the-workbook – M-- May 22 '19 at 19:22
  • or use https://stackoverflow.com/questions/19404270/run-vba-script-from-r – M-- May 22 '19 at 19:23

1 Answers1

6

There is a small work around to this issue. Create a "template" Excel spreadsheet and create the cell styles that you are interested in. For example, in Excel, I create a new cell style called my.header and my.table. my.header is 11 pt bold Calibri, and my.regular is 10 pt Calibri. Save this Excel file ("myExcel.xlsx") somewhere useful.

Then in R, do the following:

library(XLConnect)
wb <- loadWorkbook("myExcel.xlsx")
# bring excel styles in the spreadsheet into R
style.title  <- getCellStyle (wb , "my.header")
style.normal <- getCellStyle (wb , "my.table")
# copy the first sheet in myExcel as a "template" for use in R
sheet_names <- getSheets(wb)
sheet_template <- sheet_names[1]

# do something useful in R    
df<-mtcars

# now clone template as a new sheet (instead of creating it)
cloneSheet(wb, sheet_template, "newSheet")
writeWorksheet  (wb , data = df , sheet ="newSheet" , startRow =1 , startCol =1 , header = TRUE )
setCellStyle(wb , sheet ="newSheet" , row =1 , col =1:dim(df)[2] , cellstyle =style.title)

saveWorkbook ( wb, "myNewExcel.xlsx")

I clone the spreadsheet tab so that I can also set the viewing zoom to my favorite zoom level ... you probably don't have to do that.

greengrass62
  • 968
  • 7
  • 19
  • Thanks. This might be useful but as I said in my question I knew that you can: "define a style within Excel and then import it to R but this option is not suitable for me since all the changes I make have to be reproducible by a R script." I am not trying to the `xlsx` package working instead. http://stackoverflow.com/questions/42699557/xlsx-r-package-overwriting-previous-formatting – s_baldur Mar 09 '17 at 15:55
  • 1
    Missed the detail, and your intent, about reproducibility--your definition is a little tighter than mine. Good luck with your endeavors and xlsx. – greengrass62 Mar 09 '17 at 19:46
  • I mean "now trying to move to the `xlsx`.... And things are going pretty well over there. Your answer will definitely be useful on others insisting on `xlconnect`. – s_baldur Mar 09 '17 at 20:33
  • Same technique can be used by overwriting a complete predefined template file without cloning, using the option: setStyleAction(wb, XLC$"STYLE_ACTION.PREDEFINED"), and then saving it as a new file. – aliawadh980 Jan 03 '18 at 10:03