NEWS.md
dataValidation(..., type = "list") (#342)loadWorkbook to decide if empty/blank cells should be converted to NA_character_ (the default) or left blank as issaveWorkbook() now succeeds when called after the user has set column widths for a range of columns (e.g. 1:2), saved the workbook, then set column widths for a new range that is inclusive of the previous one (e.g. 1:5) (#493).getWindowSize() and setWindowSize() (466)openxlsx_setOp() now works with named list (#215)loadWorkbook() imports inlineStr. Values remain inlineStr when writing the workbook with saveWorkbook(). Similar read.xlsx and readWorkbook import inlineStr.read.xlsx() no longer changes random seed (#183)write.xlsx() now throws an error if it doesn’t have write permissions (#190)write.xlsx() now again uses the default of overwrite = TRUE for saving files (#249)as.character.formula() exported to warn about potential conflicts with other packages (#312, #315)options() are more consistently set in functions (see: #289)Workbook$show() no longer fails when called in a 0 sheet workbook(#240)read.xlsx() again accepts .xlsm files (#205, #209)makeHyperlinkString() does no longer require a sheet argument (#57, #58)openxlsx creates temporary directories (see #262)writeData() calls force(x) to evaluate the object before options are set (#264)createComment() now correctly handles integers in width and height (#275)setStyles() accepts halign="justify" (#305)write.xlsx() now successfully passes withFilter (#151)buildWorkbook() to generate a Workbook object from a (named) list or a data.frame (#192, #187)
write.xlsx(x, file) ; wb <- read.xlsx(file) functionality beforewrite.xlsx() is now a wrapper for wb <- buildWorkbook(x); saveWorkbook(x, file)
write.xlsx() >> buildWorkbook() are now held off until passed to writeData(), writeDataTable(), etcrow.names is now deprecated for writeData() and writeDataTable(); please use rowNames insteadread.xlsx() now checks for the file extension .xlsx; previously it would throw an error when the file was .xls or .xlm filesminWidth and maxWidth
write.xlsx() >> buildWorkbook() can now handle colWidths passed as either a single element or a list()
summaryCol and summaryRow arguments in pageSetup().activeSheet allows to set and get the active (displayed) sheet of a workbook.?op.openxlsx)Most of functions in openxlsx now support non-ASCII arguments better. More specifically, we can use non-ASCII strings as names or contents for createNamedRegion() (#103), writeComment(), writeData(), writeDataTable() and writeFormula(). In addition, openxlsx now reads comments and region names that contain non-ASCII strings correctly on Windows. Thanks to @shrektan for the PR #118.
setColWidths() now supports zero-length cols, which is convenient when cols is dynamically provided #128. Thanks to @shrektan for the feature request and the PR.
Fix to pass the tests for link-time optimization type mismatches
Fix to pass the checks of native code (C/C++) based on static code analysis
Grouping columns after setting widths no longer throws an error (#100)
Fix inability to save workbook more than once (#106)
Fix loadWorkbook() sometimes importing incorrect column attributes
Added features for conditionalFormatting to support also ‘contains not’, ‘begins with’ and ‘ends with’
Added return value for saveWorkbook() the default value for returnValue is FALSE (#71)
Added Tests for new parameter of saveWorkbook()
groupColumns(), groupRows(), ungroupColumns(), and ungroupRows() to group/ugroup columns/rows (#32)Add functions to get and set the creator of the xlsx file
add function to set the name of the user who last modified the xlsx file
Fixed NEWS hyperlink
Fixed writing of mixed EST/EDT datetimes
Added description for writeFormula() to use only English function names
Fixed validateSheet for special characters
Added getCellRefs() as function. #7
Added parameter for customizing na.strings
Use zip::zipr() instead of zip::zip().
Keep correct visibility option for loadWorkbook. #12
Add space surrounding “wrapText” #17
Corrected Percentage, Accounting, Comma, Currency class on column level
deleteNamedRegions to delete named region and optionally the worksheet data
set Workbook properties ‘title’, ‘subject’, ‘category’
pageSetup fails when passing in sheet by name
matching sheet names with special characters now works
skipEmptyCols being ignored by read.xlsx.Workbook
zero column data.frames would throw an error.
read.xlsx on files created using apache poi failed to match sheet name to xml file.
deleted table re-appearing after save & load.
newline characters in table names would corrupt file
datetime precision
getNamedRegions returns sheet name and cell references along with the named regions.
borderStyle and borderColour can be vector to specify different values for each side
dataValidation type “list”
dataBar showValue, gradient and border can now be set through conditionalFormatting()
options(“openxlsx.zipflags”) to pass additional flags to zip application e.g. compression level
getTables() and removeTable() to show and remove Excel table objects
set column to ‘hidden’ with setColWidths()
skipEmptyRows & skipEmptyCols was being ignored by read.xlsx
date detection basic_string error
multiple spaces in table column names were not being maintained thus corrupting the xlsx file.
openXL fail silently on relative paths
headerStyle failed when writing a list of length 1 using write.xlsx
detectDate for read.xlsx issues
some Excel column types causing existing styling to be removed
na.strings no longer ignored for read.xlsx.Workbook
partial dollar matches on ‘font’ and ‘fill’ fixed
maintain hidden columns and their custom widths in loadWorkbook()
overwriting cells with borders sometimes removed the border styling
Reduced RAM usage and improved performance
maintain vbaProject, slicers, pivotTables on load
Read and load from URL
Fix date time conversion accuracy issues.
Allow multibyte characters in names and comments.
Remove tolower() over style number formats to allow uppercase cell formatting
Stacking styles fixed.
“between” type for conditional formatting values in some interval.
colWidths parameter added to write.xlsx for auto column widths.
freezePane parameter handling added to write.xlsx.
visible parameter to addWorksheet to hide worksheets.
sheetVisible function to get and assign worksheet visibility state “hidden”/“visible”
pageBreak function to add page breaks to worksheets.
improved performance of read.xlsx and loadWorkbook
writeFormula function added to write cell formulas. Also columns with class “formula” are written as cell formulas similar how column classes determine cell styling
Functionality to write comments and maintain comments with loadWorkbook
check.names argument added read.xlsx to make syntactically valid variable names
loadWorkbook maintains cell indents
namedRegion parameter added to read.xlsx to read a named region.
getNamed regions to return names of named regions in a workbook
getSheetNames to get worksheet names within an xlsx file.
stackable cell styling
getDateOrigin function to return the date origin used internally by the xlsx file to pass to convertToDate
Auto-detection of date cells. Cells that “look” like dates will be converted to dates when reading from file.
read.xlsx.Workbook to read from workbook objects
colIndex, rowIndex added to read.xlsx to only read specified rows and columns
Excel slicers now maintained by loadWorkbook
fill styles extended to support gradientFill
Encoding fixed and multi-byte characters now supported.
read.xlsx now maintains multiple consecutive spaces and newline characters.
convertToDate & convertToDateTime now handle NA values.
multiple selected worksheet issue which preventing adding of new worksheets in Excel.
zoom parameter now limited to [10, 400] and documentation updated.
write.xlsx colnames parameter being assigned to rownames
Handling of NaN and Inf values in writeData
conditionalFormatting type “databar”
asTable parameter to write.xlsx to writing using writeDataTable.
extended numFmt formatting to numeric rounding also added option(“openxlsx.numFmt” = …) for default number formatting of numeric columns
additional numFmt “comma” to format numerics with “,” thousands separator
tableName parameter to writeDataTable to assign the table a name
headerStyle parameter to writeDataTable for additional column names styling
textRotation parameter to createStyle to rotate cell text
functions addFilter & removeFilter to add filters to columns
Headers & footers extended, can now be set with addWorksheet and setHeaderFooter. setHeader & setFooter deprecated.
“fitToWidth” and “fitToHeight” logicals in pageSetup.
“zoom” parameter in addWorksheet to set worksheet zoom level.
“withFilter”” parameter to writeDataTable and writeData to remove table filters
keepNa parameter to writeDataTable and writeData to write NA values as #N/A
auto column widths can now be set with width = “auto”
Fix reading in of apostrophes
Styling blank cells no longer corrupts workbooks
read.xlsx now correctly reads sharedStrings with inline styling
sharedStrings now exact matches true/false to determine logical values from workbooks.
fomulas in column caused openxlsx to crash. This has been fixed.
writeData now style based on column class the same as writeDataTable
Vignette “Formatting” for examples focused on formatting
Customizable date formatting with createStyle and also through option(“openxlsx.dateFormat” = …)
Customizable POSIX formatting with createStyle and also through option(“openxlsx.datetimeFormat” = …)
Generalised conditionalFormat function to complex expressions and color scales.
writeData border type “all” to draw all borders and maintain column styling.
Deprecated “sheets” and replaced with “names” function
column class “scientific” to automatically style as scientific numbers
writeData now handles additional object classes: coxph, cox.zph, summary.coxph1 from Survival package
Invalid XML characters in hyperlinks now replaced.
Encoding issues when writing data read in with read.xlsx
scientific notation resulting in corrupt workbooks fix
Multiple saves of Workbooks containing conditional formatting were corrupt.
Latin1 characters now write correctly.
logicals written as 0/1 instead of TRUE/FALSE
write.xlsx function to write data directly to file via the writeData function with basic cell styling.
writeDataTable now styles columns of class ‘Date’, ‘POSIXct’, ‘POSIXt’, ‘currency’, ‘accounting’, ‘percentage’ as Excel formats Date, Date, Date, Currency, Accounting, Percentage respectively.
Data of class ‘Date’, ‘POSIXct’, ‘POSIXt’, ‘currency’, ‘accounting’ are converted to integers upon writing (as opposed to characters).
writeDataTable converts columns of class ‘hyperlink’ to hyperlinks.
logicals are converted to Excel booleans
hyperlinks in loaded workbooks are now maintained
borderStyle argument to createStyle to modify border line type.
borderStyle argument to writeData to modify border line type.
“worksheetOrder” function to shuffle order of worksheets when writing to file
openXL function to open an excel file or Workbook object
conversion of numeric data to integer in read.xlsx fixed.
readWorkbook/read.xlsx should work now. Empty values are now padded with NA. Many other bugs fixed.
borders on single row and/or column data.frames now work.
readWorkbook/read.xlsx check for TRUE/FALSE values is now case-insensitive.
sheet names containing invalid xml characters (&, <, >, ’, “) now work when referencing by name and will not result in a corrupt workbook.
sheet names containing non-local characters can now be referenced by name.
Invalid factor level when missing values in writeData
saveWorkbook now accepts relative paths.
Non-local character encoding issues.
errors in vignette examples.
numbers with > 8 digits were rounded in writeData