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