Working with cells¶
Aside from working with structured data, it's also possible to work with individual Excel cells.
Cells() method returns a list of cells in a range:
Cells()returns a list of cells in the current selection
Cells("A1:B2")returns a list of four cells in the active sheet (A1, A2, B1, B2)
Cells("Sheet2!A1:B2")same as above, but the cells belong to Sheet2
Cells("abc")returns a list of cells in a named range or a table called "abc"
Modifying cell values¶
In the following example, some cells do not have spaces between the first and last name of the person. I select the cells in Excel, and use the
Regex.Replace() method to insert a space where a lowercase letter is immediately followed by an uppercase letter.
Cells().Do(cell => cell.Value = Regex.Replace(cell.Value, "([a-z])(A-Z)", "$1 $2"))
Modifying cell formatting¶
We can also format cells. Let's highlight all cells of the current selection that contain a value of type
Cells() .Where(cell=>cell.Value?.GetType() == typeof(string)) .Do(cell => cell.Interior.ColorIndex = 40)