R/excel_CreatePrettyTable.R

In kiwimic/prettyExcelExpoRt: Ultity tools for automation of task I use in my dailywork. Some of this are in polish language.

#' excel_CreatePrettyTable
#'
#' Goal of this fun is to export nice table to excel
#'
#' @param data
#' @param pathToFile
#' @param wbCon
#' @param sheet
#' @param overWriteSheet
#' @param overWriteFile
#' @param styles
#' @param tabStartRow
#' @param tabStartColumn
#'
#' @return
#' @export
#'
#' @examples
excel_CreatePrettyTable <- function(data,
                                    pathToFile,
                                    wbCon = NULL,
                                    sheet = "Arkusz1",
                                    overWriteSheet = F,
                                    overWriteFile = F,
                                    styles = list(),
                                    tabStartRow = 1,
                                    tabStartColumn = 1,
                                    colnamesStyle = "colNames",
                                    rowsStyle = "basic",
                                    debugMode = F) {
  ##Tests
  if (file.exists(pathToFile) & !overWriteFile) {
    if(debugMode) {
      print(glue::glue("File exists:[{path}]; {result}", path = pathToFile, result = file.exists(pathToFile)))
    }
    stop(glue::glue("overWriteFile argument == {overWriteFile}, and file: {file} alredy exists", overWriteFile = overWriteFile, file = pathToFile))
  }

  if (is.null(wbCon)) {
    wbCon <- xlsx::createWorkbook()
  }


  sheetCon <- xlsx::createSheet(wbCon, sheet)

  colStyleList <- vector("list", length = ncol(data))
  for (i in 1:length(colStyleList)) {
    colStyleList[[i]] <- prettyExcelExpoRt::addStyle(wbCon, rowsStyle)
    if (i == length(colStyleList)) {
      colStyleList[[i]] <-prettyExcelExpoRt::addStyle(wbCon, colnamesStyle)
    }
  }


  tabPosition <- tabStartColumn:(ncol(data)+tabStartColumn - 1)
  if (debugMode) {
    print(tabPosition)
  }
  names(colStyleList) <- base::enquote(1:ncol(data))


  if (debugMode) {
    print(colStyleList)
  }

  #names(colStyleList) <-  base::enquote(tabPosition-1) ## Robimy przesunięcie aby formaty dla column trafiły do przesuniętych col
  #addPicture(file = "picToTest_reScaled.png", sheet = sheetCon, startRow = 1, startColumn = 1, scale = 1)

  if (debugMode) {
    print(head(data))
  }
  xlsx::addDataFrame(data, sheetCon, startRow=tabStartRow, startColumn=tabStartColumn,
               row.names = F,
               colnamesStyle = prettyExcelExpoRt::addStyle(wbCon, colnamesStyle),
               rownamesStyle = prettyExcelExpoRt::addStyle(wbCon, colnamesStyle),
               colStyle = colStyleList)

  xlsx::autoSizeColumn(sheetCon, colIndex = tabPosition)


  xlsx::saveWorkbook(wbCon, pathToFile)
  prettyExcelExpoRt::jgc()
}