Blog

How To Set Effects: Glow Using xlwings?

【Example】

【Code】

import xlwings as xw
import os

def set_style(cht):
    cht.ChartArea.Format.Line.Visible=False
    cht.PlotArea.Format.Fill.Visible=False
    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('A2:B7').Select()  #数据
shp=sht.api.Shapes.AddChart2(-1,xw.constants.ChartType.xlColumnClustered,20,20,350,250,True)
cht=shp.Chart  #添加图表
shadow=cht.SeriesCollection(1).Format.Shadow
shadow.Type=21
shadow.Visible=True
shadow.Style=2    #msoShadowStyleOuterShadow
shadow.Blur=4
shadow.OffsetX=4.9497474683
shadow.OffsetY=4.9497474683
shadow.RotateWithShape=False
shadow.ForeColor.RGB=xw.utils.rgb_to_int((0,0,0))
shadow.Transparency=0.599999994
shadow.Size=100

sht.api.Range('A2:C11').Select()  #数据
shp2=sht.api.Shapes.AddChart2(-1, xw.constants.ChartType.xlLine, 30, 20, 350, 250, True)
cht2=shp2.Chart  #添加图表
cht2.SeriesCollection(1).Format.Shadow.Type=24
cht2.SeriesCollection(2).Format.Shadow.Type=24

set_style(cht)

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

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

xlwings-Chart

March 28, 2026 (0)

How To Set Picture/Texture Fill for Areas Using xlwings?

【Method】

FillFormat object

UserPicture method
UserTextured method

sht.api.Range(‘A1:B7’).Select()

cht=sht.api.Shapes.AddChart().Chart

ser=cht.SeriesCollection(‘P1’) #First series

ff=ser.Format.Fill

ff.UserPicture(root+r’/picpy.jpg’)

ser2=cht.SeriesCollection(‘P2’) #Second series

ff2=ser2.Format.Fill

ff2.UserTextured(root+r’/picpy2.jpg’)

【Example】

【Code】

#Area settings - Picture and texture fill

import xlwings as xw
import os

root = os.getcwd()
app = xw.App(visible=True, add_book=False)
wb=app.books.open(root+r'/P1P2.xlsx',read_only=False)
sht=wb.sheets(1)

sht.api.Range('A1:B7').Select()
cht=sht.api.Shapes.AddChart().Chart
ser=cht.SeriesCollection('P1')      #First series
ff=ser.Format.Fill
ff.UserPicture(root+r'/picpy.jpg')
ser2=cht.SeriesCollection('P2')      #Second series
ff2=ser2.Format.Fill
ff2.UserTextured(root+r'/picpy2.jpg')

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

xlwings-Chart

March 27, 2026 (0)

How To Set Pattern Fill for Areas Using xlwings?

【Method】

FillFormat object

Patterned method

sht.api.Range(‘A1:B7’).Select()

cht=sht.api.Shapes.AddChart().Chart

ser=cht.SeriesCollection(‘P1’) #First series

ff=ser.Format.Fill

ff.ForeColor.RGB=xw.utils.rgb_to_int((0,0,255))

ff.Patterned(43)

ser2=cht.SeriesCollection(‘P2’) #Second series

ff2=ser2.Format.Fill

ff2.ForeColor.RGB=xw.utils.rgb_to_int((255,0,0))

ff2.TwoColorGradient(1,1)

ff2.BackColor.RGB=xw.utils.rgb_to_int((255,255,0))

【Example】

【Code】

#Area settings - Two-color gradient and pattern fill

import xlwings as xw
import os

root = os.getcwd()
app = xw.App(visible=True, add_book=False)
wb=app.books.open(root+r'/P1P2.xlsx',read_only=False)
sht=wb.sheets(1)

sht.api.Range('A1:B7').Select()
cht=sht.api.Shapes.AddChart().Chart
ser=cht.SeriesCollection('P1')      #First series
ff=ser.Format.Fill
ff.ForeColor.RGB=xw.utils.rgb_to_int((0,0,255))
ff.Patterned(43)
ser2=cht.SeriesCollection('P2')      #Second series
ff2=ser2.Format.Fill
ff2.ForeColor.RGB=xw.utils.rgb_to_int((255,0,0))
ff2.TwoColorGradient(1,1)
ff2.BackColor.RGB=xw.utils.rgb_to_int((255,255,0))

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

xlwings-Chart

March 27, 2026 (0)

How To Set Transparency and Color Fill of Areas Using xlwings?

【Method】

By referencing the `Format.Fill` property of the Series object, a `FillFormat` object is returned, representing the area object in the series. The members of the `FillFormat` object can be used to set properties for the area object.

– Transparency property

– ForeColor property

– OneColorGradient method

– TwoColorGradient method

– Solid method

sht.api.Range(‘A1:B7’).Select()

