+ - 0:00:00
Notes for current slide
Notes for next slide

No API, No Problem

Automating content delivery with R and Excel


Reda Mastouri
@redamastouri

slides at github.com/redamastouri/no-api-no-problem

1 / 20

We are in the future...

2 / 20

So we can move on, right?

3 / 20

4 / 20

Your boss wants 49 Excel sheets

5 / 20

Data dump

sales_data
## # A tibble: 9,994 x 21
##    Person `Order ID` `Order Date`        `Ship Date`         `Ship Mode`
##    <chr>  <chr>      <dttm>              <dttm>              <chr>      
##  1 Cassa… CA-2016-1… 2016-11-08 00:00:00 2016-11-11 00:00:00 Second Cla…
##  2 Cassa… CA-2016-1… 2016-11-08 00:00:00 2016-11-11 00:00:00 Second Cla…
##  3 Anna … CA-2016-1… 2016-06-12 00:00:00 2016-06-16 00:00:00 Second Cla…
##  4 Cassa… US-2015-1… 2015-10-11 00:00:00 2015-10-18 00:00:00 Standard C…
##  5 Cassa… US-2015-1… 2015-10-11 00:00:00 2015-10-18 00:00:00 Standard C…
##  6 Anna … CA-2014-1… 2014-06-09 00:00:00 2014-06-14 00:00:00 Standard C…
##  7 Anna … CA-2014-1… 2014-06-09 00:00:00 2014-06-14 00:00:00 Standard C…
##  8 Anna … CA-2014-1… 2014-06-09 00:00:00 2014-06-14 00:00:00 Standard C…
##  9 Anna … CA-2014-1… 2014-06-09 00:00:00 2014-06-14 00:00:00 Standard C…
## 10 Anna … CA-2014-1… 2014-06-09 00:00:00 2014-06-14 00:00:00 Standard C…
## # … with 9,984 more rows, and 16 more variables: `Customer ID` <chr>,
## #   `Customer Name` <chr>, Segment <chr>, Country <chr>, City <chr>,
## #   State <chr>, `Postal Code` <dbl>, Region <chr>, `Product ID` <chr>,
## #   Category <chr>, `Sub-Category` <chr>, `Product Name` <chr>,
## #   Sales <dbl>, Quantity <dbl>, Discount <dbl>, Profit <dbl>
6 / 20

XLConnect created by Mirai Solutions GmbH

7 / 20

Excel interface

What can XLConnect do?

8 / 20

Excel interface

What can XLConnect do?

  • Create/load Excel workbooks
8 / 20

Excel interface

What can XLConnect do?

  • Create/load Excel workbooks

  • Add/read data from Excel

8 / 20

Excel interface

What can XLConnect do?

  • Create/load Excel workbooks

  • Add/read data from Excel

  • Format worksheets (some limitations)

8 / 20

Excel interface

What can XLConnect do?

  • Create/load Excel workbooks

  • Add/read data from Excel

  • Format worksheets (some limitations)

  • Include images

8 / 20

Excel interface

What can XLConnect do?

  • Create/load Excel workbooks

  • Add/read data from Excel

  • Format worksheets (some limitations)

  • Include images

  • And much more... see here

8 / 20

Excel interface

What can XLConnect do?

  • Create/load Excel workbooks

  • Add/read data from Excel

  • Format worksheets (some limitations)

  • Include images

  • And much more... see here

All programatically!

8 / 20

Make a template

9 / 20

Create cell styles

10 / 20

Load/create workbook and styles

wb <- loadWorkbook("template.xlsx")
date_cellstyle <- getCellStyle(wb, "date")
num_comma_cellstyle <- getCellStyle(wb, "number_comma")
dec_cellstyle <- getCellStyle(wb, "decimal")
setStyleAction(wb, XLC$STYLE_ACTION.NONE)
11 / 20

Write data/format data

writeWorksheet(wb, data = data,
               sheet_name,
               startRow = 2,
               header = FALSE)
