Changelog
Source:NEWS.md
openxlsx2 (development version)
New features
-
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)
Fixes
- 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
Breaking changes
-
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
openxlsx2 1.11
CRAN release: 2024-11-14
New features
- Add
hide_no_data_items
option inwb_add_slicer()
. 1169
Fixes
- 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
openxlsx2 1.10
CRAN release: 2024-10-17
New features
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. 1111wb_to_df()
gained a new argumentshow_hyperlinks
which returns the target or location of a hyperlink, instead of the links description. 1136A 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. 1137Add
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()
. 1139wb_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
Fixes
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()
. 1131Fixed 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. 1133Fix 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. 1147Improve 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
Internal changes
- 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
openxlsx2 1.9
CRAN release: 2024-09-08
New features
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
. 1074Experimental 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. 1091It is possible to read cells containing formulas as formula. 1103
Fixes
If a font unknown to
openxlsx2
is usedwb_set_col_widths()
defaults to using the workbooks default font. 1080Previously, if only
cols
androws
were passed towb_dims()
and row1
was selected, incorrect results were returned. This has been fixed. 1094wb_dims()
no longer ignoresabove
,below
,left
,right
iffrom_dims
is not supplied. 1104wb_to_df()
withskip_hidden_rows = TRUE
works now if a file path is passed. 1122
openxlsx2 1.8
CRAN release: 2024-07-07
Maintenance
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).
New features
wb_to_df()
now has acheck_names
argument. 1050The set of conditional formatting icon sets now includes x14 icons. This commit also fixed adding conditional formatting to worksheets with pivot tables. 1053
Fixes
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, 1054write_xlsx()
now usessheet
. Previously it required the undocumentedsheet_name
. 1057Fixed 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
openxlsx2 1.7
CRAN release: 2024-06-02
New features
Add function to remove conditional formatting from worksheet
wb_remove_conditional_formatting()
1011Silence a warning triggered by a folder called
"[trash]"
. 1012Initial support for pivot table timelines. 1016
Add
wb_add_timeline()
and extendwb_add_slicer()
. 1017
Fixes
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. 1016Fixed 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
openxlsx2 1.6
CRAN release: 2024-05-01
New features
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)
Fixes
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).
openxlsx2 1.5
CRAN release: 2024-03-26
(This was updated post release.)
New features
It’s now possible to pass array formula vectors to
wb_add_formula()
. 958wb_add_data_table()
gained a newtotal_row
argument. This allows to add a total row to spreadsheets including text and spreadsheet formulas. 959wb_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.
openxlsx2 1.4
CRAN release: 2024-02-15
Fixes
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. 895Restore
first_active_row
/first_active_col
andfirst_col
/first_row
functionality inwrite_xlsx()
. 903Further attempts to fix pivot table sorting. 912 In addition improve handling of non distinct names in
wb_data()
and addcreate_pivottablestyle()
. 914Document 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. 935Hyperlinks 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).
Breaking changes
- 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
wb_workbook(theme = 'Office 2013 - 2022 Theme')
orwb_set_base_font(font_name = "Calibri")
. - This includes switching to the new default font
openxlsx2 1.3
CRAN release: 2024-01-12
Documentation improvement
- 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
-
New features
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
Fixes
wb_add_ignore_error()
now returns awbWorkbook
. 865Deactivate 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. 876Column 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. 879wb_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
openxlsx2 1.2
CRAN release: 2023-11-29
New features
- 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
Fixes
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
openxlsx2 1.1
CRAN release: 2023-10-23
New features
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. 622Enable 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. 810It is no longer needed to manually create columns for
wb_group_cols()
. 781It 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
openxlsx2 1.0
CRAN release: 2023-08-25
Breaking changes
-
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)
Deprecated functions
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)
New features
-
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. 252wb_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. 733wb_add_comment(comment = "x")
no longer errors when a comment as a character vector no longer fails (758, @olivroy)
Fixes
-
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
Internal changes
-
wb_get_active_sheet()
,wb_set_active_sheet()
,wb_get_selected()
andwb_set_selected()
,wb_get_named_regions()
are now wrapper functions. 735
openxlsx2 0.8
CRAN release: 2023-08-07
API Change
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
Breaking changes
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
make
get_cell_refs()
,get_date_origin()
,guess_col_type()
, andwrite_file()
,dataframe_to_dims()
,dims_to_dataframe()
,wb_get_sheet_name()
internal functionsmake classes
styles_mgr()
,wbSheetData
,wbWorksheet
,wbComment
,wbHyperlink
internal
New features
-
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.
Fixes
- Improve
show_formula
. Previously it was called to early in the function and skipped a few cases. 715
Refactoring
Cleanup / revisit documentation and vignettes (682, @olivroy)
The function index has been improved. (717, @olivroy)
openxlsx2 0.7.1
CRAN release: 2023-06-30
New features
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()
. 636Improve 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. 639Added 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. 628Provide 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
Fixes
On load
app.xml
is now assigned towb$app
. Previously it was loaded but not assigned. 629Previously 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. 631Fix a bug in
wb_load()
that was modifying the cell range of conditional formatting. 647
Breaking changes
Order of arguments in
wb_add_conditional_formatting()
changed, because previously overlookeddims
argument was added. 642New argument
gradientFill
was added tocreate_dxfs_style()
. 651Special characters are now escaped in conditional formatting. Hence, previously manually escaped conditional formatting needs updates. 666
openxlsx2 0.7
CRAN release: 2023-05-26
New features
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. 593Allow using custom data table styles. This fixes a few minor style inconsistencies. 594
Allow reading and writing
hms
columns. 601Import
tableStyles
withwb_load()
and improvedxf
style creation. 603Add new wrapper to ignore worksheet errors
wb_add_ignore_error()
. 617Add new wrapper to update table references
wb_update_table()
. 606
Fixes
Improve handling of non standard
OutDec
options. 611openxlsx2
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. 615We now return a few additional xml arguments from worksheets. 617
openxlsx2 0.6.1
CRAN release: 2023-04-21
New features
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"))
. 575Add
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()
. 578Add
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. 579When writing
tibble
useto.data.frame()
just like in thedata.table
case. 582Add cleanup internal comment code in
write_comment()
. This should not impact the workbook wrapper code inwb_add_comment()
. 586Added chain functions for
wb_to_df()
andwb_load()
. 587
openxlsx2 0.6
CRAN release: 2023-04-02
New features
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
Fixes
na.strings = NULL
is no longer ignored inwrite_xlsx()
552Explicit type conversion to date and datetime is finally available. 551
Breaking changes
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 functions -
file
fromxl_open()
-
definedName
fromwb_to_df()
interacting functions
-
- deprecating function
-
get_named_regions()
forwb_get_named_regions()
-
- remove deprecated functions
openxlsx2 0.5.1
CRAN release: 2023-02-26
New features
Allow hierarchical grouping.
wb_group_cols
/wb_group_rows
now accept nested lists as grouping variable. 537It is now possible to add form control types
Checkbox
,Radio
andDrop
to a workbook usingwb_add_form_control()
. 533Improve
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. 541Speedup cell initialization. This is used in wb_style functions like
wb_add_numfmt()
. The previous loop was replaced with a faster implementation. 545Improve date detection in
wb_to_df()
. This improves date and posix detection with custom date formats. 547na_strings()
is now used as the explicit default value forna.strings
parameters in exported workbook functions. 473waiver functions (i.e.,
next_worksheet()
,current_worksheet()
, andna_strings()
) are now exports 474
Fixes
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
openxlsx2 0.5
CRAN release: 2023-01-29
New features
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()
. 500Add
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. 499Initial 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.
Fixes
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. 496Improve 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
Breaking changes
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
openxlsx2 0.4.1
CRAN release: 2022-12-18
New features
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. 461Tweaks 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. 462Add
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. 475Add
wb_add_chartsheet()
for chart sheet support. Along with internal cleanup around chart sheet code. 466
Fixes
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. 465Previously, 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
openxlsx2 0.4
CRAN release: 2022-12-05
New features
Provide
rvg
support viawb_add_drawing()
. This allows integratingrvg
plots into xlsx files. 449Improve 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. 386Add … as argument to
read_xlsx()
andwb_read()
. 381Allow reading files with xml namespace created by third party software. 405
Fixes
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. 419Fixed saving files with
<sheetPr/>
tag. Previously this was wrapped in a secondsheetPr
node. This issue occurs with xlsm files only. 417Fixed a case where embedded files were assigned incorrectly in worksheet relationships. This caused corrupted output. 403
Breaking changes
Remove
merge_
functions for styles. 450Previously 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. 378Remove never used
all.equal.wbWorkbook()
. The idea was nice, but it never developed into something useful.Remove never used
wb_chart_sheet()
function. 399
Internal changes
- Provide
set_sheetview()
in sheets. Can be used to provide awbWorkbook
function and wrapper in the future. 399
openxlsx2 0.3.1
CRAN release: 2022-10-31
New features
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. 365Reading 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
Internal changes
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
Fixes
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. 350Improve
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_
. 375Fix 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
. 363Adding cell styles has been streamlined to increase consistency. This includes all style functions like
wb_add_font()
and covers all cases of hyperlinks. 365Fix 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
. 361Align 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). 356It is now possible to use special characters in formulas without coding. Previously
&
had to be encoded like&
251
Breaking changes
Previously deprecated
names.wbWorkbook()
andnames<-.wbWorkbook()
have been removed. 367Conditional 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
openxlsx2 0.3
CRAN release: 2022-09-30
New features
New argument
startCol
in read to data frame functionswb_to_df()
,wb_read()
andread_xlsx()
. 330New function
wb_colour()
to ease working with color vectors used inopenxlsx2
styles. 292Deprecated
get_cell_style()
andset_cell_style()
in favor of newly introduced wrapper functionswb_get_cell_style()
andwb_set_cell_style()
. 306Improvements 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. 305Allow writing class
data.table
. 313Provide
na.numbers
for reading functions, that convert numbers toNA
in R output. Handlena.strings
inwrite_xlsx()
. 301Add 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. 280Add new options to data validation. allow type custom, add arguments
errorStyle
,errorTitle
,error
,promptTitle
,prompt
. 271Provide
wb_clone_sheet_style()
. This improves upon the now deprecatedcloneSheetStyle()
that existed as an early draft. 233wb$add_data()
now checks earlier for missingx
argument. 246
Internal changes
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. 311Implement 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. 304Moving 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
. 299Removed 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. 280Changes to various internal pugixml functions, to improve handling of XML strings. 279
Provide internal helper
xml_rm_child()
to remove children of XML strings. 273Fixes 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 276Various (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
Breaking changes
- Order of arguments in reading functions
wb_to_df()
,wb_read()
andread_xls()
has changed.
openxlsx2 0.2.1
New features
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. 256Many
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, 179New 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. 230New 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. 229New 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. 228New 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. 222New 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. 220Enable 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 names -
wbWorkbook$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
Fixes
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()
202Instruct 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. 185Fix 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
Breaking changes
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 167xl_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
Internal changes
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 191Update 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 175wbWorkbook$validate_sheet()
added as an object methodsprivate
wbWorkbook
fieldoriginal_sheet_names
added to track the original names passed to sheetsprivate
$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()
-