writexl is now maintained by Bill Denney; Jeroen Ooms remains an author.
The version number is 2.0.0 for these, each of which can change what an existing script writes. Everything else in this release is additive.
POSIXct columns are no longer silently converted to UTC. When every
datetime in the workbook shares one time zone, the zone is dropped and local
wall-clock time is written; when they differ, all are converted to UTC with a
warning. Code that relied on always getting the UTC instant will see shifted
values.
Date values before 1900-03-01 were written one day too late, and now agree
with POSIXct. Files written earlier that contain such dates disagree with
files written now.
Columns of a type writexl cannot represent (complex, raw, a bare list
column) are an error naming the column, where before they warned and wrote
empty cells. A script that ignored the warning now stops.
Sheet names are repaired differently: truncated to a genuine 31 characters
rather than 29, with characters Excel forbids replaced and the resulting
duplicates resolved. A workbook with long or awkward sheet names may end up
with different tab names than before -- and, in the "2024/Q1" case, one
Excel will actually open.
A cell column has no column-wide notion of "these are all formulas", so
df[i, j] <- "=SUM(A1:A2)" writes the eleven characters rather than a
formula, and warns that it has. xl_formula() returned a classed character
vector in 1.5.4 and the class survived a row assignment, which is what made
the older spelling work; it now returns a cell object, where a formula is a
property of each cell. Build the column and mark it once:
NOTES[[2]] <- xl_formula(NOTES[[2]]) # after the rows are filled in
xl_hyperlink(name = ) is deprecated in favour of value, which now
occupies the position name used to, so positional calls are unaffected.
Supplying name warns; supplying both is an error.
writexl now reaches the whole feature set of the libxlsxwriter it bundles. Each entry below names the one or two functions to start from; the vignettes carry the detail.
Cell content — xl_cell_general() writes any combination of value,
formula, hyperlink, format and comment, in mixed-type columns. It also
carries array and dynamic array formulas, comments (xl_comment()) and rich
strings, one cell in several fonts (xl_rich_string()). xl_formula() and
xl_hyperlink() return these objects and stay backward compatible;
xl_hyperlink() now writes a real URL hyperlink, with display text and a
tooltip.
Formatting — xl_format() and the group constructors xl_font(),
xl_fill(), xl_border(), xl_align(), xl_num_format() and
xl_protection() build reusable format objects that combine with +, and
apply to a cell, a column, a row, a sheet or the workbook.
Conditional formatting — xl_sheet(conditional =), with xl_cond_cell()
pairing a rule with a format and xl_cond_scale(), xl_cond_bar() and
xl_cond_icons() for colour scales, data bars and icon sets.
Data validation — xl_sheet(validation = xl_validation(...)): dropdown
lists, numeric, date, time and text-length bounds, and custom formulas, with
the input and error messages Excel shows (#43).
Autofilters — xl_sheet(filter = xl_filter(...)). Excel does not apply a
filter when a file is opened, so writexl also hides the rows the criteria
exclude; without that the sheet looks filtered but shows every row.
xl_filter_keep() exposes the same matching rule on its own. The rules
reproduce Excel's, which were measured rather than assumed.
Worksheets and workbooks — xl_sheet() carries column and row geometry
(in Excel's units or in pixels), frozen and split panes, gridlines, tab state
and the opening view, protection, outline display (xl_outline()), and the
error indicators Excel shows on cells it believes are wrong. xl_workbook()
and xl_properties() set document metadata — custom properties may now be
Date or POSIXct — and the workbook-wide formatting defaults, including
hyperlink_format = NULL for hyperlinks with no styling at all.
Page setup and printing — xl_sheet(page = xl_page_setup(...)):
orientation, paper size, margins, scaling and fit-to-pages, centring, headers
and footers, print area, repeating heading rows and columns, manual page
breaks, and the print options.
Tables — xl_sheet(table = xl_table(...)) and xl_table_column(): a
named, styled range with banded rows, a filter dropdown, an optional total
row, and per-column headers, formats and formulas.
Merged cells — xl_sheet(merge = xl_merge(...)). A merged range holds one
value, so xl_merge() carries its own; merging over cells the data frame
filled keeps only that value, as it does in Excel.
Images — xl_sheet(image = xl_image(...)), floating over the cells or
placed inside one with embed = TRUE. The source may be a file path, a raw
vector or an in-memory picture (a raster, colour matrix, RGB array or
nativeRaster), so a plot never has to touch the disk. Also a tiled screen
backdrop via xl_sheet(background_image =), and images in printed headers and
footers. Two arrangements libxlsxwriter miscounts — an embedded image
alongside any other, and a header/footer or background image on a sheet before
one with a floating image — are refused with the order that works.
Charts — xl_sheet(chart = xl_chart(...)) and xl_chart_series(), in all
22 types Excel offers, with axes (xl_chart_axis()), the parts of a series
(markers, data labels, trendlines, error bars and a format per point) and the
chart's own furniture (legend, data table, plot and chart areas, manual
layouts). xl_chartsheet() gives one chart a tab of its own.
A series names its values and categories either as an A1 range
("Data!B2:B10") or by column (list(cols = "revenue")), so a range follows
the data when rows are added or a header is written, and a series that plots a
column is named after that column's header. Series and titles are styled with
the ordinary xl_format() groups — xl_border() becomes the line,
xl_fill() the fill, xl_font() the title text — so one format object can
style both a cell and a chart.
Anything the chart cannot draw is refused by name rather than dropped
silently, which is what Excel does with it: a format group no chart shape has,
a value-axis option on a category axis, a doughnut hole on a bar chart, a data
label in a position its chart type disallows. Tests read the function lists
out of the bundled chart.h, so a function added upstream surfaces as a
failure rather than as a gap.
A stand-in for missing values via na, which writexl has always written
as an empty cell (#76). write_xlsx(df, na = "not measured") sets it for a
whole workbook, xl_properties(na = ) does the same on a workbook object,
and xl_col_spec(na = ) and xl_cell_general(na = ) narrow it to one column
or one cell --- the innermost setting wins. It covers NaN as well as NA,
and keeps its own type, so na = 0 writes a number and leaves a numeric
column numeric. The default, na = NA, is the empty cell as before.
Argument names are consistent across the new functions. Whatever a cell,
label or box will show is value, whatever its type; a size in pixels says
so (width_pixels); and a caption is title, with title_format and
title_layout beside it. as.character() methods on xl_cell_general() and
xl_rich_string() mean a cell built for a sheet can be reused anywhere a
plain string is wanted.
write_xlsx() now errors informatively when a data frame exceeds the xlsx
column limit (16384) or row limit (1048576).
Bundled libxlsxwriter updated to 1.2.4.
See the "Getting started with writexl" vignette, and the five that follow it for formatting, worksheets and workbooks, charts and images, formulas and tables, and one runnable example of everything.
Fix installation on systems without GNU make, by replacing a GNU-specific
pattern rule in src/Makevars with a portable static library recipe
(#97).
Fix a strcpy() buffer overflow in the internal C_set_tempdir(); a tempdir
path of 2048 bytes or more now errors informatively.
write_xlsx() now gives a warning if a column is of unsupported typewrite_xlsx() for corrupted data frameswrite_xlsx() gains option use_zip64 for 4GB+ file supportREprintf instead of fprintfxl_formula() and xl_hyperlink() now correctly support NANA and "" character valuesDate types as proper datetime stringswrite_xlsx() to create xlsx with multiple sheetsDate and hms to strings