#getInfo.py
from openpyxl import Workbook,load_workbook
from openpyxl.styles import Font, Border, Side, Alignment, PatternFill
from datetime import datetime
#设置全局样式
border = Border(bottom=Side(style='thin', color='000000'),
right=Side(style='thin', color='000000'),
left=Side(style='thin', color='000000'),
top=Side(style='thin', color='000000'))
alignment = Alignment(horizontal='center', vertical='center')
#定义get_info()函数，根据身份证号计算出生日期，性别，年龄等信息
def get_info(IdNumber):
#创建省份字典，后面用身份证号的前2位到字典里查找得到所在地，属相、星座可用类似方法
pDict = {'11':'北京','12':'天津','13':'河北','14':'山西','15':'内蒙古','21':'辽宁',\
'22':'吉林','23':'黑龙江','31':'上海','32':'江苏','33':'浙江','34':'安徽',\
'35':'福建','36':'江西','37':'山东','41':'河南','42':'湖北','43':'湖南',\
'44':'广东','45':'广西','46':'海南','50':'重庆','51':'四川','52':'贵州',\
'53':'云南','54':'西藏','61':'陕西','62':'甘肃','63':'青海','64':'宁夏',\
'65':'新疆','71':'台湾','81':'香港','82':'澳门'}
birthStr = IdNumber[6:10] + '-' + IdNumber[10:12] + '-' + IdNumber[12:14]
birth = datetime.strptime(birthStr, "%Y-%m-%d").date()
gender = '女' if eval(IdNumber[-2])%2==0 else '男'
age = datetime.now().year-eval(IdNumber[6:10])
site = pDict.get(IdNumber[:2],'')
return birth,gender,age,site
#写一行数据到ws工作表
def writeLine(ws,row_index,DataList):
for index,item in enumerate(DataList):
cell = ws.cell(row_index, index+1, item)
cell.border = border
cell.alignment = alignment
row_index += 1
return row_index
def main():
#打开sample工作簿，并打开sheet1
try:
wb1 = load_workbook("sample.xlsx")
ws1 = wb1["Sheet1"]
#创建工作簿和工作表
wb2 = Workbook()
sheet_active = wb2.active
sheet_active.title = 'info_sheet'
ws2 = wb2['info_sheet']
ws2.column_dimensions['B'].width = 20 #设置列宽
ws2.column_dimensions['D'].width = 12
#写标题行
title = ['姓名','身份证号','关系','出生日期','性别','年龄','所在地']
rows = writeLine(ws2, 1, title)
#写数据行
row_index = 2
data = list(ws1.iter_rows(min_row=2,min_col=2,values_only=True))
for row in data:
pInfo = list(row)
pInfo += list(get_info(row[1]))
row_index = writeLine(ws2, row_index, pInfo)
#接收用户输入添加信息并保存
while True:
UserInput = input("请输入用户的姓名 身份证号 关系，以空格分隔，直接按回车键退出：\n")
if UserInput!='':
UserInfo = UserInput.split()
UserInfo += get_info(UserInfo[1])
row_index = writeLine(ws2, row_index,UserInfo)
else:
break
wb2.save("info.xlsx") #保存文件
except FileNotFoundError:
print("sample.xlsx文件未找到，请检查文件名和路径！")
main()
