python中openpyxl给excel表去重和身份证号信息提取

前言：

python操作excel用openpyxl库非常方便，今天学习一下给excel表去重，还有身份证号信息提取，自动计算年龄。

# coding:utf-8from openpyxl import load_workbookfrom openpyxl.styles import PatternFilldef dum(): wb = load_workbook("./加班时间.xlsx") sh = wb.active index =[] # 存储哪一行是重复数据 tmp = [] # 没有重复的数据 for i,c in enumerate(sh["B"]): flag = c.value not in tmp # print(flag, f'-----{c}======{tmp}') if flag: tmp.append(c.value) else: index.append(i) fill = PatternFill('solid',fgColor='AEEEEE') for i,r in enumerate(sh.rows): if i in index: for c in r: c.fill =fill print(f'第{i+1}条数据是重复数据') wb.save('查找重复数据.xlsx')if __name__ == '__main__': dum()

# coding:utf-8from openpyxl import load_workbookfrom datetime import datetimedef creat_time(): now_year = datetime.now().year wb = load_workbook('身份证号.xlsx') sh = wb.active max_column = sh.max_column for i, cell in enumerate(sh['B']): pno = cell.value year = pno[6:10] mouth = pno[10:12] day = pno[12:14] print(f'year:{year} mouth:{mouth} day:{day}') age = now_year - int(year) sh.cell(i+1, max_column + 1).value = year sh.cell(i + 1, max_column + 2).value = mouth sh.cell(i + 1, max_column + 3).value = day sh.cell(i + 1, max_column + 4).value = age wb.save('提取身份证号信息.xlsx')if __name__ == '__main__': creat_time()