import xlrd



xls = xlrd.open_workbook(filename='./datas/1.xls')

sheet = xls.sheets()[0]
# a
sum_people = sheet.nrows - 1
print(f'表格中有{sum_people}人')
# 所有行数减去 列名称哪一行

# b
_str = '134.135.136.137.138.139.147.150.151.152.157.158.159.178.182.183.184.187.188.1703.1705.1706'
# 移动网络
_str2 = '130.131.132.145.155.156.175.176.185.186.1704.1707.1708.1709.171'
# 联通网络
_str3 = '133.149.153.173.177.180.181.189.1700.1701.1702'
# 电信网络

map = {}
for e in _str.split('.'):
    map[e] = True

map2 = {}
for e in _str2.split('.'):
    map2[e] = True

map3 = {}
for e in _str3.split('.'):
    map3[e] = True

arr4 = sheet.col_values(colx=5,start_rowx=1)
# 取第6列数据 ，从第一行数据开始
num1,num2,num3 = 0,0,0
for e in arr4:
    _strLen3 = e[:3]
    _strLen4 = e[:4]
    # 取前3个字符 和 前4个字符
    if map.get(_strLen3,False) or map.get(_strLen4,False):
        num1 += 1

    if map2.get(_strLen3, False) or map2.get(_strLen4, False):
        num2 += 1

    if map3.get(_strLen3,False) or map3.get(_strLen4,False):
        num3 += 1

print('移动：{}人 联通：{}人 电信：{}人'.format(num1,num2,num3))
print('移动：{:.2f}% 联通：{:.2f}% 电信：{:.2f}%'.format(num1/sum_people,num2/sum_people,num3/sum_people))


# c
arr5 = sheet.col_values(colx=8,start_rowx=1)
maleNum = 0
# 男性
womanNum = 0
# 女性

for e in arr5:
    maleNum += 1 if e == '男' else 0
    womanNum += 1 if e == '女' else 0
print(f'男：{maleNum}人，女：{womanNum}人')

# d
arr6 = sheet.col_values(colx=7,start_rowx=1)
num45 = 0
# 年龄超过45岁
for e in arr6:
    # e 为 浮点型float
    if int(e) > 45:
        num45 += 1

print(f'年龄超过45岁的人数有:{num45}人')

# e

arr7 = sheet.col_values(colx=11,start_rowx=1)
num8000 = 0
num3000 = 0
for e in arr7:
    # e 为 浮点型float
    if int(e) > 8000:
        num8000 += 1

    if int(e) < 3000:
        num3000 += 1

print(f'薪资超过8000的人数有:{num8000}人，低于3000的人数有：{num3000}人')


# f
arr8 = sheet.col_values(colx=13,start_rowx=1)
num4 = 0
# 传媒人数
for e in arr8:
    if e.strip().find('传媒') != -1:
        num4 += 1

print(f'传媒人数为：{num4}人')

# g

arr8 = sheet.col_values(colx=9,start_rowx=1)
num5 = 0
for e in arr8:
    for e2 in ['黑龙江','北京','四川','福建']:
        if e.strip().find(e2) !=-1:
            num5 += 1
            break

print(f'高危地区人数为：{num5}人')