R/functionsToGenerateWorkBook.R

In gowthamrao/StudyManagement: An OHDSI R package to conduct reproducible research.

# @file functionsToGenerateWorkBook.R
#
# Copyright 2020 Observational Health Data Sciences and Informatics
#
# This file is part of StudyManagement
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#     http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.

#' Helper function to generate Excel workbook in standard format
#'
#' @param wb    wb
#'
#'
#' @export
createExcelSheetFormat <- function(wb, sheetName, data,
                                   mainTitle = NULL,
                                   subTitle = NULL,
                                   percentColumns = NULL,
                                   integerColumns = NULL,
                                   decimalColumns = NULL,
                                   dateColumns = NULL,
                                   dateTimeColumns = NULL,
                                   paperSize = 9,
                                   paperOrientation = "landscape",
                                   tableStyle = "TableStyleLight1",
                                   withFilter = FALSE,
                                   zoom = 80,
                                   columnWidths = "auto",
                                   gridLines = FALSE) {


  options("openxlsx.paperSize" = paperSize) #A4
  options("openxlsx.orientation" = paperOrientation)
  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" =  "#,##0.0") ## For default style rounding of numeric columns

  headerStyle1 <- openxlsx::createStyle(fontSize = 12, fontColour = "black",
                              halign = "center", valign = "center",
                              border = "TopBottomLeftRight", borderColour = "black",
                              textDecoration = "bold", wrapText = TRUE)
  headerStyle2 <- openxlsx::createStyle(fontSize = 10, fontColour = "black",
                                        halign = "center", valign = "center",
                                        border = "TopBottomLeftRight", borderColour = "black",
                                        textDecoration = "bold", wrapText = TRUE)
  # openxlsx::createStyle(
  #   fontName = NULL,
  #   fontSize = NULL,
  #   fontColour = NULL,
  #   numFmt = getOption("openxlsx.numFmt"),
  #   border = NULL,
  #   borderColour = getOption("openxlsx.borderColour", "black"),
  #   borderStyle = getOption("openxlsx.borderStyle", "thin"),
  #   bgFill = NULL,
  #   fgFill = NULL,
  #   halign = NULL,
  #   valign = NULL,
  #   textDecoration = NULL,
  #   wrapText = TRUE,
  #   textRotation = NULL,
  #   indent = NULL,
  #   locked = NULL,
  #   hidden = NULL
  # )

  openxlsx::addWorksheet(wb = wb,
                         sheet = sheetName,
                         gridLines = gridLines,
                         zoom = zoom
  )

  startRowForTable <- 1
  if (!is.null(mainTitle)) {
    # Write title in worksheet
    openxlsx::writeData(wb = wb, sheet = sheetName, x = mainTitle, startRow = startRowForTable, startCol = 1)
    openxlsx::addStyle(wb = wb, sheet = sheetName, headerStyle1, rows = startRowForTable, cols = 1:ncol(data))
    openxlsx::mergeCells(wb = wb, sheet = sheetName, cols = 1:ncol(data), rows = startRowForTable)
    startRowForTable <- startRowForTable + 1
    if (!is.null(subTitle)) {
      # Write subtitle of a given dataset
      openxlsx::writeData(wb = wb, sheet = sheetName, x = subTitle, startRow = startRowForTable, startCol = 1)
      openxlsx::addStyle(wb = wb, sheet = sheetName, headerStyle1, rows = startRowForTable, cols = 1:ncol(data))
      openxlsx::mergeCells(wb = wb, sheet = sheetName, cols = 1:ncol(data), rows = startRowForTable)
      startRowForTable <- startRowForTable + 1
    }
  }
  openxlsx::writeDataTable(wb = wb,
                           sheet = sheetName,
                           x = data,
                           tableStyle = tableStyle,
                           withFilter = withFilter,
                           startRow = startRowForTable
  )

  openxlsx::setColWidths(wb = wb,
                         sheet = sheetName,
                         cols = 1:ncol(data),
                         widths = columnWidths,
                         ignoreMergedCells = TRUE)
  # openxlsx::setRowHeights(wb = wb,
  #                         sheet = sheetName,
  #                         rows = 1:3,
  #                         heights = "auto")
  # https://github.com/awalker89/openxlsx/pull/382
}