输入与输出

asTable

library(openxlsx)
write.xlsx(iris, file = "writeXLSX1.xlsx")
write.xlsx(iris, file = "writeXLSXTable1.xlsx", asTable = TRUE)

!openxlsx-asTable.png

write.xlsx 可以返回工作簿对象以供进一步编辑

wb <- write.xlsx(iris, "writeXLSX6.xlsx")
setColWidths(wb, sheet = 1, cols = 1:5, widths = 20)
saveWorkbook(wb, "writeXLSX6.xlsx", overwrite = TRUE)

设置工作簿样式

最简单的方法：设置默认选项并设置列类

env <- new.env()
with(env, {
  library(openxlsx)
  df <- data.frame("Date" = Sys.Date()-0:19, "LogicalT" = TRUE,
                   "Time" = Sys.time()-0:19*60*60,
                   "Cash" = paste("$",1:20), "Cash2" = 31:50,
                   "hLink" = "https://CRAN.R-project.org/",
                   "Percentage" = seq(0, 1, length.out=20),
                   "TinyNumbers" = runif(20) / 1E9,  stringsAsFactors = FALSE)
  write.xlsx(df, "Output/writeXLSX3.optionds.null.xlsx")
})


env <- new.env()
with(env, {
  library(openxlsx)
  df <- data.frame("Date" = Sys.Date()-0:19, "LogicalT" = TRUE,
                   "Time" = Sys.time()-0:19*60*60,
                   "Cash" = paste("$",1:20), "Cash2" = 31:50,
                   "hLink" = "https://CRAN.R-project.org/",
                   "Percentage" = seq(0, 1, length.out=20),
                   "TinyNumbers" = runif(20) / 1E9,  stringsAsFactors = FALSE)
  
  options("openxlsx.borderColour" = "#4F80BD")
  options("openxlsx.borderStyle" = "thin")
  options("openxlsx.dateFormat" = "mm/dd/yyyy")
  options("openxlsx.datetimeFormat" = "yyyy-mm-dd hh:mm:ss")
  options("openxlsx.numFmt" = NULL) ## For default style rounding of numeric columns
  
  class(df$Cash) <- "currency"
  class(df$Cash2) <- "accounting"
  class(df$hLink) <- "hyperlink"
  class(df$Percentage) <- "percentage"
  class(df$TinyNumbers) <- "scientific"
  
  write.xlsx(df, "Output/writeXLSX3.optionds.xlsx")
})

!openxlsx-oprions.png

定义列标题的样式：headerStyle

hs <- createStyle(fontColour = "#ffffff", fgFill = "#4F80BD",
                  halign = "center", valign = "center", textDecoration = "Bold",
                  border = "TopBottomLeftRight", textRotation = 45)

write.xlsx(iris, file = "Output/writeXLSX4.xlsx", borders = "rows", headerStyle = hs)

!openxlsx-headerStyle.png

输出列表时，样式将应用于所有列表元素

l <- list("IRIS" = iris, "colClasses" = df)
write.xlsx(l, file = "Output/writeXLSXTable5.xlsx", asTable = TRUE, tableStyle = "TableStyleLight2")

!475
!475

创建工作簿

require(ggplot2)
wb <- createWorkbook()
options("openxlsx.borderColour" = "#4F80BD")
options("openxlsx.borderStyle" = "thin")
modifyBaseFont(wb, fontSize = 10, fontName = "Arial Narrow")