Python利用openpyxl库修改excel中身份证号单元格的方法

一、安装openpyxl库

      pip install openpyxl

 

二、实例

 

 1 import random
 2 import time
 3 import re
 4 
 5 from openpyxl import load_workbook
 6 
 7 def idcard_generator():
 8 
 9     ARR = (7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2)
10     LAST = ('1', '0', 'X', '9', '8', '7', '6', '5', '4', '3', '2')
11     t = time.localtime()[0]
12     x = '%02d%02d%02d%04d%02d%02d%03d' % (random.randint(10, 99), random.randint(1, 99), random.randint(1, 99), random.randint(t - 80, t - 18), random.randint(1, 12), random.randint(1, 28), random.randint(1, 999))
13     y = 0
14     for i in range(17):
15         y += int(x[i]) * ARR[i]
16     IDCard = '%s%s' % (x, LAST[y % 11])
17     # birthday = '%s-%s-%s 00:00:00' % (IDCard[6:14][0:4], IDCard[6:14][4: 6], IDCard[6:14][6:8])
18 
19     return IDCard
20 
21 
22 wb=load_workbook(r'C:\Users\admin\Desktop\tztest.xlsx')
23 
24 sheet=wb['Sheet1']
25 
26 for i in range(2,4):
27     
28     print sheet.cell(row=i, column=2).value
29     sheet.cell(row=i, column=2).value = idcard_generator()
30 wb.save(r'C:\Users\admin\Desktop\tztest.xlsx')