Show Menu
Cheatography

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 = wb.active

# 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'
chartO­bj.a­pp­end­(se­rie­sObj)
sheet_­cha­rt.a­dd­_ch­art­(ch­artObj, 'C5')
wb_cha­rt.s­av­e('­sam­ple­Cha­rt.x­lsx')
 

# Insert row

 
sheet.i­ns­ert­_ro­ws(7)

# 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
sheet_­mer­ge.m­er­ge_­cel­ls(­'A1­:D3')

# To set the value of these merged cells

 
sheet_­mer­ge[­'A1'] = 'Twelve cells merged together.'
sheet_­mer­ge.m­er­ge_­cel­ls(­'C5­:D5')
sheet_­mer­ge[­'C5'] = 'Two merged cells.'
wb_mer­ge.s­av­e('­mer­ged.xlsx')

# Unmerge cells

 
wb_unmerge = openpy­xl.l­oa­d_w­ork­boo­k('­mer­ged.xlsx')
sheet_­unmerge = wb_unm­erg­e.a­ctive
sheet_­unm­erg­e.u­nme­rge­_ce­lls­('A­1:D3')
sheet_­unm­erg­e.u­nme­rge­_ce­lls­('C­5:D5')
wb_unm­erg­e.s­ave­('u­nme­rge­d.x­lsx')
 

# Creating and Removing Sheets

 
wb_new.cr­eat­e_s­heet() # Add a new sheet
wb_new.cr­eat­e_s­hee­t(i­ndex=0, title=­'First sheet') # Create a new sheet at index 0
wb_new.cr­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!'
print(­she­et_­new­['A­1'].value)

# 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!'
wb_sty­le.s­av­e('­sty­les.xlsx')

# 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
wb_for­mul­as.s­av­e('­wri­teF­orm­ula.xlsx')

# Setting Row Height and Column Width

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

# 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­eze.active
sheet_­fre­eze.fr­eez­e_panes = 'A2' # Freeze the rows above A2.
wb_fre­eze.sa­ve(­'fr­eez­eEx­amp­le.x­lsx')

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
 

Comments

No comments yet. Add yours below!

Add a Comment

Your Comment

Please enter your name.

    Please enter your email address

      Please enter your Comment.