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.


No comments yet. Add yours below!

Add a Comment

Your Comment

Please enter your name.

    Please enter your email address

      Please enter your Comment.