前言：

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


# coding:utf-8
from openpyxl import load_workbook
from openpyxl.styles import PatternFill

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

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


# coding:utf-8
from openpyxl import load_workbook
from datetime import datetime

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

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