Posts

Posted on

How To Create Conditional Formatting Data Bars For Specified Data Using OpenPyXL?

Method

from openpyxl.formatting.rule import DataBarRule

from openpyxl.styles import colors

 

workbook= load_workbook(filename=’wb2.xlsx’)

sheet= workbook.active

rule= DataBarRule(start_type=’percentile’, \

                    start_value=10, \

                    end_type=’percentile’, \

                    end_value=90, \

                    color=’0000FF’)

sheet.conditional_formatting.add(‘B2:B11’, rule)

Sample Code

#Data Bars

from openpyxl import load_workbook
from openpyxl.formatting.rule import DataBarRule
from openpyxl.styles import colors

#Load Data
workbook= load_workbook(filename='wb2.xlsx')
sheet= workbook.active

#Draw Data Bars
#Plot with Percentage Data
rule= DataBarRule(start_type='percentile', \
                    start_value=10, \
                    end_type='percentile', \
                    end_value=90, \
                    color='0000FF')
sheet.conditional_formatting.add('B2:B11', rule)
workbook.save('test.xlsx')

workbook= load_workbook(filename='wb2.xlsx')
sheet= workbook.active

#Plot with Actual Data
rule= DataBarRule(start_type='num', \
                    start_value=150000, \
                    end_type='num', \
                    end_value=1300000, \
                    color='FF0000')
sheet.conditional_formatting.add('A2:A11', rule)
workbook.save('test2.xlsx')
Create Conditional Formatting Data Bars For Specified Data Using OpenPyXL
Posted on

How To Refresh Pivot Tables Using OpenPyXL?

Method

pivot=ws._pivots[0]    #Reference the 1st Pivot Table

dt[‘F2′]=’China’    #Modify Data Source

#Refresh Pivot Table

pivot.cache.refreshOnLoad=True

Sample Code

#Refresh Pivot Table

from openpyxl import load_workbook

#Load Workbook
wb=load_workbook('Pivot.xlsx')
dt=wb['Data Source']
ws=wb['Pivot Table']

pivot=ws._pivots[0]    #Reference the 1st Pivot Table
dt['F2']='China'    #Modify Data Source
#Refresh Pivot Table
pivot.cache.refreshOnLoad=True

wb.save('test.xlsx')
Refresh Pivot Tables Using OpenPyXL

Posted on

How To Reference Pivot Tables Using OpenPyXL?

Method

pivot=ws._pivots[0]    #Reference the 1st Pivot Table

print(pivot.name)    #Name of the Pivot Table

Sample Code

#Reference Pivot Table

from openpyxl import load_workbook

#Load Workbook
wb=load_workbook('Pivot.xlsx')
#Get Worksheet
ws=wb['Pivot Table']

pivot=ws._pivots[0]    #Reference the 1st Pivot Table
print(pivot.name)    #Name of the Pivot Table

#Pivot Table Overview
print(pivot.summary)

wb.save('test.xlsx')
Reference Pivot Tables Using OpenPyXL

Posted on

How To Set Data Points in Series Using OpenPyXL?

Method

#Modify point properties in the first series

chart.series[0].marker.symbol=’triangle’

chart.series[0].marker.size=10

dp=DataPoint(idx=2,marker=Marker(size=16,symbol=’diamond’))  #size  max 72

chart.series[0].data_points=[dp]

Sample Code

#Points in the Series

from openpyxl import Workbook, load_workbook
from openpyxl.chart import LineChart, Reference
from openpyxl.chart.marker import DataPoint,Marker

# Read the file
wb=load_workbook('Sales.xlsx')
# Select the first worksheet
ws=wb.worksheets[0]

# Get the data from the range B1:D6 and save it as a Reference object
data=Reference(ws, min_col=2, min_row=1, max_col=4, max_row=6)
# Get the data from the range A2:A6 for the x-axis of the bar chart
labels=Reference(ws, min_col=1, min_row=2, max_col=1, max_row=6)

# Create a bar chart object
chart=LineChart()
# Set the data source
chart.add_data(data, titles_from_data=True)
# Set x-axis labels
chart.set_categories(labels)

#Modify point properties in the first series
chart.series[0].marker.symbol='triangle'
chart.series[0].marker.size=10
dp=DataPoint(idx=2,marker=Marker(size=16,symbol='diamond'))  #size  max 72
chart.series[0].data_points=[dp]

# Insert the chart into the worksheet
ws.add_chart(chart, 'E3')
# Save the workbook
wb.save('test.xlsx')
Set Data Points in Series Using OpenPyXL
Posted on

How To Set Properties of Series in Charts Using OpenPyXL?

Method

#Get the first series and modify its properties

ser=chart.series[0]

ser.graphicalProperties.line.solidFill=’00FF00′

ser.graphicalProperties.line.Width=600000

ser.graphicalProperties.line.dashStyle=’sysDot’

Sample Code

#Series

# Import relevant modules such as Workbook, load_workbook,
# and charts from openpyxl
from openpyxl import Workbook, load_workbook
from openpyxl.chart import LineChart, Reference, Series

# Read the file
wb = load_workbook('Sales.xlsx')
# Select the first worksheet
ws = wb.worksheets[0]

# Get the data from the range B1:D6 and save it as a Reference object
data = Reference(ws, min_col=2, min_row=1, max_col=4, max_row=6)
# Get the data from the range A2:A6 for the x-axis of the bar chart
labels = Reference(ws, min_col=1, min_row=2, max_col=1, max_row=6)

