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
0meaning that the start row is determined automatically.- startCol
Start reference column for the bounding box. Defaults to
0meaning that the start column is determined automatically.- endRow
End reference row for the bounding box. Defaults to
0meaning that the end row is determined automatically.- endCol
End reference column for the bounding box. Defaults to
0meaning that the end column is determined automatically.- autofitRow
logicalspecifying if leading and trailing empty rows should be skipped. Defaults toTRUE.- autofitCol
logicalspecifying 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))
# }