cht=sht.api.Shapes.AddChart().Chart

ser=cht.SeriesCollection(‘P1’) #First series

ff=ser.Format.Fill

ff.Transparency=0.7

ser2=cht.SeriesCollection(‘P2’) #Second series

ff2=ser2.Format.Fill

ff2.OneColorGradient(1,1,1)

【Example】

【Code】

#Area settings - Transparency and solid gradient

import xlwings as xw
import os

root = os.getcwd()
app = xw.App(visible=True, add_book=False)
wb=app.books.open(root+r'/P1P2.xlsx',read_only=False)
sht=wb.sheets(1)

sht.api.Range('A1:B7').Select()
cht=sht.api.Shapes.AddChart().Chart
ser=cht.SeriesCollection('P1')    #First series
ff=ser.Format.Fill
ff.Transparency=0.7
ser2=cht.SeriesCollection('P2')    #Second series
ff2=ser2.Format.Fill
ff2.OneColorGradient(1,1,1)

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

xlwings-Chart

March 26, 2026 (0)

How To Set Properties of Line Graphic Elements Using xlwings?

【Method】

By referencing the `Format.Line` property of a Series object, a `LineFormat` object is returned, representing the line object in the series. The members of the `LineFormat` object are used to set the line properties.

sht.api.Range(‘A1:B7’).Select()

cht=sht.api.Shapes.AddChart().Chart

ser2=cht.SeriesCollection(‘P2’) #Second series

ser2.ChartType=xw.constants.ChartType.xlLine #Line chart

ser2.Smooth=True #Smooth processing

ser2.MarkerStyle=xw.constants.MarkerStyle.xlMarkerStyleTriangle #Markers

ser2.MarkerForegroundColor=xw.utils.rgb_to_int((0,0,255)) #Color

ser2.HasDataLabels=True #Data labels

【Example】

【Code】

#Line chart element settings

import xlwings as xw
import os

root = os.getcwd()
app = xw.App(visible=True, add_book=False)
wb=app.books.open(root+r'/P1P2.xlsx',read_only=False)
sht=wb.sheets(1)

sht.api.Range('A1:B7').Select()
cht=sht.api.Shapes.AddChart().Chart
ser2=cht.SeriesCollection('P2')    #Second series
ser2.ChartType=xw.constants.ChartType.xlLine    #Line chart
ser2.Smooth=True    #Smooth processing
ser2.MarkerStyle=xw.constants.MarkerStyle.xlMarkerStyleTriangle    #Markers
ser2.MarkerForegroundColor=xw.utils.rgb_to_int((0,0,255))    #Color
ser2.HasDataLabels=True    #Data labels
ser=cht.SeriesCollection('P1')
ser.Format.Fill.ForeColor.RGB=xw.utils.rgb_to_int((0,255,0))
ser.Format.Fill.ForeColor.ObjectThemeColor=10
ser.Format.Fill.ForeColor.SchemeColor=3
ser2.Format.Line.DashStyle=4
ser2.Format.Line.ForeColor.RGB=xw.utils.rgb_to_int((0,0,255))

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

xlwings-Chart

March 26, 2026 (0)

How To Set Chart Colors Using xlwings?

【Method】

sht.api.Range(‘A1:B7’).Select()

cht=sht.api.Shapes.AddChart().Chart

ser2=cht.SeriesCollection(‘P2’) #Second series

ser2.ChartType=xw.constants.ChartType.xlLine #Line chart

ser2.Smooth=True #Smooth processing

ser2.MarkerStyle=xw.constants.MarkerStyle.xlMarkerStyleTriangle #Markers

ser2.MarkerForegroundColor=xw.utils.rgb_to_int((0,0,255)) #Color

ser2.HasDataLabels=True #Data labels

ser=cht.SeriesCollection(‘P1’)

ser.Format.Fill.ForeColor.RGB=xw.utils.rgb_to_int((0,255,0))

ser.Format.Fill.ForeColor.ObjectThemeColor=10

ser.Format.Fill.ForeColor.SchemeColor=3

【Example】

【Code】

#Color Settings

import xlwings as xw
import os

root = os.getcwd()
app = xw.App(visible=True, add_book=False)
wb=app.books.open(root+r'/P1P2.xlsx',read_only=False)
sht=wb.sheets(1)

sht.api.Range('A1:B7').Select()
cht=sht.api.Shapes.AddChart().Chart
ser2=cht.SeriesCollection('P2')    #Second series
ser2.ChartType=xw.constants.ChartType.xlLine    #Line chart
ser2.Smooth=True    #Smooth processing
ser2.MarkerStyle=xw.constants.MarkerStyle.xlMarkerStyleTriangle    #Markers
ser2.MarkerForegroundColor=xw.utils.rgb_to_int((0,0,255))    #Color
ser2.HasDataLabels=True    #Data labels
ser=cht.SeriesCollection('P1')
ser.Format.Fill.ForeColor.RGB=xw.utils.rgb_to_int((0,255,0))
#ser.Format.Fill.ForeColor.ObjectThemeColor=10
#ser.Format.Fill.ForeColor.SchemeColor=3

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

