# R/xl.r In mattdelhey/mdutils: Utilities for dealing with data.

```#' mnaually write formula
xl_manual2 <- function(rows, sheet, outcol) {
n <- length(rows)
out <- rep(NA, n)
for (i in 1:n) {
out[i] <- var_sub("weights!\$C\$2*TANH(\$O\$%s/weights!\$C\$14) + weights!\$C\$3*TANH(\$P\$%s/weights!\$C\$15) + weights!\$C\$4*TANH(\$Q\$%s/weights!\$C\$16) + weights!\$C\$5*(\$AA\$%s/5) + weights!\$C\$6*(\$AC\$%s/5) + weights!\$C\$7*(\$AE\$%s/5) + weights!\$C\$8*TANH(\$AB\$%s/weights!\$C\$17) + weights!\$C\$9*TANH(\$AD\$%s/weights!\$C\$18) + weights!\$C\$10*TANH(\$AF\$%s/weights!\$C\$19) + weights!\$C\$11*\$AG\$%s + weights!\$C\$12*\$AH\$%s + weights!\$C\$13*\$AI\$%s", "%s"=rows[i])
setCellFormula(wb, sheet=sheet, row=rows[i], col=outcol, formula=out[i])
}
}

#' mnaually write formula
xl_manual <- function(rows, sheet, outcol) {
n <- length(rows)
out <- rep(NA, n)
for (i in 1:n) {
out[i] <- var_sub("WEIGHTS!\$C%s*TANH(\$K%s/WEIGHTS!\$C\$14) + WEIGHTS!\$C\$3*TANH(\$L%s/WEIGHTS!\$C\$15) + WEIGHTS!\$C\$4*TANH(\$M%s/WEIGHTS!\$C\$16) + WEIGHTS!\$C\$5*(\$W%s/5) + WEIGHTS!\$C\$6*(\$Y%s/5) + WEIGHTS!\$C\$7*(\$AA%s/5) + WEIGHTS!\$C\$8*TANH(\$X%s/WEIGHTS!\$C\$17) + WEIGHTS!\$C\$9*TANH(\$Z%s/WEIGHTS!\$C\$18) + WEIGHTS!\$C\$10*TANH(\$AB%s/WEIGHTS!\$C\$19) + WEIGHTS!\$C\$11*\$AC%s + WEIGHTS!\$C\$1%s*\$AD%s + WEIGHTS!\$C\$13*\$AE%s", "%s"=rows[i])
setCellFormula(wb, sheet=sheet, row=rows[i], col=outcol, formula=out[i])
}
}

#' Write data to region
#' @param wb XLConnect workbook
#' @param data data.frame
#' @param sheet 'character' sheet to write in
#' @param region 'character' excel region to name
#' @param where 'character' excel location formula
#' @param overwrite 'logical' overwrite region name?
#' @param header 'logical' write header?
#' @inheritParams XLConnect::writeNamedRegion
xl_write <- function(wb, data, sheet, region, where="\$A\$1", overwrite=TRUE, header=TRUE, rownames=NULL) {
formula <- sprintf("%s!%s", sheet, where)
createName(wb, name=region,  formula=formula, overwrite=overwrite)
writeNamedRegion(wb, data, name=region, header=header, rownames=rownames)
}

#' write xl weights sheet
xl_weights <- function(wb, w, sheet) {
createSheet(wb, name=sheet)
xl_write(wb, data=w, sheet=sheet, region="weights", where="\$A\$1")
## Write sum of weights
setCellFormula(wb, sheet=sheet, row=20, col=3, formula="SUM(\$C\$2:\$C\$13)")
}

#' write xl sheet
xl_sheet <- function(wb, data, sheet, outcol, where="\$A\$1") {
## Create new sheet in wb
createSheet(wb, name=sheet)
## Write data (where="\$E\$1")
xl_write(wb, data=data, sheet=sheet, region=sprintf("%s_tclist", sheet),  where=where)
## Write formulas
exrows <- 2:(nrow(data)+1)
xl_manual2(exrows, sheet=sheet, outcol=outcol)
## Calculate formulas
setForceFormulaRecalculation(wb, sheet=sheet, value=TRUE)
## default to world
setActiveSheet(wb, sheet="World")
## set autofilter (reference = aref("E1", dim(data)))
setAutoFilter(wb, sheet=sheet, reference = aref("A1", dim(data)))
}

#' create a workbook
#' @param fname 'character' output file name
#' @param overwrite 'logical' overwrite if file exists?
#' @return XLConnect workbook
xl_create <- function(fname, overwrite=TRUE) {
if (file.exists(fname)) {
if (overwrite) {
warning("file exists; overwriting")
file.remove(fname)
}
if (!overwrite) {
stop("file exists; not removing. failed to create workbook")
}
}
loadWorkbook(fname, create=TRUE)
}
```
mattdelhey/mdutils documentation built on May 21, 2019, 12:57 p.m.