getBoundingBox-methods {XLConnect}

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 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)

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.

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.

Author(s)

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

See Also

workbook

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))

## End(Not run)