How To Create Lollipop Plot Using xlwings?

【Example】

【Code】

import xlwings as xw
import numpy as np
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 = 'Values'
    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 = 'Categories'
    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

def draw_rnd_scatter(cht,x,y,n,r,g,b):
    '''绘制散点图
    x: X坐标    y(0 to n-1): Y坐标
    '''
    ser=cht.SeriesCollection().NewSeries()
    ser.ChartType=xw.constants.ChartType.xlXYScatter
    ser.XValues=x
    ser.Values=y
    ser.Format.Line.ForeColor.RGB=xw.utils.rgb_to_int((0,0,0))
    ser.Format.Fill.ForeColor.RGB=xw.utils.rgb_to_int((r,g,b))
    ser.MarkerSize=22
    for i in range(n):
        lf=shape_x(cht,x[i]-0.04)
        tp=shape_y(cht,y[i]+0.3)
        wd=cht.PlotArea.InsideWidth/(cht.Axes(1).MaximumScale-cht.Axes(1).MinimumScale)*0.08
        ht=cht.PlotArea.InsideHeight/(cht.Axes(2).MaximumScale-cht.Axes(2).MinimumScale)*0.6
        shp=cht.Shapes.AddLabel(1,lf,tp,wd,ht)
        shp.TextFrame2.TextRange.Characters.Text=str(x[i])
        shp.TextFrame2.TextRange.Characters.Font.Size=8
        shp.TextFrame.HorizontalAlignment=xw.constants.HAlign.xlHAlignCenter
        shp.TextFrame.VerticalAlignment=xw.constants.VAlign.xlVAlignCenter


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')

shp=sht.api.Shapes.AddChart2()
shp.Left=20
shp.Top=20
shp.Width=220
shp.Height=320
cht=shp.Chart    #添加图表
cht.ChartType=xw.constants.ChartType.xlXYScatter
ax1=cht.Axes(1)
ax2=cht.Axes(2)
ax1.MinimumScale=0
ax1.MaximumScale=0.28
ax2.MinimumScale=0
ax2.MaximumScale=11

set_style(cht)

data=sht.range('B2:C11').value
dt=np.transpose(data)
dt1=dt[0]
dt2=dt[1]

#绘线形图
for i in range(10):
    ser=cht.SeriesCollection().NewSeries()
    ser.ChartType=xw.constants.ChartType.xlXYScatterLinesNoMarkers
    ser.XValues=[0,dt1[i]]
    ser.Values=[dt2[i],dt2[i]]
    ser.Format.Line.ForeColor.RGB=xw.utils.rgb_to_int((0,0,255))
    ser.Format.Line.Weight=1
  
#绘散点图
draw_rnd_scatter(cht,dt1,dt2,10,255,128,0)

cht.HasLegend=False

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

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