Skip to main content

ExcelConnector

Suggest edit Updated on November 10, 2021

Use the ExcelConnector to integrate Excel data, functions, and events into a solution.

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.

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

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 header in the same table, nor can it be empty. This also causes Excel to recover the workbook when you open it.

Properties

PropertyDescription
CsvOptionsOptions for loading and saving workbooks in CSV (Comma Separated Values) format.
FileNameEnter the path to the Excel workbook that you want the Excel connector to work with.
PasswordIf the Excel workbook uses a password, enter that password.
SheetNameEnter the name of the sheet that you want the Excel connector to work with.
StringFormat

Use strings formatting to set cell values and for data import. When you assign a string value to a cell that has formatting, Robot Runtime automatically performs the type conversion for you. The system uses the machine’s localization settings for the type conversion.

Set the value to Formatted to enable this feature. Disable it by setting the value to Unformatted. If turned off, the system passes the string as is, with no conversion.

For example, if you imported a value of $500 and had StringFormatting enabled, the system imports it as an integer with the value of 500. To include the currency symbol ($), set the cell format afterwards. If you disable string formatting, the system imports it as a string that contains “$500”.

Events

EventDescription
SavedThis event occurs when you save the Excel connector workbook.

MethodDescriptionParametersResult type
AddColumnAdds a column at the index that you specify. Int32 indexVoid
AddressToRowColumnConverts an alphanumeric address into a row and column integer. String address, out Int32 row, out Int32 column Void
AddRowAdds a row to the worksheet at the index that you specify. Int32 index Void
AddSheetAdds a sheet to the workbook. String sheetNameBoolean

Calculate (no parameters)

Recalculates all formulas on the sheet. NoneVoid
Calculate (1 parameter)Recalculates all formulas on the sheet that you specify. String sheetName Void
ClearCells (3 parameters)

Clears the cell values and formulas within the cell address parameters that you specify.

String startAddress, String endAddress, ExcelOption option Void
ClearCells (5 parameters)

Clears the cell values and formulas within the supplied row and column parameters.

The ValueOnly option only clears the values and formulas for cells. The ValueAndStyle option additionally clears any formatting information that overrides the defaults, such as color for cells.

Int32 startRow, Int32 startColumn, Int32 endRow, Int32 endColumn, ExcelOption option Void
ClearColumn

Clears the cell values and formulas in the column that you specify.

The ValueOnly option only clears the values and formulas for cells. The ValueAndStyle option additionally clears any formatting information that overrides the defaults, such as color for cells.

Int32 column, ExcelOption option Void
ClearRow

Clears the cell values and formulas within the row that you specify.

The ValueOnly option only clears the values and formulas for cells. The ValueAndStyle option additionally clears any formatting information that overrides the defaults, such as color for cells.

Int32 row, ExcelOption optionVoid
CloseCloses the workbook and clears all of the properties. NoneVoid
CopyCells

Copies the cells that you specify to another sheet at a position that you also specify.

The ValueOnly option only copies the values. The ValueAndStyle option additionally copies all of the formatting information.

String destinationSheetName, Int32 startRow, Int32 startColumn, Int32 endRow, Int32 endColumn, Int32 destinationRow, Int32 destinationColumn, ExcelOption option Void
CopyColumns

Copies the columns to another sheet at the position that you specify.

The ValueOnly option only copies the values. The ValueAndStyle option additionally copies all of the formatting information.

String destinationSheetName, Int32 startColumn, Int32 endColumn, Int32 destinationColumn, ExcelOption option Void
CopyRows

Copies the rows to another sheet at the position that you specify.

The ValueOnly option only copies the values. The ValueAndStyle option additionally copies all of the formatting information.

String destinationSheetName, Int32 startRow, Int32 endRow, Int32 destinationRow, ExcelOption option Void
DeleteColumnsRemoves multiple columns. Int32 startColumn, Int32 endColumnVoid
DeleteRowsRemoves multiple rows. Int32 startRow, Int32 endRowVoid
DeleteSheetDeletes the sheet that you specify. String sheetName Boolean
ExportToTable (2 parameters)

Exports the current spreadsheet into a data table.

Use the Format option to determine whether to export the values with or without formatting information.

Set the Header property to Header, if you want the first row to be the header for the data table.

ExcelFormat option, ExcelHeader header Data table
ExportToTable (4 parameters)

Exports the values within the cell address parameters that you specify in the current spreadsheet into a data table.

Use the Format option to determine whether to export the values with or without formatting information.

Set the Header property to Header, if you want the first row to be the header for the data table.

