How To Set Chart Area Using xlwings?
【Method】
The chart area is the rectangular area that contains the entire chart, while the plot area is the rectangular area defined by the two axes. In Excel, the chart area is represented by the `ChartArea` object, and the plot area is represented by the `PlotArea` object. You can access these areas using the `ChartArea` and `PlotArea` properties of the `Chart` object.
By continuously referencing the `ChartArea` and `PlotArea` objects’ `Format.Fill` property, you can set the fill properties of both areas, such as their color, transparency, gradient fills, pattern fills, picture fills, texture fills, etc.
Using the `Format.Shadow` property of the `ChartArea` and `PlotArea` objects, you can set additional shadow properties for these areas. The `Format.Shadow` property returns a `ShadowFormat` object with the following main properties:
– **Visible**: Determines whether the shadow is visible.
– **Blur**: Gets or sets the blur radius of the shadow.
– **Transparency**: Gets or sets the transparency of the shadow (from 0.0 for opaque to 1.0 for fully transparent).
– **OffsetX**: Gets or sets the horizontal offset of the shadow in points. Positive values shift the shadow to the right, while negative values shift it to the left.
– **OffsetY**: Gets or sets the vertical offset of the shadow in points. Positive values shift the shadow downward, while negative values shift it upward.
sht.api.Range(‘A1:B7’).Select()
cht=sht.api.Shapes.AddChart().Chart
cha=cht.ChartArea #Chart Area
cha.Format.Fill.ForeColor.RGB=xw.utils.rgb_to_int((155,255,0))
cha.Shadow=True #Plot area shows shadow
pla=cht.PlotArea #Plot area
pla.Format.Fill.UserPicture(root+r’/picpy2.jpg’) #Picture fill
cht.SeriesCollection(1).Format.Fill.ForeColor.RGB=xw.utils.rgb_to_int((255,255,0))
cht.Axes(2).HasMajorGridlines=False
cha.Shadow=False
pla.Format.Shadow.Visible=True #Plot area shows shadow
pla.Format.Shadow.OffsetX=3 #Horizontal offset of the shadow
pla.Format.Shadow.OffsetY=3 #Vertical offset of the shadow
【Example】
【Code】
import xlwings as xw
import os
def set_style(cht):
cht.ChartArea.Format.Line.Visible=False
cht.PlotArea.Format.Fill.Visible=True
cht.PlotArea.Format.Line.Visible=True
cht.PlotArea.Format.Line.ForeColor.RGB=xw.utils.rgb_to_int((200,200,200))
#cht.PlotArea.Format.Line.ForeColor.ObjectThemeColor = msoThemeColorText1
ax1=cht.Axes(1)
ax2=cht.Axes(2)
ax1.HasTitle=True
ax1.AxisTitle.Text='Categories'
ax1.AxisTitle.Font.Size=10
ax1.AxisTitle.Font.Color = xw.utils.rgb_to_int((255,255,255))
ax1.TickLabels.Font.Size=8
ax1.TickLabels.Font.Color = xw.utils.rgb_to_int((255,255,255))
#ax1.TickLabels.NumberFormat='0.00'
ax1.HasMajorGridlines=False
ax2.HasTitle=True
ax2.AxisTitle.Text='Values'
ax2.AxisTitle.Font.Size=10
ax2.AxisTitle.Font.Color = xw.utils.rgb_to_int((255,255,255))
ax2.TickLabels.Font.Size=8
ax2.TickLabels.Font.Color = xw.utils.rgb_to_int((255,255,255))
ax2.HasMajorGridlines=False
cht.HasTitle=True
#cht.ChartTitle.Caption='Plot'
#cht.ChartTitle.Font.Size=12
root=os.getcwd()
app=xw.App(visible=True,add_book=False)
wb=app.books.open(root+r'/data.xlsx',read_only=False)
sht=wb.sheets('Sheet1')
sht.api.Range('A1:B7').Select() #
cht=sht.api.Shapes.AddChart2(-1, \
xw.constants.ChartType.xlColumnClustered,20,20,350,250,True).Chart
cht.ChartArea.Format.Fill.UserPicture('d:/pic.jpg')
cht.SeriesCollection(1).Format.Fill.ForeColor.RGB=xw.utils.rgb_to_int((255,255,0))
set_style(cht)
cht.Legend.Format.TextFrame2.TextRange.Font.Fill.ForeColor.RGB=xw.utils.rgb_to_int((255,255,255))
cht.Export(root+'/cht.jpg')
cht.Export(root+'/cht.svg')
cht.ExportAsFixedFormat(0,root+'/cht.pdf')
#wb.save()
#app.kill()
