Excel Connector for R

Provides comprehensive functionality to read, write and format Excel data.

CRAN_Status_Badge Rdoc Research software impact Build Status codecov

XLConnect is a comprehensive and cross-platform R package for manipulating Microsoft Excel files from within R. XLConnect differs from other related R packages in that it is completely cross-platform and as such runs under Windows, Unix/Linux and Mac (32- and 64-bit). Moreover, it does not require any installation of Microsoft Excel or any other special drivers to be able to read & write Excel files. The only requirement is a recent version of a Java Runtime Environment (JRE).

The package can easily be installed from CRAN via install.packages("XLConnect"). In order to get started have a look at the XLConnect and XLConnect for the Impatient package vignettes, the numerous demos available via demo(package = "XLConnect") or browse through the comprehensive reference manual.

Alternatively, you may install XLConnect directly from our github repository using the excellent devtools package:

install_github("xlconnect", username = "miraisolutions", ref = "master")
# Installs XLConnect 0.2-14
install_github("xlconnect", username = "miraisolutions", ref = "0.2-14")

For more examples see also our wordpress site.

Please send any enhancement requests or bug reports with a simple and self-contained reproducible example to [email protected] or log a corresponding issue on our github repository. For other questions you may also use Stackoverflow.


XLConnect News

0.2-15 2018-04-05

  • Upgraded XLConnectJars dependency to version 0.2-15
  • Rely on Java version check from XLConnectJars which now supports Java 10

