Add a new spreadsheet containing data and an image to an Excel workbook

This function adds a data frame and optional image to a new spreadsheet in an Excel workbook.

add_new_spreadsheet(
  spreadsheet_name,
  data,
  image_path = NA_character_,
  image_width,
  image_height,
  filter = TRUE
)

Arguments

spreadsheet_name	The name of the spreadsheet to which the data and optionally an image will be added.
data	The name of the data frame containing the data to add to the spreadsheet.
image_path	The path name for the image to be optionally added to the spreadsheet.
image_width	The width of image in inches to be optionally added to the spreadsheet.
image_height	The height of image in inches to be optionally added to the spreadsheet.
filter	Should a filter be added to the data (default TRUE) in the spreadsheet or not (FALSE).

Value

The data frame is returned invisibly so that the function can be used in a piped workflow.

Details

The text is left aligned with the first header row made bold. The widths of the columns in the spreadsheet are also automatically adjusted to fit the text and the filter functionality is switched on.

An image may be inserted to the right of the text with both the image's width and height sizes adjustable by providing their sizes in inches in the function's parameters.

Examples

# example taken from palmerpenguins example analysis of mass vs. flipper length
# https://allisonhorst.github.io/palmerpenguins/articles/examples.html

suppressPackageStartupMessages({
  library(store)
  suppressWarnings({
    library(palmerpenguins)
    library(fs)
    library(dplyr)
    library(ggplot2)
    library(ragg)
  })
})

# create temp directory
dir_create(path(tempdir(), "figures"))

# data
penguins_mass_flipper <- penguins %>%
  select(species, flipper_length_mm, body_mass_g)

# graph
penguins_mass_flipper_plot <- ggplot(data = penguins_mass_flipper,
                                     aes(x = flipper_length_mm,
                                         y = body_mass_g)) +
  geom_point(aes(color = species,
                 shape = species),
             size = 3,
             alpha = 0.8) +
  theme_minimal() +
  scale_color_manual(values = c("darkorange","purple","cyan4")) +
  labs(title = "Penguin size, Palmer Station LTER",
       subtitle = "Flipper length and body mass for Adelie, Chinstrap and Gentoo Penguins",
       x = "Flipper length (mm)",
       y = "Body mass (g)",
       color = "Penguin species",
       shape = "Penguin species") +
  theme(legend.position = c(0.2, 0.7),
        legend.background = element_rect(fill = "white", color = NA),
        plot.title.position = "plot",
        plot.caption = element_text(hjust = 0, face= "italic"),
        plot.caption.position = "plot")

# save graph
suppressWarnings({
  ggsave(filename = path(tempdir(), "figures", "penguins_mass_flipper_plot.png"),
       plot = penguins_mass_flipper_plot,
       device = agg_png,
       width = 6,
       height = 6,
       units = "in",
       dpi = 72)
})

if (FALSE) {
## export data and graph into spreadsheet
suppressPackageStartupMessages({
  suppressWarnings({
    library(fs)
    library(stringr)
    library(here)
    library(lubridate)
    library(openxlsx)
  })
})

workbook_path <- path(tempdir(), str_glue("example_{today()}.xlsx"))
if(!file_exists(workbook_path)) {
  workbook <- createWorkbook()
} else {
  loadWorkbook(workbook_path)
}
add_new_spreadsheet(spreadsheet_name = "palmerpenguins",
                    data = penguins_mass_flipper,
                    image_path = path(tempdir(), "figures", "penguins_mass_flipper_plot.png"),
                    image_width = 6,
                    image_height = 6)
saveWorkbook(workbook, workbook_path, overwrite = TRUE)
}