| Title: | Excel Connector for R |
|---|---|
| Description: | Provides comprehensive functionality to read, write and format Excel data. |
| Authors: | Mirai Solutions GmbH [aut], Martin Studer [cre], The Apache Software Foundation [ctb, cph] (Apache POI), Graph Builder [ctb, cph] (Curvesapi Java library), Brett Woolridge [ctb, cph] (SparseBitSet Java library) |
| Maintainer: | Martin Studer <[email protected]> |
| License: | GPL-3 |
| Version: | 1.1.0 |
| Built: | 2024-11-21 06:50:34 UTC |
| Source: | CRAN |
Provides comprehensive functionality to read, write and format Excel data.
For an overview over the package please refer to the available demos:demo(package = "XLConnect")
Mirai Solutions GmbH, [email protected]
Mirai Solutions GmbH: https://mirai-solutions.ch
XLConnect on GitHub: https://github.com/miraisolutions/xlconnect
Mirai Solutions on GitHub: https://github.com/miraisolutions
Apache POI: https://poi.apache.org
## Not run: # Load workbook; create if not existing wb <- loadWorkbook("XLConnect.xlsx", create = TRUE) # Create a worksheet createSheet(wb, name = "mtcars") # Create a name reference createName(wb, name = "mtcars", formula = "mtcars!$C$5") # Write built-in data.frame 'mtcars' to the specified named region writeNamedRegion(wb, mtcars, name = "mtcars") # Save workbook saveWorkbook(wb) # clean up file.remove("XLConnect.xlsx") ## End(Not run)## Not run: # Load workbook; create if not existing wb <- loadWorkbook("XLConnect.xlsx", create = TRUE) # Create a worksheet createSheet(wb, name = "mtcars") # Create a name reference createName(wb, name = "mtcars", formula = "mtcars!$C$5") # Write built-in data.frame 'mtcars' to the specified named region writeNamedRegion(wb, mtcars, name = "mtcars") # Save workbook saveWorkbook(wb) # clean up file.remove("XLConnect.xlsx") ## End(Not run)
Allows to execute workbook methods in workbook-object$method(...) form.
x |
x$method(...) (where x is a workbook-object) is equivalent to method(x, ...)
The workbook $-operator allows to call workbook-methods in workbook-object$method(...) form.
This form might be considered more convenient or readable for programmers coming from other object-oriented languages such as Java, C#, ...
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("dollar.xlsx", create = TRUE) # Create a worksheet called 'CO2' wb$createSheet(name = "CO2") # Write built-in data set 'CO2' to the worksheet created above wb$writeWorksheet(CO2, sheet = "CO2", startRow = 4, startCol = 2) # Save workbook wb$saveWorkbook() # clean up file.remove("dollar.xlsx") ## End(Not run)## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("dollar.xlsx", create = TRUE) # Create a worksheet called 'CO2' wb$createSheet(name = "CO2") # Write built-in data set 'CO2' to the worksheet created above wb$writeWorksheet(CO2, sheet = "CO2", startRow = 4, startCol = 2) # Save workbook wb$saveWorkbook() # clean up file.remove("dollar.xlsx") ## End(Not run)
Adds an image to a worksheet using a named region.
## S4 method for signature 'workbook' addImage(object, filename, name, originalSize, worksheetScope)## S4 method for signature 'workbook' addImage(object, filename, name, originalSize, worksheetScope)
object |
The |
filename |
Name of the image file. Supported are images of the following formats: JPG/JPEG, PNG, WMF, EMF, BMP, PICT. |
name |
Name of the named region that the image is set to |
originalSize |
If |
worksheetScope |
Optional - the name of the worksheet in which the name is scoped; useful if different sheets have scoped regions with the same name. |
There is an known issue in Apache POI with adding images to xls workbooks. The result of adding
images to workbooks that already contain shapes or images may be that previous images are
removed or that existing images are replaced with newly added ones. It is therefore advised that
you use the addImage functionality only with workbooks that have no existing shapes or images.
Note that this only holds for xls workbooks (Excel 97-2003) and not for xlsx (Excel 2007+). There should
be no issues with xlsx workbooks.
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
## Not run: ## Write an R plot to a specified named region ## This example makes use of the 'Tonga Trench Earthquakes' example # Load workbook (create if not existing) wb <- loadWorkbook("earthquake.xlsx", create = TRUE) # Create a sheet named 'earthquake' createSheet(wb, name = "earthquake") # Create a named region called 'earthquake' referring to the sheet # called 'earthquake' createName(wb, name = "earthquake", formula = "earthquake!$B$2") # Create R plot to a png device require(lattice) png(filename = "earthquake.png", width = 800, height = 600) devAskNewPage(ask = FALSE) Depth <- equal.count(quakes$depth, number=8, overlap=.1) xyplot(lat ~ long | Depth, data = quakes) update(trellis.last.object(), strip = strip.custom(strip.names = TRUE, strip.levels = TRUE), par.strip.text = list(cex = 0.75), aspect = "iso") dev.off() # Write image to the named region created above using the image's # original size; i.e. the image's top left corner will match the # specified cell's top left corner addImage(wb, filename = "earthquake.png", name = "earthquake", originalSize = TRUE) # Save workbook (this actually writes the file to disk) saveWorkbook(wb) # clean up file.remove("earthquake.xlsx") file.remove("earthquake.png") ## End(Not run)## Not run: ## Write an R plot to a specified named region ## This example makes use of the 'Tonga Trench Earthquakes' example # Load workbook (create if not existing) wb <- loadWorkbook("earthquake.xlsx", create = TRUE) # Create a sheet named 'earthquake' createSheet(wb, name = "earthquake") # Create a named region called 'earthquake' referring to the sheet # called 'earthquake' createName(wb, name = "earthquake", formula = "earthquake!$B$2") # Create R plot to a png device require(lattice) png(filename = "earthquake.png", width = 800, height = 600) devAskNewPage(ask = FALSE) Depth <- equal.count(quakes$depth, number=8, overlap=.1) xyplot(lat ~ long | Depth, data = quakes) update(trellis.last.object(), strip = strip.custom(strip.names = TRUE, strip.levels = TRUE), par.strip.text = list(cex = 0.75), aspect = "iso") dev.off() # Write image to the named region created above using the image's # original size; i.e. the image's top left corner will match the # specified cell's top left corner addImage(wb, filename = "earthquake.png", name = "earthquake", originalSize = TRUE) # Save workbook (this actually writes the file to disk) saveWorkbook(wb) # clean up file.remove("earthquake.xlsx") file.remove("earthquake.png") ## End(Not run)
Appends data to an existing named region.
## S4 method for signature 'workbook,ANY' appendNamedRegion(object,data,name,header,overwriteFormulaCells,rownames,worksheetScope)## S4 method for signature 'workbook,ANY' appendNamedRegion(object,data,name,header,overwriteFormulaCells,rownames,worksheetScope)
object |
The |
data |
Data to write |
name |
Name of the (existing) named region to which to append the |
header |
Specifies if the column names should be written. The default is |
overwriteFormulaCells |
Specifies if existing formula cells in the |
rownames |
Name ( |
worksheetScope |
Optional character vector with worksheet name(s) to target a |
Appends data to the existing named region specified by name. The data is
appended at the bottom of the named region. See writeNamedRegion
for further information on writing named regions.
Named regions are automatically redefined to the area occupied by the
previous and the newly appended data. This guarantees that the complete set
of data can be re-read using readNamedRegion.
Note however, that no checks are performed to see whether the appended data has the
same shape/structure as the previous data.
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
workbook,
writeNamedRegion,
readNamedRegion,
writeWorksheet,
appendWorksheet,
readWorksheet
## Not run: # mtcars xlsx file from demoFiles subfolder of package XLConnect demoExcelFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(demoExcelFile) # Append mtcars data set to named region named 'mtcars' appendNamedRegion(wb, mtcars, name = "mtcars") ## End(Not run)## Not run: # mtcars xlsx file from demoFiles subfolder of package XLConnect demoExcelFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(demoExcelFile) # Append mtcars data set to named region named 'mtcars' appendNamedRegion(wb, mtcars, name = "mtcars") ## End(Not run)
Appends data to worksheets of a workbook.
## S4 method for signature 'workbook,ANY,character' appendWorksheet(object,data,sheet,header,rownames) ## S4 method for signature 'workbook,ANY,numeric' appendWorksheet(object,data,sheet,header,rownames)## S4 method for signature 'workbook,ANY,character' appendWorksheet(object,data,sheet,header,rownames) ## S4 method for signature 'workbook,ANY,numeric' appendWorksheet(object,data,sheet,header,rownames)
object |
The |
data |
Data to append |
sheet |
The name or index of the sheet to append the |
header |
Specifies if the column names should be written. The default is |
rownames |
Name ( |
Appends data to the worksheet specified by sheet. Data will be appended at the
bottom and left most column containing some data. If more complex "appending schemes" are required
you may make direct use of writeWorksheet.
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
workbook,
writeWorksheet,
readWorksheet,
writeNamedRegion,
appendNamedRegion,
readNamedRegion
## Not run: # mtcars xlsx file from demoFiles subfolder of package XLConnect demoExcelFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(demoExcelFile) # Append mtcars data set to worksheet named 'mtcars' appendWorksheet(wb, mtcars, sheet = "mtcars") ## End(Not run)## Not run: # mtcars xlsx file from demoFiles subfolder of package XLConnect demoExcelFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(demoExcelFile) # Append mtcars data set to worksheet named 'mtcars' appendWorksheet(wb, mtcars, sheet = "mtcars") ## End(Not run)
Constructs an Excel area reference
aref(topLeft, dimension)aref(topLeft, dimension)
topLeft |
Top left corner. Either a |
dimension |
Dimensions ( |
Returns the area reference (character) for the specified top left cell and dimension.
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
aref2idx, idx2aref,
idx2cref, col2idx, idx2col
## Not run: aref("A1", dim(mtcars)) aref(c(1, 1), dim(mtcars)) ## End(Not run)## Not run: aref("A1", dim(mtcars)) aref(c(1, 1), dim(mtcars)) ## End(Not run)
Converts Excel cell references to row and column based cell references
aref2idx(x)aref2idx(x)
x |
|
Returns a numeric matrix with four columns and as many rows as cell references that have been provided. The first two columns represent the coordinates of the top left corner (row, column) and the third and fourth columns represent the bottom right corner of the referenced area.
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
idx2aref, aref, cref2idx,
idx2cref, col2idx, idx2col
## Not run: aref2idx(c("A1:B6", "B6:C17")) ## End(Not run)## Not run: aref2idx(c("A1:B6", "B6:C17")) ## End(Not run)
This class represents a cell style in a Microsoft Excel workbook. S4 objects of this class
and corresponding methods are used to manipulate cell styles. This includes setting data formats,
borders, background- and foreground-colors, etc.
Cell styles are created by calling the createCellStyle method on a
workbook object.
jobj:Object of class jobjRef (see package rJava) which represents a Java object reference that is used
in the back-end to manipulate the underlying Excel cell style instance.
XLConnect generally makes use of custom (named) cell styles. This allows users to more easily manage cell styles via Excel's cell style menu. For example, assuming you were using a specific custom cell style for your data table headers, you can change the header styling with a few clicks in Excel's cell style menu across all tables.
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
Apply, create, or remove a cell style:
https://support.microsoft.com/en-us/office/apply-create-or-remove-a-cell-style-472213bf-66bd-40c8-815c-594f0f90cd22?ocmsassetid=hp001216732&correlationid=5691ac73-b7a2-40c3-99aa-a06e806bb566&ui=en-us&rs=en-us&ad=us
workbook, createCellStyle, setStyleAction,
setCellStyle
## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("cellstyles.xlsx", create = TRUE) # We don't set a specific style action in this demo, so the # default 'XLConnect' will be used (XLC$"STYLE_ACTION.XLCONNECT") # Create a sheet named 'mtcars' createSheet(wb, name = "mtcars") # Create a named region called 'mtcars' referring to the sheet # called 'mtcars' createName(wb, name = "mtcars", formula = "mtcars!$C$4") # Write built-in data set 'mtcars' to the above defined named region. # This will use the default style action 'XLConnect'. writeNamedRegion(wb, mtcars, name = "mtcars") # Now let's color all weight cells of cars with a weight > 3.5 in red # (mtcars$wt > 3.5) # First, create a corresponding (named) cell style heavyCar <- createCellStyle(wb, name = "HeavyCar") # Specify the cell style to use a solid foreground color setFillPattern(heavyCar, fill = XLC$"FILL.SOLID_FOREGROUND") # Specify the foreground color to be used setFillForegroundColor(heavyCar, color = XLC$"COLOR.RED") # Which cars have a weight > 3.5 ? rowIndex <- which(mtcars$wt > 3.5) # NOTE: The mtcars data.frame has been written offset with top # left cell C4 - and we have also written a header row! # So, let's take that into account appropriately. Obviously, # the two steps could be combined directly into one ... rowIndex <- rowIndex + 4 # The same holds for the column index colIndex <- which(names(mtcars) == "wt") + 2 # Set the 'HeavyCar' cell style for the corresponding cells. # Note: the row and col arguments are vectorized! setCellStyle(wb, sheet = "mtcars", row = rowIndex, col = colIndex, cellstyle = heavyCar) # Save workbook (this actually writes the file to disk) saveWorkbook(wb) # clean up file.remove("cellstyles.xlsx") ## End(Not run)## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("cellstyles.xlsx", create = TRUE) # We don't set a specific style action in this demo, so the # default 'XLConnect' will be used (XLC$"STYLE_ACTION.XLCONNECT") # Create a sheet named 'mtcars' createSheet(wb, name = "mtcars") # Create a named region called 'mtcars' referring to the sheet # called 'mtcars' createName(wb, name = "mtcars", formula = "mtcars!$C$4") # Write built-in data set 'mtcars' to the above defined named region. # This will use the default style action 'XLConnect'. writeNamedRegion(wb, mtcars, name = "mtcars") # Now let's color all weight cells of cars with a weight > 3.5 in red # (mtcars$wt > 3.5) # First, create a corresponding (named) cell style heavyCar <- createCellStyle(wb, name = "HeavyCar") # Specify the cell style to use a solid foreground color setFillPattern(heavyCar, fill = XLC$"FILL.SOLID_FOREGROUND") # Specify the foreground color to be used setFillForegroundColor(heavyCar, color = XLC$"COLOR.RED") # Which cars have a weight > 3.5 ? rowIndex <- which(mtcars$wt > 3.5) # NOTE: The mtcars data.frame has been written offset with top # left cell C4 - and we have also written a header row! # So, let's take that into account appropriately. Obviously, # the two steps could be combined directly into one ... rowIndex <- rowIndex + 4 # The same holds for the column index colIndex <- which(names(mtcars) == "wt") + 2 # Set the 'HeavyCar' cell style for the corresponding cells. # Note: the row and col arguments are vectorized! setCellStyle(wb, sheet = "mtcars", row = rowIndex, col = colIndex, cellstyle = heavyCar) # Save workbook (this actually writes the file to disk) saveWorkbook(wb) # clean up file.remove("cellstyles.xlsx") ## End(Not run)
Clears named regions in a workbook.
## S4 method for signature 'workbook,character' clearNamedRegion(object, name, worksheetScope)## S4 method for signature 'workbook,character' clearNamedRegion(object, name, worksheetScope)
object |
The |
name |
The name of the named region to clear |
worksheetScope |
Optional - the name of the worksheet in which the region is scoped; useful if different sheets have scoped regions with the same name. |
Clearing a named region/range means to clear all the cells associated with that named region. Clearing named regions can be useful if (named) data sets in a worksheet need to be replaced, i.e. data is first read, modified in R and finally written back to the the same named region. Without clearing the named region first, (parts of) the original data may still be visible if they occupied a larger range in the worksheet.
If worksheetScope is unspecified, the first matching name found anywhere in the workbook
will be cleared. Otherwise, only a name specifically scoped to the worksheet may be cleared.
To only clear a name in global scope, pass "" as the value.
Nicola Lambiase
Mirai Solutions GmbH https://mirai-solutions.ch
workbook, clearSheet,
clearRange,
clearRangeFromReference,
clearSheet
## Not run: # mtcars xlsx file from demoFiles subfolder of # package XLConnect demoExcelFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(demoExcelFile) # Read named region 'mtcars' data <- readNamedRegion(wb, name = "mtcars", header = TRUE) # Only consider cars with a weight >= 5 data <- data[data$wt >= 5, ] # Clear original named region clearNamedRegion(wb, name = "mtcars") # Write subsetted data back # Note: this is covering a smaller area now - # writeNamedRegion automatically redefines the named region # to the size/area of the data writeNamedRegion(wb, data = data, name = "mtcars", header = TRUE) ## End(Not run)## Not run: # mtcars xlsx file from demoFiles subfolder of # package XLConnect demoExcelFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(demoExcelFile) # Read named region 'mtcars' data <- readNamedRegion(wb, name = "mtcars", header = TRUE) # Only consider cars with a weight >= 5 data <- data[data$wt >= 5, ] # Clear original named region clearNamedRegion(wb, name = "mtcars") # Write subsetted data back # Note: this is covering a smaller area now - # writeNamedRegion automatically redefines the named region # to the size/area of the data writeNamedRegion(wb, data = data, name = "mtcars", header = TRUE) ## End(Not run)
Clears cell ranges in a workbook.
## S4 method for signature 'workbook,numeric' clearRange(object, sheet, coords) ## S4 method for signature 'workbook,character' clearRange(object, sheet, coords)## S4 method for signature 'workbook,numeric' clearRange(object, sheet, coords) ## S4 method for signature 'workbook,character' clearRange(object, sheet, coords)
object |
The |
sheet |
The name or index of the worksheet in which to clear cell ranges |
coords |
Numeric vector of length 4 or numeric matrix with 4 columns
where the elements of the vector or rows in the matrix refer to
the coordinates of the top-left and bottom-right corners of the ranges
to clear. I.e. a vector or each row specifies the coordinates
{top row, left column, bottom row, right column}. You may use
|
Clearing a cell range means to clear all the cells associated with that range.
Nicola Lambiase
Mirai Solutions GmbH https://mirai-solutions.ch
workbook, clearSheet,
clearNamedRegion,
clearRangeFromReference,
clearSheet
## Not run: # mtcars xlsx file from demoFiles subfolder of # package XLConnect demoExcelFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(demoExcelFile) # Clear range from top left corner (4,2) ^= B4 to # bottom right corner (6,4) ^= D6 clearRange(wb, sheet = "mtcars", coords = c(4, 2, 6, 4)) # Clear two ranges in one go ... mat = matrix(c(5, 1, 6, 4, 5, 7, 7, 9), ncol = 4, byrow = TRUE) clearRange(wb, sheet = "mtcars", coords = mat) # The above is equivalent to ... clearRange(wb, sheet = "mtcars", coords = aref2idx(c("A5:D6", "G5:I7"))) # This in turn is the same as ... clearRangeFromReference(wb, reference = c("mtcars!A5:D6", "mtcars!G5:I7")) ## End(Not run)## Not run: # mtcars xlsx file from demoFiles subfolder of # package XLConnect demoExcelFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(demoExcelFile) # Clear range from top left corner (4,2) ^= B4 to # bottom right corner (6,4) ^= D6 clearRange(wb, sheet = "mtcars", coords = c(4, 2, 6, 4)) # Clear two ranges in one go ... mat = matrix(c(5, 1, 6, 4, 5, 7, 7, 9), ncol = 4, byrow = TRUE) clearRange(wb, sheet = "mtcars", coords = mat) # The above is equivalent to ... clearRange(wb, sheet = "mtcars", coords = aref2idx(c("A5:D6", "G5:I7"))) # This in turn is the same as ... clearRangeFromReference(wb, reference = c("mtcars!A5:D6", "mtcars!G5:I7")) ## End(Not run)
Clears cell ranges specified by area reference in a workbook.
## S4 method for signature 'workbook,character' clearRangeFromReference(object, reference)## S4 method for signature 'workbook,character' clearRangeFromReference(object, reference)
object |
The |
reference |
|
Clearing a cell range means to clear all the cells associated with that range. This
method is very similar to clearRange.
Nicola Lambiase
Mirai Solutions GmbH https://mirai-solutions.ch
workbook, clearSheet,
clearNamedRegion,
clearRange,
clearSheet
## Not run: # mtcars xlsx file from demoFiles subfolder of # package XLConnect demoExcelFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(demoExcelFile) # Clear ranges A5:D6 and G5:I7 on sheet mtcars clearRangeFromReference(wb, reference = c("mtcars!A5:D6", "mtcars!G5:I7")) ## End(Not run)## Not run: # mtcars xlsx file from demoFiles subfolder of # package XLConnect demoExcelFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(demoExcelFile) # Clear ranges A5:D6 and G5:I7 on sheet mtcars clearRangeFromReference(wb, reference = c("mtcars!A5:D6", "mtcars!G5:I7")) ## End(Not run)
Clears worksheets with specified names or indices in a workbook.
## S4 method for signature 'workbook,numeric' clearSheet(object, sheet) ## S4 method for signature 'workbook,character' clearSheet(object, sheet)## S4 method for signature 'workbook,numeric' clearSheet(object, sheet) ## S4 method for signature 'workbook,character' clearSheet(object, sheet)
object |
The |
sheet |
The name or the index of the worksheet to clear |
Clearing a worksheet means to clear all the cells in that worksheet. Consequently, the saved workbook should be smaller in size. Clearing a worksheet can be useful if data sets in a worksheet need to be replaced, i.e. data are first read, modified in R and finally written back to the worksheet. Without clearing the worksheet first, (parts of) the original data may still be visible if they occupied a larger range of the worksheet.
Nicola Lambiase
Mirai Solutions GmbH https://mirai-solutions.ch
workbook, clearNamedRegion,
clearRange,
clearRangeFromReference
## Not run: # mtcars xlsx file from demoFiles subfolder of # package XLConnect demoExcelFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(demoExcelFile) # Clear worksheets named 'mtcars' and 'mtcars2' clearSheet(wb, sheet = c("mtcars", "mtcars2")) # Clear 3rd worksheet clearSheet(wb, sheet = 3) ## End(Not run)## Not run: # mtcars xlsx file from demoFiles subfolder of # package XLConnect demoExcelFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(demoExcelFile) # Clear worksheets named 'mtcars' and 'mtcars2' clearSheet(wb, sheet = c("mtcars", "mtcars2")) # Clear 3rd worksheet clearSheet(wb, sheet = 3) ## End(Not run)
Clones (copies) a worksheet in a workbook.
## S4 method for signature 'workbook,numeric' cloneSheet(object,sheet,name) ## S4 method for signature 'workbook,character' cloneSheet(object,sheet,name)## S4 method for signature 'workbook,numeric' cloneSheet(object,sheet,name) ## S4 method for signature 'workbook,character' cloneSheet(object,sheet,name)
object |
The |
sheet |
The name or index of the worksheet to clone |
name |
The name to assign to the cloned worksheet. Throws an exception if the name to assign is the name of an already existing worksheet. |
If any worksheet-scoped named ranges are present on the original sheet, these named ranges will not be present on the cloned worksheet.
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
workbook, createSheet,
removeSheet, renameSheet,
getSheets, existsSheet
## Not run: # mtcars xlsx file from demoFiles subfolder of package XLConnect demoExcelFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(demoExcelFile) # Clone the 'mtcars' worksheet and assign it the name 'mtcars cloned' cloneSheet(wb, sheet = "mtcars", name = "mtcars cloned") ## End(Not run)## Not run: # mtcars xlsx file from demoFiles subfolder of package XLConnect demoExcelFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(demoExcelFile) # Clone the 'mtcars' worksheet and assign it the name 'mtcars cloned' cloneSheet(wb, sheet = "mtcars", name = "mtcars cloned") ## End(Not run)
Converts Excel column names to indices.
col2idx(x)col2idx(x)
x |
Character vector of Excel column names (e.g. "A", "AF", ...) |
Returns a vector of integers representing the corresponding column indices. Note that passing invalid column name references may result in an arbitrary number.
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
idx2col, cref2idx, idx2cref,
idx2aref, aref2idx, aref
## Not run: col2idx(c("A", "BTG")) ## End(Not run)## Not run: col2idx(c("A", "BTG")) ## End(Not run)
Creates a custom named or anonymous cellstyle.
## S4 method for signature 'workbook,character' createCellStyle(object,name)## S4 method for signature 'workbook,character' createCellStyle(object,name)
object |
The |
name |
The name of the new |
Creates a named cellstyle with the specified name. Named cell
styles may be used in conjunction with the name prefix style
action (see setStyleAction) or
may also be used directly with the method
setCellStyle. Named cell styles can
easily be changed from within Excel using the cell styles menu.
If name is missing, an anonymous cell style is created.
Anonymous cell styles can be used in conjunction with the
setCellStyle method.
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
workbook, cellstyle, getOrCreateCellStyle,
existsCellStyle, setStyleAction,
setStyleNamePrefix, setCellStyle,
setDataFormat, setBorder,
setFillBackgroundColor, setFillForegroundColor,
setFillPattern, setWrapText
## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("createCellstyles.xlsx", create = TRUE) # We don't set a specific style action in this demo, so the # default 'XLConnect' will be used (XLC$"STYLE_ACTION.XLCONNECT") # Create a sheet named 'mtcars' createSheet(wb, name = "mtcars") # Create a named region called 'mtcars' referring to the sheet # called 'mtcars' createName(wb, name = "mtcars", formula = "mtcars!$C$4") # Write built-in data set 'mtcars' to the above defined named region. # This will use the default style action 'XLConnect'. writeNamedRegion(wb, mtcars, name = "mtcars") # Now let's color all weight cells of cars with a weight > 3.5 in red # (mtcars$wt > 3.5) # First, create a corresponding (named) cell style heavyCar <- createCellStyle(wb, name = "HeavyCar") # Specify the cell style to use a solid foreground color setFillPattern(heavyCar, fill = XLC$"FILL.SOLID_FOREGROUND") # Specify the foreground color to be used setFillForegroundColor(heavyCar, color = XLC$"COLOR.RED") # Which cars have a weight > 3.5 ? rowIndex <- which(mtcars$wt > 3.5) # NOTE: The mtcars data.frame has been written offset with # top left cell C4 - and we have also written a header row! # So, let's take that into account appropriately. Obviously, # the two steps could be combined directly into one ... rowIndex <- rowIndex + 4 # The same holds for the column index colIndex <- which(names(mtcars) == "wt") + 2 # Set the 'HeavyCar' cell style for the corresponding cells. # Note: the row and col arguments are vectorized! setCellStyle(wb, sheet = "mtcars", row = rowIndex, col = colIndex, cellstyle = heavyCar) # Save workbook (this actually writes the file to disk) saveWorkbook(wb) # clean up file.remove("createCellstyles.xlsx") ## End(Not run)## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("createCellstyles.xlsx", create = TRUE) # We don't set a specific style action in this demo, so the # default 'XLConnect' will be used (XLC$"STYLE_ACTION.XLCONNECT") # Create a sheet named 'mtcars' createSheet(wb, name = "mtcars") # Create a named region called 'mtcars' referring to the sheet # called 'mtcars' createName(wb, name = "mtcars", formula = "mtcars!$C$4") # Write built-in data set 'mtcars' to the above defined named region. # This will use the default style action 'XLConnect'. writeNamedRegion(wb, mtcars, name = "mtcars") # Now let's color all weight cells of cars with a weight > 3.5 in red # (mtcars$wt > 3.5) # First, create a corresponding (named) cell style heavyCar <- createCellStyle(wb, name = "HeavyCar") # Specify the cell style to use a solid foreground color setFillPattern(heavyCar, fill = XLC$"FILL.SOLID_FOREGROUND") # Specify the foreground color to be used setFillForegroundColor(heavyCar, color = XLC$"COLOR.RED") # Which cars have a weight > 3.5 ? rowIndex <- which(mtcars$wt > 3.5) # NOTE: The mtcars data.frame has been written offset with # top left cell C4 - and we have also written a header row! # So, let's take that into account appropriately. Obviously, # the two steps could be combined directly into one ... rowIndex <- rowIndex + 4 # The same holds for the column index colIndex <- which(names(mtcars) == "wt") + 2 # Set the 'HeavyCar' cell style for the corresponding cells. # Note: the row and col arguments are vectorized! setCellStyle(wb, sheet = "mtcars", row = rowIndex, col = colIndex, cellstyle = heavyCar) # Save workbook (this actually writes the file to disk) saveWorkbook(wb) # clean up file.remove("createCellstyles.xlsx") ## End(Not run)
Creates a freeze pane on a specified worksheet.
## S4 method for signature 'workbook,character' createFreezePane(object, sheet, colSplit, rowSplit, leftColumn, topRow) ## S4 method for signature 'workbook,numeric' createFreezePane(object, sheet, colSplit, rowSplit, leftColumn, topRow)## S4 method for signature 'workbook,character' createFreezePane(object, sheet, colSplit, rowSplit, leftColumn, topRow) ## S4 method for signature 'workbook,numeric' createFreezePane(object, sheet, colSplit, rowSplit, leftColumn, topRow)
object |
The |
sheet |
The name or index of the sheet on which to create a freeze pane |
colSplit |
Horizontal position of freeze (as column index or name) |
rowSplit |
Vertical position of freeze (as number of rows) |
leftColumn |
Left column (as column index or name) visible in right pane. If not specified, the default is |
topRow |
Top row (as index) visible in bottom pane. If not specified, the default is |
To keep an area of a worksheet visible while you scroll to another area of the worksheet, you can lock specific rows or columns in one area by freezing or splitting panes.
When you freeze panes, you keep specific rows or columns visible when you scroll in the worksheet. For example, you might want to keep row and column labels visible as you scroll.
When you split panes, you create separate worksheet areas that you can scroll within, while rows or columns in the non-scrolled area remain visible.
Nicola Lambiase
Mirai Solutions GmbH https://mirai-solutions.ch
How to create a freeze pane/split pane in Office 2007 https://support.microsoft.com/en-us/office/freeze-panes-to-lock-rows-and-columns-dab2ffc9-020d-4026-8121-67dd25f2508f?ocmsassetid=hp001217048&correlationid=b4f5baeb-b622-4487-a96f-514d2f00208a&ui=en-us&rs=en-us&ad=us
workbook
createSplitPane
removePane
## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("freezePaneTest.xlsx", create = TRUE) # Create a worksheet named 'Sheet1' createSheet(wb, name = "Sheet1") # Create a freeze pane on Sheet1, using as reference position the 5th column and the 5th row, # showing the 10th column as the leftmost visible one in the right pane # and the 10th row as the top visible one in the bottom pane. createFreezePane(wb, "Sheet1", 5, 5, 10, 10) # Save workbook (this actually writes the file to disk) saveWorkbook(wb) # clean up file.remove("freezePaneTest.xlsx") ## End(Not run)## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("freezePaneTest.xlsx", create = TRUE) # Create a worksheet named 'Sheet1' createSheet(wb, name = "Sheet1") # Create a freeze pane on Sheet1, using as reference position the 5th column and the 5th row, # showing the 10th column as the leftmost visible one in the right pane # and the 10th row as the top visible one in the bottom pane. createFreezePane(wb, "Sheet1", 5, 5, 10, 10) # Save workbook (this actually writes the file to disk) saveWorkbook(wb) # clean up file.remove("freezePaneTest.xlsx") ## End(Not run)
Creates a named range for a specified formula in a workbook.
## S4 method for signature 'workbook' createName(object, name, formula, overwrite, worksheetScope)## S4 method for signature 'workbook' createName(object, name, formula, overwrite, worksheetScope)
object |
The |
name |
The name of the range to be created |
formula |
Excel formula specifying the value / data the name refers to |
overwrite |
If a name with the same |
worksheetScope |
Optional - specific worksheet the name should be scoped to. If unspecified the name will be scoped to the whole workbook. |
Creates a named range called name for the specified formula.
The formula should be specified as you would type it
in Excel. Make sure that the worksheets, functions, ...
exist that you are referring to in the formula.
The name, formula and overwrite arguments are
vectorized such that multiple names can be created in one method call.
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
What are named regions/ranges?
https://web.archive.org/web/20240821110221/https://www.officearticles.com/excel/named_ranges_in_microsoft_excel.htm
How to create named regions/ranges?
https://www.youtube.com/watch?v=iAE9a0uRtpM
workbook,
removeName,
existsName,
getDefinedNames,readNamedRegion,
writeNamedRegion
## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("createName.xlsx", create = TRUE) # Create a worksheet named 'mtcars' createSheet(wb, name = "mtcars") # Create a named region called 'mtcars' on the sheet called 'mtcars' createName(wb, name = "mtcars", formula = "mtcars!$A$1") # Write built-in data set 'mtcars' to the above defined named region writeNamedRegion(wb, mtcars, name = "mtcars") # Save workbook saveWorkbook(wb) # clean up file.remove("createName.xlsx") ## End(Not run)## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("createName.xlsx", create = TRUE) # Create a worksheet named 'mtcars' createSheet(wb, name = "mtcars") # Create a named region called 'mtcars' on the sheet called 'mtcars' createName(wb, name = "mtcars", formula = "mtcars!$A$1") # Write built-in data set 'mtcars' to the above defined named region writeNamedRegion(wb, mtcars, name = "mtcars") # Save workbook saveWorkbook(wb) # clean up file.remove("createName.xlsx") ## End(Not run)
Creates worksheets with specified names in a workbook.
## S4 method for signature 'workbook' createSheet(object, name)## S4 method for signature 'workbook' createSheet(object, name)
object |
The |
name |
The name of the sheet to create |
Creates a worksheet with the specified name if it does not
already exist. Note that the naming of worksheets needs to be in line
with Excel's convention, otherwise an exception will be thrown. For
example, worksheet names cannot be longer than 31 characters. Also note
that the name argument is vectorized, so multiple worksheets can
be created in one method call.
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
workbook, removeSheet,
renameSheet,
existsSheet, getSheets,
cloneSheet
## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("createSheet.xlsx", create = TRUE) # Create a worksheet called 'CO2' createSheet(wb, name = "CO2") # Save workbook (this actually writes the file to disk) saveWorkbook(wb) # clean up file.remove("createSheet.xlsx") ## End(Not run)## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("createSheet.xlsx", create = TRUE) # Create a worksheet called 'CO2' createSheet(wb, name = "CO2") # Save workbook (this actually writes the file to disk) saveWorkbook(wb) # clean up file.remove("createSheet.xlsx") ## End(Not run)
Creates a split pane on a specified worksheet.
## S4 method for signature 'workbook,character' createSplitPane(object,sheet,xSplitPos,ySplitPos,leftColumn,topRow) ## S4 method for signature 'workbook,numeric' createSplitPane(object,sheet,xSplitPos,ySplitPos,leftColumn,topRow)## S4 method for signature 'workbook,character' createSplitPane(object,sheet,xSplitPos,ySplitPos,leftColumn,topRow) ## S4 method for signature 'workbook,numeric' createSplitPane(object,sheet,xSplitPos,ySplitPos,leftColumn,topRow)
object |
The |
sheet |
The name or index of the sheet on which to create a split pane |
xSplitPos |
Horizontal position of split (in 1/20th of a point) |
ySplitPos |
Vertical position of split (in 1/20th of a point) |
leftColumn |
Left column (as index or column name) visible in right pane |
topRow |
Top row visible in bottom pane |
To keep an area of a worksheet visible while you scroll to another area of the worksheet, you can lock specific rows or columns in one area by freezing or splitting panes.
When you freeze panes, you keep specific rows or columns visible when you scroll in the worksheet. For example, you might want to keep row and column labels visible as you scroll.
When you split panes, you create separate worksheet areas that you can scroll within, while rows or columns in the non-scrolled area remain visible.
Nicola Lambiase
Mirai Solutions GmbH https://mirai-solutions.ch
How to create a freeze pane/split pane in Office 2007 https://support.microsoft.com/en-us/office/freeze-panes-to-lock-rows-and-columns-dab2ffc9-020d-4026-8121-67dd25f2508f?ocmsassetid=hp001217048&correlationid=b4f5baeb-b622-4487-a96f-514d2f00208a&ui=en-us&rs=en-us&ad=us
workbook
createFreezePane
removePane
## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("splitPaneTest.xlsx", create = TRUE) # Create a worksheet named 'Sheet1' createSheet(wb, name = "Sheet1") # Create a split pane on Sheet1, with coordinates (10000, 5000) expressed as 1/20th of a point, # 10 (-> J) as left column visible in right pane and 10 as top row visible in bottom pane createSplitPane(wb, "Sheet1", 10000, 5000, 10, 10) # Save workbook (this actually writes the file to disk) saveWorkbook(wb) # clean up file.remove("splitPaneTest.xlsx") ## End(Not run)## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("splitPaneTest.xlsx", create = TRUE) # Create a worksheet named 'Sheet1' createSheet(wb, name = "Sheet1") # Create a split pane on Sheet1, with coordinates (10000, 5000) expressed as 1/20th of a point, # 10 (-> J) as left column visible in right pane and 10 as top row visible in bottom pane createSplitPane(wb, "Sheet1", 10000, 5000, 10, 10) # Save workbook (this actually writes the file to disk) saveWorkbook(wb) # clean up file.remove("splitPaneTest.xlsx") ## End(Not run)
Converts Excel cell references to row & column indices
cref2idx(x)cref2idx(x)
x |
Character vector of Excel cell references (e.g. "$A$20", "B18", ...) |
Returns a numeric matrix with two columns and as many rows as cell references that have been provided. The first column represents the row indices and the second column represents the column indices.
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
idx2cref, col2idx, idx2col,
idx2aref, aref2idx, aref
## Not run: cref2idx(c("$A$20", "B18")) ## End(Not run)## Not run: cref2idx(c("$A$20", "B18")) ## End(Not run)
Checks whether a named cell style exists in a workbook.
## S4 method for signature 'workbook' existsCellStyle(object,name)## S4 method for signature 'workbook' existsCellStyle(object,name)
object |
The |
name |
The name of the |
Checks whether the cellstyle with the specified name exists.
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
workbook, cellstyle, setCellStyle,
createCellStyle, getOrCreateCellStyle
## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("existsCellStyle.xlsx", create = TRUE) # Cell style 'MyStyle' does not exist yet stopifnot(!existsCellStyle(wb, "MyStyle")) # Create the style "MyStyle" createCellStyle(wb, "MyStyle") # And now it is here stopifnot(existsCellStyle(wb, "MyStyle")) # clean up file.remove("existsCellStyle.xlsx") ## End(Not run)## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("existsCellStyle.xlsx", create = TRUE) # Cell style 'MyStyle' does not exist yet stopifnot(!existsCellStyle(wb, "MyStyle")) # Create the style "MyStyle" createCellStyle(wb, "MyStyle") # And now it is here stopifnot(existsCellStyle(wb, "MyStyle")) # clean up file.remove("existsCellStyle.xlsx") ## End(Not run)
Checks the existence of a named range in a workbook.
## S4 method for signature 'workbook' existsName(object, name, worksheetScope)## S4 method for signature 'workbook' existsName(object, name, worksheetScope)
object |
The |
name |
The name to check for |
worksheetScope |
Optional - the specific worksheet to check |
Returns TRUE if the specified name exists and FALSE
otherwise. Note that the name argument is vectorized and
therefore multiple names can be checked for existence in one method call.
If worksheetScope is provided, TRUE will be returned only if a matching
named range exists in the local scope of the specified sheet. To explicitly match only
in the global scope, pass "" as the value.
If option XLConnect.setCustomAttributes is TRUE (default FALSE),
the worksheet scope in which the name is defined is set as attribute worksheetScope on the result.
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
workbook, createName, removeName,
getDefinedNames, readNamedRegion,writeNamedRegion
## Not run: # mtcars xlsx file from demoFiles subfolder of package XLConnect mtcarsFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(mtcarsFile) # Check if the name 'mtcars' exists # (should return TRUE since the name is defined as 'mtcars!$A$1:$K$33') existsName(wb, name = "mtcars") # check if the worksheet-scoped name 'iris' exists options(XLConnect.setCustomAttributes = TRUE) wb <- loadWorkbook("demoFiles/iris.xlsx") # should return TRUE with worksheet scope "iris" res <- existsName(wb, name = "iris") res attributes(res) ## End(Not run)## Not run: # mtcars xlsx file from demoFiles subfolder of package XLConnect mtcarsFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(mtcarsFile) # Check if the name 'mtcars' exists # (should return TRUE since the name is defined as 'mtcars!$A$1:$K$33') existsName(wb, name = "mtcars") # check if the worksheet-scoped name 'iris' exists options(XLConnect.setCustomAttributes = TRUE) wb <- loadWorkbook("demoFiles/iris.xlsx") # should return TRUE with worksheet scope "iris" res <- existsName(wb, name = "iris") res attributes(res) ## End(Not run)
Checks the existence of a worksheet in a workbook.
## S4 method for signature 'workbook' existsSheet(object,name)## S4 method for signature 'workbook' existsSheet(object,name)
object |
The |
name |
The sheet name to check for |
Checks if the specified worksheet exists. Returns TRUE if it
exists, otherwise FALSE. The name argument is vectorized
which allows to check for existence of multiple worksheets with one call.
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
workbook, createSheet,
removeSheet, renameSheet,
getSheets, cloneSheet
## Not run: # mtcars xlsx file from demoFiles subfolder of package XLConnect demoExcelFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(demoExcelFile) # Check for existence of a worksheet called 'mtcars' existsSheet(wb, "mtcars") ## End(Not run)## Not run: # mtcars xlsx file from demoFiles subfolder of package XLConnect demoExcelFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(demoExcelFile) # Check for existence of a worksheet called 'mtcars' existsSheet(wb, "mtcars") ## End(Not run)
Operators that allow to extract/replace data from/on a workbook.
x |
The |
i |
Name of worksheet ( |
j |
Only used with |
drop |
Not used |
value |
Data object used for replacement |
... |
Arguments passed to the corresponding underlying function to read/write the data |
The workbook extraction operators are basically syntactic sugar for the common methods readWorksheet ([),
writeWorksheet ([<-), readNamedRegion ([[),
writeNamedRegion ([[<-).
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
workbook, readWorksheet, writeWorksheet,
readNamedRegion, writeNamedRegion
## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("extraction.xlsx", create = TRUE) # Write mtcars data set on a worksheet named 'mtcars1'. # Note: The 'mtcars1' sheet will be created automatically if it does # not exist yet. Also, default values for other writeWorksheet arguments # hold, i.e. the data set is written starting at the top left corner. wb["mtcars1"] = mtcars # Write mtcars data set on a worksheet named 'mtcars2'. # Again, the 'mtcars2' worksheet is created automatically. # Additionally specify arguments passed to the underlying method # writeWorksheet. wb["mtcars2", startRow = 6, startCol = 11, header = FALSE] = mtcars # Read worksheets 'mtcars1' and 'mtcars2'. # Note: The default arguments hold for the underlying method # readWorksheet. wb["mtcars1"] wb["mtcars2"] # Write mtcars data set to a named region named 'mtcars3'. Since # it doesn't exist yet we also need to specify the formula to # define it. Also note that the sheet 'mtcars3' referenced in the # formula does not yet exist - it will be created automatically! # Moreover, default values for other writeNamedRegion arguments hold. wb[["mtcars3", "mtcars3!$B$7"]] = mtcars # Redefine named region 'mtcars3'. Note that no formula specification # is required since named region is already defined (see above example). wb[["mtcars3"]] = mtcars # Write mtcars data set to a named region 'mtcars4'. Since the named # region does not yet exist a formula specification is required. Also, # additional arguments are specified that are passed to the underlying # method writeNamedRegion. wb[["mtcars4", "mtcars4!$D$8", rownames = "Car"]] = mtcars # Read the named regions 'mtcars3' and 'mtcars4'. # Note: Default values hold for the underlying method readNamedRegion. wb[["mtcars3"]] wb[["mtcars4"]] # clean up file.remove("extraction.xlsx") ## End(Not run)## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("extraction.xlsx", create = TRUE) # Write mtcars data set on a worksheet named 'mtcars1'. # Note: The 'mtcars1' sheet will be created automatically if it does # not exist yet. Also, default values for other writeWorksheet arguments # hold, i.e. the data set is written starting at the top left corner. wb["mtcars1"] = mtcars # Write mtcars data set on a worksheet named 'mtcars2'. # Again, the 'mtcars2' worksheet is created automatically. # Additionally specify arguments passed to the underlying method # writeWorksheet. wb["mtcars2", startRow = 6, startCol = 11, header = FALSE] = mtcars # Read worksheets 'mtcars1' and 'mtcars2'. # Note: The default arguments hold for the underlying method # readWorksheet. wb["mtcars1"] wb["mtcars2"] # Write mtcars data set to a named region named 'mtcars3'. Since # it doesn't exist yet we also need to specify the formula to # define it. Also note that the sheet 'mtcars3' referenced in the # formula does not yet exist - it will be created automatically! # Moreover, default values for other writeNamedRegion arguments hold. wb[["mtcars3", "mtcars3!$B$7"]] = mtcars # Redefine named region 'mtcars3'. Note that no formula specification # is required since named region is already defined (see above example). wb[["mtcars3"]] = mtcars # Write mtcars data set to a named region 'mtcars4'. Since the named # region does not yet exist a formula specification is required. Also, # additional arguments are specified that are passed to the underlying # method writeNamedRegion. wb[["mtcars4", "mtcars4!$D$8", rownames = "Car"]] = mtcars # Read the named regions 'mtcars3' and 'mtcars4'. # Note: Default values hold for the underlying method readNamedRegion. wb[["mtcars3"]] wb[["mtcars4"]] # clean up file.remove("extraction.xlsx") ## End(Not run)
Extracts the sheet name from a formula of the form <SHEET_NAME>!<CELL_ADDRESS>
extractSheetName(formula)extractSheetName(formula)
formula |
Formula string of the form <SHEET_NAME>!<CELL_ADDRESS>. Note that the validity of the formula won't be checked. |
Returns the name of the sheet referenced in the formula. For quoted sheet names (required if names contain e.g. whitespaces or exclamation marks (!)) in formulas the function returns the unquoted name.
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
## Not run: extractSheetName(c("MySheet!$A$1", "'My Sheet'!$A$1", "'My!Sheet'!$A$1")) ## End(Not run)## Not run: extractSheetName(c("MySheet!$A$1", "'My Sheet'!$A$1", "'My!Sheet'!$A$1")) ## End(Not run)
Queries the index of the active worksheet in a workbook.
## S4 method for signature 'workbook' getActiveSheetIndex(object)## S4 method for signature 'workbook' getActiveSheetIndex(object)
object |
The |
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
## Not run: # mtcars xlsx file from demoFiles subfolder of package XLConnect demoExcelFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(demoExcelFile) # Query the active sheet index activeSheet <- getActiveSheetIndex(wb) ## End(Not run)## Not run: # mtcars xlsx file from demoFiles subfolder of package XLConnect demoExcelFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(demoExcelFile) # Query the active sheet index activeSheet <- getActiveSheetIndex(wb) ## End(Not run)
Queries the name of the active worksheet in a workbook.
## S4 method for signature 'workbook' getActiveSheetName(object)## S4 method for signature 'workbook' getActiveSheetName(object)
object |
The |
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
## Not run: # mtcars xlsx file from demoFiles subfolder of package XLConnect demoExcelFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(demoExcelFile) # Query the active sheet name activeSheet <- getActiveSheetName(wb) ## End(Not run)## Not run: # mtcars xlsx file from demoFiles subfolder of package XLConnect demoExcelFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(demoExcelFile) # Query the active sheet name activeSheet <- getActiveSheetName(wb) ## End(Not run)
This function queries the coordinates of a bounding box in an Excel worksheet. A bounding box is the rectangular region of minimum size containing all the non-empty cells in a sheet.
## S4 method for signature 'workbook,character' getBoundingBox(object,sheet,startRow,startCol,endRow,endCol,autofitRow,autofitCol) ## S4 method for signature 'workbook,numeric' getBoundingBox(object,sheet,startRow,startCol,endRow,endCol,autofitRow,autofitCol)## S4 method for signature 'workbook,character' getBoundingBox(object,sheet,startRow,startCol,endRow,endCol,autofitRow,autofitCol) ## S4 method for signature 'workbook,numeric' getBoundingBox(object,sheet,startRow,startCol,endRow,endCol,autofitRow,autofitCol)
object |
The |
sheet |
The name or index of the sheet from which to get the bounding box |
startRow |
Start reference row for the bounding box. Defaults to |
startCol |
Start reference column for the bounding box. Defaults to |
endRow |
End reference row for the bounding box. Defaults to |
endCol |
End reference column for the bounding box. Defaults to |
autofitRow |
|
autofitCol |
|
The result is a matrix containing the following coordinates:[1,] top left row[2,] top left column[3,] bottom right row[4,] bottom right column
In case more than one sheet is selected, the result matrix will contain a column for each sheet.
The bounding box resolution algorithm works as follows:
If startRow <= 0 then the first available row in the sheet is assumed.
If endRow <= 0 then the last available row in the sheet is assumed.
If startCol <= 0 then the minimum column between startRow and
endRow is assumed. If endCol <= 0 then the maximum column between
startRow and endRow is assumed.
The arguments autofitRow and autofitCol (both defaulting to TRUE) can be used to skip leading and trailing empty rows even in case startRow, endRow, startCol and endCol are specified to values > 0. This can be useful if data is expected within certain given boundaries but the exact location is not available.
Nicola Lambiase
Mirai Solutions GmbH https://mirai-solutions.ch
## Not run: # multiregion xlsx file from demoFiles subfolder of package XLConnect demoExcelFile <- system.file("demoFiles/multiregion.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(demoExcelFile) # Query bounding box for the second sheet print(getBoundingBox(wb, sheet="SecondSheet")) # Query bounding box for the first sheet, selecting the columns from 5 to 8 print(getBoundingBox(wb, sheet="FirstSheet", startCol=5, endCol=8)) ## End(Not run)## Not run: # multiregion xlsx file from demoFiles subfolder of package XLConnect demoExcelFile <- system.file("demoFiles/multiregion.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(demoExcelFile) # Query bounding box for the second sheet print(getBoundingBox(wb, sheet="SecondSheet")) # Query bounding box for the first sheet, selecting the columns from 5 to 8 print(getBoundingBox(wb, sheet="FirstSheet", startCol=5, endCol=8)) ## End(Not run)
Retrieves a cell formula from a workbook.
## S4 method for signature 'workbook,character' getCellFormula(object,sheet,row,col) ## S4 method for signature 'workbook,numeric' getCellFormula(object,sheet,row,col)## S4 method for signature 'workbook,character' getCellFormula(object,sheet,row,col) ## S4 method for signature 'workbook,numeric' getCellFormula(object,sheet,row,col)
object |
The |
sheet |
The name or index of the worksheet containing the cell |
row |
The one-based row index of the cell to query |
col |
The one-based column index of the cell to query |
Retrieves the formula of the specified cell as a character, without the initial
= character displayed in Excel. Raises an error if the specified cell
is not a formula cell.
Thomas Themel
Mirai Solutions GmbH https://mirai-solutions.ch
## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("cellFormula.xlsx", create = TRUE) createSheet(wb, "Formula") # Assign a formula to A1 setCellFormula(wb, "Formula", 1, 1, "SUM($B$1:$B$29)") # Returns the formula for Sheet1!A1 getCellFormula(wb, "Formula", 1, 1) # The same with a numeric sheet index getCellFormula(wb, 1, 1, 1) # clean up file.remove("cellFormula.xlsx") ## End(Not run)## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("cellFormula.xlsx", create = TRUE) createSheet(wb, "Formula") # Assign a formula to A1 setCellFormula(wb, "Formula", 1, 1, "SUM($B$1:$B$29)") # Returns the formula for Sheet1!A1 getCellFormula(wb, "Formula", 1, 1) # The same with a numeric sheet index getCellFormula(wb, 1, 1, 1) # clean up file.remove("cellFormula.xlsx") ## End(Not run)
Retrieves a named cell style from a workbook.
## S4 method for signature 'workbook' getCellStyle(object,name)## S4 method for signature 'workbook' getCellStyle(object,name)
object |
The |
name |
The name of the |
Retrieves the cellstyle with the specified name.
Thomas Themel
Mirai Solutions GmbH https://mirai-solutions.ch
workbook, cellstyle, setStyleAction,
createCellStyle, getOrCreateCellStyle,
existsCellStyle,
setStyleNamePrefix, setCellStyle,
setDataFormat, setBorder,
setFillBackgroundColor, setFillForegroundColor,
setFillPattern, setWrapText
## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("getCellstyles.xlsx", create = TRUE) # You wouldn't usually ignore the return value here... createCellStyle(wb, 'Header') # ... but if you did it doesn't hurt. cs <- getCellStyle(wb, 'Header') # Specify the cell style to use a solid foreground color setFillPattern(cs, fill = XLC$"FILL.SOLID_FOREGROUND") # Specify the foreground color to be used setFillForegroundColor(cs, color = XLC$"COLOR.RED") # clean up file.remove("getCellstyles.xlsx") ## End(Not run)## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("getCellstyles.xlsx", create = TRUE) # You wouldn't usually ignore the return value here... createCellStyle(wb, 'Header') # ... but if you did it doesn't hurt. cs <- getCellStyle(wb, 'Header') # Specify the cell style to use a solid foreground color setFillPattern(cs, fill = XLC$"FILL.SOLID_FOREGROUND") # Specify the foreground color to be used setFillForegroundColor(cs, color = XLC$"COLOR.RED") # clean up file.remove("getCellstyles.xlsx") ## End(Not run)
Queries the cell style for a specific data type as used by the DATATYPE style action.
## S4 method for signature 'workbook' getCellStyleForType(object,type)## S4 method for signature 'workbook' getCellStyleForType(object,type)
object |
The |
type |
The data type for which to get the |
Based on the (cell) data type the DATATYPE style action (see setStyleAction)
sets the cellstyle for the corresponding cells. The data type is normally specified via a
corresponding data type constant from the XLC object.
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
workbook, setCellStyleForType,
setStyleAction
## Not run: file.copy(system.file("demoFiles/template2.xlsx", package = "XLConnect"), "datatype.xlsx", overwrite = TRUE) # Load workbook wb <- loadWorkbook("datatype.xlsx") # Get current (existing) cell style for numerics cs <- getCellStyleForType(wb, XLC$"DATA_TYPE.NUMERIC") # Could also say cs <- getCellStyleForType(wb, "numeric") # Change style setBorder(cs, side = c("bottom", "right"), type = XLC$"BORDER.THICK", color = c(XLC$"COLOR.BLACK", XLC$"COLOR.RED")) # Set style action to 'datatype' setStyleAction(wb, XLC$"STYLE_ACTION.DATATYPE") # Write built-in data set 'mtcars' to the named region # 'mtcars' as defined by the Excel template. writeNamedRegion(wb, mtcars, name = "mtcars") # Save workbook saveWorkbook(wb) # clean up file.remove("datatype.xlsx") ## End(Not run)## Not run: file.copy(system.file("demoFiles/template2.xlsx", package = "XLConnect"), "datatype.xlsx", overwrite = TRUE) # Load workbook wb <- loadWorkbook("datatype.xlsx") # Get current (existing) cell style for numerics cs <- getCellStyleForType(wb, XLC$"DATA_TYPE.NUMERIC") # Could also say cs <- getCellStyleForType(wb, "numeric") # Change style setBorder(cs, side = c("bottom", "right"), type = XLC$"BORDER.THICK", color = c(XLC$"COLOR.BLACK", XLC$"COLOR.RED")) # Set style action to 'datatype' setStyleAction(wb, XLC$"STYLE_ACTION.DATATYPE") # Write built-in data set 'mtcars' to the named region # 'mtcars' as defined by the Excel template. writeNamedRegion(wb, mtcars, name = "mtcars") # Save workbook saveWorkbook(wb) # clean up file.remove("datatype.xlsx") ## End(Not run)
Retrieves the defined names in a workbook.
## S4 method for signature 'workbook' getDefinedNames(object, validOnly, worksheetScope)## S4 method for signature 'workbook' getDefinedNames(object, validOnly, worksheetScope)
object |
The |
validOnly |
If |
worksheetScope |
Optional - the name of the worksheet in which the names are scoped;
to only query names in the global scope, use the value |
If option XLConnect.setCustomAttributes is TRUE (default FALSE),
a list of the worksheet scopes in which the names were found is set as attribute worksheetScope on the result.
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
workbook, createName, removeName,
existsName, readNamedRegion,writeNamedRegion
## Not run: # mtcars xlsx file from demoFiles subfolder of package XLConnect mtcarsFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(mtcarsFile) # Retrieve defined names with valid references getDefinedNames(wb) ## End(Not run)## Not run: # mtcars xlsx file from demoFiles subfolder of package XLConnect mtcarsFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(mtcarsFile) # Retrieve defined names with valid references getDefinedNames(wb) ## End(Not run)
Queries the "force formula recalculation" flag on an Excel worksheet.
## S4 method for signature 'workbook,character' getForceFormulaRecalculation(object,sheet) ## S4 method for signature 'workbook,numeric' getForceFormulaRecalculation(object,sheet)## S4 method for signature 'workbook,character' getForceFormulaRecalculation(object,sheet) ## S4 method for signature 'workbook,numeric' getForceFormulaRecalculation(object,sheet)
object |
The |
sheet |
The name or index of the sheet to query. This argument is vectorized such that
multiple sheets can be queried with one method call. If |
Thomas Themel
Mirai Solutions GmbH https://mirai-solutions.ch
workbook, getSheets,
setForceFormulaRecalculation
## Not run: # mtcars xlsx file from demoFiles subfolder of package XLConnect demoExcelFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(demoExcelFile) # Ask whether Excel will automatically recalculate formulas on sheet mtcars print(getForceFormulaRecalculation(wb, sheet = "mtcars")) ## End(Not run)## Not run: # mtcars xlsx file from demoFiles subfolder of package XLConnect demoExcelFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(demoExcelFile) # Ask whether Excel will automatically recalculate formulas on sheet mtcars print(getForceFormulaRecalculation(wb, sheet = "mtcars")) ## End(Not run)
Queries the last (non-empty) column on a worksheet.
## S4 method for signature 'workbook,character' getLastColumn(object,sheet) ## S4 method for signature 'workbook,numeric' getLastColumn(object,sheet)## S4 method for signature 'workbook,character' getLastColumn(object,sheet) ## S4 method for signature 'workbook,numeric' getLastColumn(object,sheet)
object |
The |
sheet |
The name or index of the sheet of which to query the last column |
Returns the (1-based) numeric index of the last non-empty column in the specified worksheet.
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
## Not run: # mtcars xlsx file from demoFiles subfolder of package XLConnect demoExcelFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(demoExcelFile) # Query the last row of the 'mtcars' worksheet getLastColumn(wb, "mtcars") # Query the last row of the 'mtcars2' worksheet getLastColumn(wb, "mtcars2") # Query the last row of the 'mtcars3' worksheet getLastColumn(wb, "mtcars3") ## End(Not run)## Not run: # mtcars xlsx file from demoFiles subfolder of package XLConnect demoExcelFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(demoExcelFile) # Query the last row of the 'mtcars' worksheet getLastColumn(wb, "mtcars") # Query the last row of the 'mtcars2' worksheet getLastColumn(wb, "mtcars2") # Query the last row of the 'mtcars3' worksheet getLastColumn(wb, "mtcars3") ## End(Not run)
Queries the last (non-empty) row on a worksheet.
## S4 method for signature 'workbook,character' getLastRow(object,sheet) ## S4 method for signature 'workbook,numeric' getLastRow(object,sheet)## S4 method for signature 'workbook,character' getLastRow(object,sheet) ## S4 method for signature 'workbook,numeric' getLastRow(object,sheet)
object |
The |
sheet |
The name or index of the sheet of which to query the last row |
Returns the numeric index of the last non-empty row in the specified worksheet.
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
## Not run: # mtcars xlsx file from demoFiles subfolder of package XLConnect demoExcelFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(demoExcelFile) # Query the last row of the 'mtcars' worksheet getLastRow(wb, "mtcars") # Query the last row of the 'mtcars2' worksheet getLastRow(wb, "mtcars2") # Query the last row of the 'mtcars3' worksheet getLastRow(wb, "mtcars3") ## End(Not run)## Not run: # mtcars xlsx file from demoFiles subfolder of package XLConnect demoExcelFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(demoExcelFile) # Query the last row of the 'mtcars' worksheet getLastRow(wb, "mtcars") # Query the last row of the 'mtcars2' worksheet getLastRow(wb, "mtcars2") # Query the last row of the 'mtcars3' worksheet getLastRow(wb, "mtcars3") ## End(Not run)
Retrieves or creates cell styles in workbooks.
## S4 method for signature 'workbook,character' getOrCreateCellStyle(object,name)## S4 method for signature 'workbook,character' getOrCreateCellStyle(object,name)
object |
The |
name |
The name of the |
Retrieves an existing cellstyle if it exists or creates a new one if it
does not exist yet.
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
workbook, cellstyle, setCellStyle,
createCellStyle, existsCellStyle
## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("getOrCreateCellStyle.xlsx", create = TRUE) # The first time, the style does not exist yet and gets created myStyle <- getOrCreateCellStyle(wb, name = "MyStyle") # The second time, we retrieve the already existing style myStyle <- getOrCreateCellStyle(wb, name = "MyStyle") # clean up file.remove("getOrCreateCellStyle.xlsx") ## End(Not run)## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("getOrCreateCellStyle.xlsx", create = TRUE) # The first time, the style does not exist yet and gets created myStyle <- getOrCreateCellStyle(wb, name = "MyStyle") # The second time, we retrieve the already existing style myStyle <- getOrCreateCellStyle(wb, name = "MyStyle") # clean up file.remove("getOrCreateCellStyle.xlsx") ## End(Not run)
(DEPRECATED) Queries the coordinates of an Excel named range in a workbook.
## S4 method for signature 'workbook' getReferenceCoordinates(object,name)## S4 method for signature 'workbook' getReferenceCoordinates(object,name)
object |
The |
name |
The name to query. This argument is vectorized such that multiple names can be queried with one method call. |
This function is deprecated. Use getReferenceCoordinatesForName
instead.
Thomas Themel
Mirai Solutions GmbH https://mirai-solutions.ch
workbook, createName, existsName,
removeName, getReferenceFormula
## Not run: # mtcars xlsx file from demoFiles subfolder of package XLConnect demoExcelFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(demoExcelFile) # Query reference coordinate for name 'mtcars' print(getReferenceCoordinatesForName(wb, name = "mtcars")) ## End(Not run)## Not run: # mtcars xlsx file from demoFiles subfolder of package XLConnect demoExcelFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(demoExcelFile) # Query reference coordinate for name 'mtcars' print(getReferenceCoordinatesForName(wb, name = "mtcars")) ## End(Not run)
Queries the coordinates of an Excel named range in a workbook.
## S4 method for signature 'workbook' getReferenceCoordinatesForName(object,name, worksheetScope)## S4 method for signature 'workbook' getReferenceCoordinatesForName(object,name, worksheetScope)
object |
The |
name |
The name to query. This argument is vectorized such that multiple names can be queried with one method call. |
worksheetScope |
Optional, the name of the worksheet to use for resolving the named region |
If worksheetScope is defined, only coordinates for a range scoped strictly to the specified worksheet are
returned. To explicitly only query for named ranges in the global scope, pass "" as the value.
Thomas Themel
Mirai Solutions GmbH https://mirai-solutions.ch
workbook, createName, existsName,
removeName, getReferenceFormula,
getReferenceCoordinatesForTable
## Not run: # mtcars xlsx file from demoFiles subfolder of package XLConnect demoExcelFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(demoExcelFile) # Query reference coordinate for name 'mtcars' print(getReferenceCoordinatesForName(wb, name = "mtcars")) ## End(Not run)## Not run: # mtcars xlsx file from demoFiles subfolder of package XLConnect demoExcelFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(demoExcelFile) # Query reference coordinate for name 'mtcars' print(getReferenceCoordinatesForName(wb, name = "mtcars")) ## End(Not run)
Queries the coordinates of an Excel table (Office 2007+) in a workbook.
## S4 method for signature 'workbook,numeric' getReferenceCoordinatesForTable(object,sheet,table) ## S4 method for signature 'workbook,character' getReferenceCoordinatesForTable(object,sheet,table)## S4 method for signature 'workbook,numeric' getReferenceCoordinatesForTable(object,sheet,table) ## S4 method for signature 'workbook,character' getReferenceCoordinatesForTable(object,sheet,table)
object |
The |
sheet |
The index or name of the worksheet on which to look for the specified |
table |
The name of the table to query. This argument is vectorized such that multiple tables can be queried with one method call. |
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
workbook, createName, existsName,
removeName, getReferenceFormula,
getReferenceCoordinatesForName
## Not run: # mtcars xlsx file from demoFiles subfolder of package XLConnect demoExcelFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(demoExcelFile) # Query reference coordinates for table 'MtcarsTable' on sheet # 'mtcars_table' print(getReferenceCoordinatesForTable(wb, sheet = "mtcars_table", table = "MtcarsTable")) ## End(Not run)## Not run: # mtcars xlsx file from demoFiles subfolder of package XLConnect demoExcelFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(demoExcelFile) # Query reference coordinates for table 'MtcarsTable' on sheet # 'mtcars_table' print(getReferenceCoordinatesForTable(wb, sheet = "mtcars_table", table = "MtcarsTable")) ## End(Not run)
Queries the reference formula of an Excel named range in a workbook.
## S4 method for signature 'workbook' getReferenceFormula(object,name, worksheetScope)## S4 method for signature 'workbook' getReferenceFormula(object,name, worksheetScope)
object |
The |
name |
The named range to query. This argument is vectorized such that multiple names can be queried with one method call. |
worksheetScope |
Optional - the name of the worksheet in which the name is scoped;
if undefined a matching name in any scope may be returned. To specify global scope only, use the value |
.
If option XLConnect.setCustomAttributes is TRUE (default FALSE),
the worksheet scope in which the queried name is defined is set as attribute worksheetScope on the result.
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
workbook, createName, existsName,
removeName
## Not run: # mtcars xlsx file from demoFiles subfolder of package XLConnect demoExcelFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(demoExcelFile) # Query reference formula for name 'mtcars' print(getReferenceFormula(wb, name = "mtcars")) ## End(Not run)## Not run: # mtcars xlsx file from demoFiles subfolder of package XLConnect demoExcelFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(demoExcelFile) # Query reference formula for name 'mtcars' print(getReferenceFormula(wb, name = "mtcars")) ## End(Not run)
Queries the position of a worksheet in a workbook.
## S4 method for signature 'workbook,character' getSheetPos(object,sheet)## S4 method for signature 'workbook,character' getSheetPos(object,sheet)
object |
The |
sheet |
The name of the worksheet ( |
Returns the position index of the corresponding worksheet. Note that querying a non-existing worksheet results in a 0 index and does not throw an exception!
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
workbook, setSheetPos,
getSheets
## Not run: # mtcars xlsx file from demoFiles subfolder of package XLConnect demoExcelFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(demoExcelFile) # Query worksheet positions for the worksheets 'mtcars2', 'mtcars3', # 'mtcars' and 'NotThere' (which actually does not exist) print(getSheetPos(wb, sheet = c("mtcars2", "mtcars3", "mtcars", "NotThere"))) ## End(Not run)## Not run: # mtcars xlsx file from demoFiles subfolder of package XLConnect demoExcelFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(demoExcelFile) # Query worksheet positions for the worksheets 'mtcars2', 'mtcars3', # 'mtcars' and 'NotThere' (which actually does not exist) print(getSheetPos(wb, sheet = c("mtcars2", "mtcars3", "mtcars", "NotThere"))) ## End(Not run)
Returns all worksheet names in a workbook.
## S4 method for signature 'workbook' getSheets(object)## S4 method for signature 'workbook' getSheets(object)
object |
The |
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
workbook, createSheet,
removeSheet, renameSheet,
getSheetPos, setSheetPos
## Not run: # mtcars xlsx file from demoFiles subfolder of package XLConnect demoExcelFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(demoExcelFile) # Query available worksheets sheets <- getSheets(wb) ## End(Not run)## Not run: # mtcars xlsx file from demoFiles subfolder of package XLConnect demoExcelFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(demoExcelFile) # Query available worksheets sheets <- getSheets(wb) ## End(Not run)
Queries the available Excel tables on the specified worksheet.
## S4 method for signature 'workbook,numeric' getTables(object,sheet,simplify) ## S4 method for signature 'workbook,character' getTables(object,sheet,simplify)## S4 method for signature 'workbook,numeric' getTables(object,sheet,simplify) ## S4 method for signature 'workbook,character' getTables(object,sheet,simplify)
object |
The |
sheet |
Index ( |
simplify |
|
Since this is a vectorized function (multiple sheets can be specified) the result is a
named list (one component per sheet) if no simplification is applied. In cases where only
one sheet is queried and simplify = TRUE (default) the result is simplified to a
vector.
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
workbook, getSheets,
readTable
## Not run: # mtcars xlsx file from demoFiles subfolder of package XLConnect demoExcelFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(demoExcelFile) # Query available tables (table names) on sheet 'mtcars_table' tables <- getTables(wb, sheet = "mtcars_table") # ... or via sheet index tables <- getTables(wb, sheet = 4) ## End(Not run)## Not run: # mtcars xlsx file from demoFiles subfolder of package XLConnect demoExcelFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(demoExcelFile) # Query available tables (table names) on sheet 'mtcars_table' tables <- getTables(wb, sheet = "mtcars_table") # ... or via sheet index tables <- getTables(wb, sheet = 4) ## End(Not run)
(Very) hides the specified worksheets in a workbook.
## S4 method for signature 'workbook,character' hideSheet(object, sheet, veryHidden) ## S4 method for signature 'workbook,numeric' hideSheet(object, sheet, veryHidden)## S4 method for signature 'workbook,character' hideSheet(object, sheet, veryHidden) ## S4 method for signature 'workbook,numeric' hideSheet(object, sheet, veryHidden)
object |
The |
sheet |
The name or index of the sheet to hide |
veryHidden |
If |
The arguments sheet and veryHidden are vectorized such
that multiple worksheets can be (very) hidden with one method call. An
exception is thrown if the specified sheet does not exist.
Note that hidden worksheets can be unhidden by users directly within
Excel via standard functionality. Therefore Excel knows the concept
of "very hidden" worksheets. These worksheets cannot be unhidden with
standard Excel functionality but need programatic intervention to be
made visible.
Also note that in case the specified worksheet to hide is the
currently active worksheet, then hideSheet tries to set the
active worksheet to the first non-hidden (not hidden and not very
hidden) worksheet in the workbook. If there is no such worksheet,
hideSheet will throw an exception.
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
workbook, unhideSheet,
isSheetHidden, isSheetVeryHidden,
isSheetVisible
## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("hiddenWorksheet.xlsx", create = TRUE) # Write a couple of built-in data.frame's into sheets # with corresponding name for(obj in c("CO2", "airquality", "swiss")) { createSheet(wb, name = obj) writeWorksheet(wb, get(obj), sheet = obj) } # Hide sheet 'airquality'; # the sheet may be unhidden by a user from within Excel # since veryHidden defaults to FALSE hideSheet(wb, sheet = "airquality") # Save workbook saveWorkbook(wb) # clean up file.remove("hiddenWorksheet.xlsx") ## End(Not run)## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("hiddenWorksheet.xlsx", create = TRUE) # Write a couple of built-in data.frame's into sheets # with corresponding name for(obj in c("CO2", "airquality", "swiss")) { createSheet(wb, name = obj) writeWorksheet(wb, get(obj), sheet = obj) } # Hide sheet 'airquality'; # the sheet may be unhidden by a user from within Excel # since veryHidden defaults to FALSE hideSheet(wb, sheet = "airquality") # Save workbook saveWorkbook(wb) # clean up file.remove("hiddenWorksheet.xlsx") ## End(Not run)
Converts row & column based area references to Excel area references
idx2aref(x)idx2aref(x)
x |
Numeric (integer) matrix or vector of indices. If a matrix is provided it should have four columns with the first two columns representing the top left corner (row and column indices) and the third & fourth column representing the bottom right corner. If a vector is provided it will be converted to a matrix by filling the vector into a 4-column matrix by row. |
Returns a character vector of corresponding Excel area references.
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
aref2idx, aref, idx2cref,
cref2idx, idx2col, col2idx
## Not run: idx2aref(c(1, 1, 5, 4)) ## End(Not run)## Not run: idx2aref(c(1, 1, 5, 4)) ## End(Not run)
Converts column indices to Excel column names.
idx2col(x)idx2col(x)
x |
Numeric (integer) vector of column indices |
Returns a character vector of corresponding Excel column names.
Numbers <= 0 result in the empty string ("").
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
col2idx, idx2cref, cref2idx,
idx2aref, aref2idx, aref
## Not run: idx2col(c(1, 347)) ## End(Not run)## Not run: idx2col(c(1, 347)) ## End(Not run)
Converts row & column indices to Excel cell references
idx2cref(x, absRow = TRUE, absCol = TRUE)idx2cref(x, absRow = TRUE, absCol = TRUE)
x |
Numeric (integer) matrix or vector of indices. If a matrix is provided it should have two columns with the first column representing the row indices and the second column representing the column indices (i.e. each row represents a index-based cell reference). If a vector is provided it will be converted to a matrix by filling the vector into a 2-column matrix by row. |
absRow |
Boolean determining if the row index should be considered absolute.
If |
absCol |
Boolean determining if the column index should be considered absolute.
If |
Returns a character vector of corresponding Excel cell references.
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
cref2idx, idx2col, col2idx,
idx2aref, aref2idx, aref
## Not run: idx2cref(c(5, 8, 14, 38)) ## End(Not run)## Not run: idx2cref(c(5, 8, 14, 38)) ## End(Not run)
Checks if the specified worksheets are hidden (but not very hidden) in a workbook.
## S4 method for signature 'workbook,character' isSheetHidden(object,sheet) ## S4 method for signature 'workbook,numeric' isSheetHidden(object,sheet)## S4 method for signature 'workbook,character' isSheetHidden(object,sheet) ## S4 method for signature 'workbook,numeric' isSheetHidden(object,sheet)
object |
The |
sheet |
The name or index of the sheet to check |
Returns TRUE if the specified sheet is hidden (not visible
but also not very hidden), otherwise FALSE. sheet is
vectorized such that multiple worksheets can be queried with one method
call. An exception is thrown if the specified sheet does not exist.
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
workbook, hideSheet, unhideSheet,
isSheetVeryHidden, isSheetVisible
## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("isSheetHidden.xlsx", create = TRUE) # Write a couple of built-in data.frame's into sheets # with corresponding name for(obj in c("CO2", "airquality", "swiss")) { createSheet(wb, name = obj) writeWorksheet(wb, get(obj), sheet = obj) } # Hide sheet 'airquality' hideSheet(wb, sheet = "airquality") # Check if sheet 'airquality' is hidden; # this should obviously return TRUE isSheetHidden(wb, "airquality") # Check if sheet 'swiss' is hidden; # this should obviously return FALSE isSheetHidden(wb, "swiss") # clean up file.remove("isSheetHidden.xlsx") ## End(Not run)## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("isSheetHidden.xlsx", create = TRUE) # Write a couple of built-in data.frame's into sheets # with corresponding name for(obj in c("CO2", "airquality", "swiss")) { createSheet(wb, name = obj) writeWorksheet(wb, get(obj), sheet = obj) } # Hide sheet 'airquality' hideSheet(wb, sheet = "airquality") # Check if sheet 'airquality' is hidden; # this should obviously return TRUE isSheetHidden(wb, "airquality") # Check if sheet 'swiss' is hidden; # this should obviously return FALSE isSheetHidden(wb, "swiss") # clean up file.remove("isSheetHidden.xlsx") ## End(Not run)
Checks if the specified worksheets are very hidden (but not just hidden) in a workbook.
## S4 method for signature 'workbook,character' isSheetVeryHidden(object,sheet) ## S4 method for signature 'workbook,numeric' isSheetVeryHidden(object,sheet)## S4 method for signature 'workbook,character' isSheetVeryHidden(object,sheet) ## S4 method for signature 'workbook,numeric' isSheetVeryHidden(object,sheet)
object |
The |
sheet |
The name or index of the sheet to check |
Returns TRUE if the specified named sheet is very hidden
(not visible but also not just hidden), otherwise
FALSE. sheet is vectorized such that multiple worksheets
can be queried with one method call. An exception is thrown if the
specified sheet does not exist.
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
workbook, hideSheet, unhideSheet,
isSheetHidden, isSheetVisible
## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("isSheetVeryHidden.xlsx", create = TRUE) # Write a couple of built-in data.frame's into sheets # with corresponding name for(obj in c("CO2", "airquality", "swiss")) { createSheet(wb, name = obj) writeWorksheet(wb, get(obj), sheet = obj) } # Very hide sheet 'airquality' hideSheet(wb, sheet = "airquality", veryHidden = TRUE) # Hide sheet 'CO2' hideSheet(wb, sheet = "CO2", veryHidden = FALSE) # Check if sheet 'airquality' is very hidden; # this should obviously return TRUE isSheetVeryHidden(wb, "airquality") # Check if sheet 'swiss' is very hidden; # this should obviously return FALSE isSheetVeryHidden(wb, "swiss") # Check if sheet 'CO2' is very hidden; # this should also return FALSE - the sheet # is just hidden but not very hidden isSheetVeryHidden(wb, "CO2") # clean up file.remove("isSheetVeryHidden.xlsx") ## End(Not run)## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("isSheetVeryHidden.xlsx", create = TRUE) # Write a couple of built-in data.frame's into sheets # with corresponding name for(obj in c("CO2", "airquality", "swiss")) { createSheet(wb, name = obj) writeWorksheet(wb, get(obj), sheet = obj) } # Very hide sheet 'airquality' hideSheet(wb, sheet = "airquality", veryHidden = TRUE) # Hide sheet 'CO2' hideSheet(wb, sheet = "CO2", veryHidden = FALSE) # Check if sheet 'airquality' is very hidden; # this should obviously return TRUE isSheetVeryHidden(wb, "airquality") # Check if sheet 'swiss' is very hidden; # this should obviously return FALSE isSheetVeryHidden(wb, "swiss") # Check if sheet 'CO2' is very hidden; # this should also return FALSE - the sheet # is just hidden but not very hidden isSheetVeryHidden(wb, "CO2") # clean up file.remove("isSheetVeryHidden.xlsx") ## End(Not run)
Checks if the specified worksheets are visible in a workbook.
## S4 method for signature 'workbook,character' isSheetVisible(object,sheet) ## S4 method for signature 'workbook,numeric' isSheetVisible(object,sheet)## S4 method for signature 'workbook,character' isSheetVisible(object,sheet) ## S4 method for signature 'workbook,numeric' isSheetVisible(object,sheet)
object |
The |
sheet |
The name or index of the sheet to check |
Returns TRUE if the specified named sheet is visible (not
hidden and not very hidden), otherwise FALSE. sheet is
vectorized such that multiple worksheets can be queried with one method
call. An exception is thrown if the specified sheet does not
exist.
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
workbook, hideSheet, unhideSheet,
isSheetHidden, isSheetVeryHidden
## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("isSheetVisible.xlsx", create = TRUE) # Write a couple of built-in data.frame's into sheets # with corresponding name for(obj in c("CO2", "airquality", "swiss")) { createSheet(wb, name = obj) writeWorksheet(wb, get(obj), sheet = obj) } # Hide sheet 'CO2' hideSheet(wb, sheet = "CO2", veryHidden = FALSE) # Very hide sheet 'airquality' hideSheet(wb, sheet = "airquality", veryHidden = TRUE) # Check if sheet 'swiss' is visible; # this should obviously return TRUE isSheetVisible(wb, "swiss") # Check if sheet 'CO2' is visible; # this should obviously return FALSE isSheetVisible(wb, "CO2") # Check if sheet 'airquality' is visible; # this should obviously return FALSE isSheetVisible(wb, "airquality") # clean up file.remove("isSheetVisible.xlsx") ## End(Not run)## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("isSheetVisible.xlsx", create = TRUE) # Write a couple of built-in data.frame's into sheets # with corresponding name for(obj in c("CO2", "airquality", "swiss")) { createSheet(wb, name = obj) writeWorksheet(wb, get(obj), sheet = obj) } # Hide sheet 'CO2' hideSheet(wb, sheet = "CO2", veryHidden = FALSE) # Very hide sheet 'airquality' hideSheet(wb, sheet = "airquality", veryHidden = TRUE) # Check if sheet 'swiss' is visible; # this should obviously return TRUE isSheetVisible(wb, "swiss") # Check if sheet 'CO2' is visible; # this should obviously return FALSE isSheetVisible(wb, "CO2") # Check if sheet 'airquality' is visible; # this should obviously return FALSE isSheetVisible(wb, "airquality") # clean up file.remove("isSheetVisible.xlsx") ## End(Not run)
Loads or creates a Microsoft Excel workbook for further manipulation.
loadWorkbook(filename, create = FALSE, password = NULL)loadWorkbook(filename, create = FALSE, password = NULL)
filename |
Filename (absolute or relative) of Excel workbook to be loaded. Supported are Excel '97 (*.xls) and
OOXML (Excel 2007+, *.xlsx) file formats. Paths are expanded using |
create |
Specifies if the file should be created if it does not already exist (default is |
password |
Password to use when opening password protected files. The default |
Returns a workbook object for further manipulation.
loadWorkbook is basically just a shortcut form of new("workbook", filename, create) with some additional error checking. As such
it is the preferred way of creating workbook instances.
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
Wikipedia: Office Open XML
https://en.wikipedia.org/wiki/Office_Open_XML
## Not run: # Load existing demo Excel file 'mtcars.xlsx' from the XLConnect package wb.mtcars <- loadWorkbook(system.file("demoFiles/mtcars.xlsx", package = "XLConnect")) # Create new workbook wb.new <- loadWorkbook("myNewExcelFile.xlsx", create = TRUE) # NOTE: The above statement does not write the file to disk! # saveWorkbook(wb.new) would need to be called in order to write/save # the file to disk! # clean up file.remove("myNewExcelFile.xlsx") ## End(Not run)## Not run: # Load existing demo Excel file 'mtcars.xlsx' from the XLConnect package wb.mtcars <- loadWorkbook(system.file("demoFiles/mtcars.xlsx", package = "XLConnect")) # Create new workbook wb.new <- loadWorkbook("myNewExcelFile.xlsx", create = TRUE) # NOTE: The above statement does not write the file to disk! # saveWorkbook(wb.new) would need to be called in order to write/save # the file to disk! # clean up file.remove("myNewExcelFile.xlsx") ## End(Not run)
Merges cells in a worksheet.
## S4 method for signature 'workbook,character' mergeCells(object,sheet,reference) ## S4 method for signature 'workbook,numeric' mergeCells(object,sheet,reference)## S4 method for signature 'workbook,character' mergeCells(object,sheet,reference) ## S4 method for signature 'workbook,numeric' mergeCells(object,sheet,reference)
object |
The |
sheet |
The name or index of the sheet on which to merge cells |
reference |
A cell range specification ( |
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
workbook, unmergeCells,
idx2cref
## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("mergeCells.xlsx", create = TRUE) # Create a worksheet called 'merge' createSheet(wb, name = "merge") # Merge the cells A1:B8 on the worksheet created above mergeCells(wb, sheet = "merge", reference = "A1:B8") # Save workbook saveWorkbook(wb) # clean up file.remove("mergeCells.xlsx") ## End(Not run)## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("mergeCells.xlsx", create = TRUE) # Create a worksheet called 'merge' createSheet(wb, name = "merge") # Merge the cells A1:B8 on the worksheet created above mergeCells(wb, sheet = "merge", reference = "A1:B8") # Save workbook saveWorkbook(wb) # clean up file.remove("mergeCells.xlsx") ## End(Not run)
Utility object to easily get to the Mirai Solutions GmbH web page. Just enter mirai in the R console.
miraimirai
Mirai Solutions GmbH https://mirai-solutions.ch
This function defines the behavior when reading data from a worksheet and error cells are detected.
## S4 method for signature 'workbook' onErrorCell(object,behavior)## S4 method for signature 'workbook' onErrorCell(object,behavior)
object |
The |
behavior |
The behavior to follow when an error cell is detected. This is normally
specified by a corresponding |
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
workbook, readNamedRegion,
readNamedRegionFromFile, readWorksheet,readWorksheetFromFile
## Not run: # errorCell xlsx file from demoFiles subfolder of package XLConnect demoExcelFile <- system.file("demoFiles/errorCell.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(demoExcelFile) # Set error behavior to XLC$ERROR.WARN when detecting error cells # Note: this is the default behavior onErrorCell(wb, XLC$ERROR.WARN) # Alternatively: wb$onErrorCell(XLC$ERROR.WARN) # Read named region 'MyData' (with default header = TRUE) data <- readNamedRegion(wb, name = "MyData") # Now set error behavior to XLC$ERROR.STOP to immediately # issue an exception and stop in case an error cell is # detected onErrorCell(wb, XLC$ERROR.STOP) # Alternatively: wb$onErrorCell(XLC$ERROR.STOP) # Read (again) named region 'MyData' (with default header = TRUE) res <- try(readNamedRegion(wb, name = "MyData")) # Did we get an error? print(is(res, "try-error")) ## End(Not run)## Not run: # errorCell xlsx file from demoFiles subfolder of package XLConnect demoExcelFile <- system.file("demoFiles/errorCell.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(demoExcelFile) # Set error behavior to XLC$ERROR.WARN when detecting error cells # Note: this is the default behavior onErrorCell(wb, XLC$ERROR.WARN) # Alternatively: wb$onErrorCell(XLC$ERROR.WARN) # Read named region 'MyData' (with default header = TRUE) data <- readNamedRegion(wb, name = "MyData") # Now set error behavior to XLC$ERROR.STOP to immediately # issue an exception and stop in case an error cell is # detected onErrorCell(wb, XLC$ERROR.STOP) # Alternatively: wb$onErrorCell(XLC$ERROR.STOP) # Read (again) named region 'MyData' (with default header = TRUE) res <- try(readNamedRegion(wb, name = "MyData")) # Did we get an error? print(is(res, "try-error")) ## End(Not run)
Prints the workbook's underlying filename.
## S4 method for signature 'workbook' print(x,...)## S4 method for signature 'workbook' print(x,...)
x |
The |
... |
Arguments passed on to standard |
Prints the specified workbook's filename (see also the S4 filename
slot of the workbook class).
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
## Not run: # Load existing demo Excel file 'mtcars.xlsx' from the XLConnect package wb.mtcars <- loadWorkbook(system.file("demoFiles/mtcars.xlsx", package = "XLConnect")) # Print the workbook's underlying filename print(wb.mtcars) ## End(Not run)## Not run: # Load existing demo Excel file 'mtcars.xlsx' from the XLConnect package wb.mtcars <- loadWorkbook(system.file("demoFiles/mtcars.xlsx", package = "XLConnect")) # Print the workbook's underlying filename print(wb.mtcars) ## End(Not run)
Reads named regions from a workbook.
## S4 method for signature 'workbook' readNamedRegion(object, name, header, rownames, colTypes, forceConversion, dateTimeFormat, check.names, useCachedValues, keep, drop, simplify, readStrategy, worksheetScope)## S4 method for signature 'workbook' readNamedRegion(object, name, header, rownames, colTypes, forceConversion, dateTimeFormat, check.names, useCachedValues, keep, drop, simplify, readStrategy, worksheetScope)
object |
The |
name |
The name of the named region to read |
header |
The argument |
rownames |
Index ( |
colTypes |
Column types to use when reading in the data. Specified as a |
forceConversion |
|
dateTimeFormat |
Date/time format used when doing date/time conversions. Defaults to |
check.names |
|
useCachedValues |
|
keep |
List of column names or indices to be kept in the output data frame.
It is possible to specify either |
drop |
List of column names or indices to be dropped in the output data frame.
It is possible to specify either |
simplify |
|
readStrategy |
|
worksheetScope |
Optional, the name of the worksheet to use for resolving the named region |
The arguments name, header, and worksheetScope are vectorized. As such,
multiple named regions can be read with one method call. If only one
single named region is read, the return value is a data.frame.If
multiple named regions are specified, the return value is a (named)
list of data.frame's returned in the order they have been
specified with the argument name.
When reading dates, if your system uses a time zone that has / had daylight saving time,
certain dates / timestamps will not be read exactly as they were written. See
https://poi.apache.org/apidocs/dev/org/apache/poi/ss/usermodel/DateUtil.html#getJavaDate-double-
If worksheetScope is unspecified, the contents of the name found anywhere in the workbook will be read.
Otherwise, only a name specifically scoped to the specified sheet may be read.
To read only names defined in the global scope, pass "" as the value.
If option XLConnect.setCustomAttributes is TRUE (default FALSE),
the worksheet scope in which the name was found is set as attribute worksheetScope on the result.
If no specific column types (see argument colTypes) are specified,
readNamedRegion tries to determine the resulting column types
based on the read cell types. If different cell types are found in a
specific column, the most general of those is used and mapped to the
corresponding R data type. The order of data types from least to most
general is Boolean (logical) < DateTime (POSIXct) <
Numeric (numeric) < String (character). E.g. if a column
is read that contains cells of type Boolean, Numeric and String then the
resulting column in R would be character since character
is the most general type.
Some additional information with respect to forcing data type conversion
using forceConversion = TRUE:
Forcing conversion from String to Boolean: TRUE is returned
if and only if the target string is "true" (ignoring any capitalization).
Any other string will return FALSE.
Forcing conversion from Numeric to DateTime: since Excel understands Dates/Times as Numerics with some additional formatting, a conversion from a Numeric to a DateTime is actually possible. Numerics in this case represent the number of days since 1900-01-00 (yes, day 00! - see https://web.archive.org/web/20240821110422/http://www.cpearson.com/excel/datetime.htm). Note that in R 0 is represented as 1899-12-31 since there is no 1900-01-00. Fractional days represent hours, minutes, and seconds.
Martin Studer
Thomas Themel
Nicola Lambiase
Mirai Solutions GmbH https://mirai-solutions.ch
What are named regions/ranges?
https://web.archive.org/web/20240821110221/https://www.officearticles.com/excel/named_ranges_in_microsoft_excel.htm
How to create named regions/ranges?
https://www.youtube.com/watch?v=iAE9a0uRtpM
workbook,
readWorksheet,
writeNamedRegion,writeWorksheet,
readNamedRegionFromFile,
readTable,
onErrorCell
## Not run: ## Example 1: # mtcars xlsx file from demoFiles subfolder of package XLConnect demoExcelFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(demoExcelFile) # Read named region 'mtcars' (with default header = TRUE) data <- readNamedRegion(wb, name = "mtcars") ## Example 2; # conversion xlsx file from demoFiles subfolder of package XLConnect excelFile <- system.file("demoFiles/conversion.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(excelFile) # Read named region 'conversion' with pre-specified column types # Note: in the worksheet all data was entered as strings! # forceConversion = TRUE is used to force conversion from String # into the less generic data types Numeric, DateTime & Boolean df <- readNamedRegion(wb, name = "conversion", header = TRUE, colTypes = c(XLC$DATA_TYPE.NUMERIC, XLC$DATA_TYPE.DATETIME, XLC$DATA_TYPE.BOOLEAN), forceConversion = TRUE, dateTimeFormat = "%Y-%m-%d %H:%M:%S") ## Example 3: # mtcars xlsx file from demoFiles subfolder of package XLConnect demoExcelFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(demoExcelFile) # Read the columns 1, 3 and 5 of the named region 'mtcars' (with default header = TRUE) data <- readNamedRegion(wb, name = "mtcars", keep=c(1,3,5)) # activate attributes (used by worksheet scope) options(XLConnect.setCustomAttributes = TRUE) # read the iris dataset from worksheet-scoped named region 'iris' wb <- loadWorkbook("demoFiles/iris.xlsx") data <- readNamedRegion(wb, name = "iris", worksheetScope = "iris") # show worksheet scope attribute attr(data, "worksheetScope") ## End(Not run)## Not run: ## Example 1: # mtcars xlsx file from demoFiles subfolder of package XLConnect demoExcelFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(demoExcelFile) # Read named region 'mtcars' (with default header = TRUE) data <- readNamedRegion(wb, name = "mtcars") ## Example 2; # conversion xlsx file from demoFiles subfolder of package XLConnect excelFile <- system.file("demoFiles/conversion.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(excelFile) # Read named region 'conversion' with pre-specified column types # Note: in the worksheet all data was entered as strings! # forceConversion = TRUE is used to force conversion from String # into the less generic data types Numeric, DateTime & Boolean df <- readNamedRegion(wb, name = "conversion", header = TRUE, colTypes = c(XLC$DATA_TYPE.NUMERIC, XLC$DATA_TYPE.DATETIME, XLC$DATA_TYPE.BOOLEAN), forceConversion = TRUE, dateTimeFormat = "%Y-%m-%d %H:%M:%S") ## Example 3: # mtcars xlsx file from demoFiles subfolder of package XLConnect demoExcelFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(demoExcelFile) # Read the columns 1, 3 and 5 of the named region 'mtcars' (with default header = TRUE) data <- readNamedRegion(wb, name = "mtcars", keep=c(1,3,5)) # activate attributes (used by worksheet scope) options(XLConnect.setCustomAttributes = TRUE) # read the iris dataset from worksheet-scoped named region 'iris' wb <- loadWorkbook("demoFiles/iris.xlsx") data <- readNamedRegion(wb, name = "iris", worksheetScope = "iris") # show worksheet scope attribute attr(data, "worksheetScope") ## End(Not run)
Reads named regions from an Excel file.
readNamedRegionFromFile(file, ...)readNamedRegionFromFile(file, ...)
file |
The file name of the workbook to read |
... |
Arguments passed to readNamedRegion |
This is a convenience wrapper to read named regions from a file
without creating an intermediate workbook object.
See readNamedRegion for more details.
Thomas Themel
Mirai Solutions GmbH https://mirai-solutions.ch
readNamedRegion,
readWorksheetFromFile,
writeNamedRegionToFile,writeWorksheetToFile,
onErrorCell
## Not run: # multiregion xlsx file from demoFiles subfolder of package XLConnect demoExcelFile <- system.file("demoFiles/multiregion.xlsx", package = "XLConnect") # Load a single named region into a single data.frame. df <- readNamedRegionFromFile(demoExcelFile, name="Iris") # Load multiple regions at once - returns a (named) list # of data.frames. df <- readNamedRegionFromFile(demoExcelFile, name=c("Calendar", "Iris", "IQ")) ## End(Not run)## Not run: # multiregion xlsx file from demoFiles subfolder of package XLConnect demoExcelFile <- system.file("demoFiles/multiregion.xlsx", package = "XLConnect") # Load a single named region into a single data.frame. df <- readNamedRegionFromFile(demoExcelFile, name="Iris") # Load multiple regions at once - returns a (named) list # of data.frames. df <- readNamedRegionFromFile(demoExcelFile, name=c("Calendar", "Iris", "IQ")) ## End(Not run)
Reads Excel tables (Office 2007+) from a workbook.
## S4 method for signature 'workbook,numeric' readTable(object, sheet, table, header, rownames, colTypes, forceConversion, dateTimeFormat, check.names, useCachedValues, keep, drop, simplify, readStrategy) ## S4 method for signature 'workbook,character' readTable(object, sheet, table, header, rownames, colTypes, forceConversion, dateTimeFormat, check.names, useCachedValues, keep, drop, simplify, readStrategy)## S4 method for signature 'workbook,numeric' readTable(object, sheet, table, header, rownames, colTypes, forceConversion, dateTimeFormat, check.names, useCachedValues, keep, drop, simplify, readStrategy) ## S4 method for signature 'workbook,character' readTable(object, sheet, table, header, rownames, colTypes, forceConversion, dateTimeFormat, check.names, useCachedValues, keep, drop, simplify, readStrategy)
object |
The |
sheet |
The index or name of the worksheet on which to look for the specified |
table |
The name of the table to read |
header |
The argument |
rownames |
Index ( |
colTypes |
Column types to use when reading in the data. Specified as a |
forceConversion |
|
dateTimeFormat |
Date/time format used when doing date/time conversions. Defaults to |
check.names |
|
useCachedValues |
|
keep |
List of column names or indices to be kept in the output data frame.
It is possible to specify either |
drop |
List of column names or indices to be dropped in the output data frame.
It is possible to specify either |
simplify |
|
readStrategy |
|
If no specific column types (see argument colTypes) are specified,
readNamedRegion tries to determine the resulting column types
based on the read cell types. If different cell types are found in a
specific column, the most general of those is used and mapped to the
corresponding R data type. The order of data types from least to most
general is Boolean (logical) < DateTime (POSIXct) <
Numeric (numeric) < String (character). E.g. if a column
is read that contains cells of type Boolean, Numeric and String then the
resulting column in R would be character since character
is the most general type.
Some additional information with respect to forcing data type conversion
using forceConversion = TRUE:
Forcing conversion from String to Boolean: TRUE is returned
if and only if the target string is "true" (ignoring any capitalization).
Any other string will return FALSE.
Forcing conversion from Numeric to DateTime: since Excel understands Dates/Times as Numerics with some additional formatting, a conversion from a Numeric to a DateTime is actually possible. Numerics in this case represent the number of days since 1900-01-01. Fractional days represent hours, minutes, and seconds.
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
Overview of Excel tables
https://support.microsoft.com/en-us/office/overview-of-excel-tables-7ab0bb7d-3a9e-4b56-a3c9-6c94334e492c?ocmsassetid=ha010048546&correlationid=ecf0d51a-596f-42e5-9c05-8653648bb180&ui=en-us&rs=en-us&ad=us
workbook,
readNamedRegion,
readWorksheet,
writeNamedRegion,writeWorksheet,
readNamedRegionFromFile,
onErrorCell
## Not run: # mtcars xlsx file from demoFiles subfolder of package XLConnect demoExcelFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(demoExcelFile) # Read table 'MtcarsTable' from sheet 'mtcars_table' data <- readTable(wb, sheet = "mtcars_table", table = "MtcarsTable") ## End(Not run)## Not run: # mtcars xlsx file from demoFiles subfolder of package XLConnect demoExcelFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(demoExcelFile) # Read table 'MtcarsTable' from sheet 'mtcars_table' data <- readTable(wb, sheet = "mtcars_table", table = "MtcarsTable") ## End(Not run)
Reads data from worksheets of a workbook.
## S4 method for signature 'workbook,numeric' readWorksheet(object,sheet,startRow,startCol,endRow,endCol,autofitRow,autofitCol, region,header,rownames,colTypes,forceConversion,dateTimeFormat,check.names, useCachedValues,keep,drop, simplify, readStrategy) ## S4 method for signature 'workbook,character' readWorksheet(object,sheet,startRow,startCol,endRow,endCol,autofitRow,autofitCol, region,header,rownames,colTypes,forceConversion,dateTimeFormat,check.names, useCachedValues,keep,drop, simplify, readStrategy)## S4 method for signature 'workbook,numeric' readWorksheet(object,sheet,startRow,startCol,endRow,endCol,autofitRow,autofitCol, region,header,rownames,colTypes,forceConversion,dateTimeFormat,check.names, useCachedValues,keep,drop, simplify, readStrategy) ## S4 method for signature 'workbook,character' readWorksheet(object,sheet,startRow,startCol,endRow,endCol,autofitRow,autofitCol, region,header,rownames,colTypes,forceConversion,dateTimeFormat,check.names, useCachedValues,keep,drop, simplify, readStrategy)
object |
The |
sheet |
The name or index of the worksheet to read from |
startRow |
The index of the first row to read from. Defaults to |
startCol |
The index of the first column to read from. Defaults to |
endRow |
The index of the last row to read from. Defaults to |
endCol |
The index of the last column to read from. Defaults to |
autofitRow |
|
autofitCol |
|
region |
A range specifier in the form 'A10:B18'. This provides an alternative way to specify
|
header |
Interpret the first row of the specified area as column
headers. The default is |
rownames |
Index ( |
colTypes |
Column types to use when reading in the data. Specified as a |
forceConversion |
|
dateTimeFormat |
Date/time format used when doing date/time conversions. Defaults to |
check.names |
|
useCachedValues |
|
keep |
Vector of column names or indices to be kept in the output data frame.
It is possible to specify either |
drop |
Vector of column names or indices to be dropped in the output data frame.
It is possible to specify either |
simplify |
|
readStrategy |
|
Reads data from the worksheet specified by sheet. Data is read
starting at the top left corner specified by startRow and
startCol down to the bottom right corner specified by
endRow and endCol. If header = TRUE, the first row
is interpreted as column names of the resulting data.frame.
If
startRow <= 0 then the first available row in the sheet is assumed.
If endRow = 0 then the last available row in the sheet is assumed.
For endRow = -n with n > 0, the 'last row' - n rows is assumed. This
is useful in cases where you want to skip the last n rows.
If startCol <= 0 then the minimum column between startRow and
endRow is assumed. If endCol = 0 then the maximum column between
startRow and endRow is assumed. If endCol = -n with n > 0,
the maximum column between startRow and endRow except for the last n
columns is assumed.
In other words, if no boundaries are specified readWorksheet assumes the "bounding box" of the data as the corresponding boundaries.
The arguments autofitRow and autofitCol (both defaulting to TRUE) can be used to skip leading and trailing empty rows even in case startRow, endRow, startCol and endCol are specified to values > 0. This can be useful if data is expected within certain given boundaries but the exact location is not available.
If all four coordinate arguments are missing this behaves as above with
startRow = 0, startCol = 0, endRow = 0 and
endCol = 0. In this case readWorksheet assumes the
"bounding box" of the data as the corresponding boundaries.
All arguments (except object) are vectorized. As such, multiple worksheets (and also multiple data regions
from the same worksheet) can be read with one method call. If only one single data region is read, the return value
is a data.frame. If multiple data regions are specified, the return value is a list of data.frame's
returned in the order they have been specified. If worksheets have been specified by name, the list will be a
named list named by the corresponding worksheets.
If no specific column types (see argument colTypes) are specified,
readWorksheet tries to determine the resulting column types based on the read cell types. If different cell
types are found in a specific column, the most general of those is used and mapped to the corresponding R data type.
The order of data types from least to most general is Boolean (logical) < DateTime (POSIXct) <
Numeric (numeric) < String (character). E.g. if a column is read that contains cells of type Boolean,
Numeric and String then the resulting column in R would be character since character is the most general
type.
Some additional information with respect to forcing data type conversion
using forceConversion = TRUE:
Forcing conversion from String to Boolean: TRUE is returned
if and only if the target string is "true" (ignoring any capitalization).
Any other string will return FALSE.
Forcing conversion from Numeric to DateTime: since Excel understands Dates/Times as Numerics with some additional formatting, a conversion from a Numeric to a DateTime is actually possible. Numerics in this case represent the number of days since 1900-01-00 (yes, day 00! - see https://web.archive.org/web/20240821110422/http://www.cpearson.com/excel/datetime.htm). Note that in R 0 is represented as 1899-12-31 since there is no 1900-01-00. Fractional days represent hours, minutes, and seconds.
Martin Studer
Thomas Themel
Nicola Lambiase
Mirai Solutions GmbH https://mirai-solutions.ch
workbook,
writeWorksheet,
readNamedRegion,
writeNamedRegion,readWorksheetFromFile,
readTable,
onErrorCell
## Not run: ## Example 1: # mtcars xlsx file from demoFiles subfolder of package XLConnect demoExcelFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(demoExcelFile) # Read worksheet 'mtcars' (providing no specific area bounds; # with default header = TRUE) data <- readWorksheet(wb, sheet = "mtcars") ## Example 2: # mtcars xlsx file from demoFiles subfolder of package XLConnect demoExcelFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(demoExcelFile) # Read worksheet 'mtcars' (providing area bounds; with default header = TRUE) data <- readWorksheet(wb, sheet = "mtcars", startRow = 1, startCol = 3, endRow = 15, endCol = 8) ## Example 3: # mtcars xlsx file from demoFiles subfolder of package XLConnect demoExcelFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(demoExcelFile) # Read worksheet 'mtcars' (providing area bounds using the region argument; # with default header = TRUE) data <- readWorksheet(wb, sheet = "mtcars", region = "C1:H15") ## Example 4: # conversion xlsx file from demoFiles subfolder of package XLConnect excelFile <- system.file("demoFiles/conversion.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(excelFile) # Read worksheet 'Conversion' with pre-specified column types # Note: in the worksheet all data was entered as strings! # forceConversion = TRUE is used to force conversion from String # into the less generic data types Numeric, DateTime & Boolean df <- readWorksheet(wb, sheet = "Conversion", header = TRUE, colTypes = c(XLC$DATA_TYPE.NUMERIC, XLC$DATA_TYPE.DATETIME, XLC$DATA_TYPE.BOOLEAN), forceConversion = TRUE, dateTimeFormat = "%Y-%m-%d %H:%M:%S") ## Example 5: # mtcars xlsx file from demoFiles subfolder of package XLConnect demoExcelFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(demoExcelFile) # Read the columns 1, 3 and 5 from the sheet 'mtcars' (with default header = TRUE) data <- readWorksheet(wb, sheet = "mtcars", keep=c(1,3,5)) ## End(Not run)## Not run: ## Example 1: # mtcars xlsx file from demoFiles subfolder of package XLConnect demoExcelFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(demoExcelFile) # Read worksheet 'mtcars' (providing no specific area bounds; # with default header = TRUE) data <- readWorksheet(wb, sheet = "mtcars") ## Example 2: # mtcars xlsx file from demoFiles subfolder of package XLConnect demoExcelFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(demoExcelFile) # Read worksheet 'mtcars' (providing area bounds; with default header = TRUE) data <- readWorksheet(wb, sheet = "mtcars", startRow = 1, startCol = 3, endRow = 15, endCol = 8) ## Example 3: # mtcars xlsx file from demoFiles subfolder of package XLConnect demoExcelFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(demoExcelFile) # Read worksheet 'mtcars' (providing area bounds using the region argument; # with default header = TRUE) data <- readWorksheet(wb, sheet = "mtcars", region = "C1:H15") ## Example 4: # conversion xlsx file from demoFiles subfolder of package XLConnect excelFile <- system.file("demoFiles/conversion.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(excelFile) # Read worksheet 'Conversion' with pre-specified column types # Note: in the worksheet all data was entered as strings! # forceConversion = TRUE is used to force conversion from String # into the less generic data types Numeric, DateTime & Boolean df <- readWorksheet(wb, sheet = "Conversion", header = TRUE, colTypes = c(XLC$DATA_TYPE.NUMERIC, XLC$DATA_TYPE.DATETIME, XLC$DATA_TYPE.BOOLEAN), forceConversion = TRUE, dateTimeFormat = "%Y-%m-%d %H:%M:%S") ## Example 5: # mtcars xlsx file from demoFiles subfolder of package XLConnect demoExcelFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(demoExcelFile) # Read the columns 1, 3 and 5 from the sheet 'mtcars' (with default header = TRUE) data <- readWorksheet(wb, sheet = "mtcars", keep=c(1,3,5)) ## End(Not run)
Reads data from worksheets in an Excel file.
readWorksheetFromFile(file, ...)readWorksheetFromFile(file, ...)
file |
The path name of the file to read from. |
... |
Arguments passed to readWorksheet |
See readWorksheet for more information.
Thomas Themel
Mirai Solutions GmbH https://mirai-solutions.ch
readWorksheet,
readNamedRegionFromFile,
writeWorksheetToFile,writeNamedRegionToFile,
onErrorCell
## Not run: # multiregion xlsx file from demoFiles subfolder of # package XLConnect demoExcelFile <- system.file("demoFiles/multiregion.xlsx", package = "XLConnect") # Read single area from first sheet of existing file, # "B2:C3" in Excel speak df.one <- readWorksheetFromFile(demoExcelFile, sheet = 1, header = FALSE, startCol = 2, startRow = 2, endCol = 3, endRow = 3) # Read three data sets in one from known positions dflist <- readWorksheetFromFile(demoExcelFile, sheet = c("FirstSheet", "FirstSheet", "SecondSheet"), header = TRUE, startRow = c(2,2,3), startCol = c(2,5,2), endCol = c(5,8,6), endRow = c(9,15,153)) ## End(Not run)## Not run: # multiregion xlsx file from demoFiles subfolder of # package XLConnect demoExcelFile <- system.file("demoFiles/multiregion.xlsx", package = "XLConnect") # Read single area from first sheet of existing file, # "B2:C3" in Excel speak df.one <- readWorksheetFromFile(demoExcelFile, sheet = 1, header = FALSE, startCol = 2, startRow = 2, endCol = 3, endRow = 3) # Read three data sets in one from known positions dflist <- readWorksheetFromFile(demoExcelFile, sheet = c("FirstSheet", "FirstSheet", "SecondSheet"), header = TRUE, startRow = c(2,2,3), startCol = c(2,5,2), endCol = c(5,8,6), endRow = c(9,15,153)) ## End(Not run)
Removes a named range reference from a workbook.
## S4 method for signature 'workbook' removeName(object,name,worksheetScope)## S4 method for signature 'workbook' removeName(object,name,worksheetScope)
object |
The |
name |
The name to delete |
worksheetScope |
Optional - the name of the worksheet in which the name is scoped; useful if different sheets have locally-scoped named ranges with the same name. |
Removes the named range reference name from the specified workbook object if it does exist.
Data in the referenced cells remains unchanged. Multiple names can be specified to be removed.
Use worksheetScope = "" to only target names defined in the global scope.
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
workbook,
createName,
existsName,getDefinedNames,
readNamedRegion,
writeNamedRegion
## Not run: # mtcars xlsx file from demoFiles subfolder of package XLConnect mtcarsFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(mtcarsFile) # Remove the named region called 'mtcars' from the above file # (this named region is defined as 'mtcars!$A$1:$K$33') removeName(wb, name = "mtcars") ## End(Not run)## Not run: # mtcars xlsx file from demoFiles subfolder of package XLConnect mtcarsFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(mtcarsFile) # Remove the named region called 'mtcars' from the above file # (this named region is defined as 'mtcars!$A$1:$K$33') removeName(wb, name = "mtcars") ## End(Not run)
Removes the split pane/freeze pane from the specified worksheet.
## S4 method for signature 'workbook,character' removePane(object,sheet) ## S4 method for signature 'workbook,numeric' removePane(object,sheet)## S4 method for signature 'workbook,character' removePane(object,sheet) ## S4 method for signature 'workbook,numeric' removePane(object,sheet)
object |
The |
sheet |
The name or index of the sheet from which to remove the split pane/freeze pane |
To keep an area of a worksheet visible while you scroll to another area of the worksheet, you can lock specific rows or columns in one area by freezing or splitting panes.
When you freeze panes, you keep specific rows or columns visible when you scroll in the worksheet. For example, you might want to keep row and column labels visible as you scroll.
When you split panes, you create separate worksheet areas that you can scroll within, while rows or columns in the non-scrolled area remain visible.
Nicola Lambiase
Mirai Solutions GmbH https://mirai-solutions.ch
How to create a freeze pane/split pane in Office 2007 https://support.microsoft.com/en-us/office/freeze-panes-to-lock-rows-and-columns-dab2ffc9-020d-4026-8121-67dd25f2508f?ocmsassetid=hp001217048&correlationid=b4f5baeb-b622-4487-a96f-514d2f00208a&ui=en-us&rs=en-us&ad=us
workbook
createFreezePane
createSplitPane
## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("removePaneTest.xlsx", create = TRUE) # Create a worksheet named 'Sheet1' createSheet(wb, name = "Sheet1") # Create a split pane on Sheet1, with coordinates (10000, 5000) expressed as 1/20th of a point, # 10 (-> J) as left column visible in right pane and 10 as top row visible in bottom pane createSplitPane(wb, "Sheet1", 10000, 5000, 10, 10) # Remove the split pane from Sheet1 removePane(wb, "Sheet1") # Save workbook (this actually writes the file to disk). Now the workbook has no split pane. saveWorkbook(wb) # clean up file.remove("removePaneTest.xlsx") ## End(Not run)## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("removePaneTest.xlsx", create = TRUE) # Create a worksheet named 'Sheet1' createSheet(wb, name = "Sheet1") # Create a split pane on Sheet1, with coordinates (10000, 5000) expressed as 1/20th of a point, # 10 (-> J) as left column visible in right pane and 10 as top row visible in bottom pane createSplitPane(wb, "Sheet1", 10000, 5000, 10, 10) # Remove the split pane from Sheet1 removePane(wb, "Sheet1") # Save workbook (this actually writes the file to disk). Now the workbook has no split pane. saveWorkbook(wb) # clean up file.remove("removePaneTest.xlsx") ## End(Not run)
Removes a worksheet from a workbook.
## S4 method for signature 'workbook,character' removeSheet(object,sheet) ## S4 method for signature 'workbook,numeric' removeSheet(object,sheet)## S4 method for signature 'workbook,character' removeSheet(object,sheet) ## S4 method for signature 'workbook,numeric' removeSheet(object,sheet)
object |
The |
sheet |
The name or index of the sheet to remove |
When removing a worksheet that is the currently active sheet then XLConnect resets
the active sheet to the first possible worksheet in the workbook.
Also note that deleting worksheets may result in invalid name references.
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
workbook, createSheet,
existsSheet, getSheets,
renameSheet, cloneSheet,
setActiveSheet
## Not run: # mtcars xlsx file from demoFiles subfolder of package XLConnect mtcarsFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(mtcarsFile) # Remove the worksheet called 'mtcars' from the above file removeSheet(wb, sheet = "mtcars") ## End(Not run)## Not run: # mtcars xlsx file from demoFiles subfolder of package XLConnect mtcarsFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(mtcarsFile) # Remove the worksheet called 'mtcars' from the above file removeSheet(wb, sheet = "mtcars") ## End(Not run)
Renames a worksheet from a workbook.
## S4 method for signature 'workbook,character' renameSheet(object,sheet,newName) ## S4 method for signature 'workbook,numeric' renameSheet(object,sheet,newName)## S4 method for signature 'workbook,character' renameSheet(object,sheet,newName) ## S4 method for signature 'workbook,numeric' renameSheet(object,sheet,newName)
object |
The |
sheet |
The name or index of the sheet to rename |
newName |
The new name of the sheet |
Note that renaming worksheets may result in invalid name references.
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
workbook, createSheet,
existsSheet, getSheets,
removeSheet, cloneSheet,
setActiveSheet
## Not run: # mtcars xlsx file from demoFiles subfolder of package XLConnect mtcarsFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(mtcarsFile) # Rename the worksheet called 'mtcars' from the above file to 'MyCars' renameSheet(wb, sheet = "mtcars", newName = "MyCars") ## End(Not run)## Not run: # mtcars xlsx file from demoFiles subfolder of package XLConnect mtcarsFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(mtcarsFile) # Rename the worksheet called 'mtcars' from the above file to 'MyCars' renameSheet(wb, sheet = "mtcars", newName = "MyCars") ## End(Not run)
Saves a workbook to the corresponding Excel file. This method actually writes the workbook object to disk.
## S4 method for signature 'workbook,missing' saveWorkbook(object,file) ## S4 method for signature 'workbook,character' saveWorkbook(object,file)## S4 method for signature 'workbook,missing' saveWorkbook(object,file) ## S4 method for signature 'workbook,character' saveWorkbook(object,file)
object |
The |
file |
The file to which to save the |
Saves the specified workbook object to disk.
As already mentioned in the documentation of the
workbook class, a workbook's
underlying Excel file is not saved (or being created in case the file
did not exist and create = TRUE has been specified) unless the
saveWorkbook method has been called on the object. This provides
more flexibility to the user to decide when changes are saved and also
provides better performance in that several changes can be written in
one go (normally at the end, rather than after every operation causing
the file to be rewritten again completely each time). This is due to the
fact that workbooks are manipulated in-memory and are only written to
disk with specifically calling saveWorkbook.
Further note that calling saveWorkbook more than once leads to an
exception. This is due to a current issue in the underlying POI libraries.
However, with XLConnect there should be no need to call saveWorkbook
more than once so virtually this is no issue.
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
## Not run: # Create a new workbook 'saveMe.xlsx' # (assuming the file to not exist already) wb <- loadWorkbook("saveMe.xlsx", create = TRUE) # Create a worksheet called 'mtcars' createSheet(wb, name = "mtcars") # Write built-in dataset 'mtcars' to sheet 'mtcars' created above writeWorksheet(wb, mtcars, sheet = "mtcars") # Save workbook - this actually writes the file 'saveMe.xlsx' to disk saveWorkbook(wb) # clean up file.remove("saveMe.xlsx") ## End(Not run)## Not run: # Create a new workbook 'saveMe.xlsx' # (assuming the file to not exist already) wb <- loadWorkbook("saveMe.xlsx", create = TRUE) # Create a worksheet called 'mtcars' createSheet(wb, name = "mtcars") # Write built-in dataset 'mtcars' to sheet 'mtcars' created above writeWorksheet(wb, mtcars, sheet = "mtcars") # Save workbook - this actually writes the file 'saveMe.xlsx' to disk saveWorkbook(wb) # clean up file.remove("saveMe.xlsx") ## End(Not run)
Sets the active worksheet of a workbook.
## S4 method for signature 'workbook,character' setActiveSheet(object,sheet) ## S4 method for signature 'workbook,numeric' setActiveSheet(object,sheet)## S4 method for signature 'workbook,character' setActiveSheet(object,sheet) ## S4 method for signature 'workbook,numeric' setActiveSheet(object,sheet)
object |
The |
sheet |
The name or index of the sheet to activate |
The active worksheet of a workbook is the worksheet that is displayed
when the corresponding Excel file is opened.
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
workbook, createSheet,
removeSheet, renameSheet,
existsSheet, getSheets
## Not run: # mtcars xlsx file from demoFiles subfolder of package XLConnect mtcarsFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(mtcarsFile) # Sets the active sheet to the sheet 'mtcars3' setActiveSheet(wb, sheet = "mtcars3") ## End(Not run)## Not run: # mtcars xlsx file from demoFiles subfolder of package XLConnect mtcarsFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(mtcarsFile) # Sets the active sheet to the sheet 'mtcars3' setActiveSheet(wb, sheet = "mtcars3") ## End(Not run)
Sets an auto-filter on a specified worksheet.
## S4 method for signature 'workbook,character' setAutoFilter(object,sheet,reference) ## S4 method for signature 'workbook,numeric' setAutoFilter(object,sheet,reference)## S4 method for signature 'workbook,character' setAutoFilter(object,sheet,reference) ## S4 method for signature 'workbook,numeric' setAutoFilter(object,sheet,reference)
object |
The |
sheet |
The name or index of the sheet on which to set the auto-filter |
reference |
A cell range specification ( |
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("autofilter.xlsx", create = TRUE) # Create a worksheet named 'mtcars' createSheet(wb, name = "mtcars") # Create a named region called 'mtcars' on the sheet called 'mtcars' createName(wb, name = "mtcars", formula = "mtcars!$A$1") # Write built-in data set 'mtcars' to the above defined named region # (using header = TRUE) writeNamedRegion(wb, mtcars, name = "mtcars") # Set an auto-filter for the named region written above setAutoFilter(wb, sheet = "mtcars", reference = aref("A1", dim(mtcars))) # Save workbook (this actually writes the file to disk) saveWorkbook(wb) # clean up file.remove("autofilter.xlsx") ## End(Not run)## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("autofilter.xlsx", create = TRUE) # Create a worksheet named 'mtcars' createSheet(wb, name = "mtcars") # Create a named region called 'mtcars' on the sheet called 'mtcars' createName(wb, name = "mtcars", formula = "mtcars!$A$1") # Write built-in data set 'mtcars' to the above defined named region # (using header = TRUE) writeNamedRegion(wb, mtcars, name = "mtcars") # Set an auto-filter for the named region written above setAutoFilter(wb, sheet = "mtcars", reference = aref("A1", dim(mtcars))) # Save workbook (this actually writes the file to disk) saveWorkbook(wb) # clean up file.remove("autofilter.xlsx") ## End(Not run)
Specifies borders for a cellstyle.
## S4 method for signature 'cellstyle' setBorder(object,side,type,color)## S4 method for signature 'cellstyle' setBorder(object,side,type,color)
object |
The |
side |
A vector with any combination of {"bottom", "left", "right", "top", "all"} |
type |
Specifies the border type to be used - it is normally
specified by a corresponding |
color |
Defines the border color and is normally also specified
via an |
Specifies the border for a cellstyle. Note that the arguments
type and color should be of the same length as side. In
other words, for each specified side there should be a
corresponding specification of type and color. If this is
not the case the arguments will be automatically replicated to the
length of side.
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
workbook, cellstyle, setCellStyle,
setStyleAction, XLC
## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("setBorder.xlsx", create = TRUE) # Create a worksheet createSheet(wb, name = "cellstyles") # Create a custom anonymous cell style cs <- createCellStyle(wb) # Specify the border for the cell style created above setBorder(cs, side = c("bottom", "right"), type = XLC$"BORDER.THICK", color = c(XLC$"COLOR.BLACK", XLC$"COLOR.RED")) # Set the cell style created above for the top left cell (A1) in the # 'cellstyles' worksheet setCellStyle(wb, sheet = "cellstyles", row = 1, col = 1, cellstyle = cs) # Save the workbook saveWorkbook(wb) # clean up file.remove("setBorder.xlsx") ## End(Not run)## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("setBorder.xlsx", create = TRUE) # Create a worksheet createSheet(wb, name = "cellstyles") # Create a custom anonymous cell style cs <- createCellStyle(wb) # Specify the border for the cell style created above setBorder(cs, side = c("bottom", "right"), type = XLC$"BORDER.THICK", color = c(XLC$"COLOR.BLACK", XLC$"COLOR.RED")) # Set the cell style created above for the top left cell (A1) in the # 'cellstyles' worksheet setCellStyle(wb, sheet = "cellstyles", row = 1, col = 1, cellstyle = cs) # Save the workbook saveWorkbook(wb) # clean up file.remove("setBorder.xlsx") ## End(Not run)
Sets cell formulas for specific cells in a workbook.
## S4 method for signature 'workbook,character' setCellFormula(object,sheet,row,col,formula) ## S4 method for signature 'workbook,numeric' setCellFormula(object,sheet,row,col,formula)## S4 method for signature 'workbook,character' setCellFormula(object,sheet,row,col,formula) ## S4 method for signature 'workbook,numeric' setCellFormula(object,sheet,row,col,formula)
object |
The |
sheet |
Name or index of the sheet the cell is on |
row |
Row index of the cell to edit |
col |
Column index of the cell to edit |
formula |
The formula to apply to the cell, without the initial
|
Note that the arguments are vectorized such that multiple cells can be set with one method call.
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("setCellFormula.xls", create = TRUE) # Create a sheet named 'mtcars' createSheet(wb, name = "mtcars") # Create a named region called 'mtcars' referring to the sheet # called 'mtcars' createName(wb, name = "mtcars", formula = "mtcars!$A$1") # Write built-in data set 'mtcars' to the above defined named region. writeNamedRegion(wb, mtcars, name = "mtcars") # Now, let us get Excel to calculate average weights. # Where did we write the dataset? corners <- getReferenceCoordinatesForName(wb, "mtcars") # Put the average under the wt column colIndex <- which(names(mtcars) == "wt") rowIndex <- corners[2,1] + 1 # Construct the input range & formula input <- paste(idx2cref(c(corners[1,1], colIndex, corners[2,1], colIndex)), collapse=":") formula <- paste("AVERAGE(", input, ")", sep="") setCellFormula(wb, "mtcars", rowIndex, colIndex, formula) # Save workbook (this actually writes the file to disk) saveWorkbook(wb) # clean up file.remove("setCellFormula.xls") ## End(Not run)## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("setCellFormula.xls", create = TRUE) # Create a sheet named 'mtcars' createSheet(wb, name = "mtcars") # Create a named region called 'mtcars' referring to the sheet # called 'mtcars' createName(wb, name = "mtcars", formula = "mtcars!$A$1") # Write built-in data set 'mtcars' to the above defined named region. writeNamedRegion(wb, mtcars, name = "mtcars") # Now, let us get Excel to calculate average weights. # Where did we write the dataset? corners <- getReferenceCoordinatesForName(wb, "mtcars") # Put the average under the wt column colIndex <- which(names(mtcars) == "wt") rowIndex <- corners[2,1] + 1 # Construct the input range & formula input <- paste(idx2cref(c(corners[1,1], colIndex, corners[2,1], colIndex)), collapse=":") formula <- paste("AVERAGE(", input, ")", sep="") setCellFormula(wb, "mtcars", rowIndex, colIndex, formula) # Save workbook (this actually writes the file to disk) saveWorkbook(wb) # clean up file.remove("setCellFormula.xls") ## End(Not run)
Sets cell styles for specific cells in a workbook.
## S4 method for signature 'workbook,missing,character' setCellStyle(object,formula,sheet,row,col,cellstyle) ## S4 method for signature 'workbook,missing,numeric' setCellStyle(object,formula,sheet,row,col,cellstyle) ## S4 method for signature 'workbook,character,missing' setCellStyle(object,formula,sheet,row,col,cellstyle)## S4 method for signature 'workbook,missing,character' setCellStyle(object,formula,sheet,row,col,cellstyle) ## S4 method for signature 'workbook,missing,numeric' setCellStyle(object,formula,sheet,row,col,cellstyle) ## S4 method for signature 'workbook,character,missing' setCellStyle(object,formula,sheet,row,col,cellstyle)
object |
The |
formula |
A formula specification in the form Sheet!B8:C17. Use either the argument |
sheet |
Name or index of the sheet the cell is on. Use either the argument |
row |
Row index of the cell to apply the cellstyle to. |
col |
Column index of the cell to apply the cellstyle to. |
cellstyle |
|
Sets the specified cellstyle for the specified cell
(row, col) on the specified sheet or alternatively for the cells referred to by formula. Note that the
arguments are vectorized such that multiple cells can be styled with one
method call. Use either the argument formula or the combination of sheet, row and col.
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
workbook,
cellstyle,
createCellStyle,
setDataFormat,
setBorder,setFillBackgroundColor,
setFillForegroundColor,
setFillPattern,setWrapText
## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("setCellStyle.xlsx", create = TRUE) # We don't set a specific style action in this demo, so the default # 'XLConnect' will be used (XLC$"STYLE_ACTION.XLCONNECT") # Create a sheet named 'mtcars' createSheet(wb, name = "mtcars") # Create a named region called 'mtcars' referring to the sheet # called 'mtcars' createName(wb, name = "mtcars", formula = "mtcars!$C$4") # Write built-in data set 'mtcars' to the above defined named region. # This will use the default style action 'XLConnect'. writeNamedRegion(wb, mtcars, name = "mtcars") # Now let's color all weight cells of cars with a weight > 3.5 in red # (mtcars$wt > 3.5) # First, create a corresponding (named) cell style heavyCar <- createCellStyle(wb, name = "HeavyCar") # Specify the cell style to use a solid foreground color setFillPattern(heavyCar, fill = XLC$"FILL.SOLID_FOREGROUND") # Specify the foreground color to be used setFillForegroundColor(heavyCar, color = XLC$"COLOR.RED") # Which cars have a weight > 3.5 ? rowIndex <- which(mtcars$wt > 3.5) # NOTE: The mtcars data.frame has been written offset with # top left cell C4 - and we have also written a header row! # So, let's take that into account appropriately. Obviously, # the two steps could be combined directly into one ... rowIndex <- rowIndex + 4 # The same holds for the column index colIndex <- which(names(mtcars) == "wt") + 2 # Set the 'HeavyCar' cell style for the corresponding cells. # Note: the row and col arguments are vectorized! setCellStyle(wb, sheet = "mtcars", row = rowIndex, col = colIndex, cellstyle = heavyCar) # Save workbook (this actually writes the file to disk) saveWorkbook(wb) # clean up file.remove("setCellStyle.xlsx") ## End(Not run)## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("setCellStyle.xlsx", create = TRUE) # We don't set a specific style action in this demo, so the default # 'XLConnect' will be used (XLC$"STYLE_ACTION.XLCONNECT") # Create a sheet named 'mtcars' createSheet(wb, name = "mtcars") # Create a named region called 'mtcars' referring to the sheet # called 'mtcars' createName(wb, name = "mtcars", formula = "mtcars!$C$4") # Write built-in data set 'mtcars' to the above defined named region. # This will use the default style action 'XLConnect'. writeNamedRegion(wb, mtcars, name = "mtcars") # Now let's color all weight cells of cars with a weight > 3.5 in red # (mtcars$wt > 3.5) # First, create a corresponding (named) cell style heavyCar <- createCellStyle(wb, name = "HeavyCar") # Specify the cell style to use a solid foreground color setFillPattern(heavyCar, fill = XLC$"FILL.SOLID_FOREGROUND") # Specify the foreground color to be used setFillForegroundColor(heavyCar, color = XLC$"COLOR.RED") # Which cars have a weight > 3.5 ? rowIndex <- which(mtcars$wt > 3.5) # NOTE: The mtcars data.frame has been written offset with # top left cell C4 - and we have also written a header row! # So, let's take that into account appropriately. Obviously, # the two steps could be combined directly into one ... rowIndex <- rowIndex + 4 # The same holds for the column index colIndex <- which(names(mtcars) == "wt") + 2 # Set the 'HeavyCar' cell style for the corresponding cells. # Note: the row and col arguments are vectorized! setCellStyle(wb, sheet = "mtcars", row = rowIndex, col = colIndex, cellstyle = heavyCar) # Save workbook (this actually writes the file to disk) saveWorkbook(wb) # clean up file.remove("setCellStyle.xlsx") ## End(Not run)
Sets the cell style for a specific data type as used by the DATATYPE style action.
## S4 method for signature 'workbook' setCellStyleForType(object,type,style)## S4 method for signature 'workbook' setCellStyleForType(object,type,style)
object |
The |
type |
The data type for which to set the |
style |
The |
Based on the (cell) data type the DATATYPE style action (see setStyleAction)
sets the cellstyle for the corresponding cells. The data type is normally specified via a
corresponding data type constant from the XLC object.
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
workbook, getCellStyleForType,
setStyleAction
## Not run: file.copy(system.file("demoFiles/template2.xlsx", package = "XLConnect"), "datatype.xlsx", overwrite = TRUE) # Load workbook wb <- loadWorkbook("datatype.xlsx") # Create a new cell style to be used cs <- createCellStyle(wb, name = "mystyle") # Set data format (number format) as numbers with aligned fractions setDataFormat(cs, format = "# ???/???") # Define the above created cell style as style to be used for # numerics setCellStyleForType(wb, type = XLC$"DATA_TYPE.NUMERIC", style = cs) # Could also say cs <- setCellStyleForType(wb, "numeric") # Set style action to 'datatype' setStyleAction(wb, XLC$"STYLE_ACTION.DATATYPE") # Write built-in data set 'mtcars' to the named region # 'mtcars' as defined by the Excel template. writeNamedRegion(wb, mtcars, name = "mtcars") # Save workbook saveWorkbook(wb) # clean up file.remove("datatype.xlsx") ## End(Not run)## Not run: file.copy(system.file("demoFiles/template2.xlsx", package = "XLConnect"), "datatype.xlsx", overwrite = TRUE) # Load workbook wb <- loadWorkbook("datatype.xlsx") # Create a new cell style to be used cs <- createCellStyle(wb, name = "mystyle") # Set data format (number format) as numbers with aligned fractions setDataFormat(cs, format = "# ???/???") # Define the above created cell style as style to be used for # numerics setCellStyleForType(wb, type = XLC$"DATA_TYPE.NUMERIC", style = cs) # Could also say cs <- setCellStyleForType(wb, "numeric") # Set style action to 'datatype' setStyleAction(wb, XLC$"STYLE_ACTION.DATATYPE") # Write built-in data set 'mtcars' to the named region # 'mtcars' as defined by the Excel template. writeNamedRegion(wb, mtcars, name = "mtcars") # Save workbook saveWorkbook(wb) # clean up file.remove("datatype.xlsx") ## End(Not run)
Sets the width of a column in a worksheet.
## S4 method for signature 'workbook,character' setColumnWidth(object,sheet,column,width) ## S4 method for signature 'workbook,numeric' setColumnWidth(object,sheet,column,width)## S4 method for signature 'workbook,character' setColumnWidth(object,sheet,column,width) ## S4 method for signature 'workbook,numeric' setColumnWidth(object,sheet,column,width)
object |
The |
sheet |
The name or index of the sheet |
column |
The index of the column to resize |
width |
The width of the specified column in units of 1/256th of a character width.
If |
Note that the arguments sheet, column and width are
vectorized. As such the column width of multiple columns (potentially on
different sheets) can be set with one method call.
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
## Not run: # mtcars xlsx file from demoFiles subfolder of package XLConnect mtcarsFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(mtcarsFile) # Sets the column width of the 3rd column on sheet 'mtcars' # to 4000/256th (= 15.625) character width setColumnWidth(wb, sheet = "mtcars", column = 3, width = 4000) ## End(Not run)## Not run: # mtcars xlsx file from demoFiles subfolder of package XLConnect mtcarsFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(mtcarsFile) # Sets the column width of the 3rd column on sheet 'mtcars' # to 4000/256th (= 15.625) character width setColumnWidth(wb, sheet = "mtcars", column = 3, width = 4000) ## End(Not run)
Specifies a custom data format for a cellstyle.
## S4 method for signature 'cellstyle' setDataFormat(object,format)## S4 method for signature 'cellstyle' setDataFormat(object,format)
object |
The |
format |
A data format string |
Specifies the data format to be used by the corresponding
cellstyle. Data formats are specified the standard
Excel way. Refer to the Excel help or to the link below for more
information.
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
workbook, cellstyle, setCellStyle,
setStyleAction
## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("setDataFormat.xlsx", create = TRUE) # Create a worksheet createSheet(wb, name = "cellstyles") # Create a dummy data set with the current date/time (as POSIXct) now <- data.frame(Now = Sys.time()) # Write the value to the 'cellstyles' worksheet in the top left # corner (cell A1) writeWorksheet(wb, now, sheet = "cellstyles", startRow = 1, startCol = 1, header = FALSE) # Create a custom anonymous cell style cs <- createCellStyle(wb) # Specify a custom data format setDataFormat(cs, format = "dddd d-m-yyyy h:mm AM/PM") # Set the cell style created above for the top left cell (A1) in # the 'cellstyles' worksheet setCellStyle(wb, sheet = "cellstyles", row = 1, col = 1, cellstyle = cs) # Set column width to display whole time/date string setColumnWidth(wb, sheet = "cellstyles", column = 1, width = 6000) # Save the workbook saveWorkbook(wb) # clean up file.remove("setDataFormat.xlsx") ## End(Not run)## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("setDataFormat.xlsx", create = TRUE) # Create a worksheet createSheet(wb, name = "cellstyles") # Create a dummy data set with the current date/time (as POSIXct) now <- data.frame(Now = Sys.time()) # Write the value to the 'cellstyles' worksheet in the top left # corner (cell A1) writeWorksheet(wb, now, sheet = "cellstyles", startRow = 1, startCol = 1, header = FALSE) # Create a custom anonymous cell style cs <- createCellStyle(wb) # Specify a custom data format setDataFormat(cs, format = "dddd d-m-yyyy h:mm AM/PM") # Set the cell style created above for the top left cell (A1) in # the 'cellstyles' worksheet setCellStyle(wb, sheet = "cellstyles", row = 1, col = 1, cellstyle = cs) # Set column width to display whole time/date string setColumnWidth(wb, sheet = "cellstyles", column = 1, width = 6000) # Save the workbook saveWorkbook(wb) # clean up file.remove("setDataFormat.xlsx") ## End(Not run)
Sets the data format for a specific data type as used by the DATA_FORMAT_ONLY style action.
## S4 method for signature 'workbook' setDataFormatForType(object,type,format)## S4 method for signature 'workbook' setDataFormatForType(object,type,format)
object |
The |
type |
The data type for which to set the |
format |
A data format string |
Based on the (cell) data type the DATA_FORMAT_ONLY style action (see setStyleAction)
sets the data format for the corresponding cells. The data type is normally specified via a
corresponding data type constant from the XLC object. Data formats are specified the standard
Excel way. Refer to the Excel help or to the link below for more information.
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
## Not run: # Copy existing Excel template to working directory file.copy(system.file("demoFiles/template2.xlsx", package = "XLConnect"), "dataformat.xlsx", overwrite = TRUE) # Load workbook wb <- loadWorkbook("dataformat.xlsx") # Set the data format for numeric columns (cells) # (keeping the defaults for all other data types) setDataFormatForType(wb, type = XLC$"DATA_TYPE.NUMERIC", format = "0.00") # Set style action to 'data format only' setStyleAction(wb, XLC$"STYLE_ACTION.DATA_FORMAT_ONLY") # Write built-in data set 'mtcars' to the named region # 'mtcars' as defined by the Excel template. writeNamedRegion(wb, mtcars, name = "mtcars") # Save workbook saveWorkbook(wb) # clean up file.remove("dataformat.xlsx") ## End(Not run)## Not run: # Copy existing Excel template to working directory file.copy(system.file("demoFiles/template2.xlsx", package = "XLConnect"), "dataformat.xlsx", overwrite = TRUE) # Load workbook wb <- loadWorkbook("dataformat.xlsx") # Set the data format for numeric columns (cells) # (keeping the defaults for all other data types) setDataFormatForType(wb, type = XLC$"DATA_TYPE.NUMERIC", format = "0.00") # Set style action to 'data format only' setStyleAction(wb, XLC$"STYLE_ACTION.DATA_FORMAT_ONLY") # Write built-in data set 'mtcars' to the named region # 'mtcars' as defined by the Excel template. writeNamedRegion(wb, mtcars, name = "mtcars") # Save workbook saveWorkbook(wb) # clean up file.remove("dataformat.xlsx") ## End(Not run)
Specifies the fill background color for a cellstyle.
## S4 method for signature 'cellstyle,numeric' setFillBackgroundColor(object,color)## S4 method for signature 'cellstyle,numeric' setFillBackgroundColor(object,color)
object |
The |
color |
The fill background color to use for the |
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
workbook, cellstyle, setCellStyle,
setStyleAction, XLC
## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("setFillBackgroundColor.xlsx", create = TRUE) # Create a worksheet createSheet(wb, name = "cellstyles") # Create a custom anonymous cell style cs <- createCellStyle(wb) # Specify the fill background color for the cell style created above setFillBackgroundColor(cs, color = XLC$"COLOR.CORNFLOWER_BLUE") # Specify the fill foreground color setFillForegroundColor(cs, color = XLC$"COLOR.YELLOW") # Specify the fill pattern setFillPattern(cs, fill = XLC$"FILL.BIG_SPOTS") # Set the cell style created above for the top left cell (A1) in the # 'cellstyles' worksheet setCellStyle(wb, sheet = "cellstyles", row = 1, col = 1, cellstyle = cs) # Save the workbook saveWorkbook(wb) # clean up file.remove("setFillBackgroundColor.xlsx") ## End(Not run)## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("setFillBackgroundColor.xlsx", create = TRUE) # Create a worksheet createSheet(wb, name = "cellstyles") # Create a custom anonymous cell style cs <- createCellStyle(wb) # Specify the fill background color for the cell style created above setFillBackgroundColor(cs, color = XLC$"COLOR.CORNFLOWER_BLUE") # Specify the fill foreground color setFillForegroundColor(cs, color = XLC$"COLOR.YELLOW") # Specify the fill pattern setFillPattern(cs, fill = XLC$"FILL.BIG_SPOTS") # Set the cell style created above for the top left cell (A1) in the # 'cellstyles' worksheet setCellStyle(wb, sheet = "cellstyles", row = 1, col = 1, cellstyle = cs) # Save the workbook saveWorkbook(wb) # clean up file.remove("setFillBackgroundColor.xlsx") ## End(Not run)
Specifies the fill foreground color for a cellstyle.
## S4 method for signature 'cellstyle,numeric' setFillForegroundColor(object,color)## S4 method for signature 'cellstyle,numeric' setFillForegroundColor(object,color)
object |
The |
color |
The fill foreground color to use for the |
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
workbook, cellstyle, setCellStyle,
setStyleAction, XLC
## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("setFillForegroundColor.xlsx", create = TRUE) # Create a worksheet createSheet(wb, name = "cellstyles") # Create a custom anonymous cell style cs <- createCellStyle(wb) # Specify the fill background color for the cell style created above setFillBackgroundColor(cs, color = XLC$"COLOR.CORNFLOWER_BLUE") # Specify the fill foreground color setFillForegroundColor(cs, color = XLC$"COLOR.YELLOW") # Specify the fill pattern setFillPattern(cs, fill = XLC$"FILL.BIG_SPOTS") # Set the cell style created above for the top left cell (A1) in the # 'cellstyles' worksheet setCellStyle(wb, sheet = "cellstyles", row = 1, col = 1, cellstyle = cs) # Save the workbook saveWorkbook(wb) # clean up file.remove("setFillForegroundColor.xlsx") ## End(Not run)## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("setFillForegroundColor.xlsx", create = TRUE) # Create a worksheet createSheet(wb, name = "cellstyles") # Create a custom anonymous cell style cs <- createCellStyle(wb) # Specify the fill background color for the cell style created above setFillBackgroundColor(cs, color = XLC$"COLOR.CORNFLOWER_BLUE") # Specify the fill foreground color setFillForegroundColor(cs, color = XLC$"COLOR.YELLOW") # Specify the fill pattern setFillPattern(cs, fill = XLC$"FILL.BIG_SPOTS") # Set the cell style created above for the top left cell (A1) in the # 'cellstyles' worksheet setCellStyle(wb, sheet = "cellstyles", row = 1, col = 1, cellstyle = cs) # Save the workbook saveWorkbook(wb) # clean up file.remove("setFillForegroundColor.xlsx") ## End(Not run)
Specifies the fill pattern for a cellstyle.
## S4 method for signature 'cellstyle' setFillPattern(object,fill)## S4 method for signature 'cellstyle' setFillPattern(object,fill)
object |
The |
fill |
The fill pattern to use for the |
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
workbook, cellstyle, setCellStyle,
setStyleAction, XLC
## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("setFillPattern.xlsx", create = TRUE) # Create a worksheet createSheet(wb, name = "cellstyles") # Create a custom anonymous cell style cs <- createCellStyle(wb) # Specify the fill background color for the cell style created above setFillBackgroundColor(cs, color = XLC$"COLOR.CORNFLOWER_BLUE") # Specify the fill foreground color setFillForegroundColor(cs, color = XLC$"COLOR.YELLOW") # Specify the fill pattern setFillPattern(cs, fill = XLC$"FILL.BIG_SPOTS") # Set the cell style created above for the top left cell (A1) in the # 'cellstyles' worksheet setCellStyle(wb, sheet = "cellstyles", row = 1, col = 1, cellstyle = cs) # Save the workbook saveWorkbook(wb) # clean up file.remove("setFillPattern.xlsx") ## End(Not run)## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("setFillPattern.xlsx", create = TRUE) # Create a worksheet createSheet(wb, name = "cellstyles") # Create a custom anonymous cell style cs <- createCellStyle(wb) # Specify the fill background color for the cell style created above setFillBackgroundColor(cs, color = XLC$"COLOR.CORNFLOWER_BLUE") # Specify the fill foreground color setFillForegroundColor(cs, color = XLC$"COLOR.YELLOW") # Specify the fill pattern setFillPattern(cs, fill = XLC$"FILL.BIG_SPOTS") # Set the cell style created above for the top left cell (A1) in the # 'cellstyles' worksheet setCellStyle(wb, sheet = "cellstyles", row = 1, col = 1, cellstyle = cs) # Save the workbook saveWorkbook(wb) # clean up file.remove("setFillPattern.xlsx") ## End(Not run)
This function controls a flag that forces Excel to recalculate formula values when a workbook is opened.
## S4 method for signature 'workbook,character' setForceFormulaRecalculation(object,sheet,value) ## S4 method for signature 'workbook,numeric' setForceFormulaRecalculation(object,sheet,value)## S4 method for signature 'workbook,character' setForceFormulaRecalculation(object,sheet,value) ## S4 method for signature 'workbook,numeric' setForceFormulaRecalculation(object,sheet,value)
object |
The |
sheet |
The name or index of the sheet for which to force formula recalculation.
If |
value |
|
The arguments sheet and value are vectorized such
that multiple worksheets can be controlled with one method call.
A typical use for this flag is forcing Excel into updating formulas
that reference cells affected by writeWorksheet
or writeNamedRegion.
The exact behavior of Excel when the flag is set depends on version and file format.
Thomas Themel
Mirai Solutions GmbH https://mirai-solutions.ch
workbook,
getForceFormulaRecalculation
## Not run: # mtcars xlsx file from demoFiles subfolder of package XLConnect demoExcelFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(demoExcelFile) # Tell Excel to automatically recalculate formulas on sheet mtcars setForceFormulaRecalculation(wb, sheet = "mtcars", TRUE) # The same with a numerical sheet index setForceFormulaRecalculation(wb, sheet = 1, TRUE) ## End(Not run)## Not run: # mtcars xlsx file from demoFiles subfolder of package XLConnect demoExcelFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(demoExcelFile) # Tell Excel to automatically recalculate formulas on sheet mtcars setForceFormulaRecalculation(wb, sheet = "mtcars", TRUE) # The same with a numerical sheet index setForceFormulaRecalculation(wb, sheet = 1, TRUE) ## End(Not run)
Sets hyperlinks for specific cells in a workbook.
## S4 method for signature 'workbook,missing,character' setHyperlink(object,formula,sheet,row,col,type,address) ## S4 method for signature 'workbook,missing,numeric' setHyperlink(object,formula,sheet,row,col,type,address) ## S4 method for signature 'workbook,character,missing' setHyperlink(object,formula,sheet,row,col,type,address)## S4 method for signature 'workbook,missing,character' setHyperlink(object,formula,sheet,row,col,type,address) ## S4 method for signature 'workbook,missing,numeric' setHyperlink(object,formula,sheet,row,col,type,address) ## S4 method for signature 'workbook,character,missing' setHyperlink(object,formula,sheet,row,col,type,address)
object |
The |
formula |
A formula specification in the form Sheet!B8:C17. Use either the argument |
sheet |
Name or index of the sheet the cell is on. Use either the argument |
row |
Row index of the cell to apply the cellstyle to. |
col |
Column index of the cell to apply the cellstyle to. |
type |
Hyperlink type. See the corresponding "HYPERLINK.*" constants from the |
address |
Hyperlink address. This needs to be a valid URI including scheme. E.g. for email |
Sets a hyperlink for the specified cells. Note that cellstyles for hyperlinks can be defined independently using
setCellStyle. The arguments are vectorized such that multiple hyperlinks can be set in one
method call. Use either the argument formula or the combination of sheet, row and col.
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("setHyperlink.xlsx", create = TRUE) # Create a sheet named 'mtcars' createSheet(wb, name = "mtcars") # Write built-in data set 'mtcars' to the above defined worksheet writeWorksheet(wb, mtcars, sheet = "mtcars", rownames = "Car") # Set hyperlinks links <- paste0("https://www.google.com?q=", gsub(" ", "+", rownames(mtcars))) setHyperlink(wb, sheet = "mtcars", row = seq_len(nrow(mtcars)) + 1, col = 1, type = XLC$HYPERLINK.URL, address = links) # Save workbook (this actually writes the file to disk) saveWorkbook(wb) # clean up file.remove("setHyperlink.xlsx") ## End(Not run)## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("setHyperlink.xlsx", create = TRUE) # Create a sheet named 'mtcars' createSheet(wb, name = "mtcars") # Write built-in data set 'mtcars' to the above defined worksheet writeWorksheet(wb, mtcars, sheet = "mtcars", rownames = "Car") # Set hyperlinks links <- paste0("https://www.google.com?q=", gsub(" ", "+", rownames(mtcars))) setHyperlink(wb, sheet = "mtcars", row = seq_len(nrow(mtcars)) + 1, col = 1, type = XLC$HYPERLINK.URL, address = links) # Save workbook (this actually writes the file to disk) saveWorkbook(wb) # clean up file.remove("setHyperlink.xlsx") ## End(Not run)
Defines the set of missing values (character or numeric) used when reading and writing data.
## S4 method for signature 'workbook,ANY' setMissingValue(object,value)## S4 method for signature 'workbook,ANY' setMissingValue(object,value)
object |
The |
value |
|
If there are no specific missing value identifiers defined the default behavior is
to map missing values to blank (empty) cells. Otherwise, each string or numeric cell is
checked if it matches one of the defined missing value identifiers. In addition, the first
missing value identifier (i.e. the first element of the value argument) is used to represent missing values when writing data.
Note that the missing value identifiers have to be either character or numeric.
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
workbook, writeNamedRegion,
writeWorksheet
## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("missingValue.xlsx", create = TRUE) # Create a worksheet named 'airquality' createSheet(wb, name = "airquality") # Create a named region called 'airquality' on the sheet called # 'airquality' createName(wb, name = "airquality", formula = "airquality!$A$1") # Set the missing value string to 'missing' setMissingValue(wb, value = "missing") # Write built-in data set 'airquality' to the above defined named region writeNamedRegion(wb, airquality, name = "airquality") # Save workbook saveWorkbook(wb) # clean up file.remove("missingValue.xlsx") ## End(Not run)## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("missingValue.xlsx", create = TRUE) # Create a worksheet named 'airquality' createSheet(wb, name = "airquality") # Create a named region called 'airquality' on the sheet called # 'airquality' createName(wb, name = "airquality", formula = "airquality!$A$1") # Set the missing value string to 'missing' setMissingValue(wb, value = "missing") # Write built-in data set 'airquality' to the above defined named region writeNamedRegion(wb, airquality, name = "airquality") # Save workbook saveWorkbook(wb) # clean up file.remove("missingValue.xlsx") ## End(Not run)
Sets the height of a row in a worksheet.
## S4 method for signature 'workbook,character' setRowHeight(object,sheet,row,height) ## S4 method for signature 'workbook,numeric' setRowHeight(object,sheet,row,height)## S4 method for signature 'workbook,character' setRowHeight(object,sheet,row,height) ## S4 method for signature 'workbook,numeric' setRowHeight(object,sheet,row,height)
object |
The |
sheet |
The name or index of the sheet to edit |
row |
The index of the row to resize |
height |
The height in points. If |
Note that the arguments sheet, row and height are
vectorized. As such the row height of multiple rows (potentially on
different worksheets) can be set with one method call.
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
## Not run: # mtcars xlsx file from demoFiles subfolder of package XLConnect mtcarsFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(mtcarsFile) # Sets the row height of the 1st row on sheet 'mtcars' # to 20 points setRowHeight(wb, sheet = "mtcars", row = 1, height = 20) ## End(Not run)## Not run: # mtcars xlsx file from demoFiles subfolder of package XLConnect mtcarsFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(mtcarsFile) # Sets the row height of the 1st row on sheet 'mtcars' # to 20 points setRowHeight(wb, sheet = "mtcars", row = 1, height = 20) ## End(Not run)
Sets a color on a specified worksheet tab. This only works for xlsx files.
## S4 method for signature 'workbook,character' setSheetColor(object,sheet,color) ## S4 method for signature 'workbook,numeric' setSheetColor(object,sheet,color)## S4 method for signature 'workbook,character' setSheetColor(object,sheet,color) ## S4 method for signature 'workbook,numeric' setSheetColor(object,sheet,color)
object |
The |
sheet |
The name or index of the sheet on which to set the tab color |
color |
The color to use for the sheet tab. The color is normally specified
via a corresponding color constant from the |
Nicola Lambiase
Mirai Solutions GmbH https://mirai-solutions.ch
## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("sheetcolor.xlsx", create = TRUE) # Create a worksheet named 'Sheet1' createSheet(wb, name = "Sheet1") # Set the "Sheet1" tab color as red setSheetColor(wb, "Sheet1", XLC$COLOR.RED) # Create a worksheet named 'Sheet2' createSheet(wb, name = "Sheet2") # Set the tab color of the second workbook sheet as green setSheetColor(wb, 2, XLC$COLOR.GREEN) # Save workbook (this actually writes the file to disk) saveWorkbook(wb) # clean up file.remove("sheetcolor.xlsx") ## End(Not run)## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("sheetcolor.xlsx", create = TRUE) # Create a worksheet named 'Sheet1' createSheet(wb, name = "Sheet1") # Set the "Sheet1" tab color as red setSheetColor(wb, "Sheet1", XLC$COLOR.RED) # Create a worksheet named 'Sheet2' createSheet(wb, name = "Sheet2") # Set the tab color of the second workbook sheet as green setSheetColor(wb, 2, XLC$COLOR.GREEN) # Save workbook (this actually writes the file to disk) saveWorkbook(wb) # clean up file.remove("sheetcolor.xlsx") ## End(Not run)
Sets the position of a worksheets in a workbook.
## S4 method for signature 'workbook,character,numeric' setSheetPos(object,sheet,pos)## S4 method for signature 'workbook,character,numeric' setSheetPos(object,sheet,pos)
object |
The |
sheet |
The name of the worksheet ( |
pos |
The position index to set for the corresponding |
It is important to note that the worksheet positions will be applied one after the other in the order they have been specified.
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
workbook, getSheetPos,
getSheets
## Not run: # mtcars xlsx file from demoFiles subfolder of package XLConnect demoExcelFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(demoExcelFile) # Move the 'mtcars3' worksheet to the front setSheetPos(wb, sheet = "mtcars3", pos = 1) ## End(Not run)## Not run: # mtcars xlsx file from demoFiles subfolder of package XLConnect demoExcelFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect") # Load workbook wb <- loadWorkbook(demoExcelFile) # Move the 'mtcars3' worksheet to the front setSheetPos(wb, sheet = "mtcars3", pos = 1) ## End(Not run)
Controls the application of cellstyles when writing data to Excel.
## S4 method for signature 'workbook' setStyleAction(object,type)## S4 method for signature 'workbook' setStyleAction(object,type)
object |
The |
type |
Defines the style action to be used when writing data
( |
The following style actions are supported:
XLC$"STYLE_ACTION.XLCONNECT": This is the
default. data.frame headers (if specified to be written) are
colored in solid light grey (25 percent). character, numeric and
logical vectors are written using Excel's "General" data
format. Time/date vectors e.g. Date or POSIXt) are
written with the "mm/dd/yyyy hh:mm:ss" data format. All cells are
specified to wrap the text if necessary. The corresponding custom cell
styles are called XLConnect.Header, XLConnect.String,
XLConnect.Numeric, XLConnect.Boolean and XLConnect.Date.
XLC$"STYLE_ACTION.DATATYPE": This style action instructs
XLConnect to apply cellstyles per data type as
set by the setCellStyleForType
methods. In contrast to the XLC$"STYLE_ACTION.DATA_FORMAT_ONLY"
style action (see below) which only sets a data format to an existing
cell style, this action actually sets a new cellstyle.
XLC$"STYLE_ACTION.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.
XLC$"STYLE_ACTION.PREDEFINED": This style action
instructs XLConnect to use existing (predefined) cellstyles when
writing headers and columns. This is useful in a template-based approach
where an Excel template with predefined cellstyles for headers and
columns is available. Normally, this would be used when the column
dimensions (and potentially also the row dimensions) of the data tables
are known up-front and as such a layout and corresponding cell styles
can be pre-specified.
If a data.frame is written including
its header, it is assumed that the Excel file being written to has
predefined cellstyles in the header row. Furthermore, the first row of
data is assumed to contain the cell styles to be replicated for any
additional rows. As such, this style action may only be useful if the
same column cell style should be applied across all rows. Please refer
to the available demos for some examples.
XLC$"STYLE_ACTION.NAME_PREFIX": This style action instructs
XLConnect to look for custom (named) cellstyles with a specified
prefix when writing columns and headers. This style name prefix can be
set via the method setStyleNamePrefix.
For column headers, it first checks if there is a cell style named
<STYLE_NAME_PREFIX>.Header.<COLUMN_NAME>.
If there is no
such cell style, it checks for a cell style named
<STYLE_NAME_PREFIX>.Header.<COLUMN_INDEX>.
Again, if
there is no such cell style, it checks for
<STYLE_NAME_PREFIX>.Header
(no specific column
discrimination). As a final resort, it just takes the workbook default
cell style.
For columns, XLConnect first checks the availability of a cell
style named
<STYLE_NAME_PREFIX>.Column.<COLUMN_NAME>.
If
there is no such cell style, it checks for
<STYLE_NAME_PREFIX>.Column.<COLUMN_INDEX>.
If again there
is no such cell style, it checks for
<STYLE_NAME_PREFIX>.Column.<COLUMN_DATA_TYPE>
with
<COLUMN_DATA_TYPE> being the corresponding data type from the
set: {Numeric, String, Boolean, DateTime}. As a last resort,
it would make use of the workbook's default cell style.
XLC$"STYLE_ACTION.DATA_FORMAT_ONLY": 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 apply is determined
by the data type of the cell (which is in turn determined by the corresponding R data
type). The data format for a specific type can be set via the method
setDataFormatForType. The default data format
is "General" for the data types Numeric, String and Boolean and is
"mm/dd/yyyy hh:mm:ss" for the data type DateTime.
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
workbook, cellstyle, createCellStyle,
writeNamedRegion, writeWorksheet, setStyleNamePrefix,
setCellStyleForType,
setDataFormatForType
## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("styleaction.xlsx", create = TRUE) # Set style action to 'name prefix' setStyleAction(wb, XLC$"STYLE_ACTION.NAME_PREFIX") # Set the name prefix to 'MyPersonalStyle' setStyleNamePrefix(wb, "MyPersonalStyle") # We now create a named cell style to be used for the header # (column names) of a data.frame headerCellStyle <- createCellStyle(wb, name = "MyPersonalStyle.Header") # Specify the cell style to use a solid foreground color setFillPattern(headerCellStyle, fill = XLC$"FILL.SOLID_FOREGROUND") # Specify the foreground color to be used setFillForegroundColor(headerCellStyle, color = XLC$"COLOR.LIGHT_CORNFLOWER_BLUE") # Specify a thick black bottom border setBorder(headerCellStyle, side = "bottom", type = XLC$"BORDER.THICK", color = XLC$"COLOR.BLACK") # We now create a named cell style to be used for # the column named 'wt' (as you will see below, we will # write the built-in data.frame 'mtcars') wtColumnCellStyle <- createCellStyle(wb, name = "MyPersonalStyle.Column.wt") # Specify the cell style to use a solid foreground color setFillPattern(wtColumnCellStyle, fill = XLC$"FILL.SOLID_FOREGROUND") # Specify the foreground color to be used setFillForegroundColor(wtColumnCellStyle, color = XLC$"COLOR.LIGHT_ORANGE") # We now create a named cell style to be used for # the 3rd column in the data.frame wtColumnCellStyle <- createCellStyle(wb, name = "MyPersonalStyle.Column.3") # Specify the cell style to use a solid foreground color setFillPattern(wtColumnCellStyle, fill = XLC$"FILL.SOLID_FOREGROUND") # Specify the foreground color to be used setFillForegroundColor(wtColumnCellStyle, color = XLC$"COLOR.LIME") # Create a sheet named 'mtcars' createSheet(wb, name = "mtcars") # Create a named region called 'mtcars' referring to # the sheet called 'mtcars' createName(wb, name = "mtcars", formula = "mtcars!$A$1") # Write built-in data set 'mtcars' to the above defined named region. # The style action 'name prefix' will be used when writing the data # as defined above. writeNamedRegion(wb, mtcars, name = "mtcars") # Save workbook (this actually writes the file to disk) saveWorkbook(wb) # clean up file.remove("styleaction.xlsx") ## End(Not run)## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("styleaction.xlsx", create = TRUE) # Set style action to 'name prefix' setStyleAction(wb, XLC$"STYLE_ACTION.NAME_PREFIX") # Set the name prefix to 'MyPersonalStyle' setStyleNamePrefix(wb, "MyPersonalStyle") # We now create a named cell style to be used for the header # (column names) of a data.frame headerCellStyle <- createCellStyle(wb, name = "MyPersonalStyle.Header") # Specify the cell style to use a solid foreground color setFillPattern(headerCellStyle, fill = XLC$"FILL.SOLID_FOREGROUND") # Specify the foreground color to be used setFillForegroundColor(headerCellStyle, color = XLC$"COLOR.LIGHT_CORNFLOWER_BLUE") # Specify a thick black bottom border setBorder(headerCellStyle, side = "bottom", type = XLC$"BORDER.THICK", color = XLC$"COLOR.BLACK") # We now create a named cell style to be used for # the column named 'wt' (as you will see below, we will # write the built-in data.frame 'mtcars') wtColumnCellStyle <- createCellStyle(wb, name = "MyPersonalStyle.Column.wt") # Specify the cell style to use a solid foreground color setFillPattern(wtColumnCellStyle, fill = XLC$"FILL.SOLID_FOREGROUND") # Specify the foreground color to be used setFillForegroundColor(wtColumnCellStyle, color = XLC$"COLOR.LIGHT_ORANGE") # We now create a named cell style to be used for # the 3rd column in the data.frame wtColumnCellStyle <- createCellStyle(wb, name = "MyPersonalStyle.Column.3") # Specify the cell style to use a solid foreground color setFillPattern(wtColumnCellStyle, fill = XLC$"FILL.SOLID_FOREGROUND") # Specify the foreground color to be used setFillForegroundColor(wtColumnCellStyle, color = XLC$"COLOR.LIME") # Create a sheet named 'mtcars' createSheet(wb, name = "mtcars") # Create a named region called 'mtcars' referring to # the sheet called 'mtcars' createName(wb, name = "mtcars", formula = "mtcars!$A$1") # Write built-in data set 'mtcars' to the above defined named region. # The style action 'name prefix' will be used when writing the data # as defined above. writeNamedRegion(wb, mtcars, name = "mtcars") # Save workbook (this actually writes the file to disk) saveWorkbook(wb) # clean up file.remove("styleaction.xlsx") ## End(Not run)
Sets the style name prefix for the "name prefix" style action.
## S4 method for signature 'workbook' setStyleNamePrefix(object,prefix)## S4 method for signature 'workbook' setStyleNamePrefix(object,prefix)
object |
The |
prefix |
The name prefix |
Sets the prefix for the "name prefix" style action. See the method setStyleAction for more information.
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
workbook, cellstyle, setStyleAction,
createCellStyle
Specifies if text should be wrapped in a cell.
## S4 method for signature 'cellstyle' setWrapText(object,wrap)## S4 method for signature 'cellstyle' setWrapText(object,wrap)
object |
The |
wrap |
If |
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
workbook, cellstyle, setCellStyle,
setStyleAction
## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("setWrapText.xlsx", create = TRUE) # Create a worksheet createSheet(wb, name = "cellstyles") # Create a dummy data set with some long text text <- data.frame( Text = "Some very very very very very very very long text") # Write the value to the 'cellstyles' worksheet in the # top left corner (cell A1) writeWorksheet(wb, text, sheet = "cellstyles", startRow = 1, startCol = 1, header = FALSE) # Create a custom anonymous cell style cs <- createCellStyle(wb) # Specify to wrap the text setWrapText(cs, wrap = TRUE) # Set the cell style created above for the top left cell (A1) # in the 'cellstyles' worksheet setCellStyle(wb, sheet = "cellstyles", row = 1, col = 1, cellstyle = cs) # Save the workbook saveWorkbook(wb) # clean up file.remove("setWrapText.xlsx") ## End(Not run)## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("setWrapText.xlsx", create = TRUE) # Create a worksheet createSheet(wb, name = "cellstyles") # Create a dummy data set with some long text text <- data.frame( Text = "Some very very very very very very very long text") # Write the value to the 'cellstyles' worksheet in the # top left corner (cell A1) writeWorksheet(wb, text, sheet = "cellstyles", startRow = 1, startCol = 1, header = FALSE) # Create a custom anonymous cell style cs <- createCellStyle(wb) # Specify to wrap the text setWrapText(cs, wrap = TRUE) # Set the cell style created above for the top left cell (A1) # in the 'cellstyles' worksheet setCellStyle(wb, sheet = "cellstyles", row = 1, col = 1, cellstyle = cs) # Save the workbook saveWorkbook(wb) # clean up file.remove("setWrapText.xlsx") ## End(Not run)
Displays a workbook by printing it. This actually calls the workbook's
print method.
## S4 method for signature 'workbook' show(object)## S4 method for signature 'workbook' show(object)
object |
The |
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
## Not run: # Load existing demo Excel file 'mtcars.xlsx' from the XLConnect package wb.mtcars <- loadWorkbook(system.file("demoFiles/mtcars.xlsx", package = "XLConnect")) # Display the wb.mtcars object wb.mtcars # Alternatively, show can be called explicitely show(wb.mtcars) ## End(Not run)## Not run: # Load existing demo Excel file 'mtcars.xlsx' from the XLConnect package wb.mtcars <- loadWorkbook(system.file("demoFiles/mtcars.xlsx", package = "XLConnect")) # Display the wb.mtcars object wb.mtcars # Alternatively, show can be called explicitely show(wb.mtcars) ## End(Not run)
Outputs a workbook summary including the underlying Excel filename, contained worksheets,
hidden sheets, very hidden sheets, defined names and the active sheet name.
## S4 method for signature 'workbook' summary(object)## S4 method for signature 'workbook' summary(object)
object |
The |
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
workbook, getSheets,
isSheetHidden, isSheetVeryHidden,
getDefinedNames, getActiveSheetName
## Not run: # Load existing demo Excel file 'mtcars.xlsx' from the XLConnect package wb.mtcars <- loadWorkbook(system.file("demoFiles/mtcars.xlsx", package = "XLConnect")) # Print a workbook summary summary(wb.mtcars) ## End(Not run)## Not run: # Load existing demo Excel file 'mtcars.xlsx' from the XLConnect package wb.mtcars <- loadWorkbook(system.file("demoFiles/mtcars.xlsx", package = "XLConnect")) # Print a workbook summary summary(wb.mtcars) ## End(Not run)
This data set provides historical exchange rates (CHF vs EUR, USD, GBP) in the time frame from January 1, 2014 to February 24, 2015. The exchange rates reflect bid prices with a 0% interbank rate.
swissfrancswissfranc
A data.frame with daily exchange rates in the mentioned time frame.
retrieved via ‘https://www.oanda.com/’ - the retrieved time range is no longer available.
Unhides the specified worksheets in a workbook.
## S4 method for signature 'workbook,character' unhideSheet(object,sheet) ## S4 method for signature 'workbook,numeric' unhideSheet(object,sheet)## S4 method for signature 'workbook,character' unhideSheet(object,sheet) ## S4 method for signature 'workbook,numeric' unhideSheet(object,sheet)
object |
The |
sheet |
The name or index of the sheet to unhide |
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
workbook, hideSheet,
isSheetHidden, isSheetVeryHidden,
isSheetVisible
## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("unhideWorksheet.xlsx", create = TRUE) # Create sheet 'airquality' createSheet(wb, name = "airquality") # Write the built-in data set airquality to worksheet # 'airquality' writeWorksheet(wb, airquality, sheet = "airquality") # Create sheet 'CO2' createSheet(wb, name = "CO2") # Write the built-in data set CO2 to worksheet 'C02' writeWorksheet(wb, CO2, sheet = "CO2") # Hide sheet 'airquality' hideSheet(wb, sheet = "airquality") # Unhide sheet 'airquality' unhideSheet(wb, sheet = "airquality") # clean up file.remove("unhideWorksheet.xlsx") ## End(Not run)## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("unhideWorksheet.xlsx", create = TRUE) # Create sheet 'airquality' createSheet(wb, name = "airquality") # Write the built-in data set airquality to worksheet # 'airquality' writeWorksheet(wb, airquality, sheet = "airquality") # Create sheet 'CO2' createSheet(wb, name = "CO2") # Write the built-in data set CO2 to worksheet 'C02' writeWorksheet(wb, CO2, sheet = "CO2") # Hide sheet 'airquality' hideSheet(wb, sheet = "airquality") # Unhide sheet 'airquality' unhideSheet(wb, sheet = "airquality") # clean up file.remove("unhideWorksheet.xlsx") ## End(Not run)
Unmerges cells in a worksheet.
## S4 method for signature 'workbook,character' unmergeCells(object,sheet,reference) ## S4 method for signature 'workbook,numeric' unmergeCells(object,sheet,reference)## S4 method for signature 'workbook,character' unmergeCells(object,sheet,reference) ## S4 method for signature 'workbook,numeric' unmergeCells(object,sheet,reference)
object |
The |
sheet |
The name or index of the sheet on which to unmerge cells |
reference |
A cell range specification ( |
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
workbook, mergeCells,
idx2cref
## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("unmergeCells.xlsx", create = TRUE) # Create a worksheet called 'merge' createSheet(wb, name = "merge") # Merge the cells A1:B8 on the worksheet created above mergeCells(wb, sheet = "merge", reference = "A1:B8") # Unmerge the cells A1:B8 unmergeCells(wb, sheet = "merge", reference = "A1:B8") # clean up file.remove("unmergeCells.xlsx") ## End(Not run)## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("unmergeCells.xlsx", create = TRUE) # Create a worksheet called 'merge' createSheet(wb, name = "merge") # Merge the cells A1:B8 on the worksheet created above mergeCells(wb, sheet = "merge", reference = "A1:B8") # Unmerge the cells A1:B8 unmergeCells(wb, sheet = "merge", reference = "A1:B8") # clean up file.remove("unmergeCells.xlsx") ## End(Not run)
Evaluate an R expression in an environment constructed from the named regions of an Excel workbook.
## S3 method for class 'workbook' with(data, expr, ...)## S3 method for class 'workbook' with(data, expr, ...)
data |
A workbook object, as returned by loadWorkbook. |
expr |
expression to evaluate |
... |
Additional arguments passed to readNamedRegion |
This method will read all named regions from the workbook when creating the environment. Names in the workbook will be processed through make.names to obtain the variable names.
Changes to the variables representing named regions will not affect the workbook contents and need to be saved explicitly using writeNamedRegion and saveWorkbook. If the workbook contains names that do not map to R identifiers,
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
getDefinedNames,
readNamedRegion,
## Not run: # multiregion xlsx file from demoFiles subfolder of package XLConnect demoExcelFile <- system.file("demoFiles/multiregion.xlsx", package = "XLConnect") # load workbook wb <- loadWorkbook(demoExcelFile) # named regions: Calendar, IQ, Iris print(getDefinedNames(wb)) # named regions as variables with(wb, { print(Calendar) summary(IQ) summary(Iris) }) ## End(Not run)## Not run: # multiregion xlsx file from demoFiles subfolder of package XLConnect demoExcelFile <- system.file("demoFiles/multiregion.xlsx", package = "XLConnect") # load workbook wb <- loadWorkbook(demoExcelFile) # named regions: Calendar, IQ, Iris print(getDefinedNames(wb)) # named regions as variables with(wb, { print(Calendar) summary(IQ) summary(Iris) }) ## End(Not run)
This is XLConnect's main entity representing a Microsoft Excel workbook. S4 objects of this class and corresponding methods are used to manipulate the underlying Excel workbook instances.
Objects can be created by calls of the form loadWorkbook(filename, create). This is
a shortcut form of new("workbook", filename, create) with some additional error checking.
filename:Object of class character which represents the filename of the
underlying Microsoft Excel workbook.
jobj:Object of class jobjRef (see package rJava) which represents a Java object reference that is used
in the back-end to manipulate the underlying Excel workbook instance.
Note: The jobj slot should not be accessed directly. workbook objects should only be manipulated
via the corresponding methods.
XLConnect supports both Excel 97-2003 (*.xls) and OOXML (Excel 2007+, *.xlsx) file formats.
A workbook's underlying Excel file is not saved (or being created in case the file did not exist and create = TRUE has been
specified) unless the saveWorkbook method has been called on the object. This provides more flexibility to the user to decide
when changes are saved and also provides better performance in that several changes can be written in one go (normally at the end, rather than
after every operation causing the file to be rewritten again completely each time). This is due to the fact that workbooks are manipulated in-memory and
are only written to disk with specifically calling saveWorkbook.
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
Wikipedia: Office Open XML
https://en.wikipedia.org/wiki/Office_Open_XML
## Not run: # Create a new workbook 'myWorkbook.xlsx' # (assuming the file to not exist already) wb <- loadWorkbook("myWorkbook.xlsx", create = TRUE) # Create a worksheet called 'mtcars' createSheet(wb, name = "mtcars") # Write built-in dataset 'mtcars' to sheet 'mtcars' created above writeWorksheet(wb, mtcars, sheet = "mtcars") # Save workbook - this actually writes the file 'myWorkbook.xlsx' to disk saveWorkbook(wb) # clean up file.remove("myWorkbook.xlsx") ## End(Not run)## Not run: # Create a new workbook 'myWorkbook.xlsx' # (assuming the file to not exist already) wb <- loadWorkbook("myWorkbook.xlsx", create = TRUE) # Create a worksheet called 'mtcars' createSheet(wb, name = "mtcars") # Write built-in dataset 'mtcars' to sheet 'mtcars' created above writeWorksheet(wb, mtcars, sheet = "mtcars") # Save workbook - this actually writes the file 'myWorkbook.xlsx' to disk saveWorkbook(wb) # clean up file.remove("myWorkbook.xlsx") ## End(Not run)
Writes data to the named regions defined in a workbook.
## S4 method for signature 'workbook,ANY' writeNamedRegion(object, data, name, header, overwriteFormulaCells, rownames, worksheetScope)## S4 method for signature 'workbook,ANY' writeNamedRegion(object, data, name, header, overwriteFormulaCells, rownames, worksheetScope)
object |
The |
data |
Data to write |
name |
Name of the named region to write to |
header |
Specifies if the column names should be written. The default is |
overwriteFormulaCells |
Specifies if existing formula cells in the |
rownames |
Name ( |
worksheetScope |
Optional character vector with worksheet name(s) to limit the scope in
which the |
.
Writes data to the named region specified by name. Note
that data is assumed to be a data.frame and is coerced to
one if this is not already the case. The argument header
specifies if the column names should be written. Note also that the
arguments are vectorized and as such multiple named regions can be
written with one call. In this case data is assumed to be a list
of data objects (data.frame's).
Named regions are automatically redefined to the area occupied by the
written cells. This guarantees that the complete set of data can be
re-read using
readNamedRegion. Also, this
allows the named region just to be defined as the top left cell to be
written to. There is no need to know the exact size of the data in advance.
When writing data to Excel, writeNamedRegion further applies cell
styles to the cells as defined by the workbook's
"style action" (see
setStyleAction).
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
What are named regions/ranges?
https://web.archive.org/web/20240821110221/https://www.officearticles.com/excel/named_ranges_in_microsoft_excel.htm
How to create named regions/ranges?
https://www.youtube.com/watch?v=iAE9a0uRtpM
workbook,
writeWorksheet,
appendNamedRegion,
appendWorksheet,
readNamedRegion,
readWorksheet,
writeNamedRegionToFile
## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("writeNamedRegion.xlsx", create = TRUE) # Create a worksheet named 'mtcars' createSheet(wb, name = "mtcars") # Create a named region called 'mtcars' on the sheet called 'mtcars' createName(wb, name = "mtcars", formula = "mtcars!$A$1") # Write built-in data set 'mtcars' to the above defined named region # (using header = TRUE) writeNamedRegion(wb, mtcars, name = "mtcars") createSheet(wb, name="iris") setActiveSheet(wb, "iris") # Do the same with the iris data set, with a worksheet-scoped name createName(wb, name = "iris", formula = "iris!$A$1", worksheetScope = "iris") writeNamedRegion(wb, iris, name = "iris", worksheetScope="iris") # Save workbook (this actually writes the file to disk) saveWorkbook(wb) # clean up file.remove("writeNamedRegion.xlsx") ## End(Not run)## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("writeNamedRegion.xlsx", create = TRUE) # Create a worksheet named 'mtcars' createSheet(wb, name = "mtcars") # Create a named region called 'mtcars' on the sheet called 'mtcars' createName(wb, name = "mtcars", formula = "mtcars!$A$1") # Write built-in data set 'mtcars' to the above defined named region # (using header = TRUE) writeNamedRegion(wb, mtcars, name = "mtcars") createSheet(wb, name="iris") setActiveSheet(wb, "iris") # Do the same with the iris data set, with a worksheet-scoped name createName(wb, name = "iris", formula = "iris!$A$1", worksheetScope = "iris") writeNamedRegion(wb, iris, name = "iris", worksheetScope="iris") # Save workbook (this actually writes the file to disk) saveWorkbook(wb) # clean up file.remove("writeNamedRegion.xlsx") ## End(Not run)
Writes named regions to an Excel file.
writeNamedRegionToFile(file, data, name, formula=NA, ..., worksheetScope = NULL, styleAction = XLC$STYLE_ACTION.XLCONNECT, clearNamedRegions=FALSE)writeNamedRegionToFile(file, data, name, formula=NA, ..., worksheetScope = NULL, styleAction = XLC$STYLE_ACTION.XLCONNECT, clearNamedRegions=FALSE)
file |
The path name of the file to write to |
data |
Data to write |
name |
Name of the named region to write to |
formula |
If formula is specified, each item defines the formula of
the named region identified by the corresponding entry of
|
worksheetScope |
Optional character vector with worksheet name(s) to limit the scope in
which the |
... |
Additional arguments passed to |
styleAction |
Style action to be used when writing the data. |
clearNamedRegions |
TRUE to clear content of existing named regions before writing data |
Thomas Themel
Mirai Solutions GmbH https://mirai-solutions.ch
writeNamedRegion,
writeWorksheetToFile,
readNamedRegionFromFile,readWorksheetFromFile
## Not run: # multiregion xlsx file from demoFiles subfolder of package XLConnect demoExcelFile <- system.file("demoFiles/multiregion.xlsx", package = "XLConnect") template <- "template-ws.xlsx" file.copy(demoExcelFile, template) # Write single data.frame to a named region in an existing file writeNamedRegionToFile(template, name = "Iris", iris) # Write to a new file, defining the sheet and named region as we write. # Format according to XLConnect defaults writeNamedRegionToFile("iris.xlsx", name = "Iris", data = iris, formula = "IrisData!$C$4", styleAction = "XLCONNECT") # clean up file.remove("iris.xlsx") file.remove("template-ws.xlsx") ## End(Not run)## Not run: # multiregion xlsx file from demoFiles subfolder of package XLConnect demoExcelFile <- system.file("demoFiles/multiregion.xlsx", package = "XLConnect") template <- "template-ws.xlsx" file.copy(demoExcelFile, template) # Write single data.frame to a named region in an existing file writeNamedRegionToFile(template, name = "Iris", iris) # Write to a new file, defining the sheet and named region as we write. # Format according to XLConnect defaults writeNamedRegionToFile("iris.xlsx", name = "Iris", data = iris, formula = "IrisData!$C$4", styleAction = "XLCONNECT") # clean up file.remove("iris.xlsx") file.remove("template-ws.xlsx") ## End(Not run)
Writes data to worksheets of a workbook.
## S4 method for signature 'workbook,ANY,character' writeWorksheet(object,data,sheet,startRow,startCol,header,overwriteFormulaCells,rownames) ## S4 method for signature 'workbook,ANY,numeric' writeWorksheet(object,data,sheet,startRow,startCol,header,overwriteFormulaCells,rownames)## S4 method for signature 'workbook,ANY,character' writeWorksheet(object,data,sheet,startRow,startCol,header,overwriteFormulaCells,rownames) ## S4 method for signature 'workbook,ANY,numeric' writeWorksheet(object,data,sheet,startRow,startCol,header,overwriteFormulaCells,rownames)
object |
The |
data |
Data to write |
sheet |
The name or index of the sheet to write to |
startRow |
Index of the first row to write to. The default is |
startCol |
Index of the first column to write to. The default is |
header |
Specifies if the column names should be written. The default is |
overwriteFormulaCells |
Specifies if existing formula cells in the |
rownames |
Name ( |
Writes data to the worksheet specified by
sheet. data is assumed to be a data.frame and is
coerced to one if this is not already the case. startRow and
startCol define the top left corner of the data region to be
written. Note that the arguments are vectorized and as such multiple
data objects (data.frame's) can be written to different
worksheets in one call. In this case data is assumed to be a list
of data.frames.
When writing data to Excel, writeWorksheet further applies cell
styles to the cells as defined by the workbook's
"style action" (see
setStyleAction).
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
workbook,
writeNamedRegion,
appendWorksheet,
appendNamedRegion,
readWorksheet,
readNamedRegion,
writeWorksheetToFile
## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("writeWorksheet.xlsx", create = TRUE) # Create a worksheet called 'CO2' createSheet(wb, name = "CO2") # Write built-in data set 'CO2' to the worksheet created above; # offset from the top left corner and with default header = TRUE writeWorksheet(wb, CO2, sheet = "CO2", startRow = 4, startCol = 2) # Save workbook (this actually writes the file to disk) saveWorkbook(wb) # clean up file.remove("writeWorksheet.xlsx") ## End(Not run)## Not run: # Load workbook (create if not existing) wb <- loadWorkbook("writeWorksheet.xlsx", create = TRUE) # Create a worksheet called 'CO2' createSheet(wb, name = "CO2") # Write built-in data set 'CO2' to the worksheet created above; # offset from the top left corner and with default header = TRUE writeWorksheet(wb, CO2, sheet = "CO2", startRow = 4, startCol = 2) # Save workbook (this actually writes the file to disk) saveWorkbook(wb) # clean up file.remove("writeWorksheet.xlsx") ## End(Not run)
Writes data to worksheets in an Excel file.
writeWorksheetToFile(file, data, sheet, ..., styleAction = XLC$STYLE_ACTION.XLCONNECT, clearSheets = FALSE)writeWorksheetToFile(file, data, sheet, ..., styleAction = XLC$STYLE_ACTION.XLCONNECT, clearSheets = FALSE)
file |
The path name of the file to write to. |
data |
Data to write |
sheet |
The name or index of the sheet to write to |
... |
Additional arguments passed to writeWorksheet |
styleAction |
Style action to be used when writing the data - not vectorized!
The default is |
clearSheets |
TRUE to clear sheets before writing data. |
Thomas Themel
Mirai Solutions GmbH https://mirai-solutions.ch
writeWorksheet,
writeNamedRegionToFile,
readWorksheetFromFile,readNamedRegionFromFile
## Not run: # multiregion xlsx file from demoFiles subfolder of package XLConnect demoExcelFile <- system.file("demoFiles/multiregion.xlsx", package = "XLConnect") # create a copy of the template template <- "template-ws.xlsx" file.copy(demoExcelFile, template) # Write single data.frame to a specified location in an existing file writeWorksheetToFile(template, data=iris, sheet="FirstSheet", startRow=9, startCol = 9) # create a copy of the template template <- "template-multi-ws.xlsx" # Write a few copies of the data.frame all over a new file writeWorksheetToFile(template, data = list(i1 = iris, i2 = iris, i3 = iris), sheet = c("FirstSheet", "SecondSheet", "FirstSheet"), startRow = c(1,101,201), startCol = c(1,11,21)) # clean up file.remove("template-multi-ws.xlsx") file.remove("template-ws.xlsx") ## End(Not run)## Not run: # multiregion xlsx file from demoFiles subfolder of package XLConnect demoExcelFile <- system.file("demoFiles/multiregion.xlsx", package = "XLConnect") # create a copy of the template template <- "template-ws.xlsx" file.copy(demoExcelFile, template) # Write single data.frame to a specified location in an existing file writeWorksheetToFile(template, data=iris, sheet="FirstSheet", startRow=9, startCol = 9) # create a copy of the template template <- "template-multi-ws.xlsx" # Write a few copies of the data.frame all over a new file writeWorksheetToFile(template, data = list(i1 = iris, i2 = iris, i3 = iris), sheet = c("FirstSheet", "SecondSheet", "FirstSheet"), startRow = c(1,101,201), startCol = c(1,11,21)) # clean up file.remove("template-multi-ws.xlsx") file.remove("template-ws.xlsx") ## End(Not run)
List structure defining several constants used across XLConnect.
The format is:
List of 90 $ ERROR.WARN : chr "WARN" $ ERROR.STOP : chr "STOP" $ DATA_TYPE.BOOLEAN : chr "BOOLEAN" $ DATA_TYPE.NUMERIC : chr "NUMERIC" $ DATA_TYPE.STRING : chr "STRING" $ DATA_TYPE.DATETIME : chr "DATETIME" $ STYLE_ACTION.XLCONNECT : chr "XLCONNECT" $ STYLE_ACTION.NONE : chr "NONE" $ STYLE_ACTION.PREDEFINED : chr "PREDEFINED" $ STYLE_ACTION.NAME_PREFIX : chr "STYLE_NAME_PREFIX" $ STYLE_ACTION.DATA_FORMAT_ONLY: chr "DATA_FORMAT_ONLY" $ BORDER.DASHED : num 3 $ BORDER.DASH_DOT : num 9 $ BORDER.DASH_DOT_DOT : num 11 $ BORDER.DOTTED : num 7 $ BORDER.DOUBLE : num 6 $ BORDER.HAIR : num 4 $ BORDER.MEDIUM : num 2 $ BORDER.MEDIUM_DASHED : num 8 $ BORDER.MEDIUM_DASH_DOT : num 10 $ BORDER.MEDIUM_DASH_DOT_DOT : num 12 $ BORDER.NONE : num 0 $ BORDER.SLANTED_DASH_DOT : num 13 $ BORDER.THICK : num 5 $ BORDER.THIN : num 1 $ COLOR.BLACK : num 8 $ COLOR.WHITE : num 9 $ COLOR.RED : num 10 $ COLOR.BRIGHT_GREEN : num 11 $ COLOR.BLUE : num 12 $ COLOR.YELLOW : num 13 $ COLOR.PINK : num 14 $ COLOR.TURQUOISE : num 15 $ COLOR.DARK_RED : num 16 $ COLOR.GREEN : num 17 $ COLOR.DARK_BLUE : num 18 $ COLOR.DARK_YELLOW : num 19 $ COLOR.VIOLET : num 20 $ COLOR.TEAL : num 21 $ COLOR.GREY_25_PERCENT : num 22 $ COLOR.GREY_50_PERCENT : num 23 $ COLOR.CORNFLOWER_BLUE : num 24 $ COLOR.MAROON : num 25 $ COLOR.LEMON_CHIFFON : num 26 $ COLOR.ORCHID : num 28 $ COLOR.CORAL : num 29 $ COLOR.ROYAL_BLUE : num 30 $ COLOR.LIGHT_CORNFLOWER_BLUE : num 31 $ COLOR.SKY_BLUE : num 40 $ COLOR.LIGHT_TURQUOISE : num 41 $ COLOR.LIGHT_GREEN : num 42 $ COLOR.LIGHT_YELLOW : num 43 $ COLOR.PALE_BLUE : num 44 $ COLOR.ROSE : num 45 $ COLOR.LAVENDER : num 46 $ COLOR.TAN : num 47 $ COLOR.LIGHT_BLUE : num 48 $ COLOR.AQUA : num 49 $ COLOR.LIME : num 50 $ COLOR.GOLD : num 51 $ COLOR.LIGHT_ORANGE : num 52 $ COLOR.ORANGE : num 53 $ COLOR.BLUE_GREY : num 54 $ COLOR.GREY_40_PERCENT : num 55 $ COLOR.DARK_TEAL : num 56 $ COLOR.SEA_GREEN : num 57 $ COLOR.DARK_GREEN : num 58 $ COLOR.OLIVE_GREEN : num 59 $ COLOR.BROWN : num 60 $ COLOR.PLUM : num 61 $ COLOR.INDIGO : num 62 $ COLOR.GREY_80_PERCENT : num 63 $ COLOR.AUTOMATIC : num 64 $ FILL.NO_FILL : num 0 $ FILL.SOLID_FOREGROUND : num 1 $ FILL.FINE_DOTS : num 2 $ FILL.ALT_BARS : num 3 $ FILL.SPARSE_DOTS : num 4 $ FILL.THICK_HORZ_BANDS : num 5 $ FILL.THICK_VERT_BANDS : num 6 $ FILL.THICK_BACKWARD_DIAG : num 7 $ FILL.THICK_FORWARD_DIAG : num 8 $ FILL.BIG_SPOTS : num 9 $ FILL.BRICKS : num 10 $ FILL.THIN_HORZ_BANDS : num 11 $ FILL.THIN_VERT_BANDS : num 12 $ FILL.THIN_BACKWARD_DIAG : num 13 $ FILL.THIN_FORWARD_DIAG : num 14 $ FILL.SQUARES : num 15 $ FILL.DIAMONDS : num 16
The XLC list structure defines several constants used throughout XLConnect. The general convention for
enumeration types is to address corresponding constants via XLC$"<ENUM_TYPE>.<VALUE>" where <ENUM_TYPE> specifies
the name of the enumeration and <VALUE> specifies a corresponding enumeration value. An example is XLC$"COLOR.RED" where
"COLOR" is the enumeration type and "RED" is the corresponding color enumeration value.
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
Dumps data sets to Excel files by writing each object to a separate worksheet.
xlcDump(list, ..., file = "dump.xlsx", pos = -1, overwrite = FALSE)xlcDump(list, ..., file = "dump.xlsx", pos = -1, overwrite = FALSE)
list |
|
... |
Arguments that will be passed to the |
file |
Excel file to which objects will be dumped. Can be an existing or a new file. Defaults to |
pos |
Environment in which to look for objects. Can be specified either as an |
overwrite |
|
Each object is written to a separate worksheet named by the name of the object. Objects are written using the
writeWorksheet method - as such any object will be coerced to data.frame.
Named logical vector specifying if objects have been dumped or not. An object may not be dumped because there
was an issue with the coercion to a data.frame or the object already existed (and overwrite = FALSE) in
the workbook.
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
xlcRestore, writeNamedRegion,
writeWorksheet, writeNamedRegionToFile,
writeWorksheetToFile, xlcEdit
## Not run: require(datasets) xlcDump(c("airquality", "CO2", "iris", "PlantGrowth", "swiss"), file = "myDump.xlsx", pos = "package:datasets") xlcRestore(file = "myDump.xlsx", overwrite = TRUE) # clean up file.remove("myDump.xlsx") ## End(Not run)## Not run: require(datasets) xlcDump(c("airquality", "CO2", "iris", "PlantGrowth", "swiss"), file = "myDump.xlsx", pos = "package:datasets") xlcRestore(file = "myDump.xlsx", overwrite = TRUE) # clean up file.remove("myDump.xlsx") ## End(Not run)
Provides the capability to edit an object/data.frame in an Excel file editor. After editing, the object is restored in the R session with the corresponding changes.
xlcEdit(obj, pos = globalenv(), ext = ".xlsx")xlcEdit(obj, pos = globalenv(), ext = ".xlsx")
obj |
Object ( |
pos |
Where to look for the object specified by |
ext |
Extension to use for the Excel file being created. Defaults to ".xlsx". |
This function uses xlcDump and xlcRestore to
dump objects to and restore objects from Excel files. An OS command is invoked
to open the temporary Excel file in the default editor. Changes to the file have
to be saved in order for them to take effect in the restored object.
Invisibly returns the value of the xlcRestore operation.
This function only works under Windows and MacOS with a corresponding Excel file editor, e.g. MS Excel or LibreOffice. Attempts to use this function under another OS will result in an error being thrown.
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
xlcDump, xlcRestore,
writeNamedRegion,
writeWorksheet, writeNamedRegionToFile,
writeWorksheetToFile
## Not run: myObj = mtcars xlcEdit(myObj) ## End(Not run)## Not run: myObj = mtcars xlcEdit(myObj) ## End(Not run)
Frees Java Virtual Machine (JVM) memory.
xlcFreeMemory(...)xlcFreeMemory(...)
... |
Further arguments to be passed to R's garbage collector ( |
This function uses Java's Runtime class to run the garbage collector.
Java memory is freed by first running R's garbage collector (gc) and then
Java's garbage collector. This sequence is important as R's gc may release
objects which in turn allows Java's garbage collector to release some objects.
Note, in general there should be no need to make active use of this with XLConnect. Both
R and Java automatically perform garbage collection at times. However, this function might be useful
to reclaim memory after removing a large data object that has been written/read to/from Excel.
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
## Not run: xlcFreeMemory() ## End(Not run)## Not run: xlcFreeMemory() ## End(Not run)
Reports the amount of free memory in the Java Virtual Machine (JVM).
xlcMemoryReport()xlcMemoryReport()
This function uses Java's Runtime class to query the free JVM memory.
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
## Not run: xlcMemoryReport() ## End(Not run)## Not run: xlcMemoryReport() ## End(Not run)
These functions are provided for compatibility with older versions of XLConnect only, and will be defunct in a later release.
The following functions are deprecated and will be made defunct. Use the replacements as indicated.
getReferenceCoordinates: getReferenceCoordinatesForName
Restores objects from Excel files that have been dumped using xlcDump.
xlcRestore(file = "dump.xlsx", pos = -1, overwrite = FALSE)xlcRestore(file = "dump.xlsx", pos = -1, overwrite = FALSE)
file |
Excel file from which to restore objects. This is normally a file that has been produced with
|
pos |
Environment into which to restore objects. Can be specified either as an |
overwrite |
|
Named logical vector specifying if objects have been restored or not. An object may not be restored because there
was an issue with reading the data from the worksheet or the object already existed in the environment pos
(and overwrite = FALSE).
Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch
xlcDump, readNamedRegion,
readWorksheet, readNamedRegionFromFile,readWorksheetFromFile, xlcEdit
## Not run: require(datasets) xlcDump(c("airquality", "CO2", "iris", "PlantGrowth", "swiss"), file = "myDump.xlsx", pos = "package:datasets") xlcRestore(file = "myDump.xlsx", overwrite = TRUE) # clean up file.remove("myDump.xlsx") ## End(Not run)## Not run: require(datasets) xlcDump(c("airquality", "CO2", "iris", "PlantGrowth", "swiss"), file = "myDump.xlsx", pos = "package:datasets") xlcRestore(file = "myDump.xlsx", overwrite = TRUE) # clean up file.remove("myDump.xlsx") ## End(Not run)