---
title: "Everything at once"
output: rmarkdown::html_vignette
vignette: >
  %\VignetteIndexEntry{Everything at once}
  %\VignetteEngine{knitr::rmarkdown}
  %\VignetteEncoding{UTF-8}
---

```{r setup, include = FALSE}
knitr::opts_chunk$set(collapse = TRUE, comment = "#>")
library(writexl)
```

This vignette is one runnable script. It exercises every part of the package
into **two** workbooks, so that reviewing what writexl can do means opening two
files rather than reading five vignettes.

Two files rather than one because of a single constraint: an embedded image
cannot share a workbook with any other image, so it gets its own. Everything
else fits together.

Each section says which vignette explains it.

```{r}
sales <- data.frame(
  quarter = c("Q1", "Q2", "Q3", "Q4"),
  revenue = c(120000, 145000, 133000, 180000),
  cost    = c(90000, 110000, 105000, 130000),
  region  = c("North", "South", "North", "South"),
  stringsAsFactors = FALSE)
```

## Formatting --- [vignette](b-formatting.html)

```{r}
money <- xl_num_format("$#,##0") + xl_font(color = "darkgreen")

styled <- xl_sheet(
  sales,
  cols = list(xl_col_spec("quarter", width = 10),
              xl_col_spec(c("revenue", "cost"), width = 14, format = money)),
  rows = xl_row_spec(1, height = 18),
  conditional = list(
    xl_cond_cell(list(cols = "revenue"), "cell", ">=", 1.4e5,
                 format = xl_fill(background = "lightgreen")),
    xl_cond_bar(list(cols = "cost"), color = "steelblue")),
  freeze = "A2", tab_color = "steelblue")
```

## Cell content --- [vignette](e-formulas-and-more.html)

```{r}
cells <- data.frame(item = c("Widget", "Gadget", "Gizmo"),
                    stringsAsFactors = FALSE)
cells$price   <- xl_cell_general(value = c(1234.5, 67.25, 890), format = money)
cells$margin  <- xl_formula(sprintf("=B%d*0.3", 2:4))
cells$link    <- xl_hyperlink(rep("https://ropensci.org", 3), "rOpenSci")
cells$note    <- xl_cell_general(
  value   = c("ok", NA, "check"),
  comment = list(xl_comment("Reviewed", author = "QA"), NULL, NULL),
  format  = list(NULL, xl_fill(background = "#EEEEEE"), NULL))
# a rich string is one cell's worth of text, so it goes in a list alongside
# the other rows' values
cells$rich    <- xl_cell_general(value = list(
  xl_rich_string("Plain ", xl_rich_run("bold", xl_font(bold = TRUE)),
                 " and ", xl_rich_run("red", xl_font(color = "red"))),
  "plain text", NA))

content <- xl_sheet(
  cells,
  merge = xl_merge("A5:C5", "A merged heading",
                   format = xl_align(horizontal = "center") +
                            xl_font(bold = TRUE)),
  validation = xl_validation(list(cols = "item"), type = "list",
                             list = c("Widget", "Gadget", "Gizmo"),
                             input_title = "Pick one"),
  ignore_errors = list(number_stored_as_text = "A2:A4"))
```

## Worksheet features --- [vignette](e-formulas-and-more.html)

```{r}
filtered <- xl_sheet(sales, filter = xl_filter("region", "==", "North"))

tabled <- xl_sheet(sales[, c("quarter", "revenue")],
                   table = xl_table(name = "Revenue", style = "medium 9",
                                    total_row = TRUE,
                                    columns = xl_table_column("revenue",
                                                              total = "sum")))
```

## Printing and the sheet view --- [vignette](c-worksheets-workbooks.html)

```{r}
printed <- xl_sheet(
  sales,
  page = xl_page_setup(orientation = "landscape", paper = "A4",
                       fit_to = c(1, 0), center_horizontally = TRUE,
                       header = "&LwritexlShowcase&RPage &P of &N",
                       repeat_rows = 1),
  view = xl_sheet_view(hide_zero = TRUE),
  outline = xl_outline(symbols_below = FALSE),
  protect = list(password = "secret", sort = TRUE))
```

## Charts --- [vignette](d-charts-images.html)