walk(
  2:(row_count + 1),
  ~ setCellStyle(
    wb,
    sheet = sheet_name,
    row = .x,
    col = 2:3,
    cellstyle = date_cellstyle
  )
)
walk(
  2:(row_count + 1),
  ~ setCellStyle(
    wb,
    sheet = sheet_name,
    row = .x,
    col = c(sales, discount, profit),
    cellstyle = num_comma_cellstyle
  )
)
12 / 20

Add a plot to Excel

createName(wb, "plot", formula = glue("Summary!{idx2cref(c(1, 6))}") %>% as.character())
p <- data %>%
  group_by(Segment, Category) %>% 
  summarise_if(is.numeric, ~ sum(.x, na.rm = TRUE)) %>% 
  gather(key = key, value = value, -c("Segment", "Category", "Postal Code")) %>% 
  filter(!key %in% c("Quantity", "Discount")) %>% 
  ggplot(aes(Category, value, fill = key)) +
  geom_col(position = "dodge") +
  scale_fill_brewer(name = "Metric", type = "qual", palette = 2) +
  scale_y_continuous(label = scales::comma) +
  facet_wrap( ~ Segment) +
  ggthemes::theme_tufte() +
  theme(axis.text.x = element_text(angle = 45, hjust = 1)) +
  labs(title = "Profit/Sales by Segment/Category",
       y = NULL)
ggsave(p, filename = "segment-category.png", width = 4, height = 3)
addImage(wb, filename = "segment-category.png", name = "plot", originalSize = TRUE)
saveWorkbook(wb, file = file_name)
13 / 20

Functional

make_plot()
style()
build_excel()
14 / 20

Functional

make_plot()
style()
build_excel()
walk(sales_split, function(split) {
  build_excel(split, "reports")
})
14 / 20

Functional

make_plot()
style()
build_excel()
walk(sales_split, function(split) {
  build_excel(split, "reports")
})
plan(multiprocess)
furrr::future_map(sales_split, ~ build_excel(.x, "reports"))
14 / 20

15 / 20

16 / 20

17 / 20

Compose a message

subject = "Sales State Coverage Report"
message <- paste0(
  '<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0
Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
  <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
  <meta name="viewport" content="width=device-width, initial-scale=1.0"/>
  <title>Sales Coverage</title>
  <style type="text/css">
  </style>
</head>
<body>
    Hello,
</p>
<p>Attached is sales data for your state coverage.</p>
<p>A visualization and summary are available on the second tab.</p>
<p>Let us know if you have questions.</p>
<p>Kind regards,</p>
<p>Analysis Group,</p>
</body>
</html>'
)
18 / 20

mailR/sendmailR

send.mail(
  from = "user@domain.com",
  to = "user@domain.com",
  subject = subject,
  body = message,
  html = TRUE,
  smtp = list(
    host.name = "smtp.gmail.com",
    port = 465,
    user.name = rstudioapi::askForPassword(),
    passwd = rstudioapi::askForPassword(),
    ssl = TRUE
  ),
  attach.files = "file_path",
  authenticate = TRUE,
  send = TRUE
)
19 / 20

Nice to know

  • Java/rJava are required to use XLConnect and mailR/sendmailR.

  • Latest version of java: https://java.com/en/download/.

  • If you have trouble installing Java on Mac, see https://www.chrisjmendez.com/2018/11/16/installing-xlconnect-and-rjava-on-macos/.

  • It's typically required you set your JAVA_HOME, such as Sys.setenv(JAVA_HOME = "C:\\Program Files\\Java\\jdk1.8.0_102"). The path is relative to your machine, so you must locate your directory and version of Java.

  • Useful: vignette("XLConnect").

  • XLConnect runs on memory until saveWorkbook() is called. If you experience out-of-memory errors, try allocating more memory with options(java.parameters = "-Xmx1024m"). See vignette above.

20 / 20

We are in the future...

2 / 20
Paused

Help

Keyboard shortcuts

↑, ←, Pg Up, k Go to previous slide
↓, →, Pg Dn, Space, j Go to next slide
Home Go to first slide
End Go to last slide
Number + Return Go to specific slide
b / m / f Toggle blackout / mirrored / fullscreen mode
c Clone slideshow
p Toggle presenter mode
t Restart the presentation timer
?, h Toggle this help
Esc Back to slideshow