Archive

How To Create Gradient Filled Simple Column Chart 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 = True
    ax2.HasTitle = True
    ax2.AxisTitle.Text = 'Values'
    ax2.AxisTitle.Font.Size = 10
    ax2.TickLabels.Font.Size = 8
    ax2.HasMajorGridlines = True
    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:B9').Select()  #
shp=sht.api.Shapes.AddChart2(-1,xw.constants.ChartType.xlColumnClustered,20,20,350,250,True)
cht=shp.Chart  #
cht.ChartGroups(1).GapWidth=50
cht.SeriesCollection(1).Format.Fill.ForeColor.RGB=xw.utils.rgb_to_int((0,0,255))
cht.SeriesCollection(1).Format.Fill.OneColorGradient(1,1,1)    #msoGradientHorizontal
cht.SeriesCollection(1).Format.Line.ForeColor.RGB=xw.utils.rgb_to_int((0,0,0))

set_style(cht)

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

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

How To Create Simple Column Chart With Different Colors 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 = True
    ax2.HasTitle = True
    ax2.AxisTitle.Text = 'Values'
    ax2.AxisTitle.Font.Size = 10
    ax2.TickLabels.Font.Size = 8
    ax2.HasMajorGridlines = True
    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:B9').Select()  #
shp=sht.api.Shapes.AddChart2(-1,xw.constants.ChartType.xlColumnClustered,20,20,350,250,True)
cht=shp.Chart  #
cht.ChartGroups(1).GapWidth=50
cht.SeriesCollection(1).Points(1).Format.Fill.ForeColor.RGB=xw.utils.rgb_to_int((192,0,0))
cht.SeriesCollection(1).Points(2).Format.Fill.ForeColor.RGB=xw.utils.rgb_to_int((255,0,0))
cht.SeriesCollection(1).Points(3).Format.Fill.ForeColor.RGB=xw.utils.rgb_to_int((255,192,0))
cht.SeriesCollection(1).Points(4).Format.Fill.ForeColor.RGB=xw.utils.rgb_to_int((255,255,0))
cht.SeriesCollection(1).Points(5).Format.Fill.ForeColor.RGB=xw.utils.rgb_to_int((146,208,80))
cht.SeriesCollection(1).Points(6).Format.Fill.ForeColor.RGB=xw.utils.rgb_to_int((0,176,0))
cht.SeriesCollection(1).Points(7).Format.Fill.ForeColor.RGB=xw.utils.rgb_to_int((0,176,240))
cht.SeriesCollection(1).Points(8).Format.Fill.ForeColor.RGB=xw.utils.rgb_to_int((0,112,192))

set_style(cht)

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

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

How To Create Simple Column Chart 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 = True
    ax2.HasTitle = True
    ax2.AxisTitle.Text = 'Values'
    ax2.AxisTitle.Font.Size = 10
    ax2.TickLabels.Font.Size = 8
    ax2.HasMajorGridlines = True
    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:B9').Select()  #
shp=sht.api.Shapes.AddChart2(-1,xw.constants.ChartType.xlColumnClustered,20,20,350,250,True)
cht=shp.Chart  #
cht.ChartGroups(1).GapWidth=40

set_style(cht)

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

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

How To Create 3D Line Chart 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 = False
    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 = True
    ax2.HasTitle = True
    ax2.AxisTitle.Text = 'Values'
    ax2.AxisTitle.Font.Size = 10
    ax2.TickLabels.Font.Size = 8
    ax2.HasMajorGridlines = True
    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:C11').Select()  #
shp=sht.api.Shapes.AddChart2(-1,xw.constants.ChartType.xl3DLine,20,20,350,250,True)
cht=shp.Chart  #

set_style(cht)

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

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

How To Create Complex Line Chart 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 = True
    ax2.HasTitle = True
    ax2.AxisTitle.Text = 'Values'
    ax2.AxisTitle.Font.Size = 10
    ax2.TickLabels.Font.Size = 8
    ax2.HasMajorGridlines = True
    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:C11').Select()
shp=sht.api.Shapes.AddChart2(-1, xw.constants.ChartType.xlLine, 20, 20, 350, 220, True)
cht=shp.Chart

set_style(cht)

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

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

How To Create Simple Line Chart 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 = True
    ax2.HasTitle = True
    ax2.AxisTitle.Text = 'Values'
    ax2.AxisTitle.Font.Size = 10
    ax2.TickLabels.Font.Size = 8
    ax2.HasMajorGridlines = True
    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:B11').Select() 
shp=sht.api.Shapes.AddChart2(-1,xw.constants.ChartType.xlLine,20,20,350,220,True)
cht=shp.Chart

set_style(cht)

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

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

How To Create Complex Dot Chart 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 = True
    ax1.HasMinorGridlines = True
    ax2.HasTitle = True
    ax2.AxisTitle.Text = 'Values'
    ax2.AxisTitle.Font.Size = 10
    ax2.TickLabels.Font.Size = 8
    ax2.HasMajorGridlines = True
    ax2.HasMinorGridlines = True
    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:C11').Select()
shp=sht.api.Shapes.AddChart2(-1,xw.constants.ChartType.xlLineMarkers,20,20,320,220,True)
cht=shp.Chart
cht.SeriesCollection(1).Format.Line.Visible=False
cht.SeriesCollection(2).Format.Line.Visible=False

set_style(cht)

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

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

How To Simple Dot Chart 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 = True
    ax1.HasMinorGridlines = True
    ax2.HasTitle = True
    ax2.AxisTitle.Text = 'Values'
    ax2.AxisTitle.Font.Size = 10
    ax2.TickLabels.Font.Size = 8
    ax2.HasMajorGridlines = True
    ax2.HasMinorGridlines = True
    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:B11').Select() 
shp=sht.api.Shapes.AddChart2(-1, xw.constants.ChartType.xlLineMarkers,20,20,320,200,True)
cht=shp.Chart
cht.SeriesCollection(1).Format.Line.Visible=False

set_style(cht)

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

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

How To Save Chart as Image Using xlwings?

【Method】

The `CopyPicture` method of the `Chart` object allows you to copy the selected chart as an image to the clipboard. The method syntax is as follows: 

cht.CopyPicture(Appearance,Format)

Here, `cht` is the chart object, and the two parameters are: 

– **Appearance**: Sets how the image is copied. A value of 1 copies the image as it appears on screen (default), and a value of 2 copies the image as it appears when printed. 

– **Format**: Specifies the format of the copied image. A value of 2 copies the image as a bitmap (.bmp, .jpg, .gif, .png), and a value of -4147 copies the image as a vector format (supports .emf and .wmf).

How To Set Legend Using xlwings? 4

【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('A1:B7').Select()    #
cht=sht.api.Shapes.AddChart2(-1, \
          xw.constants.ChartType.xlColumnClustered,20,20,350,250,True).Chart
cht.HasLegend=True
leg=cht.Legend
leg.Position=xw.constants.LegendPosition.xlLegendPositionTop
leg.Shadow=True
leg.Left=80
leg.Width=200
      
set_style(cht)

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

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