credit to (https://janmarvin.github.io/openxlsx2/articles/Update-from-openxlsx.html)

2 read xlsx or xlsm files

file <- system.file("extdata", "openxlsx2_example.xlsx", package = "openxlsx2")
openxlsx::read.xlsx(xlsxFile = file) 
##    Var1 Var2   Var3 Var4  Var5         Var6  Var7       Var8
## 1  TRUE    1   1.00    a 45075 3209324 This    NA 0.06059028
## 2  TRUE   NA     NA    b 45069         <NA>   0.0 0.58538194
## 3  TRUE    2   1.34    c 44958         <NA>    NA 0.95905093
## 4 FALSE    2     NA <NA>    NA         <NA>   2.0 0.72561343
## 5 FALSE    3   1.56    e    NA         <NA>    NA         NA
## 6 FALSE    1   1.70    f 44987         <NA>   2.7 0.36525463
## 7 FALSE    2  23.00    h 45284         <NA>  25.0         NA
## 8 FALSE    3  67.30    i 45285         <NA>   3.0         NA
## 9    NA    1 123.00 <NA> 45138         <NA> 122.0         NA
# read in openxlsx2
openxlsx2::read_xlsx(file = file) # contains #NUM!
##     Var1 Var2 NA  Var3  Var4       Var5         Var6    Var7     Var8
## 3   TRUE    1 NA     1     a 2023-05-29 3209324 This #DIV/0! 01:27:15
## 4   TRUE   NA NA #NUM!     b 2023-05-23         <NA>       0 14:02:57
## 5   TRUE    2 NA  1.34     c 2023-02-01         <NA> #VALUE! 23:01:02
## 6  FALSE    2 NA  <NA> #NUM!       <NA>         <NA>       2 17:24:53
## 7  FALSE    3 NA  1.56     e       <NA>         <NA>    <NA>     <NA>
## 8  FALSE    1 NA   1.7     f 2023-03-02         <NA>     2.7 08:45:58
## 9     NA   NA NA  <NA>  <NA>       <NA>         <NA>    <NA>     <NA>
## 10 FALSE    2 NA    23     h 2023-12-24         <NA>      25     <NA>
## 11 FALSE    3 NA  67.3     i 2023-12-25         <NA>       3     <NA>
## 12    NA    1 NA   123  <NA> 2023-07-31         <NA>     122     <NA>

3 write xlsx files

output <- temp_xlsx()
openxlsx::write.xlsx(iris,file=output,colNames = TRUE)
openxlsx2::write_xlsx(iris, file = output, col_names = TRUE)

3.1 load a workbook

There are plenty of functions to interact with workbooks

wb <- openxlsx::loadWorkbook(file = file)
wb <- openxlsx2::wb_load(file=file)

4 styles

4.1 openxlsx style

## Create a new workbook
# in the openxlsx style
wb <- createWorkbook(creator = "My name here")
addWorksheet(wb, "Expenditure", gridLines = FALSE)
writeData(wb, sheet = 1, USPersonalExpenditure, rowNames = TRUE)

## style for body
bodyStyle <- createStyle(border = "TopBottom", borderColour = "#4F81BD")
addStyle(wb, sheet = 1, bodyStyle, rows = 2:6, cols = 1:6, gridExpand = TRUE)

## set column width for row names column
setColWidths(wb, 1, cols = 1, widths = 21)

4.2 chained style

chained style modify an object in place, while pipes do not.

# openxlsx2 chained style
border_color <- wb_color(hex = "4F81BD")
wb <- wb_workbook(creator = "My name here")$
  add_worksheet("Expenditure", grid_lines = FALSE)$
  add_data(x = USPersonalExpenditure, row_names = TRUE)$
  add_border( # add the outer and inner border
    dims = "A1:F6",
    top_border = "thin", top_color = border_color,
    bottom_border = "thin", bottom_color = border_color,
    inner_hgrid = "thin", inner_hcolor = border_color,
    left_border = "", right_border = ""
  )$
  set_col_widths( # set column width
    cols = 1:6,
    widths = c(20, rep(10, 5))
  )$ # remove the value in A1
  add_data(dims = "A1", x = "")

4.3 pipes style

# openxlsx2 with pipes style
border_color <- wb_color(hex = "4F81BD")
wb <- wb_workbook(creator = "My name here") %>%
  wb_add_worksheet(sheet = "Expenditure", grid_lines = FALSE) %>%
  wb_add_data(x = USPersonalExpenditure, row_names = TRUE) %>%
  wb_add_border( # add the outer and inner border
    dims = "A1:F6",
    top_border = "thin", top_color = border_color,
    bottom_border = "thin", bottom_color = border_color,
    inner_hgrid = "thin", inner_hcolor = border_color,
    left_border = "", right_border = ""
  ) %>%
  wb_set_col_widths( # set column width
    cols = 1:6,
    widths = c(20, rep(10, 5))
  ) %>% # remove the value in A1
  wb_add_data(dims = "A1", x = "")

5 conditional formating

# openxlsx2 with chains
wb <- wb_workbook()$
  add_worksheet("a")$
  add_data(x = 1:4, col_names = FALSE)$
  add_conditional_formatting(dims = "A1:A4", rule = ">2")

# openxlsx2 with pipes
wb <- wb_workbook() %>%
  wb_add_worksheet("a") %>%
  wb_add_data(x = 1:4, col_names = FALSE) %>%
  wb_add_conditional_formatting(dims = "A1:A4", rule = ">2")

6 data validation

6.1 openxlsx

# openxlsx
wb <- createWorkbook()
addWorksheet(wb, "Sheet 1")
writeDataTable(wb, 1, x = iris[1:30, ])
dataValidation(wb, 1,
  col = 1:3, rows = 2:31, type = "whole",
  operator = "between", value = c(1, 9)
)

6.2 chain

# openxlsx2 with chains
wb <- wb_workbook()$
  add_worksheet("Sheet 1")$
  add_data_table(1, x = iris[1:30, ])$
  add_data_validation(1,
    dims = wb_dims(rows = 2:31, cols = 1:3),
    # alternatively, dims can also be "A2:C31" if you know the span in your Excel workbook.
    type = "whole",
    operator = "between",
    value = c(1, 9)
  )

6.3 pipes

# openxlsx2 with pipes
wb <- wb_workbook() %>%
  wb_add_worksheet("Sheet 1") %>%
  wb_add_data_table(1, x = iris[1:30, ]) %>%
  wb_add_data_validation(
    sheet = 1,
    dims = "A2:C31", # alternatively, dims = wb_dims(rows = 2:31, cols = 1:3)
    type = "whole",
    operator = "between",
    value = c(1, 9)
  )

7 saving

# openxlsx::saveWorkbook(wb,file='openxlsx_wb.xlsx')
wb_save(wb,file='openxlsx2_wb.xlsx')