Python openpyxl Library

pip install openpyxl

import openpyxl
print(openpyxl.__version__)

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)

l1=['A','B','C','D']
for i in l1:
    ws1.column_dimensions[i].width=20

Adding format

from openpyxl import Workbook
from openpyxl.styles import PatternFill, Font

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

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

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)

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

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)

Adding dictionary to worksheet

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

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

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

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)