R/openxlsx-functions.R

In ayushnoori/brainstorm: Personal Utility Functions

#' Create Excel header style.
#'
#' Create Excel header style. Depends on the \code{openxlsx} package. All arguments have default values and are optional to allow parameters to be modified.
#'
#' @param fill Header foreground fill.
#' @param color Header font color.
#' @param font Header font name.
#' @param size Header font size.
#' @param h Horizontal alignment of header contents.
#' @param v Vertical alignment of header contents.
#' @param style Header text styling.
#' @param border Header cell border.
#' @param bstyle Header border line style.
#' @param ... Other arguments to be passed to \code{openxlsx::createStyle()}.
#' @return \code{openxlsx} style object.
#' @author Ayush Noori
#' @export
h1 = function(fill = "#1A1B41", color = "#FFFFFF",
              font = "Arial Black", size = 10,
              h = "center", v = "center",
              style = "Bold", border = "Bottom",
              bstyle = "thick", ...) {

  openxlsx::createStyle(fgFill = fill, fontColour = color,
                        fontName = font, fontSize = size,
                        halign = h, valign = v,
                        textDecoration = style, border = border,
                        borderStyle = bstyle, ...)

}


#' Create Excel primary row style.
#'
#' Create Excel primary row style. Depends on the \code{openxlsx} package. All arguments have default values and are optional to allow parameters to be modified.
#'
#' @param fill Primary row foreground fill.
#' @param color Primary row font color.
#' @param font Primary row font name.
#' @param size Primary row font size.
#' @param h Horizontal alignment of primary row contents.
#' @param v Vertical alignment of primary row contents.
#' @param border Primary row cell border.
#' @param ... Other arguments to be passed to \code{openxlsx::createStyle()}.
#' @return \code{openxlsx} style object.
#' @author Ayush Noori
#' @export
r1 = function(fill = "#FFFFFF", color = "#363635",
              font = "Arial", size = 10,
              h = "center", v = "center",
              border = "TopBottomLeftRight",
              ...) {

  openxlsx::createStyle(fgFill = fill, fontColour = color,
                        fontName = font, fontSize = size,
                        halign = h, valign = v,
                        border = border, ...)

}


#' Create Excel secondary row style.
#'
#' Create Excel secondary row style. Depends on the \code{openxlsx} package. All arguments have default values and are optional to allow parameters to be modified.
#'
#' @param fill Secondary row foreground fill.
#' @param color Secondary row font color.
#' @param font Secondary row font name.
#' @param size Secondary row font size.
#' @param h Horizontal alignment of secondary row contents.
#' @param v Vertical alignment of secondary row contents.
#' @param border Secondary row cell border.
#' @param ... Other arguments to be passed to \code{openxlsx::createStyle()}.
#' @return \code{openxlsx} style object.
#' @author Ayush Noori
#' @export
r2 = function(fill = "#F6F4F4", color = "#363635",
              font = "Arial", size = 10,
              h = "center", v = "center",
              border = "TopBottomLeftRight",
              ...) {

  openxlsx::createStyle(fgFill = fill, fontColour = color,
                        fontName = font, fontSize = size,
                        halign = h, valign = v,
                        border = border, ...)

}

#' Apply Excel primary row style.
#'
#' Apply Excel primary row style to odd rows. Depends on the \code{openxlsx} package. All arguments have default values and are optional to allow parameters to be modified.
#'
#' @param wb \code{openxlsx} Workbook object.
#' @param sheet Worksheet name.
#' @param table Table for specified worksheet. If both \code{rows} and \code{cols} are specified, then this argument is optional.
#' @param style \code{openxlsx} style object generated by \code{brainstorm::r1()}.
#' @param rows Rows to apply style to, optional if  \code{table} is specified..
#' @param cols Columns to apply style to, optional if  \code{table} is specified..
#' @param grid See \code{gridExpand} argument of \code{openxlsx::addStyle()}.
#' @param stack See \code{stack} argument of \code{openxlsx::addStyle()}.
#' @author Ayush Noori
#' @export
apply_r1 = function(wb, sheet, table = NULL,
                    style = r1(),
                    rows = 2:(nrow(table) + 1) %>% .[which(. %% 2 == 0)],
                    cols = 1:ncol(table),
                    grid = T, stack = F) {

  openxlsx::addStyle(wb, sheet, style, rows, cols, grid, stack)

}


