- Formulas that do not have a
A1
cell reference to increase, are now shareable too.wb_add_formula(x = "1", dims = "A1:A2", shared = TRUE)
wb_set_properties()
now has adatetime_modify
option. 1176- Make non consecutive equal sized dims behave similar to non equal sized non consecutive dims. This makes
dims = "A1:A5,C1:D5"
behave similar todims = "A1,C1:D1,A2:A5,C2:D5"
. 1183 - Improvements to the internal C++ code in
wb_add_data()
to avoid string copies. 1184 This is a continuation of work started in 1177 to speedupwb_add_data()
/wb_add_data_table()
. - Extend the
bookview
handling. It is now possible to add more than onebookview
usingwb_set_bookview(view = 2L)
and to remove additionalbookview
s withwb_remove_bookview()
. Availablebookview
s can be inspected withwb_get_bookview()
. 1193 - Actually implement
sep
functionality inwb_add_data()
andwb_add_data_table()
for list columns inx
. 1200 create_sparklines
now allows to add multiple sparklines as a group. (1205, @trekonom)
- Create date is not reset to the present time in each call to
wb_set_properties()
. 1176 - Improve handling of file headers and footers for a case where
wb_load()
would previously fail. 1186 - Partial labels were written only over the first element and only if assigned in an ordered fashion. 1189
- Enable use of
current_sheet()
increate_sparklines()
. This is the default for the function, but was not supported. - When setting headers and footers, elements can now be skipped with
NA
as documented. 1211
wb_workbook()
and the underlyingwbWorkbook
object gained a new argumentdatetime_modified
. This argument was added afterdatetime_created
and changes the number and ordering of arguments. 1176
- Add
hide_no_data_items
option inwb_add_slicer()
. 1169
- Previously rows that trigger scientific notation (e.g.
1e+05
) would cause issues, when matched against a non scientific version. 1170 - When using
wb_add_data_table(..., total_row = TRUE)
the last row of the data table in the workbook was mistakenly overwritten with the total row formula, which should have been placed below the last row of the table. This caused loss of data. 1179
-
When writing a file with
na.strings = NULL
, the file will not contain any reference to the blank cell. Depending on the number of missings in a data set, this can reduce the file size significantly. 1111 -
wb_to_df()
gained a new argumentshow_hyperlinks
which returns the target or location of a hyperlink, instead of the links description. 1136 -
A new wrapper function
wb_add_hyperlink()
extends the capabilities of writing hyperlinks to shared hyperlinks. Shared hyperlinks bring along internal changes that are noted below. 1137 -
Add
address
field towb_add_image()
. This can be used to add a url or mailto address to an image (1138, conversion from a PR by @jistria foropenxlsx
) -
It is now possible to remove hyperlinks with either
wb_remove_hyperlinks()
orwb_clean_sheet()
. 1139 -
wb_add_data_table()
will throw a warning if non distinct column names are found and will fix this for the user. Non distinct can be duplicated or even upper and lower casex
andX
. 1150
-
The integration of the shared formula feature in the previous release broke the silent extension of dims, if a single cell
dims
was provided for anx
that was larger than a single cell inwb_add_formula()
. 1131 -
Fixed a regression in the previous release, where
wb_dims()
would pass column names passed viacols
tocol2int()
which could cause overflow errors resulting in a failing check. 1133 -
Fix cloning from worksheets with multiple images.
-
Improved
wb_to_df(types = ...)
. Previously if used on unordered data this could cause unintended class order.types
no longer requires knowledge of the order of the variables. Not all variables must be specified and it accepts character classes as well. 1147 -
Improve creation of table ids. Previously, on a dirty workbook, unique table ids were not enforced. This caused duplicated table ids, which lead to errors in spreadsheet software. 1152
- The handling of shared hyperlinks has been updated. Previously, when loading a file with shared hyperlinks, they were converted into
wbHyperlink
objects (a legacy fromopenxlsx
). With recent internal changes, hyperlinks are no longer automatically transformed intowbHyperlink
objects. If you still require these objects, you can use the internal functionwb_to_hyperlink(wb, sheet = 1)
. However, please note that this class is not essential foropenxlsx2
and may be further simplified or removed in the future without notice. 1137
-
Experimental support for shared formulas. Similar to spreadsheet software, when a cell is dragged to horizontally or vertically. This requires the formula to be written only for a single cell and it is filled by spreadsheet software for the remaining dimensions.
wb_add_formula()
gained a new argumentshared
. 1074 -
Experimental support for reading shared formulas. If
show_formula
is used withwb_to_df()
, we try to show the value that is shown in spreadsheet software. 1091 -
It is possible to read cells containing formulas as formula. 1103
-
If a font unknown to
openxlsx2
is usedwb_set_col_widths()
defaults to using the workbooks default font. 1080 -
Previously, if only
cols
androws
were passed towb_dims()
and row1
was selected, incorrect results were returned. This has been fixed. 1094 -
wb_dims()
no longer ignoresabove
,below
,left
,right
iffrom_dims
is not supplied. 1104 -
wb_to_df()
withskip_hidden_rows = TRUE
works now if a file path is passed. 1122
-
Legacy
write_data()
,write_datatable()
,write_formula()
,write_comment()
are now deprecated in favor ofwb_add_data()
,wb_add_data_table()
,wb_add_formula()
, andwb_add_comment()
. (1064, @olivroy). -
convertToExcelDate()
is defunct and will be removed in a future version of the package. Useconvert_to_excel_date()
. (1064, @olivroy).
-
wb_dims()
is now able to handle various columns. 1019 -
wb_to_df()
now has acheck_names
argument. 1050 -
The set of conditional formatting icon sets now includes x14 icons. This commit also fixed adding conditional formatting to worksheets with pivot tables. 1053
-
Many improvements in the
xlsb
parser. This includes changes to the logic of the formula parser, rich text strings are now handled, data validation, table formulas and various corrections all over the place. It is still lacking various features and this wont change in the foreseeable future, but the parser is now in better shape than ever. 1037, 1040, 1042, 1044, 1049, 1054 -
write_xlsx()
now usessheet
. Previously it required the undocumentedsheet_name
. 1057 -
Fixed a bug were we obfuscated valid html in worksheets with vml buttons. These files previously did not load. 1062
-
Fixed slow writing of non consecutive number formats introduced in the previous release. 1067, 1068
-
Add function to remove conditional formatting from worksheet
wb_remove_conditional_formatting()
1011 -
Silence a warning triggered by a folder called
"[trash]"
. 1012 -
Initial support for pivot table timelines. 1016
-
Add
wb_add_timeline()
and extendwb_add_slicer()
. 1017
-
Fixed an issue with non consecutive dims, where columns or rows were silently dropped. 1015
-
Fixes to
wb_clone_worksheet()
cloning drawings and images should be restored. 1016 -
Fixed an issue where non consecutive columns with special types would overlap. If columns A and C were dates, column B would be formatted as date too. 1026
-
Helper to read sensitivity labels from files and apply them to workbooks. 983
-
It is now possible to pass non consecutive dims like
"A1:B1,C2:D2"
to various style helpers likewb_add_fill()
. In addition it is now possible to write a data set into a predefined dims region usingenforce = TRUE
. This handles either","
or";"
as cell separator. 993
# force a dataset into a specific cell dimension
wb <- wb_workbook()$add_worksheet()
wb$add_data(dims = "I2:J2;A1:B2;G5:H6", x = matrix(1:8, 4, 2), enforce = TRUE)
-
Allow writing data frames with zero rows. 987
-
wb_dims()
has been improved and is safer on 0-length inputs. In particular, it will error for a case where acols
doesn't exist inx
(990, @olivroy).
# Previously created a wrong dims
wb_dims(x = mtcars, cols = "non-existent-col")
# Now errors
wb_set_col_widths()
is more strict about its arguments. If you providecols
,widths
, orhidden
don't have appropriate length, it will throw a warning. This may change to an error in the future, so it is recommended to use appropriate values. (991, @olivroy).
(This was updated post release.)
-
It's now possible to pass array formula vectors to
wb_add_formula()
. 958 -
wb_add_data_table()
gained a newtotal_row
argument. This allows to add a total row to spreadsheets including text and spreadsheet formulas. 959 -
wb_dims()
now acceptsfrom_dims
to specify a starting cell 960. -
You can now set
options(openxlsx2.na.strings)
to a value to have a default value forna.strings
inwb_add_data()
,wb_add_data_table()
, andwrite_xlsx()
968. -
The direction vectors are written is now controlled via
dims
. Previously it was required to transpose a vector to write it horizontally:wb_add_data(x = t(letters), col_names = FALSE)
. Now the direction is defined bydims
. The default is still to write vectors vertically, but for a horizontal vector it is possible to writewb_add_data(x = letters, dims = "A1:Z1")
. This change impacts vectors, hyperlinks and formulas and basically everything that is not a two dimensionalx
object.
# before (workaround needed)
wb$add_data(dims = wb_dims(rows = 1, cols = 1:3), x = t(c(4, 5, 8)), col_names = FALSE)
# now (listens to dims)
wb$add_data(dims = wb_dims(rows = 1, cols = 1:3), x = c(4, 5, 8))
- Export
wb_add_ignore_error()
. 955
-
Experimental support for calculation of pivot table fields. 892
-
Improve sparkline creation with new options and support snake case arguments. 920
-
Experimental support to get and set base colors. 938
-
Character strings with XML content were not written correctly:
a <br/> b
was converted to something neither we nor spreadsheet software was able to decipher. 895 -
Restore
first_active_row
/first_active_col
andfirst_col
/first_row
functionality inwrite_xlsx()
. 903 -
Further attempts to fix pivot table sorting. 912 In addition improve handling of non distinct names in
wb_data()
and addcreate_pivottablestyle()
. 914 -
Document adding background color and images to comments and fix adding more than two images as background. 919
-
Improvements to
wb_set_base_font()
. This modifies the workbook theme now, including panose values. 935 -
Hyperlinks now use the color of the theme and the base size. 937
-
wb_add_data()
andwb_add_data_table()
yield better error messages if attempting to add data to an empty workbook (942, @olivroy).
-
Updating to themes. This includes updates to the default style
'Office Theme'
899- This includes switching to the new default font
'Aptos Narrow'
- A new style
'Office 2013 - 2022 Theme'
was added
Users that want to remain on the old style should use
wb_workbook(theme = 'Office 2013 - 2022 Theme')
orwb_set_base_font(font_name = "Calibri")
. - This includes switching to the new default font
-
Code simplifications (924, @olivroy), partial matching (923, @olivroy)
-
Updates to test files and findings by various linters (922, @olivroy)
-
Improve wbWorkbook documentation to help disambiguate the chaining and piping methods (936, @olivroy and 928).
- Further tweaks to documentation and vignettes to make them more consistent.
wb_add_pivot_table()
/wb_add_slicer()
wb_load()
:calc_chain
is no longer visible and the previous text that might have been misleading in regards of its use, has been replaced by a more detailed description of what are the consequences of keeping the calculation chain
-
Allow further modifications of comments. The background can now be filled with a color or an image. 870
-
Added
wb_set_cell_style_across()
to apply a cell style to selected columns and rows. This allows unlocking cells to make use column and row properties ofwb_protect_worksheet()
which require additional cell styles (see issue 871 for a more detailed explanation).wb_set_cell_style()
now accepts a cell dimension in thestyle
argument. 873
-
wb_add_ignore_error()
now returns awbWorkbook
. 865 -
Deactivate the
is_hyperlink
check for non-dataframe objects inwb_add_data()
. Internally,vapply()
is applied to the input object, which is applied column-wise for a data frame and cell-wise for a matrix. This speeds up the writing of larger matrices considerably. 876 -
Column style
currency
is now correctly applied to numeric vectors. Previously it was not handled. This applies the built in spreadsheet style for currency presumably linked to the spreadsheet software locale. 879 -
wb_to_df(col_names = FALSE)
no longer drops column names from logical vectors. Previously, column names were replaced byNA
. Now the column name is returned as a cell value in a character column. 884
- Add new params to
wb_add_pivot_table()
. It is now possible to set theshow_data_as
value and set a tabular table design. 833
-
Previously formulas written as data frames were not xml escaped. 834
-
Improve drawing relationship id selection that could cause issues with unordered relationship ids in loaded workbooks. 838
-
Improve copying cells in transpose mode and with hyperlinks. 850
-
Options
openxlsx2.maxWidth
andopenxlsx2.minWidth
are now respected as documented when setting column widths withwb_set_col_widths()
. 847
-
Basic support for cloning worksheets across workbooks was added in
wb_clone_worksheet()
. This should work with tabular and formula data, images and charts. Support for more complex worksheets is known pending. 622 -
Enable loading workbooks without sheets with
wb_load(sheet = NULL)
. This is helpful if the sheets contain a lot of data and only some general workbook data is of interest. 810 -
It is no longer needed to manually create columns for
wb_group_cols()
. 781 -
It is now possible to sort pivot tables created by
wb_add_pivot_table()
. 795 -
Basic support for the
xlsb
file format. We parse the binary file format into pseudo-openxml files that we can import. Therefore, after importing, it is possible to interact with the file as if it had been provided asxlsx
in the first place. This is of course slower than reading directly from the binary file. Our implementation is also still missing some features: some array formulas are still broken, conditional formatting and data validation are not implemented, nor are pivot tables and slicers. 688- Please note that
openxlsx2
is not a security tool and thexlsb
parser was written with the intention of reading validxlsb
files on little endian systems. - Please do not raise issues about
xlsb
in terms of speed or completeness of features of the openxml standard. If you have access to other file conversion tools, such as spreadsheet software, they may provide a better solution for your needs. - Writing
xlsb
files is outside the scope of this project.
- Please note that
-
New set of function
wb_get_properties()
/wb_set_properties()
to view and modify workbook properties. 782 This was subsequently improved to handle more workbook properties likecompany
andmanager
. (799, @olivroy) -
Basic (experimental) support to add slicers to pivot tables created by
openxlsx2
. 822
-
Removing the worksheet that is the active tab does no longer result in warnings in spreadsheet software. 792
-
update_table()
works now without if the table has noautofilter
. 802
-
wb_get_worksheet()
,wb_ws()
,wb_get_sheet_name()
are no longer exported. 735 They never worked as expected.- Use
wb_get_sheet_names()
,wb_get_active_sheet()
,wb_get_selected()
instead.
- Use
-
wbChartSheet
is now internal (760, @olivroy)
These functions are no longer recommended. A guide was created to help users They will continue to work for some time, but changing to newer functions is recommended.
delete_data()
is deprecated in favor ofwb_remove_tables()
orwb_clean_sheet()
create_comment()
is deprecated in favor ofwb_comment()
. Note that whilewb_comment()
has some new defaults, the behavior ofcreate_comment()
has not changed. (758, @olivroy)
-
wb_comment()
is a new helper function to help createwbComment
objects It is very similar tocreate_comment()
, with the following differences:author
looks atoptions("openxlsx2.creator")
; (create_comment()
only usedsys.getenv("USERNAME")
/Sys.getenv("USER")
)visible
defaults toFALSE
to account for modern spreadsheet software behavior. (create_comment()
, it isTRUE
).width
andheight
must now be of length 1. (Increate_comment()
, the first element is taken, other are ignored.)
-
wb_get_sheet_names()
gains aescape
argument to allow special XML characters to be escaped. 252 -
wb_color()
now accepts hex colors with leading sharp (e.g. "#FFFFF") 728. -
wb_merge_cells()
gains asolve
argument. This allows to solve cell intersecting regions. 733 -
wb_add_comment(comment = "x")
no longer errors when a comment as a character vector no longer fails (758, @olivroy)
- Tweaks to documentation and vignettes to make them more consistent.
-
wb_to_df()
now handlesdate1904
detection. Previous results from this somewhat rare file type were using the default timezone origin of 1900-01-01. 737 -
wb_load()
handles more cases. -
wb_load()
adds thepath
to thewbWorkbook
object. 741 -
wb_set_header_footer()
now works with special characters 747
wb_get_active_sheet()
,wb_set_active_sheet()
,wb_get_selected()
andwb_set_selected()
,wb_get_named_regions()
are now wrapper functions. 735
-
Function arguments are now defaulting to
snake_case
. For the time being, both arguments are accepted andcamelCase
will be switched tosnake_case
under the hood. Some examples are currently still displayingcamelCase
and maybe somecamelCase
function slipped through. 678 -
write_formula()
,write_data()
,write_datatable()
,write_comment()
are no longer recommended,- Use
wb_add_formula()
,wb_add_data()
,wb_add_data_table()
,wb_add_comment()
instead.
- Use
-
Order of arguments in
wb_add_named_region()
changed, because previously overlookeddims
argument was added. -
In various functions the order of
dims
was changed, to highlight it's importance -
Cleanups
-
remove deprecated functions
-
remove deprecated arguments
xy
argument- arguments
col
,row
,cols
,rows
.start_col
,start_row
andgridExpand
were deprecated in favor ofdims
. Row and column vectors can be converted todims
usingwb_dims()
. xlsx_file
in favor offile
inwb_to_df()
-
deprecating function
convertToExcelDate()
forconvert_to_excel_date()
wb_grid_lines()
forwb_set_grid_lines()
-
make
get_cell_refs()
,get_date_origin()
,guess_col_type()
, andwrite_file()
,dataframe_to_dims()
,dims_to_dataframe()
,wb_get_sheet_name()
internal functions -
make classes
styles_mgr()
,wbSheetData
,wbWorksheet
,wbComment
,wbHyperlink
internal
-
wb_dims()
was added as a more convenient replacement forrowcol_to_dims()
.(691 and 702, @olivroy) The new function can take either numeric (for rows or columns) or character (column) vectors, in addition it is able to create dimensions for R objects that are coercible to data frame. This allows the following variants:wb_dims(1:5, letters)
wb_dims(1:5, 1:26)
wb_dims(x = matrix(1, 5, 26))
wb_dims(x = mtcars, from_col = "C", from_row = 2, row_names = TRUE)
- Handling of thread comments is now possible via
wb_add_thread()
. This includes options to reply and resolve comments.
- Improve
show_formula
. Previously it was called to early in the function and skipped a few cases. 715
-
Cleanup / revisit documentation and vignettes (682, @olivroy)
-
The function index has been improved. (717, @olivroy)
-
It is now possible to apply a specific theme to a workbook. 630
-
Removed a few of the former example files and updated the code to use a new default example. This changes internal testing to only run locally if online and external files are required. This reduces the package footprint a little, because 1MB of xlsx files are now excluded. 632
-
The handling of
fmt_txt()
objects has been improved. It now creates objects of classfmt_txt
with their ownprint()
,+
, andas.character()
methods. Such objects can now also be used astext
increate_comment()
. 636 -
Improve support for inputs with
labels
attribute. If e.g. afactor
label is numeric, we now try to write the label as number. This should impact the way other partially labeled variables are written. 639 -
Added new wrapper function
wb_add_named_style()
this supports pre-defined theme aware cell styles likeTitle
orNote
. In addition loading of cell styles was improved and additional custom cell styles should be available as well. 628 -
Provide additional options to write special characters in non-unicode environments. 641
-
Add
wb_add_dxfs_style()
as single line wrapper to create dxf styles used in conditional formatting and custom table styles. 665
-
On load
app.xml
is now assigned towb$app
. Previously it was loaded but not assigned. 629 -
Previously if
wb_to_df()
was used with argumentcols
, columns that were missing were created at the end of the output frame. Now columns are returned ordered. 631 -
Fix a bug in
wb_load()
that was modifying the cell range of conditional formatting. 647
-
Order of arguments in
wb_add_conditional_formatting()
changed, because previously overlookeddims
argument was added. 642 -
New argument
gradientFill
was added tocreate_dxfs_style()
. 651 -
Special characters are now escaped in conditional formatting. Hence, previously manually escaped conditional formatting needs updates. 666
-
The
dims
argument ofwb_add_formula()
can be used to create a array references. A newcm
argument was added which might be useful, if formulas previously created addition@
in spreadsheet software. Examples how to use formulas were added to a new vignette. 593 -
Allow using custom data table styles. This fixes a few minor style inconsistencies. 594
-
Allow reading and writing
hms
columns. 601 -
Import
tableStyles
withwb_load()
and improvedxf
style creation. 603 -
Add
fmt_txt()
to style character strings. 607 -
Add new wrapper to ignore worksheet errors
wb_add_ignore_error()
. 617 -
Add new wrapper to update table references
wb_update_table()
. 606
-
Improve handling of non standard
OutDec
options. 611 -
openxlsx2
now does a better job of trying to returncharacter
values from classes that are foreign to it. This has been going on for quite some time, although previously we had a bug that treated such classes asnumeric
, resulting in corrupted xlsx files. 615 -
We now return a few additional xml arguments from worksheets. 617
-
Improve
col2int()
to accept column ranges likecol2int("A:Z")
. This should allow using column ranges in various places likewb_merge_cells(cols = "B:D", ...)
orwb_read(cols = c("A","C:D"))
. 575 -
Add
dims
argument towb_add_image()
andwb_add_plot()
. This can be used to place images starting at a cell or span a cell range. This deprecatesxy
inwb_add_plot()
. This adds colOffset and rowOffset towb_add_drawing()
andwb_add_mschart()
andwb_add_chart_xml()
. 578 -
Add
skipHiddenCols
andskipHiddenRows
towb_to_df()
. In this way, hidden columns and rows are ignored, assuming that the person who has hidden them assumes that they are not important. 579 -
When writing
tibble
useto.data.frame()
just like in thedata.table
case. 582 -
Add cleanup internal comment code in
write_comment()
. This should not impact the workbook wrapper code inwb_add_comment()
. 586 -
Added chain functions for
wb_to_df()
andwb_load()
. 587
-
Styles arguments now accept logical and numeric arguments where applicable. 558
-
Adding
dims
argument towb_clean_sheet()
. This allows to clean only a selected range. 563
-
na.strings = NULL
is no longer ignored inwrite_xlsx()
552 -
Explicit type conversion to date and datetime is finally available. 551
-
skipEmptyCols
andskipEmptyRows
behavior inwb_to_df()
related functions was switched to include empty columns that have a name. Previously we would exclude columns if they were empty, even if they had a name. 555 -
Cleanups in 548
-
remove deprecated functions
cloneSheetStyle()
get_cell_style()
set_cell_style()
wb_conditional_formatting()
-
remove deprecated arguments
xy
argument forwrite_data_table()
interacting functionsfile
fromxl_open()
definedName
fromwb_to_df()
interacting functions
-
deprecating function
get_named_regions()
forwb_get_named_regions()
-
-
Allow hierarchical grouping.
wb_group_cols
/wb_group_rows
now accept nested lists as grouping variable. 537 -
It is now possible to add form control types
Checkbox
,Radio
andDrop
to a workbook usingwb_add_form_control()
. 533 -
Improve
wb_to_df(fillMergedCells = TRUE)
to work better with dimensions. It is now possible to fill cells where the merged cells intersect with the selected dimensions. 541 -
Speedup cell initialization. This is used in wb_style functions like
wb_add_numfmt()
. The previous loop was replaced with a faster implementation. 545 -
Improve date detection in
wb_to_df()
. This improves date and posix detection with custom date formats. 547 -
na_strings()
is now used as the explicit default value forna.strings
parameters in exported workbook functions. 473 -
waiver functions (i.e.,
next_worksheet()
,current_worksheet()
, andna_strings()
) are now exports 474
-
Fixed a bug when loading input with multiple sheets where not every sheet contains a drawing/comment. Previously we assumed that every sheet had a comment and ordered them incorrectly. This caused confusion in spreadsheet software. 536
-
Fixed a bug with files containing 10 or more external references. In this case we did not load the references in numeric order and instead as "1.xml", "10.xml", ..., "2.xml", ... This jumbled up the external references. 538
-
Improve column and row grouping. It is now possible to group by list, so that you can create various levels of groupings. 486
-
writeData()
callsforce(x)
to evaluate the object before options are set (#264) -
tabColor
inwb_add_worksheet()
now allows passingwb_color()
. 500 -
Add
wb_copy_cells()
a wrapper that allows copying cell ranges in a workbook as direct copy, as reference or as value. 515 -
Experimental option:
openxlsx2.string_nums
to write string numerics differently. A string numeric is a numeric in a string like:as.character(1.5)
. The option can be- 0 = the current default. Writes string numeric as string (the incorrect way according to spreadsheet software)
- 1 = writes string numeric as numeric with a character flag (the correct way according to spreadsheet software)
- 2 = convert all string numeric to numeric when writing
This is experimental, because the impact is somewhat unknown. It might trigger unintended side effects. Feedback is requested.
-
Enable writing strings as
sharedStrings
with argumentinline_strings = FALSE
. This creates asharedStrings
table in openxml that allows to reuse strings in the workbook efficiently and can reduce the file size if a workbook has many cells that are duplicates. 499 -
Initial implementation of
wb_add_pivot_table()
. This allows adding native pivot tables toopenxlsx2
workbooks. The pivot table area will remain empty until the sheet is opened in spreadsheet software and evaluated successfully. This feature is newly developed and can cause unexpected side effects. Be aware that using it might currently break workbooks.
-
Reading of files with frozen panes and more than one section node was restored. 495
-
Fixed a copy and paste mistake in
add_border()
which used left borders for right borders. 496 -
Improve XML unescaping. 497
-
Fix reading and saving workbooks with multiple slicers per sheet. 505
-
Fix tab selection always selecting the first sheet since #303. 506
-
Do not export
write_data2()
anymore. This was used in development in the early stages of the package and should not be used directly anymore. -
Only documentation:
openxlsx2
defaults to American English 'color' from now on. Though, we fully support the previous 'colour'. Users will not have to adjust their code. Our documentation only listscolor
, but you can passcolour
just the same way you used to. 501 502
-
Provide new argument
calc_chain
towb_load()
. This is set toFALSE
per default, to ignore the calculation chain if it is found. This change only reflects files imported from third party spreadsheet software and should not be visible to the user. 461 -
Tweaks to
wb_data()
. Dims is now optional and will select data similar towb_to_df()
, similar it allows passing down otherwb_to_df()
arguments. Though, it probably is a good idea not be to creative passing down arguments, not all will result in a usablewb_data
object. 462 -
Add
hidden
argument and change the default forheights
toNULL
inset_row_heights()
. This allows changing the row height and/or hiding selected rows. This does not yet provide a way to hide rows per default. 475 -
Add
wb_add_chartsheet()
for chart sheet support. Along with internal cleanup around chart sheet code. 466
-
Fix
wb_freeze_pane()
. This changes the load logic a bit. Previously we put everything intosheetViews
(the frozen pane is part of this). Thoughwb_freeze_pane()
assumes thatfreezePane
is used. We now try to be smart and split sheetViews upon loading. 465 -
Previously, adding mschart objects to sheets was only possible if (1) the worksheet already contained a drawing (if the workbook was loaded) or (2) to the last sheet of the workbook. This has now been fixed. Adding mschart objects to any worksheet in the workbook is now possible as intended. 458
-
Really fix double xml escaping when saving. 467
-
Improving the drawing logic. There are some workbooks with various drawings per sheet and previously there were combinations possible that were not reflecting this. 478
-
Provide
rvg
support viawb_add_drawing()
. This allows integratingrvg
plots into xlsx files. 449 -
Improve print options. Defaults to printing grid lines, if the worksheet contains grid lines. 440
-
Support reading files with form control. 426
-
Handle input files with chart extensions. 443
-
Improve writing styles to workbook. Previously every cell was checked, this has been changed to check unique styles. 423
-
Implement reading custom file properties. 418
-
Improved
add_named_region()
. This function includes now various xml options. 386 -
Add ... as argument to
read_xlsx()
andwb_read()
. 381 -
Allow reading files with xml namespace created by third party software. 405
-
Update or remove calculation chain when overwriting formulas in a workbook. 438
-
Fix double xml escaping when saving. 435
-
Minor tweak for POSIXct dates and try to avoid the notorious 29Feb1900. 424
-
Implement reading
customXml
folder for input files with connection. 419 -
Fixed saving files with
<sheetPr/>
tag. Previously this was wrapped in a secondsheetPr
node. This issue occurs with xlsm files only. 417 -
Fixed a case where embedded files were assigned incorrectly in worksheet relationships. This caused corrupted output. 403
-
Remove
merge_
functions for styles. 450 -
Previously if a loaded workbook contained formulas pointing to cells modified by
openxlsx2
, these formulas were not updated, once the workbook was opened in spreadsheet software. This is now enforced, unless the optionopenxlsx2.disableFullCalcOnLoad
is set. In this case we would respect the original calculation properties of the workbook. -
wb_save()
no longer returns thepath
that the object was saved to, but instead thewbWorkbook
object, invisibly. This is consistent with the behavior of others wrappers. 378 -
Remove never used
all.equal.wbWorkbook()
. The idea was nice, but it never developed into something useful. -
Remove never used
wb_chart_sheet()
function. 399
- Provide
set_sheetview()
in sheets. Can be used to provide awbWorkbook
function and wrapper in the future. 399
-
Functions adding data to a workbook now bring an
applyCellStyle
argument. If this isTRUE
openxlsx2
will apply a numeric style, ifFALSE
we will simply add the numeric value without additional styling and use the previous cell style. 365 -
Reading from file or workbook with
showFormula
now returns all formulas found in the workbook. Previously only those with typee
orstr
were returned. Now we will be able to see formulas like hyperlinks too. 352
-
Moved data validation list from x14 to data validation. This enables data validation lists without x14 extension openxlsx 386. 347
-
Removed
build_cell_merges()
and replaced it with a plain R solution. 390
-
Improvements to setting column widths. Previously values set by
set_col_widths()
were a little off. This has now been improved. There are still corner cases where the column width set withopenxlsx2
does not match those shown in spreadsheet software. Notable differences can be seen with floating point values (e.g.,10L
works while10.1
is slightly off) and with column width on Mac. 350 -
Improve
rowNames
when writing data to worksheet. Previously the name for the rownames column defaulted to1
. This has been changed. Now with data it defaults to an empty cell and with a data table it defaults to_rowNames_
. 375 -
Fix the workbook xml relationship file to not include a reference to shared strings per default. This solves 360 for plain data files written from
openxlsx2
. 363 -
Adding cell styles has been streamlined to increase consistency. This includes all style functions like
wb_add_font()
and covers all cases of hyperlinks. 365 -
Fix cloning pivot charts. 361
-
Fix loading and writing files with slicers. Loading would add a few empty slicer xml files to
Content_Types
andworkbook.xml.rels
. 361 -
Align the logic for writing data to empty worksheets and updating/writing to worksheets with data. This removes
update_cell_loop()
and changes howupdate_cell()
behaves. Not only does this remove duplicated code, it also brings great speed improvements (issue 356). 356 -
It is now possible to use special characters in formulas without coding. Previously
&
had to be encoded like&
251
-
Previously deprecated
names.wbWorkbook()
andnames<-.wbWorkbook()
have been removed. 367 -
Conditional style defaults for
create_dxfs_style()
have changed to be more permissive. Previously we shipped a default font, default font size and font color. This has been changed to better reflect a behavior the user expects. 343
-
New argument
startCol
in read to data frame functionswb_to_df()
,wb_read()
andread_xlsx()
. 330 -
New function
wb_colour()
to ease working with color vectors used inopenxlsx2
styles. 292 -
Deprecated
get_cell_style()
andset_cell_style()
in favor of newly introduced wrapper functionswb_get_cell_style()
andwb_set_cell_style()
. 306 -
Improvements to
wb_clone_worksheet()
. Cloning of chartsheets as well as worksheets containing charts, pivot tables, drawings and tables is now possible or tweaked. Slicers are removed from the cloned worksheet. 305 -
Allow writing class
data.table
. 313 -
Provide
na.numbers
for reading functions, that convert numbers toNA
in R output. Handlena.strings
inwrite_xlsx()
. 301 -
Add new option to add sparklines with various style options to worksheets:
wb_add_sparklines()
. Sparklines can be created withcreate_sparklines()
. The manual page contains an example. 280 -
Add new options to data validation. allow type custom, add arguments
errorStyle
,errorTitle
,error
,promptTitle
,prompt
. 271 -
Provide
wb_clone_sheet_style()
. This improves upon the now deprecatedcloneSheetStyle()
that existed as an early draft. 233 -
wb$add_data()
now checks earlier for missingx
argument. 246
-
Worksheets added to a
wbWorkbook
no longer contain default references to thedrawings
andvmlDrawings
directories. Previously, these references were added asrId1
andrId2
even if the worksheet did not contain any drawing (e.g., an image or a chart) or vml drawing (e.g., a comment or a button). In such cases certain third party software, strictly following the references in worksheet orContent_Types
complained about missing files and the import of such files failed completely. 311 -
Implement loading of user defined chartShapes. Previously this was not implemented instead the previous logic assumed that every sheet has a matching drawing. With chartShapes this no longer is true. The number of drawings and the number of worksheets/chartsheets must not match. 323
-
When loading files with charts, they are now imported into the
wbWorkbook
object. Previously they were simply copied. This will allow easier interaction with charts in the future. 304 -
Moving the data validation code from the workbook to the worksheet. Also,
data_validation_list()
is no longer stored indataValidationLst
. It has been moved toextLst
, fixing a bug when saving and adding another data validation list. The code for retrieving the date origin from a workbook has been improved andget_date_origin(wb, origin = TRUE)
now returns the origin as an integer from awbWorkbook
. 299 -
Removed level4 from XML functions. There was only a single use case for a level4 function that has been solved differently. If level4 is needed, this can be solved using a level3 and additional level2 functions. In addition xml_nodes now return nodes for all reachable nestings, therefore
xml_node("<a/><a/>", "a")
will now return a character vector of length two. Forxml_node("<a/><b/>", "a")
only a single character vector is returned. 280 -
Changes to various internal pugixml functions, to improve handling of XML strings. 279
-
Provide internal helper
xml_rm_child()
to remove children of XML strings. 273 -
Fixes a bug in
update_cell()
that slowed down writing on worksheets with data. In addition, this function has been cleaned up and further improved. It is no longer exported, as users only need to usewb_add_data()
orwrite_data()
, each of which callsupdate_cell()
under the hood. 275 276 -
Various (mostly internal) changes to
conditional_formatting
. Createdstyle_mgr
integration fordxf
(cf-styles) and cleaned up internal code. The syntax has changed slightly, see conditional formatting vignette for reference. Addwhitespace
argument toread_xml()
. 268
- Order of arguments in reading functions
wb_to_df()
,wb_read()
andread_xls()
has changed.
-
Data adding functions now ship a
dims
argument that can be used to determine thestartCol
andstartRow
for anyx
object that is added to the worksheet. Works withadd_data()
,add_data_table()
,add_formula()
and their underlyingwrite_
functions as well as with the wrappers. -
Provide optional
na.strings
argument when writing data to sheets. It can be used to add a custom character string when writing numeric data. -
Improve writing
NA
,NaN
, and-Inf
/Inf
.NA
will be converted to#N/A
;NaN
will be converted to#VALUE!
;Inf
will be converted to#NUM!
. The same conversion is not applied when reading from a workbook. 256 -
Many
wbWorkbook
methods now contain default sheet values ofcurrent_sheet()
ornext_sheet()
(e.g.,$add_worksheet(sheet = next_sheet())
,$write_data(sheet = curret_sheet()
). These internal waiver functions allow thewbWorkbook
object to use default expectations for what sheet to interact with. This allows the easier workflow ofwb$add_worksheet()$add_data(x = data.frame())
where$add_worksheet()
knows to add a new worksheet (with a default name), sets that new worksheet to the current worksheet, and then$add_data()
picks up the new sheet and places the data there. 165, 179 -
New functions
wb_add_cell_style()
andwb$add_cell_style()
to simplify the creation of cell styles for cells on the sheet. This provides a fast way to create cell styles for regions on the worksheet. The cells for which the cell format is to be created must already exist on the worksheet. If the cells already contain a cell format, it will be preserved, except for the updated cell format entries, which will always be created. The function is applied to a continuous cell of the worksheet. 230 -
New functions
wb_add_numfmt()
andwb$add_numfmt()
to simplify the creation of number formats for cells on the sheet. This provides a fast way to create number formats for regions on the worksheet. The cells for which the number format is to be created must already exist on the worksheet. If the cells already contain a cell style, it will be preserved, except for the number format, which will always be created. The function is applied to a continuous cell of the worksheet. 229 -
New functions
wb_add_font()
andwb$add_font()
to simplify the creation of fonts for cells on the sheet. This provides a fast way to create fonts for regions on the worksheet. The cells for which the font is to be created must already exist on the worksheet. If the cells already contain a cell style, it will be preserved, except for the font, which will always be created. The function is applied to a continuous cell of the worksheet. 228 -
New functions
wb_add_fill()
andwb$add_fill()
to simplify the creation of fills for cells on the sheet. This provides a fast way to create color filled regions on the worksheet. The cells for which the fill is to be created must already exist on the worksheet. If the cells already contain a cell style, it will be preserved, except for the filled color, which will always be created. The function is applied to a continuous cell of the worksheet and allows to change the color of every n-th column or row. 222 -
New functions
wb_add_border()
andwb$add_border()
to simplify the creation of borders for cells on the sheet. This is especially useful when creating surrounding borders with different border styles for various cells. The cells for which the border is to be created must already exist on the worksheet. If the cells already contain a cell style, it will be preserved, except for the border, which will always be created. The function is applied to a continuous cell of the worksheet and allows to change the horizontal and vertical internal border grid independently. 220 -
Enable reading tables with
wb_to_df()
. Tables are handled similar to defined names. 193 -
Several enhancements have been added for checking and validation worksheet names 165
- When adding a new worksheet via
wbWorkbook$add_worksheet()
the provided name is checked for illegal characters (see note in Breaking changes) wbWorkbook$get_sheet_names()
(wb_get_sheet_names()
wrapper) added. These return both the formatted and original sheet nameswbWorkbook$set_sheet_names()
(wb_set_sheet_names()
) added- these make
names.wbWorkbook()
andnames<-.wbWorkbook()
deprecated wbWorkbook$setSheetName()
deprecated
- these make
clean_worksheet_names()
added to support removing characters that are not allowed in worksheet names
- When adding a new worksheet via
-
Various fixes to enable handling of non unicode R environments 243
-
Fix an issue with broken pageSetup reference causing corrupt excel files 216
-
Fix reading and writing comments from workbooks that already provide comments 209
-
Fix an issue with broken xml in Excels vml files and enable opening xlsm files with
wb$open()
202 -
Instruct parser to import nodes with whitespaces. This fixes a complaint in spreadsheet software. 189
-
Remove reference to
printerSettings.bin
when loading. This binary blob is not included and the reference caused file corruption warnings. 185 -
Fix loading and writing xlsx files with with
workbook$extLst
. Previously if the loaded sheet contains a slicer, a secondextLst
was added which confused spreadsheet software. Now both are combined into a single node. -
Fix writing xlsx file with multiple entries of conditional formatting type databar on any sheet. 174
-
Cell fields cm, ph and vm are now implemented for reading and writing. This is the first step to handle functions that use metadata. 173
-
wbWorkbook
:$open()
no longer overwrites the$path
field to the temporary file 171 -
xl_open()
works (better) on Windows 170
-
When writing to existing workbooks, the default value for
removeCellStyle
is nowFALSE
. Therefore if a cell contains a style, it is attempted to replace the value, but not the style of the cell itself. -
wb_conditional_formatting()
is deprecated in favor ofwb_add_conditional_formatting()
andwbWorkbook$add_conditional_formatting()
.type
must now match exactly one of:"expression"
,"colorScale"
,"dataBar"
,"duplicatedValues"
,"containsText"
,"notContainsText"
,"beginsWith"
,"endsWith"
,"between"
,"topN"
,"bottomN"
-
Assigning a new worksheet with an illegal character now prompts an error 165. See
?clean_worksheet_name
for an easy method of replacing bad characters. -
openxlsx2Coerce()
(which was called onx
objects when adding data to a workbook) has been removed. Users can no longer pass some arbitrary objects and will need to format these objects appropriately or rely onas.data.frame
methods 167 -
xl_open(file = )
is no longer valid and will throw a warning; first argument has been changes tox
to highlight thatxl_open()
can be called on a file path or awbWorkbook
object 171
-
Remove
wb$createFontNode()
which was never used. -
Switch to modern xlsx template, when creating workbooks. Imported workbooks will use the imported template
-
Rewrite
wb$tables
to use a data frame approach. This simplifies the code a bit and makes it easier to implement more upcoming changes 191 -
Update of internal pugixml library
-
The two functions
write_data()
andwrite_datatable()
now use the same internal functionwrite_data_table()
to add data to the sheet. This simplifies the code and ensures that both functions are tested. In the same pull request, the documentation has been updated and thestack=
option, which was not present before, has been removed 175 -
wbWorkbook$validate_sheet()
added as an object methods -
private
wbWorkbook
fieldoriginal_sheet_names
added to track the original names passed to sheets -
private
$get_sheet()
removed in favor of more explicit -
private
wbWorkbook
methods additions:$get_sheet_id_max()
,$get_sheet_index()
for getting ids$get_sheet_name()
for getting a sheet name$set_single_sheet_name()
for setting sheet names$pappend()
general private appending$validate_new_sheet()
for checking new sheet names$append_workbook_field()
forself$workbook[[field]]
$append_sheet_rels()
forself$worksheet_rels[[sheet]]
$get_worksheet()
to replace$ws()
- Added a
NEWS.md
file to track changes to the package. - First public release