xlwings-Chart

March 25, 2026 (0)

How To Show Data With Conditional Formating Data Bars Using xlwings?

Method

rng=sht.Range(‘A1:A15’)

rng.FormatConditions.Delete()

rng.FormatConditions.AddDatabar()

Sample Code

#Conditional Formatting - Data Bars

import xlwings as xw    #Import xlwings
import os    #Import os

root = os.getcwd()    #Get current path
#Create Excel application window, visible, no workbook added
app=xw.App(visible=True, add_book=False)
#Open data file, writable
bk=app.books.open(fullname=root+r'\cf.xlsx',read_only=False)
#Get data worksheet
sht=bk.api.Sheets(1) 

rng=sht.Range('A1:A15')
rng.FormatConditions.Delete()

rng.FormatConditions.AddDatabar()

#bk.save()
#bk.close()
#app.kill()

Show Data With Conditional Formating Data Bars Using xlwings

xlwings-Conditional Formatting

March 10, 2026 (0)

How To Refresh a Pivot Table Using xlwings?

Method

def modify_data():

#Modify data source

sht_data.range(‘F2′).value=’China’

def update():

#Refresh pivot table

pvt=sht_pvt.api.PivotTables(‘Pivot’)

pvt.RefreshTable()

Sample Code

#Refresh Pivot Table

import xlwings as xw    #Import xlwings
import os    #Import os

root = os.getcwd()    #Get current path
#Create Excel application window, visible, no workbook added
app=xw.App(visible=True, add_book=False)
#Open data file, writable
bk=app.books.open(fullname=root+r'\Pivot.xlsx',read_only=False)
#Get data source worksheet
sht_data=bk.sheets.active
#Data cell range
rng_data=sht_data.api.Range('A1').CurrentRegion
#Create a new worksheet for the pivot table
sht_pvt=bk.sheets.add()
sht_pvt.name='Pivot Table'
    
def create_pivot_table():
    #Location of data
    rng_data=sht_data.api.Range('A1').CurrentRegion
    #Location of pivot table
    rng_pvt=sht_pvt.api.Range('A1')

    #Create pivot table cache
    pvc=bk.api.PivotCaches().Create(\
          SourceType=xw.constants.PivotTableSourceType.xlDatabase,\
          SourceData=rng_data)
    #Create pivot table
    pvt=pvc.CreatePivotTable(\
          TableDestination=rng_pvt,\
          TableName='Pivot')

    #Set fields
    pvt.PivotFields('Category').Orientation=\
        xw.constants.PivotFieldOrientation.xlPageField    #Page fields
    pvt.PivotFields('Category').Position=1
    pvt.PivotFields('Product').Orientation=\
        xw.constants.PivotFieldOrientation.xlColumnField    #Column fields
    pvt.PivotFields('Product').Position=1
    pvt.PivotFields('Origin').Orientation=\
        xw.constants.PivotFieldOrientation.xlRowField    #Row fields
    pvt.PivotFields('Origin').Position=1
    pvt.PivotFields('Amount').Orientation=\
        xw.constants.PivotFieldOrientation.xlDataField    #Value fields

def modify_data():
    #Modify data source
    sht_data.range('F2').value='China'

def update():
    #Refresh pivot table
    pvt=sht_pvt.api.PivotTables('Pivot')
    pvt.RefreshTable()


create_pivot_table()
modify_data()
update()

#bk.save()
#bk.close()
#app.kill()

xlwings-Pivot Table

March 10, 2026 (0)

How To Reference a Pivot Table Using xlwings?

Method

def ref_pivot_table():

#Pivot table reference

print(sht_pvt.api.PivotTables().Count)

print(sht_pvt.api.PivotTables(1).Name) #Reference by index number

print(sht_pvt.api.PivotTables(‘Pivot’).Name) #Reference by name

Sample Code

#Pivot Table References

import xlwings as xw    #Import xlwings
import os    #Import os

root = os.getcwd()    #Get current path
#Create Excel application window, visible, no workbook added
app=xw.App(visible=True, add_book=False)
#Open data file, writable
bk=app.books.open(fullname=root+r'\Pivot.xlsx',read_only=False)
#Get data source worksheet
sht_data=bk.sheets.active
#Data cell range
rng_data=sht_data.api.Range('A1').CurrentRegion
#Create a new worksheet for the pivot table
sht_pvt=bk.sheets.add()
sht_pvt.name='Pivot Table'
    
