Skip to contents

Worksheets and workbooks

Source: vignettes/c-worksheets-workbooks.Rmd
c-worksheets-workbooks.Rmd

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 

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:

xl_col_spec("thumbnail", width_pixels = 100)
#> <xl_col_spec>
#>   target: kind=col, index=thumbnail 
#>   geometry: width=13.57143
xl_row_spec(1, height_pixels = 40)
#> <xl_row_spec>
#>   target: kind=row, index=1 
#>   geometry: height=30

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:

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))
#> <xl_sheet: 2 rows x 3 cols>
#>   column specs: 1

Protection 

xl_sheet(data.frame(id = 1:3),
         protect = list(password = "secret", sort = TRUE))
#> <xl_sheet: 3 rows x 1 cols>

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:

xl_sheet(data.frame(zip = c("01234", "02138")),
         ignore_errors = list(number_stored_as_text = "A2:A3"))
#> <xl_sheet: 2 rows x 1 cols>

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:

xl_sheet(data.frame(x = 1),
         view = xl_sheet_view(active = TRUE, selection = "B2",
                              hide_zero = TRUE))
#> <xl_sheet: 1 rows x 1 cols>

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:

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"))
#> <xl_sheet: 3 rows x 1 cols>

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:

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:

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