Range Properties and Methods

Use the following range-related properties and methods with the Excel File Connector.

Note: Do not use the CopyRows, CopyColumns, MoveColumns, and MoveRows methods if the destination range contains merged cells. The destination range cannot overlap a merged range.

When you use methods that read through the worksheet or range, keep in mind that the system starts in the first column of the first row, then reads the second column of the first row. It continues in this way until it reaches the last column of the first row. It then reads the first column of the second row and continues in this way until it reaches the last column of the last row.

You cannot use the SetCellFormula method on a table header because it causes Excel to recover the workbook when you open it. You cannot set a table header’s text to be the same as another table header in the same table, nor can it be empty. This also causes Excel to recover the workbook when you open it.

Properties

Property	Description
ColumnCount	Sets the data range column count.
EndAddress	Sets the data range ending cell address.
RowCount	Sets the data range row count.
SheetName	Sets the range sheet name.
SkipEmptyRows	Specifies whether to retrieve empty rows on data range load.
StartAddress	Sets the data range starting cell address.
This	Is a reference to this object.

Methods

Method	Description	Parameters	Result type
AddressToRowColumn	Converts a range type address into a row and column. Use this method when you use the FindCells method to extract the row and column number.	String rangeAddress, out Int32 row, out Int32 column	Void
ClearCells	Clears all cell values and formulas. The ValueOnly option only clears cells that have values and formulas. The ValueAndStyle option additionally clears any formatting information that overrides the defaults, such as color for cells.	ExcelOption option	Void
ClearColumn	Clears all column cell values and formulas. The ValueOnly option only clears cells that have values and formulas. The ValueAndStyle option additionally clears any formatting information that overrides the defaults, such as color for cells.	Int32 column, ExcelOption option	Void
ClearRow	Clears all row cell values and formulas. The ValueOnly option only clears cells that have values and formulas. The ValueAndStyle option additionally clears any formatting information that overrides the defaults, such as color for cells.	Int32 row, ExcelOption option	Void
Commit	Commits data back to the Excel File Connector.	None	Void
CopyColumns	Copies columns to a position that you specify. The ValueOnly option only copies values. The ValueAndStyle option additionally copies all formatting information.	Int32 startColumn, Int32 endColumn, Int32 destinationColumn, ExcelOption option	Void
CopyRows	Copies rows to a position that you specify. The ValueOnly option only copies values. The ValueAndStyle option additionally copies all formatting information.	Int32 startRow, Int32 endRow, Int32 destinationRow, ExcelOption option	Void
ExportToTable	Converts a cell range into a data table. You can choose to export the data table with the first row as the header row.	excelHeader header	Data table
FindCell	Returns the first matching cell. The search parameter is case sensitive.	String value, out Int32 row, out Int32 column	Boolean
FindCells	Returns all matching cells. The search parameter is case sensitive.	String value, out String[] rangeAddresses	Boolean
FindColumn	Returns the first matching column in the row that you specify. The search parameter is case sensitive.	String value, Int32 row, out Int32 column	Boolean
FindColumns (2 parameters)	Returns all matching columns. The search parameter is case sensitive.	String value, out Int32[] columns	Boolean
FindColumns (3 parameters)	Returns all matching columns in the row that you specify. The search parameter is case sensitive.	String value, Int32 row, out Int32[] columns	Boolean
FindRow	Returns the first matching row in the column that you specify. The search parameter is case sensitive.	String value, Int32 column, out Int32 row	Boolean
FindRows (2 parameters)	Returns all matching rows. The search parameter is case sensitive.	String value, out Int32[] rows	Boolean
FindRows (3 parameters)	Returns all matching rows in the column that you specify. The search parameter is case sensitive.	String value, Int32 column, out Int32[] rows	Boolean
GetCellBackgroundColor	Returns the background color of the cell.	Int32 row, Int32 column	Color
GetCellFont	Returns the font used in the cell.	Int32 row, Int32 column	Font
GetCellForegroundColor	Returns the foreground color of the cell.	Int32 row, Int32 column	Color
GetCellFormat	Returns the format used in the cell.	Int32 row, Int32 column	String
GetCellFormattedValue	Returns the formatted cell value.	Int32 row, Int32 column	String
GetCellFormula	Returns the formula used in the cell.	Int32 row, Int32 column	String
GetCellHorizontalAlignment	Returns the text alignment used in the cell.	Int32 row, Int32 column	HorizontalAlignment
GetCellStringValue	Returns the string value stored in the cell.	Int32 row, Int32 column	String
GetCellValue	Returns the value stored in the cell.	Int32 row, Int32 column	Object
GetCellVerticalAlignment	Returns the text alignment of the cell.	Int32 row, Int32 column	VerticalAlignment
GetFirstCell (3 parameters)	Returns the location of the first cell with data or its formatting information or both.	ExcelSearch option, out Int32 row, out Int32 column	Void
GetFirstCell (7 parameters)	Returns the location of the first cell with data or its formatting information or both within the starting and ending row and column numbers that you specify.	Int32 startRow, Int32 startColumn, Int32 endRow, Int32 endColumn, ExcelSearch option, out Int32 row, out Int32 column	Void
GetFirstColumn	Returns the first column with data or its formatting information or both within the column range that you specify.	Int32 startColumn, Int32 endColumn, ExcelSearch option	Int32
GetFirstRow	Returns the first row with data or its formatting information or both within the row range that you specify.	Int32 startRow, Int32 endRow, ExcelSearch option	Int32
GetLastCell (3 parameters)	Returns the location of the last cell with data or its formatting information or both.	ExcelSearch option, out Int32 row, out Int32 column	Void
GetLastCell (7 parameters)	Returns the location of the last cell with data or its formatting information or both within the starting and ending row and column numbers that you specify.	Int32 startRow, Int32 startColumn, Int32 endRow, Int32 endColumn, ExcelSearch option, out Int32 row, out Int32 column	Void
GetLastColumn	Returns the last column with data or its formatting information or both within the column range that you specify.	Int32 startColumn, Int32 endColumn, ExcelSearch option	Int32
GetLastRow	Returns the last row with data or its formatting information or both within the row range that you specify.	Int32 startRow, Int32 endRow, ExcelSearch option	Int32
ImportFromTable	Imports a cell range data from a data table	Int32 row, Int32 column, Data table table	Void
Load	Loads data from the Excel File Connector.	None	Boolean
MoveColumns	Moves columns to the position that you specify. The ValueOnly option only moves values. The ValueAndStyle option also includes all formatting information.	Int32 startColumn, Int32 endColumn, Int32 destinationColumn, ExcelOption option	Void
MoveRows	Moves rows to the position that you specify. The ValueOnly option only moves values. The ValueAndStyle option also includes all formatting information.	Int32 startRow, Int32 endRow, Int32 destinationRow, ExcelOption option	Void
SetCellBackgroundColor	Returns the background color of the cell.	Int32 row, Int32 column, Color color	Void
SetCellFont	Sets the font used in the cell.	Int32 row, Int32 column, Font font	Void
SetCellForegroundColor	Sets the foreground color of the cell.	Int32 row, Int32 column, Color color	Void
SetCellFormat	Sets the format used in the cell.	Int32 row, Int32 column, String format	Void
SetCellFormula	Sets the formula used in the cell.	Int32 row, Int32 column, String formula	Void
SetCellHorizontalAlignment	Sets the text alignment used in the cell.	Int32 row, Int32 column, HorizontalAlignment alignment	Void
SetCellStringValue	Sets string value stored in the cell.	Int32 row, Int32 column, String value	Void
SetCellValue	Sets value stored in the cell.	Int32 row, Int32 column, Object value	Void
SetCellVerticalAlignment	Sets the text alignment used in the cell.	Int32 row, Int32 column, VerticalAlignment alignment	Void
SetColumnBackgroundColor	Sets the column background color for the cell.	Int32 column, Color color	Void
SetColumnForegroundColor	Sets the column foreground color for the cell.	Int32 column, Color color	Void
SetRowBackgroundColor	Sets the row background color for the cell.	Int32 row, Color color	Void
SetRowForegroundColor	Sets the row foreground color for the cell.	Int32 row, Color color	Void
ToConnectorAddress	Converts the range cell address to a connector cell address.	Int32 row, Int32 column	String

Related information

Excel File Connector Overview

Adding the Excel File Connector

Creating a Range Object

Excel File Connector Properties, Events, and Methods

Excel File Connector Formulas