Cell

Cell

interacts with cells in your spreadsheet. It can get and set (that means ‘read’ and ‘change’) a cell’s value, formula, and formatting.

Creating a Cell

There are a few ways to create a cell:

  1. By name:

    >>> Cell("A1")
    A1
    
  2. With a (row, column) pair. column can be a letter or a number:

    >>> Cell(2,1)
    A2
    >>> Cell(2, "A")
    A2
    
  3. With a named range. The range should contain just one cell. For example, if “Why_Not_VBA” is the cell “NOT1993”:

    >>> Cell("Why_not_VBA")
    NOT1993
    

    By default, Cells are on the active worksheet. To use a different worksheet, pass its name as the first argument when calling Cell.

    If you’re using a named range, it has to be on the same sheet as Cell (which is the active sheet if you don’t pass one explicitly).

    >>> Cell("Sheet2", 2, 1)
    A2
    ## A2 on Sheet 2
    >>> Cell("Sheet2","A2")
    A2
    ## A2 on Sheet 2
    

Properties

row

returns a cell’s row.

>>> Cell("A2").row
2
col

returns a cell’s column.

>>> Cell("A2").col
1
position

returns a cell’s position as a tuple.

>>> Cell("A2").position
(2,1)
sheet

returns a cell’s sheet.

>>> Cell("A1").sheet
"Sheet1"
name

returns a cell’s name.

>>> Cell("A1").name
"A1"
>>> Cell(2,1).name
"A2"

You can’t change a cell’s row, column, position, sheet, or name.

value

gets and sets the value of a cell. If you want a cell to use an Excel formula, set it with formula instead.

A blank cell returns None. (This won’t print in the shell, unless you tell it to.)

>>> Cell("A1").value = 42
>>> Cell("A1").value
42
>>> Cell("A1").clear()
>>> Cell("A1").value
>>> print Cell("A1").value
None

If you get the value of a cell with a date in it, it’ll return a datetime object. If you set a cell to a datetime object, that date will be written to Excel. For example, if A1 has “1/31/2013” in it,

>>> Cell("A1").value
datetime.datetime(2013, 1, 31, 0, 0)
>>> Cell("A1").value = datetime.datetime(2014, 1, 31)
## changes A1 to 1/31/2014
vertical

returns the values of the cells starting with the cell it’s called from, and ending in the last non-empty cell in the same column.

Setting vertical to an iterable will set the cell to the first value of the iterable, the cell under it to the second value of that iterable, and so on.

>>> Cell("A1").vertical = [3, 1, 4, 1, 5]
>>> CellRange("A1:A6").value
[3, 1, 4, 1, 5, None]
>>> Cell("A1").vertical
[3, 1, 4, 1, 5]
vertical_range

returns the CellRange that vertical gets its values from.

>>> Cell("A1").vertical_range
A1, A2, A3, A4, A5
horizontal

works just like vertical, but for rows.

horizontal_range

works just like vertical_range, but for rows.

table
gets and sets the 2-D table starting at Cell. The table comes back as a 2-d list (a list of lists). For example, if your spreadsheet looks like this:
>>> Cell("A1").table
[[1, 2, 3], [4, 5, 6], [7, 8, 9]]

table returns the box ending in the last row in Cell’s column, and the last column in Cell’s row. Here’s what you’d get with these tables:

_images/cell_table2.png
>>> Cell("A1").table
[[1, 2, 3], [4, 5, 6], [7, 8, 9]]
_images/cell_table3.png
>>> Cell("A1").table
[[1, 2, 3], [4, 5, 6], [7, 8, 9], [1, None, None]]
When setting table, you should pass in a 2-d list where each row is the same length.
>>> Cell("A1").table = [[1, 2, 3], [4, 5], [6, 7, 8]]
## Error

won’t work; you need to do:

>>> Cell("A1").table = [[1, 2, 3], [4, 5, None], [6, 7, 8]]
table_range

table_range returns the CellRange that table gets its values from. In the first table above, you’d see:

>>> Cell("A1").table_range
A1:C3
formula

formula gets and sets the formula of a cell. You can set it by passing the Excel formula as a string. If you get the formula from a cell that doesn’t have a formula, it’ll return its value instead.

>>> Cell("A3").formula = "=A1*A2"
>>> Cell("A3").formula
"=A1*A2"
>>> Cell("A1").value = 6
>>> Cell("A2").value = 7
>>> Cell("A3").value
42
comment

comment gets and sets the comment in a cell. Setting a comment to an empty string removes it.

>>> Cell("A1").comment = 'this is a \n comment.'
>>> print Cell("A1").comment
this is a
comment

hyperlink will get and set the comment in a cell. Setting a hyperlink to an empty string removes it.

For a hyperlink to work properly in Excel, you should set it to a full web address (including http), or an email address (including the mailto: command).

>>> Cell("A1").hyperlink = "https://www.datanitro.com"
>>> Cell("A2").hyperlink = "mailto:code@datanitro.com"
alignment

A cell can have one of five alignments: left, right, center, justify, and general. ‘general’ is the default.

>>> Cell("A1").alignment
'general'
>>> Cell("A1").alignment = 'center'
>>> Cell("A1").alignment
'center'
color

