getBoundingBox-methods: Querying the coordinates of a worksheet bounding box

Description

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: The workbook to use
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 to TRUE.
autofitCol: logical specifying if leading and trailing empty columns should be skipped. Defaults to TRUE.

Author

Nicola Lambiase
Mirai Solutions GmbH https://mirai-solutions.ch

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.

Examples

Run this code

if (FALSE) {
# 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))
}