Working with Excel Automation

The Excel activities help users to automate all aspects of Microsoft Excel. It is an application intensely used by many in all types of businesses.

Most of our Excel activities takes inputs from user where they can specify the Sheet/Range/Cell. Here, we have provided you with details about setting these inputs.

Sheet Index vs. Sheet Name

A workbook can contain one or more spreadsheets. You need to specify the spreadsheet index on which this activity needs to perform the task. The index is zero-based and starts from left to right in the workbook.

For example, consider a workbook containing three spreadsheets, as indicated in the image below:

Use the following index mapping:

SpreadSheet Name	SpreadSheet Index
Sheet1	0
Sheet2	1
Sheet3	2

note

If Sheet Name or Sheet Index is not provided, the activity will consider the active spreadsheet in the current workbook.

Column Index vs. Column Letter

A Column Index represents a particular column in a sheet. It is a zero-based index starting from left most column towards the right of the sheet.

In Excel, the columns are represented using letters; hence, you can refer to the following examples, which map the corresponding letters to a numeric index.

Column Letter	Column Index
A	0
B	1
C	2
...	...
Z	25

Row Index

A Row Index represents a particular row in a spreadsheet. It is a zero-based index starting from the topmost rows and moving toward the bottom.

Cell

A cell represents one specific cell inside a spreadsheet. It combines Column Letter and Row Index.

note

The Row Index is zero-based. However, when representing a Cell, the topmost row will start from 1. Hence, ensure that you do a plus one to the row index while building the cell address

Please refer to the table below, which denotes some of the examples for cell addresses:

Cell Address	Description
"A1"	The first cell in the sheet whose Column Letter is A and Row Index is 0
"D55"	The cell whose Column Letter is D and Row Index is 54

Range

A Range specifies a range of cells in a spreadsheet. It is specified as a string value. A Range string has the following formats:

Range Type	Description
Row Range	The range is specified in terms of Row indices. For example: 1:1 - specifies all the cells in the first row. 1:100 - specifies all the cells in the first hundred rows.
Column Range	The range is specified in terms of Column indices. For example: A:A - specifies all the cells under the column A. A:D - specifies all the cells under the columns A, B, C, and D.
Cell Range	The range is specified as cell addresses. For example: A1:D4 - specifies all the cells within the first four rows and under the columns A, B, C, and D. A1 - specifies all the cells starting from the cell address A1 and continues up until the last cell which has a valid data.

Converting numeric index to letter

Sometimes, while designing process automation, converting Column Index to Column Letter becomes necessary. You can convert the numeric index to a letter-based Column Index using IntelliBuddies Excel utilities.

Similarly, converting a letter-based column index to a numeric-based column index could be achieved by using the following expression:

IntelliBuddies.Activities.Excel.ExcelExtensions.ToIndex ("A")

Get row and column index from the Cell address

You can extract the row and column index from the Cell address using IntelliBuddies Excel utilities.

In the above image, index["Row"] returns 3, and index ["Column"] returns 1.

IntelliBuddies.Activities.Excel.ExcelExtensions.GetIndex()