# Create a bar chart object
chart = LineChart()
# Set the data source
chart.add_data(data, titles_from_data=True)
# Set x-axis labels
chart.set_categories(labels)

#Get the first series and modify its properties
ser=chart.series[0]
ser.graphicalProperties.line.solidFill='00FF00'
ser.graphicalProperties.line.Width=600000
ser.graphicalProperties.line.dashStyle='sysDot'

# Set chart and axis titles
chart.title='Product Sales'
chart.x_axis.title='Product Name'
chart.y_axis.title='Sales'

# Insert the bar chart into a specified cell
ws.add_chart(chart, 'E2')

# Save the file
wb.save('test.xlsx')
Set Properties of Series in Charts Using OpenPyXL
Posted on

General Process of Creating Charts in OpenPyXL

Method

from openpyxl import Workbook, load_workbook

from openpyxl.chart import LineChart, Reference, Series

 

wb=load_workbook(‘Sales.xlsx’)

ws=wb.worksheets[0]

 

data=Reference(ws, min_col=2, min_row=1, max_col=4, max_row=6)

labels=Reference(ws, min_col=1, min_row=2, max_col=1, max_row=6)

 

chart=LineChart()

chart.add_data(data, titles_from_data=True)

chart.set_categories(labels)

 

chart.title=’Product Sales’

chart.x_axis.title=’Product Name’

chart.y_axis.title=’Sales’

 

ws.add_chart(chart, ‘E2’)

 

wb.save(‘test.xlsx’)

wb.close()

Sample Code

#General Process of Plotting with OpenPyXL

# Import relevant modules such as Workbook, load_workbook,
# and charts from openpyxl
from openpyxl import Workbook, load_workbook
from openpyxl.chart import LineChart, Reference, Series

# Read the file
wb=load_workbook('Sales.xlsx')
# Select the first worksheet
ws=wb.worksheets[0]

# Get the data from the range B2:D6 and
# save it as a Reference object
data=Reference(ws, min_col=2, min_row=1, max_col=4, max_row=6)
# Get the data from the range A2:A6 for the x-axis of the bar chart
labels=Reference(ws, min_col=1, min_row=2, max_col=1, max_row=6)

# Create a bar chart object
chart=LineChart()
# Set the data source
chart.add_data(data, titles_from_data=True)
# Set x-axis labels
chart.set_categories(labels)

# Set chart and axis titles
chart.title='Product Sales'
chart.x_axis.title='Product Name'
chart.y_axis.title='Sales'

# Insert the bar chart into a specified cell
ws.add_chart(chart, 'E2')

# Save the file
wb.save('test.xlsx')
wb.close()
Creating Charts in OpenPyXL
Posted on

How To Implement Edge Processing of Given Images Using OpenPyXL?

Method

img2=img.filter(ImageFilter.EDGE_ENHANCE)    #Edge enhancement

#img2=img.filter(ImageFilter.FIND_EDGES)    #Edge detection

Sample Code

#Edge Processing

from openpyxl import Workbook
from openpyxl.drawing.image import Image as xlImage
from PIL import Image,ImageFilter

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

# Open the original image file
img=Image.open('pic.jpg')
img2=img.filter(ImageFilter.EDGE_ENHANCE)    #Edge enhancement
#img2=img.filter(ImageFilter.FIND_EDGES)    #Edge detection

# Save the processed image as a temporary file
path='image.jpg'
img2.save(path)

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

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

# Delete the temporary file
import os
os.remove(path)
Implement Edge Processing of Given Images Using OpenPyXL
Posted on

How To Get Blur Effect of Given Images Using OpenPyXL?

Method

img2=img.filter(ImageFilter.GaussianBlur)    #Apply Gaussian blur

#img2=img.filter(ImageFilter.BLUR)    #Apply normal blur

Sample Code

#Blur Effect

from openpyxl import Workbook
from openpyxl.drawing.image import Image as xlImage
from PIL import Image,ImageFilter

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

# Open the original image file
img=Image.open('pic.jpg')
img2=img.filter(ImageFilter.GaussianBlur)    #Apply Gaussian blur
#img2=img.filter(ImageFilter.BLUR)    #Apply normal blur

# Save the blurred image as a temporary file
path='image.jpg'
img2.save(path)

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

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

# Delete the temporary file
import os
os.remove(path)
Get Blur Effect of Given Images Using OpenPyXL
Posted on

How To Symmetry Transformate Images Using OpenPyXL?

Method

img2=img.transpose(pilImage.FLIP_LEFT_RIGHT)    #Horizontal symmetry

#img2=img.transpose(pilImage.FLIP_TOP_BOTTOM)    #Vertical symmetry

Sample Code

#Symmetry 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 symmetry transformation
img2=img.transpose(pilImage.FLIP_LEFT_RIGHT)    #Horizontal symmetry
#img2=img.transpose(pilImage.FLIP_TOP_BOTTOM)    #Vertical symmetry

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

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

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

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

How To Scale Images Using OpenPyXL?

Method

img2=img.resize((300,200))

Sample Code

#Scaling 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 scaling transformation to the image
img2=img.resize((300,200))

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

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

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

# Delete the temporary file
import os
os.remove(path)
Scale Images Using OpenPyXL