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

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.