def create_pivot_table():
    #Location of data
    rng_data=sht_data.api.Range('A1').CurrentRegion
    #Location of pivot table
    rng_pvt=sht_pvt.api.Range('A1')

    #Create pivot table cache
    pvc=bk.api.PivotCaches().Create(\
          SourceType=xw.constants.PivotTableSourceType.xlDatabase,\
          SourceData=rng_data)
    #Create pivot table
    pvt=pvc.CreatePivotTable(\
          TableDestination=rng_pvt,\
          TableName='Pivot')

    #Set fields
    pvt.PivotFields('Category').Orientation=\
        xw.constants.PivotFieldOrientation.xlPageField    #Page fields
    pvt.PivotFields('Category').Position=1
    pvt.PivotFields('Product').Orientation=\
        xw.constants.PivotFieldOrientation.xlColumnField    #Column fields
    pvt.PivotFields('Product').Position=1
    pvt.PivotFields('Origin').Orientation=\
        xw.constants.PivotFieldOrientation.xlRowField    #Row fields
    pvt.PivotFields('Origin').Position=1
    pvt.PivotFields('Amount').Orientation=\
        xw.constants.PivotFieldOrientation.xlDataField    #Value fields

def ref_pivot_table():
    #Pivot table reference
    print(sht_pvt.api.PivotTables().Count)
    print(sht_pvt.api.PivotTables(1).Name)    #Reference by index number
    print(sht_pvt.api.PivotTables('Pivot').Name)    #Reference by name

create_pivot_table()
ref_pivot_table()

#bk.save()
#bk.close()
#app.kill()

xlwings-Pivot Table

March 9, 2026 (0)

How To Create a Pivot Table using Cache Using xlwings?

Method

def create_pivot_table():

#Location of pivot table

rng_pvt=sht_pvt.api.Range(‘A1’)

#Create pivot table cache

pvc=bk.api.PivotCaches().Create(\

SourceType=xw.constants.PivotTableSourceType.xlDatabase,\

SourceData=rng_data)

#Create pivot table

pvt=pvc.CreatePivotTable(\

TableDestination=rng_pvt,\

TableName=’Pivot’)

#Set fields

pvt.PivotFields(‘Category’).Orientation=\

xw.constants.PivotFieldOrientation.xlPageField #Page fields

pvt.PivotFields(‘Category’).Position=1

pvt.PivotFields(‘Product’).Orientation=\

xw.constants.PivotFieldOrientation.xlColumnField #Column fields

pvt.PivotFields(‘Product’).Position=1

pvt.PivotFields(‘Origin’).Orientation=\

xw.constants.PivotFieldOrientation.xlRowField #Row fields

pvt.PivotFields(‘Origin’).Position=1

pvt.PivotFields(‘Amount’).Orientation=\

xw.constants.PivotFieldOrientation.xlDataField #Value fields

Sample Code

#Create Pivot Table with Cache

import xlwings as xw    #Import xlwings
import os    #Import os

root = os.getcwd()    #Get current path
#Create Excel application window, visible, no workbook added
app=xw.App(visible=True, add_book=False)
#Open data file, writable
bk=app.books.open(fullname=root+r'\Pivot.xlsx',read_only=False)
#Get data source worksheet
sht_data=bk.sheets.active
#Data cell range
rng_data=sht_data.api.Range('A1').CurrentRegion
#Create a new worksheet for the pivot table
sht_pvt=bk.sheets.add()
sht_pvt.name='Pivot Table'

def create_pivot_table():
    #Location of pivot table
    rng_pvt=sht_pvt.api.Range('A1')
    #Create pivot table cache
    pvc=bk.api.PivotCaches().Create(\
          SourceType=xw.constants.PivotTableSourceType.xlDatabase,\
          SourceData=rng_data)
    #Create pivot table
    pvt=pvc.CreatePivotTable(\
          TableDestination=rng_pvt,\
          TableName='Pivot')

    #Set fields
    pvt.PivotFields('Category').Orientation=\
        xw.constants.PivotFieldOrientation.xlPageField    #Page fields
    pvt.PivotFields('Category').Position=1
    pvt.PivotFields('Product').Orientation=\
        xw.constants.PivotFieldOrientation.xlColumnField    #Column fields
    pvt.PivotFields('Product').Position=1
    pvt.PivotFields('Origin').Orientation=\
        xw.constants.PivotFieldOrientation.xlRowField    #Row fields
    pvt.PivotFields('Origin').Position=1
    pvt.PivotFields('Amount').Orientation=\
        xw.constants.PivotFieldOrientation.xlDataField    #Value fields

create_pivot_table()

#bk.save()
#bk.close()
#app.kill()

Create a Pivot Table using Cache Using xlwings

xlwings-Pivot Table

March 9, 2026 (0)