String startAddress, String endAddress, ExcelFormat option, ExcelHeader header Data table
ExportToTable (6 parameters)

Exports the values within the row and column parameters that you specify in the current spreadsheet into a data table.

Use the Format option to determine whether to export the values with or without formatting information.

Set the Header property to Header, if you want the first row to be the header for the data table.

Int32 startRow, Int32 startColumn, Int32 endRow, Int32 endColumn, ExcelFormat option, ExcelHeader header Data table
FindAddress (2 parameters)

Returns the first matching cell address.

The search parameter is case sensitive.

String value, out String addressBoolean
FindCellAddress (3 parameters)

Returns the first matching cell row and column.

The search parameter is case sensitive.

String value, out Int32 row, Int32 column Boolean
FindCellAddress (4 parameters)

Returns the first matching cell address from the address range that you specify.

The search parameter is case sensitive.

String value, String startAddress, String endAddress, out String address Boolean
FindCellAddress (5 parameters)

Returns the first matching cell row and column from the address range that you specify.

The search parameter is case sensitive.

String value, String startAddress, String endAddress, out Int32 Row, out Int32 column Boolean
FindCellAddress (6 parameters)

Returns the first matching cell address from the address row and column range that you specify.

The search parameter is case sensitive.

String value, Int32 startRow, Int32 startColumn, Int32 endRow, Int32 endColumn, out String address Boolean
FindCellAddress (7 parameters)

Returns the first matching cell row and column from the address row/column range that you specify.

The search parameter is case sensitive.

String value, Int32 startRow, Int32 startColumn, Int32 endRow, Int32 endColumn, out Int32 row, out Int32 columnBoolean
FindCellAddresses (2 parameters)

Returns all matching cell addresses.

The search parameter is case sensitive.

String value, out String[] addressesBoolean
FindCellAddresses (4 parameters)

Returns all matching cell addresses from the address range that you specify.

The search parameter is case sensitive.

String value, String startAddress, String endAddress, out String[] addresses Boolean
FindCellAddresses (6 parameters)

Returns all matching cell addresses from the address row and column range that you specify.

The search parameter is case sensitive.

String value, Int32 startRow, Int32 startColumn, Int32 endRow, Int32 endColumn, out String[] addresses Boolean
FindColumnAddresses (2 parameters)

Returns all matching columns.

The search parameter is case sensitive.

String value, out Int32[] columns Boolean
FindColumnAddresses (3 parameters)

Returns all matching columns within the row that you specify.

The search parameter is case sensitive.

String value, Int32 row, out Int32[] columns Boolean
FindRowAddresses (2 parameters)

Returns all matching rows.

The search parameter is case sensitive.

String value, out Int32[] rowsBoolean
FindRowAddresses (3 parameters)

Returns all matching rows within the column that you specify.

The search parameter is case sensitive.

String value, Int32 column, out Int32[] rows Boolean
GetAllSheetsReturns all available sheet names. NoneIList<String>
GetCellBackgroundColor (1 parameter)Returns the background color of the cell. String address Color
GetCellBackgroundColor (2 parameters)Returns the background color of the cell. Int32 row, Int32 column Color
GetCellFont (1 parameter)Returns the font used in the cell. String addressFont
GetCellFont (2 parameters)Returns the font used in the cell. Int32 row, Int32 column Font
GetCellForegroundColor (1 parameter)Returns the foreground color of the cell. String address Color
GetCellForegroundColor (2 parameters)Returns the foreground color of the cell. Int32 row, Int32 columnColor
GetCellFormat (1 parameter)Returns the value format used in the cell. String address String
GetCellFormat (2 parameters)Returns the value format used in the cell. Int32 row, Int32 column String
GetCellFormattedValue (1 parameter)Returns the formatted cell value. String address String
GetCellFormattedValue (2 parameters)Returns the formatted cell value. Int32 row, Int32 column String
GetCellFormula (1 parameter)Returns the formula used in the cell. String addressString
GetCellFormula (2 parameters)Returns the formula used in the cell Int32 row, Int32 column String
GetCellHorizontalAlignment (1 parameter)Returns the horizontal alignment of the cell. String addressHorizontalAlignment
GetCellHorizontalAlignment (2 parameters)Returns the horizontal alignment of the cell. Int32 row, Int32 column HorizontalAlignment
GetCellStringValue (1 parameter)Returns the string value used in the cell. String address String
GetCellStringValue (2 parameters)Returns the string value used in the cell. Int32 row, Int32 column String
GetCellValue (1 parameter)Returns the value stored in the cell. String address Object
GetCellValue (2 parameters)Returns the value stored in the cell. Int32 row, Int32 column Object
GetCellVerticalAlignment (1 parameter)Returns the vertical alignment of the cell. String addressVerticalAlignment
GetCellVerticalAlignment (2 parameters)Returns the vertical alignment of the cell. Int32 row, Int32 columnVerticalAlignment
GetColumnWidthGets the entire column width, rounded to two decimal points. Int32 column Double
GetFirstCellAddress (1 parameter)

