Four scenarios of processing Excel files with Python

① Cell reading and writing 、② Read and write by line 、③ Fetching calculation by line 、④ Retrieve data by line and save it as a dictionary

 ① Read cell data , Write as is , Other existing workbooks

# Read cell data , Write as is , Other existing workbooks
from openpyxl import load_workbook
# Open the workbook
s=load_workbook('D:/10 Monthly performance sheet .xlsx')
s1=load_workbook('D:/ Xiao Wang's payroll .xlsx')
# Get the active worksheet
active_s=s.active
active_s1=s1.active
active_s1['F11'].value=active_s['C2'].value
active_s1['G11'].value=active_s['D2'].value
active_s1['H11'].value=active_s['E2'].value
s1.save('D:/ Xiao Wang's payroll .xlsx')

② Put the front of a worksheet 3 Row data , Write as is to other worksheets

# Put the front of a worksheet 3 Row data , Write as is to other worksheets
from openpyxl import load_workbook,Workbook
# Open sheet
s=load_workbook('D:/name.xlsx')
active_s=s.active
# Create a new sheet
s1=Workbook()
active_s1=s1.active
for row in active_s.iter_rows(min_row=1, max_row=3,values_only=True):
active_s1.append(row)
s1.save('D:/demo.xlsx')

③ Calculate the value of some cells

# Calculate the value of some cells , And print
from openpyxl import load_workbook
# Open sheet
s=load_workbook('D:/name.xlsx')
active_s=s.active
# Get data except header
for row in active_s.iter_rows(min_row=2,values_only=True):
# Read the number
staff_id=row[0]
# Read name
staff_name=row[1]
# Read Department
staff_dep=row[2]
# Read performance
performance=row[3]
# Read Commission
bonus=row[4]
# Read basic salary
gongzi=row[5]
salary=performance+bonus+gongzi
print(' Number :{}, full name :{}, department :{}, The salary is actually paid :{}'.format(staff_id,staff_name,staff_dep,salary))

④ Create a dictionary , How to extract keys and values using a dictionary , You can also easily query data

# Set salary information dictionary （ before 8 Take data for example ）
staff_info = {
'S1001': {' full name ': ' LV Jianguo ', ' department ': ' The sales department ', ' The performance of ': 100, ' Bonus ': 2250, ' Basic salary ': 7500, ' Are you sure ': ' yes '},
'S1002': {' full name ': ' Zhang Xiang ', ' department ': ' Logistics Department ', ' The performance of ': 100, ' Bonus ': 4250, ' Basic salary ': 7500, ' Are you sure ': ' yes '},
'S1003': {' full name ': ' Wang Shulan ', ' department ': ' Logistics Department ', ' The performance of ': 300, ' Bonus ': 3500, ' Basic salary ': 6500, ' Are you sure ': ' yes '},
'S1004': {' full name ': ' Zhao Lijuan ', ' department ': ' Development Department ', ' The performance of ': 100, ' Bonus ': 2750, ' Basic salary ': 7500, ' Are you sure ': ' no '},
'S1005': {' full name ': ' Chen Li ', ' department ': ' Development Department ', ' The performance of ': 100, ' Bonus ': 1750, ' Basic salary ': 6000, ' Are you sure ': ' yes '},
'S1006': {' full name ': ' Che min ', ' department ': ' The sales department ', ' The performance of ': 300, ' Bonus ': 4250, ' Basic salary ': 7000, ' Are you sure ': ' yes '},
'S1007': {' full name ': ' Sun Fei ', ' department ': ' Operation Department ', ' The performance of ': 200, ' Bonus ': 1500, ' Basic salary ': 7000, ' Are you sure ': ' yes '},
'S1008': {' full name ': ' Ksssssss ', ' department ': ' Logistics Department ', ' The performance of ': 300, ' Bonus ': 1500, ' Basic salary ': 9500, ' Are you sure ': ' yes '}
}
# Enter the job number of the employee you want to query
staff_id = input(' Please output the job number of the employee you queried ( Such as :S1001):')
# According to the job number （ key ） Find employee information （ value ）
dict_staff = staff_info.get(staff_id)
print(dict_staff)