# Four scenarios of processing Excel files with Python

Fan Doudou 123 2021-11-25 09:27:45
scenarios processing excel files 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
# Open the workbook
# 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
# Open sheet
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
# Open sheet
active_s=s.active
for row in active_s.iter_rows(min_row=2,values_only=True):
staff_id=row[0]
staff_name=row[1]
staff_dep=row[2]
performance=row[3]
bonus=row[4]
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)``````

https://pythonmana.com/2021/11/20211109005047263c.html