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.
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)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")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"))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))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")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.
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:
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"))
#> sheet shows
#> 1 Formatted formats, conditional rules, frozen pane
#> 2 Content formulas, links, comments, rich text, merge, validation
#> 3 Filtered an autofilter with its rows hidden
#> 4 Table a worksheet table with a total row
#> 5 Printed page setup, outline symbols, protection
#> 6 Charts a column, a pie and a scatter chart
#> 7 Picture a picture anchored to a cell
#> 8 Overview a chartsheet: one chart, no cellsOpen 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, worksheets and workbooks, charts and images, or formulas, tables and the rest.