In Lightbridge-KS/lbdoc: My Package for Office Document

knitr::opts_knit$set(root.dir = rprojroot::find_rstudio_root_file()) # Set WD to Root
here::i_am("dev/openxl-intro.Rmd")
library(tidyverse)
library(here)
library(openxlsx)
source(here("my_R/mkdata.R"))

Write.xlsx

Set Default Option & Column Class

#usethis::edit_r_environ("project")
#usethis::edit_r_profile("project")

options("openxlsx.borderColour" = "black")
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

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)

class(df$Cash) <- "currency"
class(df$Cash2) <- "accounting"
class(df$hLink) <- "hyperlink"
class(df$Percentage) <- "percentage"
class(df$TinyNumbers) <- "scientific"

df

Write

write.xlsx(df, path_excel("writeXLSX3.xlsx"))

Workbook Style

Define a Style

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

class(hs)
sloop::otype(hs)
hs

Write with Style

Data Frame with Style

write.xlsx(iris, file = path_excel("writeXLSX_style1.xlsx"), 
           borders = "columns", headerStyle = hs)

List of Data Frame with Style (Not Work)

l <- list("IRIS" = iris, "colClasses" = df)
write.xlsx(l, file = path_excel("writeXLSX_style_ls1.xlsx"), 
           borders = "columns", headerStyle = hs)

Workbook from Scratch

Create WB

wb <- createWorkbook()

class(wb)

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

Add Sheet

addWorksheet(wb, sheetName = "Motor Trend Car Road Tests", gridLines = FALSE)
addWorksheet(wb, sheetName = "Iris", gridLines = FALSE)
is(wb)
wb

Write to Sheet 1

## freeze first row and column
freezePane(wb, sheet = 1, firstRow = TRUE, firstCol = TRUE) 

writeDataTable(wb, sheet = 1, x = mtcars,
colNames = TRUE, rowNames = TRUE,
tableStyle = "TableStyleLight9")

setColWidths(wb, sheet = 1, cols = "A", widths = 18)

Save Workbook

saveWorkbook(wb, path_excel("basics.xlsx"), overwrite = TRUE) ## save to working directory

openXL(path_excel("basics.xlsx"))

Lightbridge-KS/lbdoc documentation built on May 16, 2024, 3:03 p.m.