R package XLSX: Formatting Single Cell

Question

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

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

Update 3: Under the comment "#try to make cell A2 blue", I can (as of the 3rd update) reference the cell; however, I can only reference it by creating a new cell, which then creates a problem with the data I've already entered. If I create the new cell first, it is just overwritten when I add the data from the data frame. Is there a way to reference 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's 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 got around the problem by just making the rownames a new variable, and then not displaying the rownames. But if someone could still explain how to reference a single cell, that would be awesome!

Answer 1

You can reference a single cell like this

rows <- getRows(sheet)
cells <- getCells(rows)

#Then apply a style to a cell by referencing it's 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'll update the code above for a more specific example.