0.2-14 2018-01-23

  • Moved to Apache POI 3.17
  • Upgraded XLConnectJars dependency to version 0.2-14
  • Fixed Java version specification in system requirements
  • Checking Java version at runtime according to CRAN requirements
  • Added existsCellStyle to check for existence of cell styles (#71)
  • Added getOrCreateCellStyle combinator (#14, #71)
  • Updates to vignettes

0.2-13 2017-05-10

  • Moved to Apache POI 3.16
  • Performance improvements to setCellStyle for large row/col vectors
  • Added support for setting hyperlinks
  • Fixed issue with missing classes when opening password protected files (#61)

0.2-12 2016-06-23

  • Moved to Apache POI 3.15-beta1
  • Upgraded to Apache Commons Codec 1.10
  • Upgraded XLConnectJars dependency to version 0.2-12
  • Fixed an issue with formula cell evaluation
  • New feature: ability to read password protected files
  • Add additional imports to NAMESPACE as future versions of R will only have the base package attached when performing 'R CMD check'
  • Fixed github issue #52: useCachedValues=TRUE shows formulas instead of values
  • Fixed github issue #49: if region has only 2 columns then can't set first column as rownames (thanks to EldarAgalarov and Deleetdk)
  • Fixed github issue #53: 1904 date system in mixed columns (thanks to waternova)
  • Added ability to skip end rows and columns on import from worksheet (#29; thanks to psychemedia for the feature request)
  • Fixed an issue with writeNamedRegion where named regions for 0-row data.frames have been defined with an extended empty row

0.2-11 2015-03-01

  • Moved to Apache POI 3.12-beta1
  • Fixed issue where formula cells would not be evaluated
  • Simplified advanced example in package vignette and removed the dependency on fImport, forecast and scales

0.2-10 2015-01-14

  • Moved to Apache POI 3.11
  • Fixed issue where milliseconds are getting lost when converting date/time values to string (#28)
  • Fixed problem with non-character column headers
  • Fixed declaration of encoding in vignette
  • Unit test functionality is not part of the public API anymore

0.2-9 2014-08-13

  • Fixing authors declaration in DESCRIPTION file to match CRAN policy
  • Fix java/README to only mention relevant JAR files

0.2-8 2014-08-05

  • Fix documentation with respect to conversion of numbers to dates
  • Fixed a bug that prevented rownames to be specified as a list in writeWorksheet
  • Automatically adjust for 1904 windowing (thanks to Jennifer Rogers!)
  • Moved to Apache POI 3.11-beta1
  • Added inst/COPYRIGHTS file
  • Added java/README providing information on where XLConnect sources can be obtained
  • Split package again into XLConnect and XLConnectJars as requested by CRAN

0.2-7 2014-01-03

  • Fixed date/time conversion issues
  • Set timezone for tests to UTC and align R-JVM timezones

0.2-6 2013-12-31

  • General performance improvements
  • Java exception stack traces are now captured in options("java.stacktrace")
  • Added documentation for with.workbook
  • Added support for milliseconds in date/time values
  • Add tzone attribute after creating POSIXct as some versions of R don't set it
  • Added arguments clearSheets and clearNamedRegions to functions writeWorksheetToFile and writeNamedRegionToFile respectively
  • Fixed an issue with validity checking of named regions in getDefinedNames
  • Moved to Apache POI 3.10-beta2
  • Moved to Joda-Time 2.3
  • XLConnect now "Imports" rJava and utils rather than "Depends" on them
  • XLConnect settings are now applied in .onLoad rather than .onAttach (this allows XLConnect to be imported using "Imports")
  • Added support for Excel tables (Office 2007+); see method readTable
  • Coerce non-numeric columns to character when used as rownames
  • Introduction of a new argument 'readStrategy' in methods readWorksheet, readNamedRegion, readTable to support improved read performance (see the help for more information)
  • readNamedRegionFromFile now exposes all arguments from readNamedRegion via '...'
  • New style action DATATYPE that allows to define a cell style to be used for a given data type. With this there are new methods setCellStyleForType and getCellStyleForType.
  • Fixed issues reported by R CMD check
  • Added a new argument 'simplify' to the methods readWorksheet and readNamedRegion that specifies whether the result should be simplified e.g. in case the resulting data.frame only has one row or one column
  • Fixed an issue where Inf values resulted in a corrupted workbook; Inf values are now handled by setting corresponding cells as error cells with the error code NA (according to Excel's NA() function)
  • Deprecated getReferenceCoordinates in favor of getReferenceCoordinatesForName

0.2-5 2013-03-18

  • Merged XLConnect & XLConnectJars
  • Fixed namespace import issue with rJava
  • Fixed an issue with empty cells in header rows
  • Moved to Joda-Time 2.2
  • Moved to xmlbeans 2.6.0

0.2-4 2013-01-31

  • Ability to read cached cell values. There is a new argument useCachedValues to methods readWorksheet and readNamedRegion. This is especially useful for cells with complex formulas that XLConnect (Apache POI) cannot evaluate and so far would have resulted in NAs being returned. With this new option XLConnect can now read the cached values of cells.
  • There are new arguments keep and drop to readWorksheet and readNamedRegion which allow filtering of columns before the data is imported to R. This can result in much better performance compared to importing the full data to R and then doing the subsetting in R.
  • String conversions when reading in data from Excel (readWorksheet, readNamedRegion) now respect the data format of the cells
  • Support for numeric missing value identifiers (see method setMissingValue)
  • New Vignette "XLConnect for the Impatient" to get people started quickly
  • Added a new function getLastColumn to query the last non-empty column in a worksheet
  • The bounding box detection algorithm for readWorksheet has been adapted to better deal with leading and trailing rows and columns of blank cells. In addition, new arguments autofitRow and autofitCol have been introduced that allow removal of leading and trailing rows and columns even in case boundaries have been specified by the user. This is useful in situations where the data is expected within certain given boundaries but the exact location is not known.
  • Exposed setCellStyle(formula, cellstyle) to be able to set a cellstyle on a whole region for improved performance. Note that there is a slight API change to the setCellStyle method.
  • Fixed an issue with writing empty data.frames
  • Moved to Apache POI 3.9 (see package XLConnectJars)
  • Ensured vignette compatibility with ggplot2 0.9.3

0.2-3 2012-11-05

  • Fixed issue when trying to write empty data.frame with writeNamedRegion and writeNamedRegionToFile
  • Fixed issue in package vignette sample code
  • Added vectorization for rownames argument in readWorksheet, readNamedRegion, writeWorksheet and writeNamedRegion

0.2-2 2012-10-15

  • Fixed issue in package vignette sample code

0.2-1 2012-08-15

  • Fixed issue with overwriting cells of different type
  • Fixed issue with failing unit tests on R-devel (issue with Excel date/time values and increased precision of all.equal for POSIXct)
  • Added new methods for clearing cells from a sheet, a named region or a range (clearSheet, clearNamedRegion, clearRange, clearRangeFromReference)

0.2-0 2012-07-18

  • Moved to Apache POI 3.8
  • Fixed lazy evaluation issues with S4 generics. Generic methods now fully expand the argument list in order to have the arguments immediately evaluated.
  • readWorksheet now returns a named list if more than one worksheet is read. This provides convenient access to the corresponding data by list subsetting.
  • readWorksheet and readNamedRegion now provide a new argument named 'check.names' that controls whether data.frame column names are checked for syntactically valid variable names (see the 'check.names' argument of data.frame).
  • Support for setting the color of worksheet tabs via the setSheetColor method (only for *.xlsx files).
  • Support for freeze panes and split panes.
  • Removing dependency to tikzDevice package
  • Updates to the package vignette. Package vignette code examples can be found in the XLConnect.R script file in the top level library directory or by typing edit(file = system.file("XLConnect.R", package = "XLConnect"))

0.1-9 2012-03-08

  • Vignette examples now run with ggplot2 0.9.0+
  • loadWorkbook and saveWorkbook now expand paths using path.expand
  • readWorksheet provides a new argument 'region' that allows to specify area bounds in an alternative way to startRow, startCol, endRow and endCol.

0.1-8 2012-02-10

  • Moved to Apache POI 3.8-beta5
  • Improved & extended package vignette
  • Introduced xlcEdit for editing data.frames in an Excel editor, e.g. MS Excel.
  • Columns in data.frames that have a type/class with no direct correspondence in Excel (i.e. everything which is not a logical, numeric, character or Date/POSIXt) are converted to character using as.character when writing to Excel rather than the following error being thrown: "Unsupported data type (class) detected!"
  • New arguments colTypes, forceConversion and dateTimeFormat for methods readNamedRegion and readWorksheet. colTypes allows to pre-specify column types (and force them in conjunction with forceConversion) when reading in data. forceConversion forces conversion to less generic types (e.g. a string in Excel to a POSIXt in R). dateTimeFormat is a format specifier used when doing date/time conversions.

0.1-7 2011-10-19

  • Moved to Apache POI 3.8-beta4
  • Performance improvements when writing large xlsx files. This fix was implemented on the Apache POI side.
  • An issue was fixed where the bounding box was not appropriately determined when using readWorksheet. The bounding box is now determined by first determining start and end row. Afterwards, for all rows between the start and the end row the minimum and maximum column is determined.
  • An issue was fixed where the named region was automatically expanded by one row when writing data to a named region without column headers.
  • New workbook data extraction & replacement operators [, [<-, [[, [[<-. These are "syntactic sugar" for the already existing methods readWorksheet, writeWorksheet, readNamedRegion and writeNamedRegion.
  • Support for saving workbooks to a new file ("save as").
  • Support for querying the last (non-empty) row in a worksheet
  • Support for including the row names of a data.frame when writing to a worksheet or a named region. In addition, when reading data from a worksheet or a named region a column can be specified that should be turned into row names.
  • Extended use of setMissingValue. setMissingValue allows to define a set of missing value identifiers that are recognized as missing values when reading in data. The first element of this set is used as missing value identifier when writing data.
  • Improvements to the definition of several generic functions & methods
  • Improvements to the functions writeNamedRegionToFile & writeWorksheetToFile. Also, an issue was fixed with writeNamedRegionToFile where quoted sheet names (e.g. due to a whitespace in the sheet name) would have caused an error.
  • Support for auto-sizing of columns.
  • New functionality to append data to named regions and work- sheets: appendNamedRegion & appendWorksheet.
  • Several internal changes on the Java side: removing Java logging, using java.util.ArrayList instead of java.util.Vector
  • Functionality for dumping and restoring objects to/from Excel files (xlcDump, xlcRestore)
  • Added a package vignette

0.1-6 Non-public release

0.1-5 2011-07-07

  • Support for setting/getting cell formulas. See methods set/getCellFormula.
  • Support for setting/getting the force formula recalculation flag on worksheets. See methods set/getForceFormulaRecalculation.
  • Support for setting auto-filters via the setAutoFilter method.
  • There is a set of new utility functions:
    • aref2idx, idx2aref: Converts Excel area references (such as A5:D14) to row & column based cell references and vice versa.
    • aref: Constructs an Excel area reference from a specification of the top left corner and the dimensionality of an object.
    • getReferenceCoordinates: Queries the coordinates of an Excel name representing an Excel area reference (such as A5:D14)

0.1-4 2011-05-26

  • An issue was fixed where cell styles would not be applied correctly.
  • There is a new style action called NONE: This style action instructs XLConnect to apply no cell styles when writing data. Cell styles are kept as they are. This is useful in a scenario where all styling is predefined in an Excel template which is then only filled with data.
  • There is a new DATA_FORMAT_ONLY style action: This style action instructs XLConnect to only set the data format for a cell but not to apply any other styling but rather keep the existing one. The data format to be applied is determined by the corresponding R data type and can be defined by the user. This style action may be useful in scenarios where Excel templates with pre-defined styling are to be filled with XLConnect.
  • Worksheets can be renamed with the new renameSheet method.
  • The positions (indices) of worksheets can be queried & redefined using the new methods getSheetPos & setSheetPos.
  • Worksheets can be cloned using the new method cloneSheet.
  • There is a set of new utility functions:
    • col2idx, idx2col: Converts Excel column names such as BD to indices and vice versa.
    • cref2idx, idx2cref: Converts cell references to row & column indices and vice versa.
  • Setting a missing value string when writing missing values from R to Excel. By default missing values result in blank/empty cells. This is to have a nice type mapping between R and Excel. In some cases, however, you might want to define a specific missing value string to be used by XLConnect when writing cells representing missing values instead of blank/empty cells. This can be done using the new method setMissingValue.
  • The handling of error cells (cells that result in an error when trying to evaluate them) has been greatly improved. With XLConnect 0.1-3 error cells directly resulted in an exception. With XLConnect 0.1-4 this behavior can be controlled using the onErrorCell method. By default error cells now result in a missing value and the corresponding error cells will be reported via warnings. The behavior may be changed to get an exception with the first occurrence of an error cell.
  • There is two new methods for merging & un-merging cells: mergeCells & unmergeCells.

0.1-3 2011-02-27

  • Initial Release

Reference manual

It appears you don't have a PDF plugin for this browser. You can click here to download the reference manual.


1.0.3 by Martin Studer, 4 days ago

https://mirai-solutions.ch https://github.com/miraisolutions/xlconnect

Report a bug at https://github.com/miraisolutions/xlconnect/issues

Browse source code at https://github.com/cran/XLConnect

Authors: Mirai Solutions GmbH [aut] , Martin Studer [cre] , The Apache Software Foundation [ctb, cph] (Apache POI) , Graph Builder [ctb, cph] (Curvesapi Java library)

Documentation:   PDF Manual  

GPL-3 license

Imports methods, rJava

Suggests RUnit, lattice, ggplot2, zoo

System requirements: Java (>= 8, <= 15)

Imported by MLMOI, rjdqa, table1xls, xlsimple.

Suggested by Deducer, Dominance, JGR, rfordummies.

See at CRAN