In Epiconcept-Paris/STRAP-epiuf: Epiconcept Team Utility Functions for STRAP Project

knitr::opts_chunk$set(echo = TRUE)
library(epiuf)

xlsxUtility functions

Using xlsx to write simple values to cells may be repetitive and trivial. This set of functions should simplify a lot the process.

The first available function is openXlsx. This function load a workbook in memory. The workbook is a java object and having it in environment does help so much... Then this workbook is stored into the functions them self.

It is also possible to assign the workbook to a local variable if needed (to work with more than one workbook at a time).

xls_file <- externalFile("excelfile.xlsx")
openXlsx(xls_file)

the openSheet() function

This function get the content of one sheet from the previously loaded workbook.

cells <- openSheet("T2")

Optionnaly you can load cells from a local workbook.

xls_file <- externalFile("excelfile.xlsx")
wb <- openXlsx(xls_file)
cells <-  openSheet("T2",wb)

And finally the function fillCells can be used to input values into a row of cells.

num <-  67
denum <-  90 
cells <- openSheet("T2")
fillCells(cells, 2 , 2 , "Cases","Perc")
fillCells(cells, 3 , 2 , num, num/denum )
cells <- openSheet("T3")
fillCells(cells, 4 , 2 , "total",100)

It's also possible to colorize some cells using styles. fontColour must be one of the color returned by colours().

value <-  5
cells <- openSheet("T2")

# We define two style  and a default style ! 
# In order to be abble to apply defaultStyle in case a specific style was applied before
myStyle <- createXlsxStyle()
italicStyle <- createXlsxStyle(textDecoration = "Italic", fontColour = "green")
alertStyle =  createXlsxStyle(textDecoration = "Bold", fontColour = "red")
boldStyle = createXlsxStyle(textDecoration = "Bold")

fillCells(cells, 17, 1,"Total", style = alertStyle)
fillCells(cells, 17, 2, "Cases", style = italicStyle)
if (value > 10) {
  myStyle <-  alertStyle
} 
fillCells(cells, 17 , 3 , value , style = myStyle)

# if you want erase a previous formatand use wb default : 
myStyle  <- createXlsxStyle()
formatCells(cells, 17, 1, myStyle)

You can insert an image in your workbook using an existing PNG, JPEG, BMP file.

 image_file <- externalFile("image_xlsx.png")
 addImage(onesheet = cells, image = image_file, line = 20, col = 2)

Or you can insert the current plot that you have just produced with ggplot2.

image_gg <- ggplot(Orange, aes(x = age, y = circumference, colour = as.factor(Tree))) +
  geom_point() + 
  geom_line() +
  labs(colour = "Tree") + 
  theme_minimal()
addImage(onesheet = cells, image = image_gg, line = 40, col = 2)

mat <- data.frame(Id = 1:3 , Vaccs = c("1", "3", "6"))
cells <-  openSheet("T3")
fillCells(cells, 6 , 2 , mat) 
fillCells(cells, 6 , 10 , mat, names=TRUE)

testfun <- function() {
  whichage <- 5
  othervalue <- 8
  fillCells(cells, 10 , 12 , whichage, othervalue)

}

testfun()

And you can save the resulting Excel file using the saveXlsx function

saveXlsx()

Or if you want to change the name

saveXlsx(filename = "excelfile2.xlsx")
# comment the next line if you want to see the result of this vignette
file.remove("excelfile2.xlsx")

Epiconcept-Paris/STRAP-epiuf documentation built on Aug. 5, 2024, 3:41 a.m.