ExcelConnector
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.
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
| Property | Description |
| CsvOptions | Options for loading and saving workbooks in CSV (Comma Separated Values) format. |
| FileName | Enter the path to the Excel workbook that you want the Excel connector to work with. |
| Password | If the Excel workbook uses a password, enter that password. |
| SheetName | Enter 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
| Event | Description |
| Saved | This event occurs when you save the Excel connector workbook. |
| Method | Description | Parameters | Result type |
| AddColumn | Adds a column at the index that you specify. | Int32 index | Void |
| AddressToRowColumn | Converts an alphanumeric address into a row and column integer. | String address, out Int32 row, out Int32 column | Void |
| AddRow | Adds a row to the worksheet at the index that you specify. | Int32 index | Void |
| AddSheet | Adds a sheet to the workbook. | String sheetName | Boolean |
Calculate (no parameters) | Recalculates all formulas on the sheet. | None | Void |
| 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 option | Void |
| Close | Closes the workbook and clears all of the properties. | None | Void |
| 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 |
| DeleteColumns | Removes multiple columns. | Int32 startColumn, Int32 endColumn | Void |
| DeleteRows | Removes multiple rows. | Int32 startRow, Int32 endRow | Void |
| DeleteSheet | Deletes 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 address | Boolean |
| 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 column | Boolean |
| FindCellAddresses (2 parameters) | Returns all matching cell addresses. The search parameter is case sensitive. | String value, out String[] addresses | Boolean |
| 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[] rows | Boolean |
| 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 |
| GetAllSheets | Returns all available sheet names. | None | IList<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 address | Font |
| 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 column | Color |
| 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 address | String |
| 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 address | HorizontalAlignment |
| 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 address | VerticalAlignment |
| GetCellVerticalAlignment (2 parameters) | Returns the vertical alignment of the cell. | Int32 row, Int32 column | VerticalAlignment |
| GetColumnWidth | Gets 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 option | String |
| 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 option | Int32 |
| 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 option | String |
| 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 |
| GetRowHeight | Gets the height of the row, rounded to one decimal point. | Int32 row | Double |
| GetSheetCount | Returns the available sheet count. | None | Int32 |
| GetSheetName | Returns the sheet name at the index that you specify. | Int32 index | String |
| HideSheet | Hides 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 |
| InsertSheet | Inserts a new sheet at the position that you specify. | String sheetName, Int32 index | Void |
| IsSheetHidden | Checks 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 sheetName | Void |
| NewWorkbook (3 parameters) | Creates a workbook file with one empty sheet with a password that you specify. | String fileName, String sheetName, String password | Void |
| Open | Opens a workbook for data access. | None | Void |
| Reload | Reloads workbook data and all associated ranges. | Boolean save | Void |
| 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 newSheetName | Void |
| RowColumnToAddress | Converts a row and column into an alphanumeric address. | Int32 row, Int32 column | String |
| Save | Saves any changes to the workbook. | None | Void |
| 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[] sheetNames | Boolean |
| 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 color | Void |
| SetCellFont (2 parameters) | Sets the cell font for the address that you specify. | String address, Font font | Void |
| 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 format | Void |
| 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 alignment | Void |
| 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 value | Void |
| 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 |
| SetColumnBackgroundColor | Sets the background color for the column. | Int32 column, Color color | Void |
| SetColumnForegroundColor | Sets the foreground color for the column. | Int32 column, Color color | Void |
| SetColumnWidth | Sets the column width, rounded to two decimals. | Int32 column, Double width | Void |
| SetRowBackgroundColor | Sets the background color for the row. | Int32 row, Color color | Void |
| SetRowForegroundColor | Sets the foreground color for the row. | Int32 row, Color color | Void |
| SetRowHeight | Sets the row height, rounded to one decimal point. | Int32 row, Double height | Void |
| SheetExists | Checks to see if the sheet exists. | String sheetName | Boolean |
| UnhideSheet | Makes a sheet visible. | String sheetName | Boolean |
Previous topic Microsoft Office applications Next topic MicrosoftExcel connector