openpyxl to read and write data add image chart to Excel file in Python

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

1. Getting Started with OpenPyxl

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

2. Basic code using openpyxl : Creating Spreadsheets,

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)

More on managing Worksheets by using OpenPyXl library

3. Reading the data from spreadsheet

# ===== Quick Reading the Data =====
from openpyxl import  load_workbook
# Load workbook
wb_read = load_workbook(my_path)
ws_read = wb_read.active

# Print individual cells
print("A1:", ws_read['A1'].value)
print("B1:", ws_read['B1'].value)
print("A2:", ws_read['A2'].value

More on Reading data from Excel file by using OpenPyXl library
Changing width of multiple columns

l1 = ['A', 'B', 'C', 'D']

for i in l1:
    ws1.column_dimensions[i].width = 20

4. Adding Style 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

More on Styles and formatting by using OpenPyXl library

5. 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)

OpenPyXL Tutorial for Beginners — Setup on Desktop & Google Colab + First Excel File

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

Add a list to column 'B' and add one unique incremental number for each record at column 'A'.
To generate a sequence of unique numbers for List, use enumerate().

l1 = ["Alex", "King", "Ron", "Queen"]
r, c = 1, 1  # row , column ( starting from 1,1)

for id, my_name in enumerate(l1, 108): # start from 108
    ws1.cell(r, c, id)  # add data to id column
    ws1.cell(r, c + 1, my_name)  # add data to name column
    r = r + 1  # increase the row to next

my_path = "F:\data\student5.xlsx"  # Path
wb.save(my_path)

6. 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]) # add name after key
    ws1.cell(r,c+2,my_dict[k][1]) # add age after name
    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)

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

More on Formulas by using OpenPyXl library

8. Merge / Unmerge Cells

ws1.merge_cells("A7:C7")
ws1["A7"] = "Merged Title"
ws1.unmerge_cells("A7:C7")

9. Dates & Times (with Correct Formats)

from datetime import datetime

ws1["A3"] = datetime(2025,10,30)
ws1["A3"].number_format = "yyyy-mm-dd"

ws1["B3"] = datetime(2025,10,30,14,45)
ws1["B3"].number_format = "yyyy-mm-dd hh:mm"

wb.save(save_path)

10. 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)

11. 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)

More on adding Images and different type of Charts to Excel by using OpenPyXl library

12. Quick Tasks (Copy-Paste Demos)

a) Fill a Column with IDs

for i in range(1,11):
    ws1.cell(row=i, column=1, value=i)

b) Write a Table with Header Styles

headers = ["id","name","score"]
data    = [
    [101,"Alex",88],
    [102,"Ron",91]
]

ws1.append(headers)
for row in data:
    ws1.append(row)

from openpyxl.styles import Font, PatternFill
hdr_font = Font(bold=True)
hdr_fill = PatternFill(fill_type="solid",start_color="FFFFCC")

for cell in ws1["1:1"]:
    cell.font = hdr_font
    cell.fill = hdr_fill

c) Safe Reading with Types

for r in ws1.iter_rows(min_row=2, values_only=True):
    name, score = r[1], r[2]
    if isinstance(score, (int, float)):
        print(name,score)

13. Save & Close

wb.save(save_path)
wb.close()

Reading Excel file Database table to Excel file Managing Worksheets Styles and formatting Formulas Pay Calculations More on adding Images and different type of Charts to Excel by using OpenPyXl library OpenPyXL and Pandas
Import and Export SQLite database table to Excel in Colab platform
Tkinter GUI to add user inputs to Excel file by openpyxl Tkinter GUI to convert Large Excel file to SQLite with progress bar
Python xlsxwriter library

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect: