getBoundingBox-methods
Querying the coordinates of a worksheet bounding box
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.
Usage
# S4 method for workbook,character
getBoundingBox(object,sheet,startRow,startCol,endRow,endCol,autofitRow,autofitCol)
# S4 method for workbook,numeric
getBoundingBox(object,sheet,startRow,startCol,endRow,endCol,autofitRow,autofitCol)
Arguments
- object
- 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
0
meaning that the start row is determined automatically.- startCol
Start reference column for the bounding box. Defaults to
0
meaning that the start column is determined automatically.- endRow
End reference row for the bounding box. Defaults to
0
meaning that the end row is determined automatically.- endCol
End reference column for the bounding box. Defaults to
0
meaning that the end column is determined automatically.- autofitRow
logical
specifying if leading and trailing empty rows should be skipped. Defaults toTRUE
.- autofitCol
logical
specifying if leading and trailing empty columns should be skipped. Defaults toTRUE
.
Details
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.
See Also
Examples
# 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))
# }