同一信息在A表格中读取其单元格文字和其所在的行。值得注意的是list.append()只能一次装一个信息,list.extend([])一次可以填入多个信息。在这里list2填入单元格文字和所在的行,单元格文字将方便在B表格查找,所在的行将方便A表格后续写入信息。
# ==============================
wb1 = load_workbook(path1)
sheet_names1 = wb1.sheetnames # 获取sheet的名称
ws1 = wb1[sheet_names1[0]] # 打开第一个sheet
# 获取整个列的单元格(某一列)
max_row1 = ws1.max_row # 最大行
#column1 = ws1['D3':'D%d' % max_row]
column1 = ws1['D3':'D%d' % max_row1]
for column_cells in column1:
for cell in column_cells:
cl1 = str(cell.value)
cl1 = cl1.replace('*','').replace('*','') # 字符串的简单处理
list2.extend([[cl1,cell.row]]) # 单元格文字和所在的行
同一信息在B表格读取相关内容,首先这里是遍历文件夹打开读取表格信息,list1保存单元文字和相关内容。
# ==============================
for parent, dirnames, filenames in os.walk(path):
for n in filenames: # 遍历SHEET
if not n.startswith('~$'):
if n.endswith('.xlsx'): # 查找文件.lxsx
# ==============================
print('正在执行:',n)
n0 = (os.path.join(parent, n))
wb = load_workbook(n0) # 打开相关表
sheet_names = wb.sheetnames # 获取sheet的名称
ws = wb[sheet_names[0]]
# ==============================
# 获取整个列的单元格(某一列)
max_row = ws.max_row
column = ws['D9':'D%d' % max_row]
# 获取某一列对应的所有单元格对象
for column_cells in column:
for cell in column_cells:
cl = str(cell.value)
if cl != 'None':
for i in list2:
if i[0] == cl:
rc1 = ws['G%d' % cell.row].value
rc2 = ws['H%d' % cell.row].value
list1.extend([[i[0],rc1,rc2,n]])
list1[0]和list2[0]都是同一信息,相同的单元格文字,list1还保存了行,list2保存了相关内容。这里便可以在A表格中写入信息了。if i[0] == j[0]:,写入信息。
# ==============================
wb1 = load_workbook(path1)
sheet_names1 = wb1.sheetnames
ws1 = wb1[sheet_names1[0]]
ws1['N2'] = '***'
ws1['O2'] = '***'
ws1['P2'] = '***'
for i in list2:
for j in list1:
if i[0] == j[0]:
ws1['N%d' % i[1]] = j[1]
ws1['O%d' % i[1]] = j[2]
ws1['P%d' % i[1]] = j[3]
ws1.column_dimensions['N'].width = 12.0
ws1.column_dimensions['O'].width = 36.0
ws1.column_dimensions['P'].width = 36.0
wb1.save(path1)