Last active
April 30, 2019 11:00
-
-
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
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#' 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