Returns the address of the first cell with data or formatting information or both.

The ValueOnly option only searches for the values and formulas for cells. The ValueAndStyle option additionally searches for any formatting information that overrides the defaults, such as color for cells.

ExcelSearch option String
GetFirstCellAddress (3 parameters)

Returns the address of the first cell with data or formatting information or both within the start and end addresses that you specify.

The ValueOnly option only searches for the values and formulas for cells. The ValueAndStyle option additionally searches for any formatting information that overrides the defaults, such as color for cells.

String startAddress, String endAddress, ExcelSearch option String
GetFirstCellAddress (5 parameters)

Returns the address of the first cell with data or formatting information or both within the start and end row and column that you specify.

The ValueOnly option only searches for the values and formulas for cells. The ValueAndStyle option additionally searches for any formatting information that overrides the defaults, such as color for cells.

Int32 startRow, Int32 startColumn, Int32 endRow, Int32 endColumn, ExcelSearch optionString
GetFirstColumn

Returns the first column with data or formatting information or both.

The ValueOnly option only searches for the values and formulas for cells. The ValueAndStyle option additionally searches for any formatting information that overrides the defaults, such as color for cells.

Int32 startColumn, Int32 endColumn, ExcelSearch option Int32
GetFirstRow

Returns the first row with data or formatting information or both.

The ValueOnly option searches only for cells that have values or formulas. The ValueAndStyle option additionally searches for cells with a non-default style, such as formatting or color.

Int32 startRow, Int32 endRow, ExcelSearch optionInt32
GetLastCellAddress (1 parameter)

Returns the address of the last cell with data or formatting information or both.

The ValueOnly option only searches for the values and formulas for cells. The ValueAndStyle option additionally searches for any formatting information that overrides the defaults, such as color for cells.

ExcelSearch option String
GetLastCellAddress (3 parameters)

Returns the address of the last cell with data or formatting information or both within the start and end addresses that you specify.

The ValueOnly option only searches for the values and formulas for cells. The ValueAndStyle option additionally searches for any formatting information that overrides the defaults, such as color for cells.

String startAddress, String endAddress, ExcelSearch option String
GetLastCellAddress (5 parameters)

Returns the address of the last cell with data or formatting information or both within the start and end rows and columns that you specify.

The ValueOnly option only searches for the values and formulas for cells. The ValueAndStyle option additionally searches for any formatting information that overrides the defaults, such as color for cells.

Int32 startRow, Int32 startColumn, Int32 endRow, Int32 endColumn, ExcelSearch optionString
GetLastColumn

Returns the last column with data or formatting information or both.

The ValueOnly option only searches for the values and formulas for cells. The ValueAndStyle option additionally searches for any formatting information that overrides the defaults, such as color for cells.

Int32 startColumn, Int32 endColumn, ExcelSearch option Int32
GetLastRow

Returns the last row with data or formatting information or both.

The ValueOnly option only searches for the values and formulas for cells. The ValueAndStyle option additionally searches for any formatting information that overrides the defaults, such as color for cells.

Int32 startRow, Int32 endRow, ExcelSearch option Int32
GetRowHeightGets the height of the row, rounded to one decimal point. Int32 row Double
GetSheetCountReturns the available sheet count. NoneInt32
GetSheetNameReturns the sheet name at the index that you specify. Int32 indexString
HideSheetHides a sheet. String sheetName Boolean
ImportFromTable (2 parameters)Imports a data table into the spreadsheet starting at address that you specify. String address, Data table table Void
ImportFromTable (3 parameters)Imports data table values into the spreadsheet starting at the row and column number that you specify. Int32 row, Int32 column, Data table table Void
InsertSheetInserts a new sheet at the position that you specify. String sheetName, Int32 index Void
IsSheetHiddenChecks to see if the sheet is hidden. String sheetName Boolean
MoveColumns

Moves columns to another sheet at a position that you specify.

The ValueOnly option only moves the values. The ValueAndStyle option additionally includes all formatting.

String destinationSheetName, Int32 startColumn, Int32 endColumn, Int32 destinationColumn, ExcelOption option Void
MoveRows

