R/aux_excel.R

In MehdiChelh/triangle.tlbx: An Amazing Shiny App

#' aux_excel.R
#' 
#' This file contains functions for the communication between R and Excel. 
#' 

#' @title create_excel_file
#' 
#' @description Used to export the results in an excel file.
#' @param file template file used to export the results.
#' @param values list of values to be written in the template. The names of the list should be present in the first sheet of the file starting from the fourth row (see ... for details).
#' 
#' @import openxlsx
create_excel_file <- function(input_file, output_file, values){
  # 1. open file
  wb <- loadWorkbook(input_file)
  # 2. get ranges
  range <- read.xlsx(wb, sheet = 1, startRow = 4, rowNames = TRUE)
  # 3. write data
  for (var in names(values)){
    # 3.0. test if data is matrix or dataframe
    # if (!is.matrix(x$value) && !is.data.frame(x$value)
    #     stop("Les données à copier dans le fichier excel doivent être des matrices, des dataframes ou des tableaux (arrays) de dimension 2.")
    sheet <- range[var, c("Sheet")]
    cell <- excel.ref_to_col_row(range[var, c( "Value")])
    x <- values[[var]]
    # 3.1.1 write data
    openxlsx::writeData(wb, sheet, x$value,
                        startCol = cell$col, startRow = cell$row,
                        colNames = x$colNames, rowNames = x$rowNames)
    # 3.1.2 put custom format
    if (!is.null(x$format)){
      cell_style <- do.call(createStyle, x$format$style)
      addStyle(wb, sheet, cell_style, stack = TRUE,
               rows = cell$row + x$format$rows, 
               cols = cell$col + x$format$cols)
    }
    # 3.2 collapse unused columns
    if (!is.na(range[var, c( "CollapseCol")])){
      start_col <- (as.integer(cell$col) + ncol(x$value) + 1)
      cols <- start_col:excel.ref_to_col_row(range[var, "CollapseCol"])$col
      openxlsx::setColWidths(wb, range[var, "Sheet"], cols = cols, hidden = rep(TRUE, length(cols)))
    }
    if (!is.na(range[var, c( "CollapseRow")])){
      start_row <- (as.integer(cell$row) + nrow(x$value))
      rows <- start_row:excel.ref_to_col_row(range[var, "CollapseRow"])$row
      openxlsx::setRowHeights(wb, range[var, "Sheet"], rows = rows, heights = 0)
    }
      
  }
  # 4. generate temp file
  saveWorkbook(wb, output_file, overwrite = TRUE)
}


# 

# create_excel_file("./inst/app/www/excel/template_chainladder.xlsx", "./inst/app/www/excel/template_chainladder_test.xlsx",
#                   values = list("data_pos" = list("value" = matrix(1:16/4, 4, 4),
#                                                   "colNames" = FALSE,
#                                                   "rowNames" = FALSE)))
#             


#' @title excel.ref_to_col_row
#' 
#' @description Convert a valid Excel reference (string) to a list of row and columns
#' @param string A valid Excel column name.
#' 
#' @examples 
#' excel.string_col_to_number("sheet1!$AA$Z")
#' excel.string_col_to_number("sheet1!$AA$3:$AB$7")
excel.ref_to_col_row <- function(ref){
  ref <- strsplit(ref, "'!\\$")[[1]]
  ref <- ref[length(ref)]
  ref <- strsplit(ref, ":\\$")[[1]]
  # Si range
  if (length(ref) == 2){
    first <- ref[1]
    last <- ref[2]
    first <- strsplit(first, "\\$")[[1]]
    first <- first[first != ""]
    last <- strsplit(last, "\\$")[[1]]
    last <- last[last != ""]
    
    first_col <- excel.string_col_to_number(first[1])
    first_row <- first[2]
    last_col <- excel.string_col_to_number(last[1])
    last_row <- last[2]
    return(list("first_row" = first_row,
                "last_row" = last_row,
                "first_col" = first_col,
                "last_col" = last_col))
  }
  # Si cellule
  else if (length(ref) == 1) {
    cell <- strsplit(ref, "\\$")[[1]]
    cell <- cell[cell != ""]
    col <- excel.string_col_to_number(cell[1])
    row <- as.numeric(cell[2])
    return(list("row" = row,
                "col" = col))
  }
}


#' @title excel.string_col_to_number
#' 
#' @description Convert a valid Excel column name the coresponding column number.
#' @param string A valid Excel column name.
#' 
#' @example excel.string_col_to_number("AAZ")
excel.string_col_to_number <- function(string){
  vect <- strsplit(string, "")[[1]]
  vect <- match(vect, LETTERS)
  return(sum(26 ** ((length(vect)-1):0) * vect))
}