Excel Formatting Cheat Sheet

Using xlwings and win32com

xlwings is a Python library that allows you to essentially write Excel VBA code, but using the Python language. But, it has its limitations. To overcome the limitations, we can also use the win32com library which taps into Microsoft Excel's COM objects. With win32com, we can access almost all the Excel objects that we need. The downsides to using win32com is that it is platform specific, API syntax is less Pythonic, and there are no official documentation. StackOverflow and Google will be your friend. These snippets were tested on Windows 7 and Excel 2007.

UPDATE: Tested on Excel 2016 and the snippets appear to work.
UPDATE #2: Thanks to Felix at ZoomerAnalytics, found out you can get Excel constants directly from xlwings via:

from xlwings import constants  

example: constants.HAlign.xlHAlignCenter

UPDATE #3: Updated for xlwings version 0.9.x

Import the necessary libraries

In [ ]:
import xlwings as xw
from xlwings import Range, constants

app1 = xw.apps
wb = app1.active.books.active
# Disable screen updating to improve performance, then enable it when done
# app1.screen_updating = False
# Do something...
# app1.screen_updating = True

Border ids are 7 through 12 and correspond to borders for xlEdgeTop, xlEdgeBottom, xlEdgeRight, xlEdgeLeft, xlInsideHorizontal, and xlInsideVertical

In [54]:
for cell in Range('A1').current_region:
    for border_id in range(7,12):
        cell.api.Borders(border_id).LineStyle = 1
        cell.api.Borders(border_id).Weight = 2
In [58]:
Range('D1').api.Font.Size = 20
In [59]:
Range('D1').api.Interior.ColorIndex = 3
In [5]:
Range('A2').api.Font.ColorIndex = 1
In [ ]:
def rgbToInt(rgb):
    colorInt = rgb[0] + (rgb[1] * 256) + (rgb[2] * 256 * 256)
    return colorInt

Range('D1').api.Interior.Color = rgbToInt((255,255,0))
In [ ]:
Range('A1').color = (255,255,255)

Order: 1 = ascending, 2 = descending

In [180]:
Range('A2:B64').api.Sort(Key1=Range('B2').api, Order1=1, Orientation=1)
Out[180]:
True
In [283]:
# do: dir(constants.HAlign) to get list of valid constants
Range('A2').api.HorizontalAlignment = constants.HAlign.xlHAlignCenter
In [ ]:
# do: dir(constants.VAlign) to get list of valid constants
Range('A2').api.VerticalAlignment = constants.VAlign.xlHAlignCenter
In [220]:
Range('J1').api.Font.Bold = True
In [222]:
# GetCharacters(start_index, length)
Range('J1').api.GetCharacters(1,4).Font.Bold = True  # make first 4 characters bold
In [241]:
Range('J12:K12').api.MergeCells = True
In [ ]:
Range('J12:K12').api.MergeCells = False
In [ ]:
import win32com.client as win32
Range('B1').api.AutoFill(Range('B1:B10').api, constants.AutoFillType.xlFillDefault)

or to autofill to last row, create last_cell variable:

In [300]:
import win32com.client as win32
last_cell = Range('B2:J2').current_region.last_cell.get_address(False, False)
Range('B2:J2').api.AutoFill(Range('B2:'+last_cell).api, constants.AutoFillType.xlFillDefault)
Out[300]:
True
In [ ]:
Range('A2').autofit()  # autofit column(s) and row(s)
Range('A2').autofit('c') # or Range('A2').autofit('columns')
Range('A2').autofit('r') # or Range('A2').autofit('rows')
In [ ]:
for x in Range('A1:F22'):
    if x.value == 'Found me':
        print(x.get_address())

We can use the same logic for searching text to perform conditional formatting:

In [15]:
# Let's say we have a column containing numbers, highlight the background color if number is > 5
for x in Range('A1').vertical:
    if x.value > 5:
        x.color = (160,160,160)  # set background color to gray
In [ ]:
wb.api.Windows(1).SplitColumn = 0
wb.api.Windows(1).SplitRow = 1
wb.api.Windows(1).FreezePanes = True

Copy / Paste more than 1 column:

In [83]:
Range('E1').value = Range('A1:C1').vertical.value

When copying / pasting one column, you have to use the options method or else you'll get an error. Not sure why. I opened a github issue.

In [73]:
Range('E1').value = Range('A1').vertical.options(ndim=2).value

This snippet below will insert numbers 0 through 9 in a column

In [ ]:
# From any location / selected cell
current_location = wb.get_selection()  # get current location
for n in range(10):
    current_location.value = n
    current_location = current_location.offset(row_offset = 1)
    
In [ ]:
# or from a specific location (hard-coded)
start_index = 1
for n in range(10):
    Range('A' + str(start_index)).value = n
    start_index = start_index + 1
In [3]:
# From any location / selected cell
current_location = wb.get_selection()  # get current location
for n in range(10):
    current_location.value = n
    current_location = current_location.offset(column_offset = 1)

For hard-coded location, have to use Range((row, column)) syntax so that we can increment/offset the column numerically since I don't know of a way to increment the alphabet intelligently.

In [11]:
# Hard-coded location
current_column = Range('G15').column
current_row = Range('G15').row
for n in range(10):
    Range((current_row, current_column)).value = n
    current_column = current_column + 1  # increment the column
In [63]:
import xlwings as xw

app1 = xw.apps
wb = app1.active.books.active
In [50]:
wb.sheets.active.api.TextBoxes("TextBox 1").Text = 'Test'
In [51]:
wb.sheets.active.api.TextBoxes("TextBox 1").Text
Out[51]:
'Test'
In [12]:
import xlwings as xw
from xlwings import Range

app1 = xw.apps
wb = app1.active.books.active

Delete rows 1 through 10

In [5]:
Range('1:10').api.Delete()
Out[5]:
True

Delete columns A through B

In [16]:
Range('A:B').api.Delete()
Out[16]:
True

To specify direction to shift cells when deleting block of cells, import DeleteShiftDirection and choose either xlShiftUp or xlShiftToLeft

In [ ]:
from xlwings.constants import DeleteShiftDirection
Range('A1:B2').api.Delete(DeleteShiftDirection.xlShiftToLeft)  # or xlShiftUp

To insert rows or columns, just replace Delete() with Insert()

Bonus Material

In [65]:
import time

message = 'ALL YOUR BASE ARE BELONG TO US!'

for i in range(1, len(message)+1):
    Range('A1').value = message[:i]
    time.sleep(0.2)