Moves rows to another sheet at a position that you specify.

The ValueOnly option only moves the values. The ValueAndStyle option additionally includes all formatting.

String destinationSheetName, Int32 startRow, Int32 endRow, Int32 destinationRow, ExcelOption option Void
NewWorkbook (2 parameters)Creates a workbook file with one empty sheet. String filename, String sheetNameVoid
NewWorkbook (3 parameters)Creates a workbook file with one empty sheet with a password that you specify. String fileName, String sheetName, String password Void
OpenOpens a workbook for data access. NoneVoid
ReloadReloads workbook data and all associated ranges. Boolean saveVoid
RenameSheet (1 parameter)Renames the current sheet with the name that you specify. String newSheetName Void
RenameSheet (2 parameters)Renames a sheet at the index with the name that you specify. String newSheetName, Int32 Index Void
RenameSheet (2 parameters)Renames a sheet with the name that you specify. String oldSheetName, String newSheetNameVoid
RowColumnToAddressConverts a row and column into an alphanumeric address. Int32 row, Int32 column String
SaveSaves any changes to the workbook. NoneVoid
SaveToPDF (1 parameter)Saves the entire workbook to a PDF file. String fileName Boolean
SaveToPDF (2 parameters)Exports the sheets in workbook to a PDF file. String fileName, String[] sheetNamesBoolean
SetCellBackgroundColor (2 parameters)Sets the cell background color for the address that you specify. String address, Color color Void
SetCellBackgroundColor (3 parameters)Sets the cell background color for the row and column number that you specify. Int32 row, Int32 column, Color colorVoid
SetCellFont (2 parameters)Sets the cell font for the address that you specify. String address, Font fontVoid
SetCellFont (3 parameters)Sets the cell font for the row and column number that you specify. Int32 row, Int32 column, Font font Void
SetCellForegroundColor (2 parameters)Sets the cell foreground color for the address that you specify. String address, Color color Void
SetCellForegroundColor (3 parameters)Sets the cell foreground color for the row and column number that you specify. Int32 row, Int32 column, Color color Void
SetCellFormat (2 parameters)Sets the cell value format for the address that you specify. String address, String formatVoid
SetCellFormat (3 parameters)Sets the cell value format for the row and column number that you specify. Int32 row, Int32 column, String format Void
SetCellFormula (2 parameters)Sets the cell formula for the address that you specify. String address, String formula Void
SetCellFormula (3 parameters)Sets the cell formula for the row and column number that you specify. Int32 row, Int32 column, String formula Void
SetCellHorizontalAlignment (2 parameters)Sets the cell horizontal alignment for the address that you specify. String address, HorizontalAlignment alignment Void
SetCellHorizontalAlignment (3 parameters)Sets the cell horizontal alignment for the row and column number that you specify. Int32 row, Int32 column, HorizontalAlignment alignmentVoid
SetCellStringValue (2 parameters)Sets the cell string value for the address that you specify. String address, String value Void
SetCellStringValue (3 parameters)Sets the cell string value for the row and column number that you specify. Int32 row, Int32 column, String value Void
SetCellValue (2 parameters)Sets the cell value for the address that you specify. String address, Object valueVoid
SetCellValue (3 parameters)Sets the cell value for the row and column number that you specify. Int32 row, Int32 column, Object value Void
SetCellVerticalAlignment (2 parameters)Sets the cell vertical alignment for the address that you specify. String address, VerticalAlignment alignment Void
SetCellVerticalAlignment (3 parameters)Sets the cell vertical alignment for the row and column number that you specify. Int32 row, Int32 column, VerticalAlignment alignment Void
SetColumnBackgroundColorSets the background color for the column. Int32 column, Color color Void
SetColumnForegroundColorSets the foreground color for the column. Int32 column, Color color Void
SetColumnWidthSets the column width, rounded to two decimals. Int32 column, Double widthVoid
SetRowBackgroundColorSets the background color for the row. Int32 row, Color color Void
SetRowForegroundColorSets the foreground color for the row. Int32 row, Color color Void
SetRowHeightSets the row height, rounded to one decimal point. Int32 row, Double heightVoid
SheetExistsChecks to see if the sheet exists. String sheetNameBoolean
UnhideSheetMakes a sheet visible. String sheetName Boolean
Did you find this content helpful? YesNo

Have a question? Get answers now.

Visit the Support Center to ask questions, engage in discussions, share ideas, and help others.

We'd prefer it if you saw us at our best.

Pega.com is not optimized for Internet Explorer. For the optimal experience, please use:

Close Deprecation Notice
Contact us