How To Create Bivariate 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))
    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')
x=sht.range('A1:A1000').value
y=sht.range('B1:B1000').value

#频数分析
bx=10
by=10
minx=9999
maxx=-9999
miny=9999
maxy=-9999
for i in range(1000):
    if minx>x[i]: minx=x[i]
    if maxx<x[i]: maxx=x[i]
    if miny>y[i]: miny=y[i]
    if maxy<y[i]: maxy=y[i]
difx=maxx-minx
dify=maxy-miny
stepx=difx/bx
stepy=dify/by
count=[[0 for _ in range(10)] for _ in range(10)]
xi=[0 for _ in range(11)]
xi2=[0 for _ in range(11)]
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
yi=[0 for _ in range(11)]
yi2=[0 for _ in range(11)]
yi[0]=miny
yi2[0]=miny+stepy/2
for i in range(1,11):
    yi[i]=yi[i-1]+stepy
    if i!=10:
        yi2[i]=yi[i]+stepy/2
for k in range(1000):
    for i in range(10):
        if x[k]>=xi[i] and x[k]<xi[i+1]:
            for j in range(10):
                if y[k]>=yi[j] and y[k]<yi[j+1]:
                    count[i][j]+=1

#输出频数到sheet2
sht2=wb.sheets.add()
for i in range(1,11):
    for j in range(1,11):
        sht2.api.Cells(i+1,j+1).Value=count[i-1][j-1]

#根据频数绘制二元直方图
shp=sht2.api.Shapes.AddChart2(286,xw.constants.ChartType.xl3DColumn)
shp.Left=20
cht=shp.Chart
if cht.SeriesCollection().Count>0:
    for i in range(cht.SeriesCollection().Count,0,-1):
        cht.SeriesCollection(i).Delete()
cht.Legend.Delete()

countj=[0 for _ in range(10)]
for i in range(10):
    countj[i]=count[i][:]
    cht.SeriesCollection().NewSeries()
    cht.SeriesCollection(i+1).Name=str(yi2[i])    #序列轴刻度标签
    cht.SeriesCollection(i+1).XValues=xi2    #分类轴刻度标签
    cht.SeriesCollection(i+1).Values=countj[i]    #Z轴
  
cht.ChartGroups(1).GapWidth=0
cht.GapDepth=0
for i in range(10):
    fl=cht.SeriesCollection(i+1).Format.Fill
    fl.ForeColor.ObjectThemeColor=5    #msoThemeColorAccent1
    #fl.ForeColor.TintAndShade = 0
    fl.ForeColor.Brightness=0
    fl.Solid()

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

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

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