--- title: "Worksheets and workbooks" output: rmarkdown::html_vignette vignette: > %\VignetteIndexEntry{Worksheets and workbooks} %\VignetteEngine{knitr::rmarkdown} %\VignetteEncoding{UTF-8} --- ```{r setup, include = FALSE} knitr::opts_chunk$set(collapse = TRUE, comment = "#>") library(writexl) ``` `xl_sheet()` wraps a data frame with everything that is true of the sheet rather than of a cell. `xl_workbook()` does the same one level up. ## The sheet ```{r} sheet <- xl_sheet( data.frame(id = 1:3, note = c("a", "b", "c")), cols = xl_col_spec("note", width = 30), freeze = "A2", # keep the header row visible tab_color = "steelblue", zoom = 120, gridlines = FALSE) path <- write_xlsx(list(Notes = sheet), tempfile(fileext = ".xlsx")) ``` `auto_colwidth = TRUE` sizes every column to its contents. The xlsx format has no true AutoFit, so this is a character-count estimate; columns given an explicit width are left alone. Widths and heights can be given in **pixels** instead of Excel's character units and points, which is easier to reason about when sizing a column around an image: ```{r} xl_col_spec("thumbnail", width_pixels = 100) xl_row_spec(1, height_pixels = 40) ``` Excel stores the character units, so the value is converted on the way in --- a column set to 100 pixels reads back as 13.57 units and renders at 100 pixels again. ### Grouping and outlines `level` puts columns or rows into an outline group, which Excel draws with the +/- controls that collapse it. `xl_outline()` changes how those controls are *drawn* --- never which rows are grouped --- and its defaults already match Excel's: ```{r} xl_sheet(data.frame(q1 = 1:2, q2 = 3:4, total = 4:5), cols = xl_col_spec(c("q1", "q2"), level = 1), outline = xl_outline(symbols_right = FALSE)) ``` ### Protection ```{r} xl_sheet(data.frame(id = 1:3), protect = list(password = "secret", sort = TRUE)) ``` `protect = TRUE` locks everything; a list allows named actions through. Cell locking set with `xl_protection()` only takes effect once the sheet is protected, and `locked = FALSE` is how a cell is left editable on a protected sheet. ### Silencing Excel's error indicators Excel puts a green triangle in cells it thinks are wrong, most often a number deliberately stored as text: ```{r} xl_sheet(data.frame(zip = c("01234", "02138")), ignore_errors = list(number_stored_as_text = "A2:A3")) ``` Nine error types can be silenced, each taking a range in any spelling the other range arguments accept. ## The tab strip and the opening view `xl_sheet_view()` collects the rarely-used settings that would otherwise widen `xl_sheet()` past reading: ```{r} xl_sheet(data.frame(x = 1), view = xl_sheet_view(active = TRUE, selection = "B2", hide_zero = TRUE)) ``` Which tab is active, which are selected, which are hidden, which is leftmost, where the sheet is scrolled to, whether zeros show, right-to-left column order, and split panes all live here. `freeze` stays on `xl_sheet()` because keeping the header row visible is the one worksheet option almost everyone wants. Excel's rules about tabs span the whole workbook, so they are checked before anything is written: at most one active sheet, no hidden-and-active sheet, and at least one sheet visible. The error names the sheet at fault. ## Printing `xl_page_setup()` collects everything Excel's Page Layout ribbon controls. None of it affects the cells: ```{r} xl_sheet(data.frame(x = 1:3), page = xl_page_setup( orientation = "landscape", paper = "A4", fit_to = c(1, 0), # one page wide, any number tall center_horizontally = TRUE, header = "&LQuarterly report&RPage &P of &N", repeat_rows = 1, # the header row, on every page print_area = "A1:C4")) ``` Headers and footers use Excel's own `&`-codes: `&L`/`&C`/`&R` for the three sections, `&P` and `&N` for the page number and count, `&D` for the date, `&G` for a picture. Manual page breaks go in with `h_breaks` and `v_breaks`. ## The workbook `xl_workbook()` binds sheets to `xl_properties()`, which holds the document metadata and the formatting defaults that would otherwise be built in: ```{r} wb <- xl_workbook( list(Report = data.frame(quarter = c("Q1", "Q2"), sales = c(12000, 15000))), properties = xl_properties( title = "Quarterly Report", author = "Finance Team", custom = list(Project = "Alpha", Reviewed = TRUE), header_format = xl_font(bold = TRUE, color = "white") + xl_fill(background = "navy"))) path <- write_xlsx(wb, tempfile(fileext = ".xlsx")) ``` Because the defaults are ordinary `xl_format` objects you can change any of them: the header row's style, the date and datetime formats and their column widths, the hyperlink style, and a workbook-wide `default_format` beneath every cell. Custom properties may be text, numbers, dates or logicals. A workbook-wide `default_format` is *emulated* --- it is merged beneath every cell --- because libxlsxwriter has no native "Normal style" hook. The effect is the same; the file is slightly larger. ## Memory For a large workbook writexl can stream each row to disk as it is written rather than holding the whole sheet in memory. It decides per workbook: ```{r eval = FALSE} write_xlsx(big, path, constant_memory = TRUE) ``` Left at `NA` it turns streaming on only when the workbook is large enough for the saving to matter --- estimated from the cell count, with the threshold configurable through `constant_memory_threshold`. Some features cannot be written while streaming, because they touch cells above the row being written: worksheet tables, multi-cell array formulas, merged ranges and embedded images. Those turn it off, and asking for it anyway warns rather than producing a half-written file. ## Elsewhere * Styling the cells themselves: [Formatting cells](b-formatting.html) * Charts, chartsheets and pictures: [Charts and images](d-charts-images.html) * Validation, filters, tables and merges: [Formulas, tables and the rest](e-formulas-and-more.html)