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


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


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)