Programmatically interact with an Excel spreadsheet from R, allowing inputs to be modified and results from dependent cells exported. Useful for sensitivity analyses using existing Excel spreadsheet calculations

R_interact_Excel.R

#' R_interact_Excel
#' 
#' Allows a spreadsheet to be modified programatically and the 
#'   output from any number of dependent cells returned as a \code{tibble}. 
#'   Uses the \code{RDCOMClient}, \code{dplyr} and \code{stringr} packages
#'   
#' @param file_location file location of the spreadsheet to modify. The
#'   spreadsheet should be \code{.xls} format
#' @param input_values a vector of values corresponding to the cells to be modified
#' @param input_cells a character vector of the input cells 
#'   in the form c('A1', 'A2')
#' @param output_cells the cells from which the output will be read
#' @param sheet the sheet name. Defaults to \code{Sheet1}
#' @param save_spreadsheet if ' ' then will overwrite the current spreadsheet.
#'   If \code{NULL} will not save. If any other name, will save the file as an
#'   \code{.xls} object
#' @examples \dontrun{
#' # input format
#' file_location <- "** FULL PATH TO SPREADSHEET**.xls"
#' input_cells <- c('A1', 'A2')
#' input_values = c(4, 9)
#' output_cells = c('B1', 'B2')
#' save_spreadsheet <- 'new_spreadsheet.xls'
#' }
#' @export
update_spreadsheet <- 
  function(
    file_location = NULL, 
    input_values = NULL,
    input_cells = NULL,
    output_cells = NULL,
    sheet = 'Sheet1',
    save_spreadsheet = NULL) {
    
    if (is.null(input_cells)) {
      stop(message('input_cells is empty'))
    }
    if (is.null(input_values)) {
      stop(message('input_values is empty'))
    }
    if (is.null(output_cells)) {
      stop(message('output_cells is empty'))
    }
    
    # load spreadsheet
    xlApp <- RDCOMClient::COMCreate("Excel.Application")
    wb    <- xlApp[["Workbooks"]]$Open(file_location)
    sheet <- wb$Worksheets(sheet)
    
    # change the value of the input cells
    for (i in 1:length(input_cells)) {
      alpha <- gsub('[[:digit:]]', '', input_cells[i])
      num <- readr::parse_number(input_cells[i])
      #id <- stringr::str_split(input_cells[i], '')[[1]]
      cell <- sheet$cells(num, which(LETTERS == alpha))
      cell[["Value"]] <- input_values[i]
    }
    
    # read outputs
    output <- c()
    for (i in 1:length(output_cells)) {
      alpha <- gsub('[[:digit:]]', '', output_cells[i])
      num <- readr::parse_number(output_cells[i])
      cell <- sheet$cells(num, which(LETTERS == alpha))
      output[i] <- cell[["Value"]]
    }
    
    if (!is.null(save_spreadsheet)) {
      if (save_spreadsheet == ' ') {
        wb$Save()
      } else {
        if (file.exists(save_spreadsheet)) {
          file.remove(save_spreadsheet)
        }
        wb$SaveAS(save_spreadsheet)  # save as a new workbook
      }
    }
    
    xlApp$Quit()  # close Excel
    
    # return output
    output <- dplyr::tibble(
      cells = output_cells,
      value = output)
    return(output)
  }