## ----include = FALSE----------------------------------------------------------
knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>"
)

# The code below writes real workbooks. They go to a temporary folder, so building the
# vignette leaves nothing behind. The screenshots are made from the same code by
# data-raw/vignette-screenshots.R with Microsoft Excel and are stored in man/figures/.
fig_dir <- normalizePath("../man/figures", mustWork = FALSE)
out_dir <- Sys.getenv("TSG_VIGNETTE_OUT", file.path(tempdir(), "write-xlsx"))
dir.create(out_dir, showWarnings = FALSE, recursive = TRUE)
knitr::opts_knit$set(root.dir = out_dir)

# The screenshots are rendered at twice their size for sharp text; show them at half
# their pixel width (read from the PNG header), which is their size in Excel at 100%.
# A sheet wider than the page scrolls sideways instead of shrinking.
shot <- function(name) {
  path <- file.path(fig_dir, paste0("write-xlsx-", name, ".png"))
  if (!file.exists(path)) return(invisible())
  header <- readBin(path, "raw", 24)
  width <- sum(as.integer(header[17:20]) * 256^(3:0))
  knitr::asis_output(sprintf(
    '<div style="overflow-x:auto"><img src="%s" width="%d" alt="Excel sheet: %s" style="max-width:none;height:auto"></div>',
    knitr::image_uri(path), width %/% 2, name
  ))
}

## ----setup--------------------------------------------------------------------
library(tsg)
library(dplyr)

## ----eval = FALSE-------------------------------------------------------------
# write_xlsx(
#   data,                  # a tsg table, or a (nested) list of them
#   path,                  # a file, or a folder when separate_files = TRUE
#   title, subtitle,       # used when a table has none of its own
#   source_note, footnotes,
#   sheet_name,            # the sheet of a single table
#   separate_files,        # one workbook per list item
#   collapse_list,         # stack a list of tables on one sheet
#   row_group_as_column,   # show group names as a column instead of titles
#   include_table_list,    # add a "List of Tables" sheet
#   table_list_reference,  # titles, numbers and notes kept in a data frame or file
#   facade                 # the look: fonts, colours, borders
# )

## -----------------------------------------------------------------------------
frequency <- person_record |>
  generate_frequency(marital_status) |>
  add_table_title("Table 1. Marital status of household members") |>
  add_table_subtitle("Both sexes, all ages") |>
  add_source_note("Source: person_record sample data") |>
  add_footnote("Percentages are calculated from all members, including those not reported.")

write_xlsx(frequency, "frequency.xlsx")

## ----echo = FALSE-------------------------------------------------------------
shot("frequency")

## -----------------------------------------------------------------------------
write_xlsx(frequency, "frequency-named.xlsx", sheet_name = "Marital status")
openxlsx::getSheetNames("frequency-named.xlsx")

## -----------------------------------------------------------------------------
crosstab <- person_record |>
  generate_crosstab(marital_status, sex) |>
  add_table_title("Table 2. Marital status by sex") |>
  add_source_note("Source: person_record sample data")

write_xlsx(crosstab, "crosstab.xlsx")

## ----echo = FALSE-------------------------------------------------------------
shot("crosstab")

## -----------------------------------------------------------------------------
side_by_side <- person_record |>
  generate_crosstab(marital_status, sex, employed, collapse_list = TRUE)

write_xlsx(side_by_side, "crosstab-multi.xlsx")

## ----echo = FALSE-------------------------------------------------------------
shot("crosstab-multi")

## -----------------------------------------------------------------------------
summary_table <- person_record |>
  generate_summary(age, stats = c("n", "mean", "sd", "median", "min", "max")) |>
  add_table_title("Table 3. Age of household members")

write_xlsx(summary_table, "summary.xlsx")

## ----echo = FALSE-------------------------------------------------------------
shot("summary")

## -----------------------------------------------------------------------------
tables <- list(
  Sex = generate_frequency(person_record, sex),
  Marital = generate_frequency(person_record, marital_status),
  Employment = generate_crosstab(person_record, employed, sex)
)

write_xlsx(tables, "tables.xlsx")
openxlsx::getSheetNames("tables.xlsx")

## -----------------------------------------------------------------------------
several <- generate_frequency(person_record, sex, employed, seeing)
names(several)

## -----------------------------------------------------------------------------
long_names <- generate_frequency(person_record, hearing, walking)
names(long_names)

write_xlsx(long_names, "long-names.xlsx", trim_sheet_names = TRUE)
openxlsx::getSheetNames("long-names.xlsx")

## -----------------------------------------------------------------------------
write_xlsx(tables, "tables-titled.xlsx", title = "Household survey", source_note = "Source: person_record")

