R 包 XLSX : Formatting Single Cell

Question

更新:已解决 - 代码现在已更新以格式化我的单个单元格蓝色

我是使用 XLSX 的新手，我正在尝试将输出电子表格中的整个第二行(包括第一列)设为蓝色。但是我无法引用该单元格以使其变为蓝色。

更新 3:在评论“#try to make cell A2 blue”下，我可以(从第三次更新开始)引用该单元格；但是，我只能通过创建一个新单元格来引用它，这会导致我已经输入的数据出现问题。如果我先创建新单元格，当我从数据框中添加数据时，它只会被覆盖。有没有办法在已经创建的单元格中引用它？

我的代码如下:

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")

更新 2:我通过将 rownames 设为新变量，然后不显示 rownames 来解决这个问题。但是如果有人仍然可以解释如何引用单个单元格，那就太棒了!

Answer 1

您可以像这样引用单个单元格

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))

我将更新上面的代码以获得更具体的示例。