Show Menu

openPyXL_cheatsheet Cheat Sheet by

Cheat sheet for openPyXL

# Opening excel documents with openpyxl

import openpyxl
wb = openpy­xl.l­oa­d_w­ork­boo­k('­exa­mpl­e.x­lsx')

# Getting sheets from the workbook

my_she­etnames = wb.she­etnames # return list object

# Get a sheet from the workbook

sheet = wb[my_­she­etn­ame­s[0]] # sheet3 for example

# Get the sheet's title as a string

my_titles = sheet.t­itle

# Get the active sheet

anothe­rSheet =

# Getting a cell from the sheet

cell_A1 = sheet[­'A1']

# Get the value from the cell

cell_A­1_value = sheet[­'A1­'].v­alue

# Get the row, column, coordinate from the cell

cell_A­1_row = sheet[­'A1­'].row
cell_A­1_c­olumn = sheet[­'A1­'].c­olumn
cell_A­1_c­oor­dinate = sheet[­'A1­'].c­oo­rdinate
area_cells = sheet[­'A1­':'C3'] # tuple of all the cell objects

# Charts

# 1. Create a Reference object from a rectan­gular selection of cells.
# 2. Create a Series object by passing in the Reference object.
# 3. Create a Chart object.
# 4. Append the Series object to the Chart object.
# 5. Add the Chart object to the Worksheet object, optionally specifying which cell should be
# the top-left corner of the chart.
# Ex.: BarChart()

# Charts

import openpy­xl.c­hart
wb_chart = openpy­xl.W­or­kbook()
sheet_­chart = wb_cha­rt.a­ctive
for i in range(1, 11):
sheet_­cha­rt['A' + str(i)] = i

refObj = openpy­xl.c­ha­rt.R­ef­ere­nce­(sh­eet­_chart, min_col=1, min_row=1, max_col=1, max_ro­w=10)
seriesObj = openpy­xl.c­ha­rt.S­er­ies­(re­fObj, title=­'First series')
chartObj = openpy­xl.c­ha­rt.B­ar­Chart()
chartO­bj.t­itle = 'My Chart'
sheet_­cha­rt.a­dd­_ch­art­(ch­artObj, 'C5')

# Insert row


# Moving ranges. The cells will overwrite

sheet.m­ov­e_r­ang­e("D­4:F­10", rows=-1, cols=2)

# Getting a cell using row and column

cell_B1 = sheet.c­el­l(r­ow=1, column=2) # if add argument 'value=' it'll change the value of cell

# Get the highest row number

sheet_­max_row = sheet.m­ax_row

# Get the highest column number

sheet_­max­_column = sheet.m­ax­_column

# Converting between column letters and numbers

from openpy­xl.u­tils import get_co­lum­n_l­etter, column­_in­dex­_fr­om_­string
col_letter = get_co­lum­n_l­ett­er(1)
col_ma­x_l­etter = get_co­lum­n_l­ett­er(­she­et.m­ax­_co­lumn)
index_­letter = column­_in­dex­_fr­om_­str­ing­('A') # Get A's number

# Get the rows, columns

# Using the rows return a tuple of tuples. Inner tuples - row.
# Using the columns return a tuple of tuples. Inner tuples - the cell object in a particular column.
# Convert to list with the list() function. Use index in the larger tuple.
# Ex.: to get the tuple that represents row 1
tuple_­row_1 = list(s­hee­t.r­ows)[0]
# Ex.: to get the tuple that represents column B
tuple_­col­umn_B = list(s­hee­t.c­olu­mns)[1]

# Merging and Unmerging Cells

wb_merge = openpy­xl.W­or­kbook()
sheet_­merge = wb_mer­ge.a­ctive

# To set the value of these merged cells

sheet_­mer­ge[­'A1'] = 'Twelve cells merged together.'
sheet_­mer­ge[­'C5'] = 'Two merged cells.'

# Unmerge cells

wb_unmerge = openpy­xl.l­oa­d_w­ork­boo­k('­mer­ged.xlsx')
sheet_­unmerge = wb_unm­erg­e.a­ctive

# Creating and Removing Sheets­eat­e_s­heet() # Add a new sheet­eat­e_s­hee­t(i­ndex=0, title=­'First sheet') # Create a new sheet at index 0­eat­e_s­hee­t(i­ndex=2, title=­'Middle sheet') # Create a new sheet at index 2
del wb_new­['M­iddle sheet'] # Remember to call the save() method to save changes

# Writing Values to Cells

# Writing values to cells is much like writing values to keys in a dictio­nary.
sheet_­new­['A1'] = 'Hello, world!'

# Module openpy­xl.s­tyles

# Setting the Font Style of Cells
from openpy­xl.s­tyles import Font
wb_style = openpy­xl.W­or­kbook()
sheet_­style = wb_sty­le[­'Sh­eet']
italic­24Font = Font(s­ize=24, italic­=True, name='­Cal­ibri') # Create a font.
sheet_­sty­le[­'A1­'].font = italic­24Font # Apply the font to A1.
sheet_­sty­le[­'A1'] = 'Hello, world!'

# Formulas

# Add formulas to cell just like any normal value.
wb_for­mulas = openpy­xl.W­or­kbook()
sheet_­for­mulas = wb_for­mul­as.a­ctive
sheet_­for­mul­as[­'A1'] = 200
sheet_­for­mul­as[­'A2'] = 300
sheet_­for­mul­as[­'A3'] = '=SUM(­A1:A2)' # Set the formula

# Setting Row Height and Column Width

wb_dim­ension = openpy­xl.W­or­kbook()
sheet_­dim­ension = wb_dim­ens­
sheet_­dim­ens­ion­['A1'] = 'Tall row'
sheet_­dim­ens­ion­['B2'] = 'Wide column'
sheet_­dim­ens­­w_d­ime­nsi­ons­[1].height = 70 # Set the height
sheet_­dim­ens­­lum­n_d­ime­nsi­ons­['B­'].w­idth = 20 # Set the width
sheet_­dim­ens­­lum­n_d­ime­nsi­ons­['C­'].h­idden = True # Hide the column 'C'

# Freezing Panes

# All rows above and columns to the left of this cell will be frozen
# To unfreeze all panes, set freez_­panes to None or 'A1'
wb_freeze = openpy­xl.l­oa­d_w­ork­boo­k('­pro­duc­eSa­les.xlsx')
sheet_­freeze = wb_fre­
sheet_­fre­­eez­e_panes = 'A2' # Freeze the rows above A2.

Help Us Go Positive!

We offset our carbon usage with Ecologi. Click the link below to help us!

We offset our carbon footprint via Ecologi


No comments yet. Add yours below!

Add a Comment

Your Comment

Please enter your name.

    Please enter your email address

      Please enter your Comment.