## ----echo = FALSE-------------------------------------------------------------
shot("list-sheet")

## -----------------------------------------------------------------------------
write_xlsx(several, "stacked.xlsx", collapse_list = TRUE)

## ----echo = FALSE-------------------------------------------------------------
shot("stacked")

## -----------------------------------------------------------------------------
write_xlsx(tables, "separate", separate_files = TRUE)
list.files("separate")

## -----------------------------------------------------------------------------
by_sex <- person_record |>
  group_by(sex) |>
  generate_frequency(marital_status, group_as_list = TRUE)

names(by_sex)

write_xlsx(by_sex, "group-list.xlsx")
openxlsx::getSheetNames("group-list.xlsx")

## ----echo = FALSE-------------------------------------------------------------
shot("group-list")

## -----------------------------------------------------------------------------
hierarchy <- person_record |>
  group_by(sex) |>
  generate_frequency(marital_status, group_as_hierarchy = TRUE)

write_xlsx(hierarchy, "hierarchy.xlsx")

## ----echo = FALSE-------------------------------------------------------------
shot("hierarchy")

## -----------------------------------------------------------------------------
nested <- person_record |>
  group_by(sex, employed) |>
  generate_frequency(marital_status, group_as_list = TRUE)

write_xlsx(nested, "nested.xlsx")
openxlsx::getSheetNames("nested.xlsx")

## ----echo = FALSE-------------------------------------------------------------
shot("nested")

## -----------------------------------------------------------------------------
write_xlsx(by_sex, "group-one-sheet.xlsx", collapse_list = TRUE)

## ----echo = FALSE-------------------------------------------------------------
shot("group-one-sheet")

## -----------------------------------------------------------------------------
write_xlsx(by_sex, "group-column.xlsx", collapse_list = TRUE, row_group_as_column = TRUE)

## ----echo = FALSE-------------------------------------------------------------
shot("group-column")

## -----------------------------------------------------------------------------
write_xlsx(nested, "nested-column.xlsx", row_group_as_column = TRUE)

## ----echo = FALSE-------------------------------------------------------------
shot("nested-column")

## -----------------------------------------------------------------------------
write_xlsx(tables, "indexed.xlsx", include_table_list = TRUE)
openxlsx::getSheetNames("indexed.xlsx")

## ----echo = FALSE-------------------------------------------------------------
shot("toc")

## -----------------------------------------------------------------------------
reference <- data.frame(
  table_id = c("Sex", "Marital", "Employment"),
  table_name = c("By sex", "By marital status", "Employment by sex"),
  table_number = 1:3,
  title = c(
    "Household members by sex",
    "Household members by marital status",
    "Employment status by sex"
  ),
  subtitle = c(NA, NA, "Persons with a reported status"),
  source_note = "Source: person_record sample data",
  footnotes = NA
)

write_xlsx(
  tables,
  "reference.xlsx",
  include_table_list = TRUE,
  table_list_reference = reference
)

openxlsx::getSheetNames("reference.xlsx")

## ----echo = FALSE-------------------------------------------------------------
shot("reference-index")

## ----echo = FALSE-------------------------------------------------------------
shot("reference-table")

## ----eval = FALSE-------------------------------------------------------------
# generate_template("table-list.xlsx", template = "table-list")

## -----------------------------------------------------------------------------
write.csv(reference, "table-list.csv", row.names = FALSE)

write_xlsx(
  tables,
  "reference-file.xlsx",
  include_table_list = TRUE,
  table_list_reference = "table-list.csv"
)

## -----------------------------------------------------------------------------
crosstab_plain <- person_record |>
  generate_crosstab(marital_status, sex) |>
  add_table_title("Marital status by sex")

write_xlsx(crosstab_plain, "booktabs.xlsx", facade = "booktabs")
write_xlsx(crosstab_plain, "financial.xlsx", facade = "financial")
write_xlsx(crosstab_plain, "dark.xlsx", facade = "dark")

## ----echo = FALSE-------------------------------------------------------------
shot("facade-booktabs")

## ----echo = FALSE-------------------------------------------------------------
shot("facade-financial")

## ----echo = FALSE-------------------------------------------------------------
shot("facade-dark")

## -----------------------------------------------------------------------------
crosstab_plain |>
  add_facade(header.fgFill = "#FFF2CC", table.offsetRow = 1, table.offsetCol = 1) |>
  write_xlsx("offset.xlsx")

## ----echo = FALSE-------------------------------------------------------------
shot("facade-offset")

## -----------------------------------------------------------------------------
write_xlsx(frequency, "options.xlsx", creator = "Survey Unit", zoom = 120)

