R包xlsx：设置单个单元格的格式 [英] R package XLSX: Formatting Single Cell

问题描述

更新：已解决--代码现已更新，以将单元格设置为蓝色

我刚开始使用xlsx，我正在尝试将输出电子表格中的整个第二行(包括第一列)设置为蓝色。但为了把它变成蓝色，我在参考那个手机时遇到了麻烦。

更新3：在注释"#尝试将单元格A2设置为蓝色"下，我可以(在第三次更新时)引用该单元格；但是，我只能通过创建一个新单元格来引用它，这会导致我已经输入的数据出现问题。如果我首先创建新的单元格，当我从数据框中添加数据时，它将被覆盖。有没有办法在单元格已经创建后引用它？

我的代码如下：

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：我只是将行名设为一个新变量，然后不显示行名，从而解决了这个问题。但如果有人还能解释如何引用单个单元格，那就太棒了！

推荐答案

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

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

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

这篇关于R包xlsx：设置单个单元格的格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！