#' Apply Excel secondary row style.
#'
#' Apply Excel secondary row style to odd rows. Depends on the \code{openxlsx} package. All arguments have default values and are optional to allow parameters to be modified.
#'
#' @param wb \code{openxlsx} Workbook object.
#' @param sheet Worksheet name.
#' @param table Table for specified worksheet. If both \code{rows} and \code{cols} are specified, then this argument is optional.
#' @param style \code{openxlsx} style object generated by \code{brainstorm::r1()}.
#' @param rows Rows to apply style to, optional if  \code{table} is specified.
#' @param cols Columns to apply style to, optional if  \code{table} is specified..
#' @param grid See \code{gridExpand} argument of \code{openxlsx::addStyle()}.
#' @param stack See \code{stack} argument of \code{openxlsx::addStyle()}.
#' @author Ayush Noori
#' @export
apply_r2 = function(wb, sheet, table = NULL,
                    style = r2(),
                    rows = 2:(nrow(table) + 1) %>% .[which(. %% 2 == 1)],
                    cols = 1:ncol(table),
                    grid = T, stack = F) {

  openxlsx::addStyle(wb, sheet, style, rows, cols, grid, stack)

}


#' Add Excel worksheet.
#'
#' Combine \code{brainstorm} functions to add Excel worksheet. For finer control, functions can be called individually. Depends on the \code{openxlsx} package.
#'
#' @param wb \code{openxlsx} Workbook object.
#' @param sheet Worksheet name.
#' @param table Table to add to worksheet.
#' @param header Header \code{openxlsx} style object generated by \code{brainstorm::h1()}.
#' @param row1 Primary row \code{openxlsx} style object generated by \code{brainstorm::r1()}.
#' @param row2 Secondary row \code{openxlsx} style object generated by \code{brainstorm::r2()}.
#' @param sc Starting column to write to.
#' @param sr Starting row to write to.
#' @param cw Vector of column widths or \code{"auto"} for automatic sizing.
#' @param rh Vector of row heights or \code{"auto"} for automatic sizing.
#' @param fc If \code{TRUE}, freezes first column. To freeze more than the first column, set as \code{FALSE} and specify \code{fac}.
#' @param fr If \code{TRUE}, freezes first row. To freeze more than the first row, set as \code{FALSE} and specify \code{far}.
#' @param fac First active column, where \code{fac = 2} is equivalent to \code{fc = TRUE}.
#' @param far First active row, where \code{far = 2} is equivalent to \code{fr = TRUE}.
#' @param tab Tab color of worksheet.
#' @param ... Other arguments to be passed to \code{openxlsx::addWorksheet()}.
#' @author Ayush Noori
#' @export
add_worksheet = function(wb, sheet, table,
                         header = h1(), row1 = r1(), row2 = r2(),
                         sc = 1, sr = 1,
                         cw = 30, rh = 20,
                         fc = F, fr = T,
                         fac = NULL, far = NULL,
                         tab = "#1A1B41", ...) {

  # add worksheet
  openxlsx::addWorksheet(wb, sheetName = sheet, tabColour = tab, ...)

  # write data with header styling
  openxlsx::writeData(wb, sheet, x = table, headerStyle = header, startCol = sc, startRow = sr)

  # set column widths and row heights
  openxlsx::setColWidths(wb, sheet, 1:ncol(table), cw)
  openxlsx::setRowHeights(wb, sheet, 1:(nrow(table) + 1), rh)

  # add row styling
  apply_r1(wb, sheet, table, style = row1)
  apply_r2(wb, sheet, table, style = row2)

  # freeze panes
  if(fc|fr) openxlsx::freezePane(wb, sheet, far, fac, fr, fc)

}


#' Write to Excel file.
#'
#' Combine \code{brainstorm} functions to write a single data table to an Excel file with a single worksheet. For finer control, functions can be called individually. Depends on the \code{openxlsx} package.
#' @param table Table to add to worksheet.
#' @param path Path to save Excel file.
#' @param overwrite If \code{TRUE}, overwrite any existing file.
#' @param ... Other arguments to be passed to \code{brainstorm::add_worksheet()}.
#' @author Ayush Noori
#' @export
write_excel = function(table, path, overwrite, ...) {

  # create workbook
  wb = openxlsx::createWorkbook()

  # add worksheet
  add_worksheet(wb = wb, table = table, ...)

  # save file
  openxlsx::saveWorkbook(wb, path, overwrite)


}