News

openxlsx 4.1.0

NEW FEATURES

• deleteNamedRegions to delete named region and optionally the worksheet data

• set Workbook properties 'title', 'subject', 'category'

BUG FIXES

• 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

openxlsx 4.0.17

NEW FEATURES

• 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()

BUG FIXES

• 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

openxlsx 4.0.0

NEW FEATURES

• Reduced RAM usage and improved performance

• maintain vbaProject, slicers, pivotTables on load

• Read and load from URL

BUG FIXES

• 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.

openxlsx 3.0.2

NEW FEATURES

• "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.

BUG FIXES

• keepNA paramter added to write.xlsx. Passed to writeData/writeDataTable

openxlsx 3.0.1

NEW FEATURES

• improved performance of read.xlsx and loadWorkbook

• writeFormula funciton 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.

BUG FIXES

• convertToDateTime now handles NA values

• read.xlsx rows bug fixed where non-consecutive cells were skipped.

• convertToDate & convertToDateTime now handle NA values.

• out of bounds worksheet fixed for libre office xlsx files.

• loadWorkbook now maintains chartSheets

openxlsx 2.4.0

NEW FEATURES

• 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

BUG FIXES

• 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 whioch 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

openxlsx 2.1.3

NEW FEATURES

• 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"

VIGNETTE

• section on write.xlsx in Introductory vignette

BUG FIXES

• 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.

openxlsx 2.0.15

NEW FEATURES

• writeData now style based on column class the same as writeDataTable

• Vignette "Formatting" for examples focussed 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

BUG FIXES

• 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

openxlsx 2.0.1

NEW FEATURES

• 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

BUG FIXES

• 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 charcters (&, <, >, ', ") 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

Reference manual