```{r}
charted <- xl_sheet(
  sales,
  chart = list(
    xl_chart("column",
             list(xl_chart_series(values = list(cols = "revenue"),
                                  categories = list(cols = "quarter"),
                                  labels = xl_chart_labels(num_format = "$#,##0",
                                                           position = "outside_end")),
                  xl_chart_series(values = list(cols = "cost"),
                                  categories = list(cols = "quarter"))),
             title = "Revenue and cost",
             title_format = xl_font(size = 13, bold = TRUE),
             x_axis = xl_chart_axis(title = "Quarter"),
             y_axis = xl_chart_axis(title = "Dollars", min = 0,
                                    num_format = "$#,##0"),
             legend = xl_chart_legend(position = "bottom"),
             series_gap = 60, at = "G2"),
    xl_chart("pie",
             xl_chart_series(values = list(cols = "revenue"),
                             categories = list(cols = "quarter"),
                             labels = xl_chart_labels(show_percentage = TRUE),
                             points = list(xl_fill(background = "#C00000"),
                                           NULL, NULL, NULL)),
             title = "Share of revenue", at = "G20"),
    xl_chart("scatter_straight_markers",
             xl_chart_series(values = list(cols = "revenue"),
                             categories = list(cols = "cost"),
                             marker = xl_chart_marker("circle", size = 7),
                             trendline = xl_chart_trendline("linear",
                                                            equation = TRUE),
                             y_error_bars = xl_chart_error_bars("percentage", 5)),
             title = "Revenue against cost", at = "P2")))

overview <- xl_chartsheet(
  xl_chart("line",
           xl_chart_series(values = list(sheet = "Charts", cols = "revenue"),
                           categories = list(sheet = "Charts",
                                             cols = "quarter")),
           title = "Revenue, full page",
           y_axis = xl_chart_axis(title = "Revenue", min = 0)),
  tab_color = "red")
```

## An image --- [vignette](d-charts-images.html)

```{r}
have_png <- isTRUE(capabilities("png")) &&
  requireNamespace("grDevices", quietly = TRUE)
```

```{r eval = have_png}
card <- as.raster(matrix(c("#4472C4", "#ED7D31", "#A5A5A5", "#FFC000"),
                         nrow = 2))
pictured <- xl_sheet(sales[, 1:2], image = xl_image(card, at = "D2",
                                                    scale = 30,
                                                    description = "A test card"))
```

```{r eval = !have_png, echo = FALSE}
pictured <- xl_sheet(sales[, 1:2])
```

## The workbook --- [vignette](c-worksheets-workbooks.html)

```{r}
wb <- xl_workbook(
  list(Formatted = styled,
       Content   = content,
       Filtered  = filtered,
       Table     = tabled,
       Printed   = printed,
       Charts    = charted,
       Picture   = pictured,
       Overview  = overview),
  properties = xl_properties(
    title    = "writexl showcase",
    author   = "writexl",
    subject  = "Every feature in one workbook",
    custom   = list(Generated = Sys.Date(), Reviewed = FALSE),
    header_format = xl_font(bold = TRUE, color = "white") +
                    xl_fill(background = "#1F3864")))

showcase <- write_xlsx(wb, tempfile(fileext = ".xlsx"))
```

The sheet order matters: any sheet with a header, footer or background image
must come *after* every sheet carrying a floating image or a chart, because
libxlsxwriter numbers the drawing relationships from a counter that skips the
first kind. writexl checks the order and refuses rather than writing a file
Excel repairs.

## The second workbook: an embedded image

An embedded image sits *inside* a cell and sizes with it. libxlsxwriter
numbers its metadata from a counter shared with every other image, so mixing
the two writes a cell reference past the end of the metadata and Excel repairs
the sheet. writexl refuses that combination, which is why this is a second
file:

```{r eval = have_png}
embedded <- write_xlsx(
  list(Embedded = xl_sheet(sales[, 1:2],
                           image = xl_image(card, at = "D2", embed = TRUE))),
  tempfile(fileext = ".xlsx"))
```

## What was written

```{r}
sheets <- c("Formatted", "Content", "Filtered", "Table", "Printed",
            "Charts", "Picture", "Overview")
data.frame(sheet = sheets,
           shows = c("formats, conditional rules, frozen pane",
                     "formulas, links, comments, rich text, merge, validation",
                     "an autofilter with its rows hidden",
                     "a worksheet table with a total row",
                     "page setup, outline symbols, protection",
                     "a column, a pie and a scatter chart",
                     "a picture anchored to a cell",
                     "a chartsheet: one chart, no cells"))
```

```{r}
file.size(showcase)
```

Open it and every feature above is visible in one place. For why any of it
behaves as it does, follow the links back to
[formatting](b-formatting.html),
[worksheets and workbooks](c-worksheets-workbooks.html),
[charts and images](d-charts-images.html), or
[formulas, tables and the rest](e-formulas-and-more.html).
