How To Set Plot 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】

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.TickLabels.Font.Size=8
    #ax1.TickLabels.NumberFormat='0.00'
    ax1.HasMajorGridlines=False
    ax2.HasTitle=True
    ax2.AxisTitle.Text='Values'
    ax2.AxisTitle.Font.Size=10
    ax2.TickLabels.Font.Size=8
    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.PlotArea.Format.Fill.ForeColor.RGB=xw.utils.rgb_to_int((255,255,26))
cht.PlotArea.Format.Fill.OneColorGradient(1,1,1)    #msoGradientHorizontal
      
set_style(cht)

cht.Export(root+'/cht.jpg')
cht.Export(root+'/cht.svg')
cht.ExportAsFixedFormat(0,root+'/cht.pdf')

#wb.save()
#app.kill()