Author: graphnow

Posted on

How To Rotate Images Using OpenPyXL?

Method

rotated_image=img.rotate(45, expand=True)

Sample Code

#Rotation Transformation

from openpyxl import Workbook
from openpyxl.drawing.image import Image as xlImage
from PIL import Image as pilImage

#Create a workbook
wb=Workbook()
#Get the active worksheet
ws=wb.active

# Open the original image file
img=pilImage.open('pic.jpg')

#Apply a rotation transformation to the image
rotated_image=img.rotate(45, expand=True)

# Save the transformed image as a temporary file
path='image2.jpg'
rotated_image.save(path)

# Add the image to the worksheet
img=xlImage(path)
ws.add_image(img, 'A1')

# Save the workbook
wb.save('image04.xlsx')

# Delete the temporary file
import os
os.remove(path)
Posted on

How To Translate Images Using OpenPyXL?

Method

img=img.rotate(0,translate=(100,-20))

Sample Code

#Translation Transformation

from openpyxl import Workbook
from openpyxl.drawing.image import Image as xlImage
from PIL import Image as pilImage

#Create a workbook
wb=Workbook()
#Get the active worksheet
ws=wb.active

# Open the original image file
img=pilImage.open('pic.jpg')

#Apply a translation transformation to the image
img=img.rotate(0,translate=(100,-20))

# Save the transformed image as a temporary file
path='image1.jpg'
img.save(path)

# Add the image to the worksheet
img=xlImage(path)
ws.add_image(img, 'A1')

# Save the workbook
wb.save('image03.xlsx')

# Delete the temporary file
import os
os.remove(path)
Translate Images Using OpenPyXL
Posted on

How To Resize Cells and Images Using OpenPyXL?

Method

ws.column_dimensions[‘H’].width=18.0

ws.row_dimensions[2].height=48.0

 

img=Image(img_file)

img.width=46.0

img.height=46.0

ws.add_image(img,’H2′)  #Add the image to cell H2

Sample Code

#Resize Cells and Images

from openpyxl.drawing.image import Image
from openpyxl import Workbook

#Create a workbook
wb=Workbook()
#Get the active worksheet
ws=wb.active

#Change the width and height of specified columns and rows
ws.column_dimensions['H'].width=18.0
ws.row_dimensions[2].height=48.0

#The picture file
img_file='pic.jpg'

#Modify the image width and height
img=Image(img_file)
img.width=46.0
img.height=46.0
ws.add_image(img,'H2')  #Add the image to cell H2

wb.save('image02.xlsx')
wb.close()
Resize Cells and Images Using OpenPyXL

Posted on

How To Insert a Picture Using OpenPyXL?

Method

from openpyxl.drawing.image import Image

img_file=’pic.jpg’

img=Image(img_file)

ws.add_image(img,’A1′)

Sample Code

#Add Images to the Worksheet

#Import relevant classes
from openpyxl.drawing.image import Image
from openpyxl import Workbook

#Create a workbook
wb=Workbook()
#Get the active worksheet
ws=wb.active

#Open an image file
img_file='pic.jpg'
#Create an Image object from the image
img=Image(img_file)
#Add the Image object to cell A1
ws.add_image(img,'A1')

wb.save('image01.xlsx')
wb.close()
Insert a Picture Using OpenPyXL
Posted on

How To Insert a Formula Using OpenPyXL?

Method

>>>sht[“C1”]=1

>>>sht[“C2”]=2

>>>sht[“C3″]=”=SUM(C1:C2)”

Sample Code

#Insert Regular Formula

from openpyxl import Workbook

#Create a new workbook
wb=Workbook()
#Get the active worksheet
ws=wb.active

#Assign values to cells C1 and C2
ws['C1']=10
ws['C2']=20
#Insert a formula to sum values
ws['C3']='=SUM(C1:C2)'

wb.save('test.xlsx')
wb.close()
Insert a Formula Using OpenPyXL

Posted on

How To Freeze Cells Using OpenPyXL?

Method

ws.freeze_panes = ‘B2’

Sample Code

#Freeze the Cells

#Import load_workbook function
from openpyxl import Workbook

#Create a new workbook
wb=Workbook()
#Get the active worksheet
ws=wb.active

#Freeze a cell
ws.freeze_panes = 'B2'

wb.save('test.xlsx')
wb.close()
Freeze Cells Using OpenPyXL
Posted on

How To Add Comments Using OpenPyXL?

Method

The `comments` object and `comment` object

 

from openpyxl.comments import Comment

cm=Comment(‘new comment’,’ExcelCoder’)

cm.width=200

cm.height=50

ws[‘C3’].comment=cm

Sample Code

#Adding Comments

#Import load_workbook function
from openpyxl import Workbook

#Create a new workbook
wb=Workbook()
#Get the active worksheet
ws=wb.active

#Import Comment function
from openpyxl.comments import Comment

#Create a Comment object
cm=Comment('new comment','ExcelCoder')
#Set properties for the comment object
cm.width=200
cm.height=50
ws['C3'].comment=cm

wb.save('test.xlsx')
wb.close()
Add Comments Using OpenPyXL
Posted on

How To Unmerge Cell Ranges Using OpenPyXL?

Method

Use the `unmerge_cells` method to unmerge cells.

ws.unmerge_cells(range_string=None,start_row=None,start_column=None,end_row=None,end_column=None)

Sample Code

# Unmerge Cell Ranges

#Import load_workbook function
from openpyxl import Workbook

#Create a new workbook
wb=Workbook()
#Get the active worksheet
ws=wb.active

#Unmerge a cell range
ws.unmerge_cells('C3:E4')
#ws.unmerge_cells(start_row=3, start_column=3, \
#    end_row=4, end_column=5)

wb.save('test.xlsx')
wb.close()

Posted on

How To Merge Cell Ranges Using OpenPyXL?

Method

Use the `merge_cells` method of the worksheet object to merge cells:

ws.merge_cells(range_string=None,start_row=None,start_column=None,end_row=None,end_column=None)

Sample Code

#Merge Cell Ranges

#Import load_workbook function
from openpyxl import Workbook

#Create a new workbook
wb=Workbook()
#Get the active worksheet
ws=wb.active

#Merge a cell range
ws.merge_cells('C3:E4')
#ws.merge_cells(start_row=3, start_column=3, \
#    end_row=4, end_column=5)

wb.save('test.xlsx')
wb.close()
Merge Cell Ranges Using OpenPyXL

Posted on

How To Set Operations on Cell Ranges Using OpenPyXL?

Method

Methods of the `CellRange` object:

  • cr.union(other)
  • cr.intersection(other)
  • cr.issubset(other)
  • cr.issuperset(other)
  • cr.isdisjoint(other)

Sample Code

#Set Operations of Cell Ranges

#Import load_workbook function
from openpyxl import Workbook

#Create a new workbook
wb=Workbook()
#Get the active worksheet
ws=wb.active

#Import CellRange function
from openpyxl.worksheet.cell_range import CellRange

#Create two cell range objects
cr1=CellRange('D5:F7')
cr2=CellRange('E3:G5')

#Compute the union of the two ranges
print(cr1.union(cr2))

#Compute the intersection of the two ranges
print(cr1.intersection(cr2))

#Check if one range is a subset of the other
print(cr1.issubset(cr2))

#Check if one range is a superset of the other
print(cr1.issuperset(cr2))

#Check if the two ranges are disconnected
print(cr1.isdisjoint(cr2))

wb.save('test.xlsx')
wb.close()
Set Operations on Cell Ranges Using OpenPyXL