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

Quick Links:

Adding line borders
Changing font size
Adding background color to cell using ColorIndex
Changing font color using ColorIndex
Changing background color of cell using RGB with win32com
Changing background color of cell using RGB with xlwings
Sorting a column
Horizontally or vertically aligning text
Make a font bold
Modify specific characters
Merge/un-merge cells
Autofill formula
Autofit column(s) and/or row(s)
Searching for text
Conditional formatting
Freeze top row
Copy / Paste
Inserting values into a column
Inserting values into a row
Getting text from a TextBox or setting the text of the TextBox
Deleting/Inserting rows or columns or block of cells

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

Adding line borders around range of cells per this SO question ¶

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

Change font size ¶

In [58]:

Range('D1').api.Font.Size = 20

Adding background color to a cell using ColorIndex ¶

In [59]:

Range('D1').api.Interior.ColorIndex = 3

Changing font color using ColorIndex ¶

In [5]:

Range('A2').api.Font.ColorIndex = 1

Changing background color using RGB values per SO question when using win32com¶

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))

Changing background color using RGB values using xlwings ¶

In [ ]:

Range('A1').color = (255,255,255)

Sorting a column per this SO ¶

Order: 1 = ascending, 2 = descending

In [180]:

Range('A2:B64').api.Sort(Key1=Range('B2').api, Order1=1, Orientation=1)

Out[180]:

True

Horizontally or vertically align text using Excel VBA constants ¶

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

Make a font bold ¶

In [220]:

Range('J1').api.Font.Bold = True

Make specific characters in a text bold ¶

In [222]:

# GetCharacters(start_index, length)
Range('J1').api.GetCharacters(1,4).Font.Bold = True  # make first 4 characters bold

Merge / Un-merge Cells ¶

In [241]:

Range('J12:K12').api.MergeCells = True

In [ ]:

Range('J12:K12').api.MergeCells = False

Autofill Formula ¶

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

Autofit column and/or rows ¶

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')

Searching for a text ¶

In [ ]:

for x in Range('A1:F22'):
    if x.value == 'Found me':
        print(x.get_address())

Conditional formatting ¶

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

Freeze top row ¶

In [ ]:

wb.api.Windows(1).SplitColumn = 0
wb.api.Windows(1).SplitRow = 1
wb.api.Windows(1).FreezePanes = True

Copy / Paste ¶

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

Inserting values into a column ¶

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

Inserting values into a row ¶

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

Getting text from a TextBox or setting the text of the TextBox ¶

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'

Deleting/Inserting rows or columns or block of cells ¶

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)