Skip to content

Instantly share code, notes, and snippets.

@CivilEngineerUK
Last active April 30, 2019 11:00
Show Gist options
  • Save CivilEngineerUK/855cd548b89d54ed0f06dc791ea254c6 to your computer and use it in GitHub Desktop.
Save CivilEngineerUK/855cd548b89d54ed0f06dc791ea254c6 to your computer and use it in GitHub Desktop.
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
#'
#' 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)
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment