Python openpyxl Library

Openpyxl library to create Excel file and add list, dictionary, formats, formulas, charts and images


To install openpyxl , enter this command at your command prompt.
pip install openpyxl
To check the installed version of the library
import openpyxl
print(openpyxl.__version__)
To upgrade the openpyxl library to current version. Use this command at command prompt.
pip install openpyxl --upgrade --pre

Basic code using openpyxl

from openpyxl import Workbook
wb = Workbook() # create workbook 
ws1 = wb.active # work on default worksheet 
#ws1=wb.create_sheet('my_ws') # create worksheet 

ws1.column_dimensions['A'].width=10 # Column width 
ws1.row_dimensions[1].height=20 # height of the row 

ws1['A1']='Welcome '  # assign data to cell 
ws1['B1']='to plus2net' # assign data to cell B1 
d=ws1.cell(2,1,'Python') # assign using row column notation 

my_path='G:\\My drive\\testing\\openpyxl\\demo2.xlsx'#Path 
wb.save(my_path)
Changing width of multiple columns
l1=['A','B','C','D']
for i in l1:
    ws1.column_dimensions[i].width=20

Adding format

Adding format style to Excel worksheet by openpyxl
Import the required methods
from openpyxl import Workbook
from openpyxl.styles import PatternFill, Font
Adding background colour to cell
a1=ws1['A1']
a1.fill=PatternFill(fill_type='solid',start_color='FF00FF')
cell=ws1['A1']
cell.font=Font(size=20,bold=True,vertAlign='subscript') # font styles 
cell.fill=PatternFill(fill_type='solid',start_color='FF00FF')#Background color
Apply style to a row
my_font=Font(size=20,bold=True,vertAlign='subscript') # font styles
my_fill=PatternFill(fill_type='solid',start_color='FF00FF') #Background color
for cell in ws1["1:1"]: # First row 
    cell.font = my_font
    cell.fill= my_fill

Adding a list to worksheet using append

Adding List to Excel worksheet by openpyxl
Added a list at the top row.
from openpyxl import Workbook
wb = Workbook() # create workbook 
ws1 = wb.active # work on default worksheet 

l1=['A','B','C','D']
ws1.append(l1)

my_path='G:\\My drive\\testing\\openpyxl\\demo2.xlsx'#Path 
wb.save(my_path)
Adding List in a column. Note that the top left cell address is row=1, column=1
l1=['A','B','C','D']
r,c=1,1 # row , column ( starting from 1,1)
for data in l1:
    ws1.cell(r,c,data)    
    r=r+1

Adding dictionary to worksheet

Adding dictionary to Excel worksheet by openpyxl
from openpyxl import Workbook
wb = Workbook() # create workbook 
ws1 = wb.active # work on default worksheet 

my_dict={'a':['Alex',30],'b':['Ronald',40],'c':['Ronn',50]}
r,c=1,1 # starting row and column 

for k in my_dict:
    ws1.cell(r,c,k) # adding Key at first column
    ws1.cell(r,c+1,my_dict[k][0]) # adding List after key in same row
    ws1.cell(r,c+2,my_dict[k][1]) # adding List after key in same row
    r=r+1 #increase row value by 1 
    #ws1.append(my_dict[k]) # add the value only 
my_path='G:\\My drive\\testing\\openpyxl\\demo2.xlsx'#Path 
wb.save(my_path)

Adding formula to worksheet

Adding formula to Excel worksheet by openpyxl
from openpyxl import Workbook
from openpyxl.styles import PatternFill, Font
wb = Workbook() # create workbook 
ws1 = wb.active # work on default worksheet 

l1=[10,12,8,13]
ws1.append(l1)
ws1.cell(row=2,column=3).value='Sum = ' # adding text 
result=ws1.cell(row=2,column=4) # result column to add formula
result.value="=SUM(A1:D1)" # formula 

result.font=Font(size=20,bold=True) # font styles 
result.fill=PatternFill(fill_type='solid',start_color='FFFF00')#BG color

my_path='G:\\My drive\\testing\\openpyxl\\demo2.xlsx'#Path 
wb.save(my_path)

Adding image by add_image()

Adding image to Excel worksheet by openpyxl
To above code we will add one image
from openpyxl import Workbook
from openpyxl.styles import PatternFill, Font
from openpyxl.drawing.image import Image
wb = Workbook() # create workbook 
ws1 = wb.active # work on default worksheet 

my_img=Image('D:\\top2.jpg')  # Update your path 
ws1.add_image(my_img, 'A1') # Image added 

l1=[10,12,8,13] # List to add to worksheet 
r,c=3,1 # row , column ( starting from 3,1)
for data in l1:
    ws1.cell(r,c,data)    
    c=c+1 # increase the column value by 1

ws1.cell(row=4,column=3).value='Sum = ' # adding text 
result=ws1.cell(row=4,column=4) # result column to add formula
result.value="=SUM(A3:D3)" # formula 

result.font=Font(size=20,bold=True) # font styles 
result.fill=PatternFill(fill_type='solid',start_color='FFFF00')

my_path='G:\\My drive\\testing\\openpyxl\\demo2.xlsx'#Path 
wb.save(my_path)

Adding Chart to worksheet

Adding chart to Excel worksheet by openpyxl
from openpyxl import Workbook
from openpyxl.drawing.image import Image
from openpyxl.chart import BarChart, Reference, Series

wb = Workbook() # create workbook 
ws1 = wb.active # work on default worksheet 

my_img=Image('D:\\top2.jpg') 
ws1.add_image(my_img, 'A1') # Image added 

l1 = [20,10,13,16,18] # List to populate  
r,c=3,1 # row , column ( starting from 3,1)
for data in l1:
    ws1.cell(r,c,data)    
    c=c+1 # increase the column value by 1

values = Reference(ws1, min_col=1, min_row=3, max_col=5, max_row=3)
chart = BarChart()
chart.add_data(values)
ws1.add_chart(chart, "A5")

my_path='G:\\My drive\\testing\\openpyxl\\demo2.xlsx'#Path 
wb.save(my_path)
Reading Excel file Database table to Excel file
Python xlsxwriter library
Subscribe to our YouTube Channel here

Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com



    Post your comments , suggestion , error , requirements etc here





    Python Video Tutorials
    Python SQLite Video Tutorials
    Python MySQL Video Tutorials
    Python Tkinter Video Tutorials
    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2022 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer