python讀取excel資料，并可視化展現

#-*- coding: utf-8 -*-
import pandas as pda
import matplotlib.pyplot as pyl
import matplotlib.font_manager
from collections import Counter


'''配置變量區--start--'''
zhibanfile = 'zhibanbiao.xlsx'
week = 6 # 排班排了幾周
colstart = 2 # 從0開始，要統計資料開始列
colend = 9 # 從0開始，要統計資料結束列
rowend = week*4+1
dimDict = {
'1': '***', '2': '***', '3': '***', '4': '***', '5': '***', '6': '***', '7': '***',
}
print('讀取的檔案為：' + zhibanfile)
print('排班排了幾周：' + str(week))
print('參與值班的人員：')
print(dimDict)
'''配置變量區--end--'''

'''讀取檔案進行處理--start--'''
data = pda.read_excel(zhibanfile)
content = data.values
listday = []
listnight = []
for row in range(2,rowend,4):
    for col in range(2,colend):
        cellorginal = str(content[row][col])
        cell = cellorginal.strip().replace('\n',',').split(',')[0:2]
        listday.append(cell[0])
        listday.append(cell[1])

for row in range(3,25,4):
    for col in range(2,colend):
        cellorginal = str(content[row][col])
        cell = cellorginal.strip().replace('\n',',').split(',')[0:2]
        listnight.append(cell[0])
        listnight.append(cell[1])

# 下面為白班處理
daydic = Counter(listday) # 對list中資料根據個數進行統計，形成key,value的格式
xday = list(daydic.keys()) # 值班人員編号
yday = list(daydic.values()) # 值班人員班次個數
xdaylabel = []
for x in xday:
    xdaylabel.append(dimDict[x])
daydic = Counter(listday)
xday = list(daydic.keys())
yday = list(daydic.values())
# 下面為夜班處理
nightdic = Counter(listnight)
xnight = list(nightdic.keys())
ynight = list(nightdic.values())
xnightlabel = []
for x in xnight:
    xnightlabel.append(dimDict[x])
'''讀取檔案進行處理--end--'''

'''畫圖--start--'''
f = pyl.figure('監控室值班統計')
zhfont = matplotlib.font_manager.FontProperties(fname='C:\Windows\Fonts\simkai.ttf') # 設定字型格式
pyl.subplot(2,1,1) # 白班
pyl.xticks(range(len(xdaylabel)),xdaylabel,fontproperties=zhfont)
pyl.plot(yday,'b')
pyl.ylim(0,12)
pyl.grid(True)
pyl.title('白班',fontproperties=zhfont)

pyl.subplot(2,1,2) # 夜班
pyl.xticks(range(len(xnightlabel)),xnightlabel,fontproperties=zhfont)
pyl.plot(ynight,'g')
pyl.ylim(0,12)
pyl.grid(True)
pyl.title('夜班',fontproperties=zhfont)

pyl.show()
'''畫圖--end--'''