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:
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
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
Range('D1').api.Font.Size = 20
Range('D1').api.Interior.ColorIndex = 3
Range('A2').api.Font.ColorIndex = 1
def rgbToInt(rgb):
colorInt = rgb[0] + (rgb[1] * 256) + (rgb[2] * 256 * 256)
return colorInt
Range('D1').api.Interior.Color = rgbToInt((255,255,0))
Range('A1').color = (255,255,255)
Order: 1 = ascending, 2 = descending
Range('A2:B64').api.Sort(Key1=Range('B2').api, Order1=1, Orientation=1)
# do: dir(constants.HAlign) to get list of valid constants
Range('A2').api.HorizontalAlignment = constants.HAlign.xlHAlignCenter
# do: dir(constants.VAlign) to get list of valid constants
Range('A2').api.VerticalAlignment = constants.VAlign.xlHAlignCenter
Range('J1').api.Font.Bold = True
# GetCharacters(start_index, length)
Range('J1').api.GetCharacters(1,4).Font.Bold = True # make first 4 characters bold
Range('J12:K12').api.MergeCells = True
Range('J12:K12').api.MergeCells = False
import win32com.client as win32
Range('B1').api.AutoFill(Range('B1:B10').api, constants.AutoFillType.xlFillDefault)
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)
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')
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:
# 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
wb.api.Windows(1).SplitColumn = 0
wb.api.Windows(1).SplitRow = 1
wb.api.Windows(1).FreezePanes = True
Copy / Paste more than 1 column:
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.
Range('E1').value = Range('A1').vertical.options(ndim=2).value
This snippet below will insert numbers 0 through 9 in a column
# 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)
# 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
# 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.
# 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
import xlwings as xw
app1 = xw.apps
wb = app1.active.books.active
wb.sheets.active.api.TextBoxes("TextBox 1").Text = 'Test'
wb.sheets.active.api.TextBoxes("TextBox 1").Text
import xlwings as xw
from xlwings import Range
app1 = xw.apps
wb = app1.active.books.active
Delete rows 1 through 10
Range('1:10').api.Delete()
Delete columns A through B
Range('A:B').api.Delete()
To specify direction to shift cells when deleting block of cells, import DeleteShiftDirection and choose either xlShiftUp or xlShiftToLeft
from xlwings.constants import DeleteShiftDirection
Range('A1:B2').api.Delete(DeleteShiftDirection.xlShiftToLeft) # or xlShiftUp
To insert rows or columns, just replace Delete() with Insert()
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)