R XLSX Package: Single Cell Formatting

Question

UPDATE: SOLVED - now the code is updated to format my blue cell

I am new to using XLSX and I am trying to make my second blue row (including the first column) in my output table. But I'm having trouble referencing this cell to make it blue.

Update 3: Under the comment "#try to make cell A2 blue", I can (starting from the third update) refer to the cell; however, I can only reference it by creating a new cell, which then creates a problem with the data that I have already entered. If I create a new cell first, it just overwrites when I add data from the data frame. Is there a way to refer to a cell when it's already created?

My code is below:

library(xlsx) # create a new workbook for outputs wb<-createWorkbook(type="xlsx") # Define some cell styles TITLE_STYLE <- CellStyle(wb)+ Font(wb, heightInPoints=10, isBold=TRUE, name="Arial") + Alignment(horizontal="ALIGN_CENTER") # Styles for the data table row/column names TABLE_ROWNAMES_STYLE <- CellStyle(wb) + Font(wb, heightInPoints=10, name="Arial") TABLE_COLNAMES_STYLE <- CellStyle(wb) + Font(wb, heightInPoints=10, isBold=TRUE, color ="9", name="Arial") + Fill(foregroundColor="#0069AA") + Alignment(wrapText=TRUE, horizontal="ALIGN_CENTER") TABLE_STYLE <- CellStyle(wb) + Font(wb, heightInPoints=10, name="Arial") # Create a new sheet in the workbook #++++++++++++++++++++++++++++++++++++ sheet <- createSheet(wb, sheetName = "US State Facts") #++++++++++++++++++++++++ # Helper function to add titles #++++++++++++++++++++++++ # - sheet : sheet object to contain the title # - rowIndex : numeric value indicating the row to #contain the title # - title : the text to use as title # - titleStyle : style object to use for title xlsx.addTitle<-function(sheet, rowIndex, title, titleStyle){ rows <-createRow(sheet,rowIndex=rowIndex) sheetTitle <-createCell(rows, colIndex=1) setCellValue(sheetTitle[[1,1]], title) setCellStyle(sheetTitle[[1,1]], titleStyle) } # Add title xlsx.addTitle(sheet, rowIndex=1, title="US State Facts", titleStyle = TITLE_STYLE) #Add a table into a worksheet cell.format <- rep(list(TABLE_STYLE), (dim(state.x77)[2])) # style for remaining columns names(cell.format) <- seq(1, dim(state.x77)[2], by = 1) # assign names to list elements addDataFrame(state.x77, sheet, startRow=2, startColumn=1, colStyle = cell.format, colnamesStyle = TABLE_COLNAMES_STYLE, rownamesStyle = TABLE_ROWNAMES_STYLE ) # Change column width to auto autoSizeColumn(sheet, colIndex=c(1:ncol(state.x77))) #try to make cell A2 blue (as it not included in the col name style) #rows <- createRow(sheet,rowIndex=2) #update 3 #extracell <- createCell(rows, colIndex=1) #update 3 #setCellStyle(extracell[[1,1]], TABLE_COLNAMES_STYLE) #update 3 #######SOLUTION####### rows <- getRows(sheet) cells <- getCells(rows) setCellStyle(cells[[2]], TABLE_COLNAMES_STYLE) #merge header addMergedRegion(sheet, 1, 1, 1, 9) # Save the workbook to a file... saveWorkbook(wb, "h:/r-xlsx-report-example.xlsx") 

UPDATE 2: I had a problem simply by making new outlet names and then not displaying the names of the rosers. But if someone else can explain how to reference one cell, that would be awesome!

Answer 1

You can reference one cell, such as

 rows <- getRows(sheet) cells <- getCells(rows) #Then apply a style to a cell by referencing it number as a java? object: setCellStyle(cells[[37]], TABLE_LASTROW_STYLE) #You could apply it to a row like this: lapply(c(37:43), function(i) setCellStyle(cells[[i]], TABLE_LASTROW_STYLE)) #You could apply it more generically to your last row like this: lapply(c((dim(df)[1]*dim(df)[2] + 2):(dim(df)[1]*dim(df)[2] + 2 + dim(df)[2] -1)), function(i) setCellStyle(cells[[i]], TABLE_LASTROW_STYLE)) 

I will update the code above for a more specific example.