How To Create Univariate Histogram 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

def draw_hist(sht,n):
    #频数分析
    x=sht.range('A1:A1000').value
    xi=[0 for _ in range(11)]
    xi2=[0 for _ in range(10)]
    count=[0 for _ in range(10)]
    bx=10
    minx=9999
    maxx=-9999
    for i in range(n):
        if minx>x[i]:
            minx=x[i]
        if maxx<x[i]:
            maxx=x[i]
    difx=maxx-minx
    stepx=difx/bx
    for i in range(10):
        count[i]=0
    xi[0]=minx
    xi2[0]=minx+stepx/2
    for i in range(1,11):
        xi[i]=xi[i-1]+stepx
        if i!=10:
            xi2[i]=xi[i]+stepx/2 
    for i in range(n):
        for j in range(10):
            if x[i]>=xi[j] and x[i]<xi[j+1]:
                count[j]+=1

    #根据频数绘制直方图
    sht.api.Range('D3').Select()
    shp=sht.api.Shapes.AddChart2()
    shp.Left=20
    cht=shp.Chart
    for i in range(cht.SeriesCollection().Count,0,-1):
        cht.SeriesCollection(i).Delete()

    cht.SeriesCollection().NewSeries()
    cht.SeriesCollection(1).ChartType=xw.constants.ChartType.xlColumnClustered
    cht.SeriesCollection(1).XValues=xi2
    cht.SeriesCollection(1).Values=count
    cht.ChartGroups(1).GapWidth=0
    cht.GapDepth=0
    
    fl=cht.SeriesCollection(1).Format.Fill
    fl.ForeColor.ObjectThemeColor=5    #msoThemeColorAccent1
    #fl.ForeColor.TintAndShade=0
    fl.ForeColor.Brightness=0
    fl.Solid()

    ln=cht.SeriesCollection(1).Format.Line
    ln.Visible=True
    ln.ForeColor.ObjectThemeColor=13    #msoThemeColorText1
    #ln.ForeColor.TintAndShade=0
    ln.ForeColor.Brightness=0.0500000007
  
    return cht

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')
cht=draw_hist(sht,1000)
set_style(cht)

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

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