openpyxl使用笔记

#两个Excel文件簿分析数据用
import openpyxl
import datetime

wb=openpyxl.load_workbook(r’D:\py*20230408.xlsx’)
sht=wb.worksheets[0]
r=sht.max_row
nwb=openpyxl.load_workbook(r’C:\Users\Administrator\Desktop\223.xlsx’)
nsht=nwb.worksheets[0]
nr=nsht.max_row
n=0

t=datetime.datetime.now()

for i in range(4,r):
for y in range(5,nr):
if str(sht.cell(i,9).value)==str(nsht.cell(y,2).value):
print(str(sht.cell(i,9).value),str(sht.cell(i,4).value))
s=datetime.datetime.now()
print(‘第三批ok’+str(s-t))

#将文件夹内的Excel文件（后缀.xlsx）合并在一张表中
import os
import openpyxl

path=r’D:\台账\hb2’
filenames=os.listdir(path)
ouwb=openpyxl.Workbook()
oush=ouwb.create_sheet(‘汇总’)
arr=[]
r=0
n = 0
for filename in filenames:
fname=r’D:\py\台账\hb2%s’%filename
wb=openpyxl.load_workbook(fname)#打开已有工作表
#sht=wb.create_sheet(index=1,title=“汇总”)#新建工作表
sht=wb.worksheets[0]
print(fname)
rs=sht.max_row+1

cors=sht.max_column+1
print(rs,cors)
for i in range(3,rs):
    n+=1
    for x in range(1,cors):
        if x==4:
            oush.cell(n,x).value=str(sht.cell(i,x).value)
            # print(sht.cell(i,x).value)
        else:
            oush.cell(n,x).value=str(sht.cell(i,x).value)
            # print(sht.cell(i, x).value)

ouwb.save(r’d:\汇总20230408.xlsx’)
print(‘ok’)

#筛查数据范例
import openpyxl

wb=openpyxl.load_workbook(r’C:\Users\Administrator\Desktop***456.xlsx’)
#sht=wb[‘*台账’]
sht=wb.worksheets[0]
rs=sht.max_row+1
crs=sht.max_column+1

nwb=openpyxl.Workbook()
nsht=nwb.create_sheet(index=0,title=“筛查结果”)
n=0
for i in range(4,rs):
n = nsht.max_row + 1
for x in range(1,crs):
if ‘贵州’ in str(sht.cell(i,8).value) or ‘西藏’ in str(sht.cell(i,8).value)
or ‘昆明’ in str(sht.cell(i,8).value) or ‘云南’ in str(sht.cell(i,8).value):
print(sht.cell(i,7).value)
nsht.cell(n,x).value=str(sht.cell(i,x).value)
nwb.save(“d:\筛查结果2.xlsx”)
print(‘ok’)