color gets and sets the color of a cell. Excel stores colors as 24 bit numbers, with the first two hexadecimal digits describing the amount of red, the next two the amount of green, and the last two the amount of blue, like html. You can pass 24 bit numbers to color as hexadecimal numbers, strings, or regular numbers.

You can also use 16 named colors: black, white, red, blue, green, yellow, purple, gray, silver, maroon, olive, lime, teal, aqua, navy, and fuchsia

df

Cell.df lets you write Pandas dataframes directly to Excel.

>>> import pandas
... # define my_dataframe
>>> Cell("A1").df = my_dataframe

Calling Cell.df will return the same thing as Cell.table.

Formatting

font

The font property of a cell is a class that controls a cell’s font. font has the following properties: color, size, bold, italic, underline, strikethrough, subscript, and superscript. Printing font will print a cell’s font color, size, and any active font styles.

e.g: print Cell(“A1”).font will print font color: black, font size: 11 if A1 has not had any changes to its font. If A2 has a red, size 8 font with bold, italic, and superscript turned on, print Cell(“A2”).font will print font color:red, font size: 8, bold, italic, superscript.

font.size

size gets and sets font sizes. If a cell has text of more than one size, this will return 0.

>>> Cell("A1").font.size = 409
# this makes the text in A1 very large
font.color

color gets and sets font colors. This accepts colors in the same format as Cell.color. If a cell has mixed colors, font.color will return ‘black’.

>>> Cell("A1").font.color = 'red'
# this makes the text in A1 red
font.bold
font.italic
font.underline
font.strikethrough
font.subscript
font.superscript

Calling any of bold, italic, underline, strikethrough, subscript, and superscript will return True if the property is on for the cell, and False if it’s off. A value passed to the Cell will be interpreted as a boolean (i.e. 0, ‘’, [], and () evaluate to False, nonempty containers and non-zero numbers evaluate to True). If a cell has mixed formatting (e.g. part bold and part not bold), the associated style will return True. If a cell has mixed superscript and/or subscripts, both superscript and subscript will return True.

>>> Cell("A1").font.bold = True
## A1 is now bold
>>> Cell("A1").font.bold
True
>>> Cell("A1").font.italic = True
## A1 is now italic
>>> Cell("A1").font.color = 'red'
## The text in A1 is now red

Notes: Excel supports a maximum of 512 different fonts. Setting more than this number of fonts will cause Excel to freeze or raise errors. (For example, coloring a 25 by 25 block of cells with different font colors will cause errors.)

Excel font sizes must be positive, and the maximum font size is 409.

Only one of subscript and superscript can be active at a time. Turning on superscript in a cell set to subscript will turn off superscript, and vice versa.

Methods

Cells also have some methods.

clear()

This resets all of a cell’s properties.

>>> Cell("A1").clear()
>>> Cell("A1").value == None
True
>>> Cell("A1").font.color
'Black"
>>> Cell("A1").font.size
11
>>> Cell("A1").alignment
'general'
copy_from(Cell)

Copies one cell to another. This copies all of a cell’s properties.

>>> Cell("A1").formula = "=3*4"
>>> Cell("A1").font.size = 50
>>> Cell("A1").color = 'green'
>>> Cell("A2").copy_from(Cell("A1"))
>>> Cell("A2").value
12
>>> Cell("A2").formula
"=3*4"
>>> Cell("A2").font.size
50
>>> Cell("A2").color
'green'
copy_format_from(Cell)

Copies the formatting from one cell to another. This copies all of a cell’s properties (except value and formula).

>>> Cell("A1").value = 10
    >>> Cell("A2").value = 5
>>> Cell("A1").font.size = 50
>>> Cell("A1").color = 'green'
>>> Cell("A2").copy_format_from(Cell("A1"))
>>> Cell("A2").value
5
>>> Cell("A2").font.size
50
>>> Cell("A2").color
'green'
is_empty()

Returns True if a cell is empty, and False otherwise. This is the same as checking if the cell’s value is equal to the empty string.

>>> Cell("A1").value = "Hi"
>>> Cell("A1").is_empty()
False
>>> Cell("A1").clear()
>>> Cell("A1").is_empty()
True
offset(row, col)

Returns the cell offset by (row, col) from the cell it’s called on.

>>> Cell("A1").offset(0,1)
B1
subtraction

You can subtract two cells to get the offset between them.

>>> Cell("B4") - Cell("A2")
(2,1)
print

Printing a cell gives the cell’s name and its sheet.

>>> print Cell("Sheet1", "A2")
A2 on Sheet1
set_name(name)

Creates a named range containing the cell it’s called on. If the named range already exists, it’s overwritten.

>>> Cell("A1").set_name("first_cell")
>>> Cell("first_name")
A1

Range Syntax

You can enter a range as you would in Excel, using ”:”, ”,”, and ” ” (space). (The last two are union and intersection). You can use “^” for intersection instead of ” ”.

active_cell([cell])

gets and sets the active cell. Passing no argument will return the active cell, and passing a cell will make that cell active.

>>> active_cell()
A1
>>> active_cell("A2")
>>> active_cell()
A2

Unicode

If you read a cell with unicode characters into the Python shell, the characters are returned as codepoints (e.g: “u’u671du540d’). To display the characters, you have to print the value. The characters may not print properly if they aren’t local to your version of Windows.