Simplifies the creation of Excel .xlsx files by providing a high level interface to writing, styling and editing worksheets. Through the use of 'Rcpp', read/write times are comparable to the 'xlsx' and 'XLConnect' packages with the added benefit of removing the dependency on Java.
This R package simplifies the
.xlsx files by providing
a high level interface to writing, styling and editing
worksheets. Through the use of
read/write times are comparable to the
packages with the added benefit of removing the dependency on
The openxlsx package requires a zip application to be available to R,
such as the one that comes with Rtools, available here. (Windows only)
If the command
'zip' is not recognized as an internal or external command, operable program orbatch file.
or similar. Then;
Install Rtools from: http://cran.r-project.org/bin/windows/Rtools/ and modify the system PATH during installation.
If Rtools is installed, add the Rtools bin directory paths (default installation paths are c:\Rtools\bin and c:\Rtools\gcc-4.6.3\bin) to the system PATH variable.
Current stable version is available on CRAN via
Development version can be installed via GitHub once Rtools (Windows only) has been setup with:
install.packages(c("Rcpp", "devtools"), dependencies=TRUE)require(devtools)install_github("awalker89/openxlsx")
A list is automagically maintained here.
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.
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
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 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
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 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
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 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