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
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)

版权声明
本文为[Fan Doudou 123]所创,转载请带上原文链接,感谢
https://pythonmana.com/2021/11/20211109005047263c.html

  1. The difference between single quotation marks, double quotation marks, three single quotation marks and three double quotation marks in Python
  2. Python exercise (11.7)
  3. python11~20
  4. Matlab, python example exercise -- Day2
  5. Matlab, python example exercise -- Day1
  6. White Python road -- Day5
  7. Python正则表达式选择字符
  8. The white way of Python -- day4 (I can't learn it gradually)
  9. The white way of Python -- Day3
  10. The white Python road -- Day2
  11. The white way of Python -- Day1
  12. Difference between input in python2 and python3
  13. Exception handling in Python
  14. Metaclass and singleton pattern of Python
  15. Python methods and functions
  16. Python regular expression select Characters
  17. Introduction to Python object-oriented reflection and built-in methods
  18. Object oriented composition of Python
  19. Python object oriented polymorphism
  20. Object oriented encapsulation of Python
  21. Object oriented inheritance in Python
  22. Python Software Development Directory
  23. Python package
  24. Python module
  25. Python is process oriented and functional
  26. Python function recursion
  27. Python generator
  28. Python iterator
  29. Python decorator
  30. Python function closure
  31. Python namespace and scope
  32. Python function
  33. Python file processing
  34. Python data type
  35. Process control of Python
  36. Garbage collection mechanism of Python
  37. Python: the field notes of dtype = 'object' can also be arranged in ascending and descending order
  38. . value in pandas_ Usage of counts()
  39. 202111 | some hands-on practice of Python time function
  40. 202111 | in Python, timestamp, time string and real date time are converted to each other
  41. Pandas draws line chart, bar chart and bar chart
  42. Exploration and practice of easy transformation between various data structures in Python
  43. A simple practice of Python list generation
  44. Use Python to guess the number of machines and judge the number of guesses
  45. Question about Python: did you learn Python
  46. 20210928 | Python case: building tax calculation function
  47. Python basic syntax collation
  48. Some small accumulation of writing programs in python (4)
  49. Some small accumulation of writing programs in python (3)
  50. Python leak detection tips (3)
  51. Python leak detection tips (2)
  52. Python leak detection tips (1)
  53. Python foundation and MySQL Foundation
  54. Some small accumulation of writing programs in python (2)
  55. Some small accumulation of writing programs in Python
  56. Python 3.7.3 + cuda9.2 installing Python
  57. Python knowledge used to write programs
  58. Python installation + vscode configuration Python environment
  59. Some small problems during Python installation
  60. Answer and Q & A of Python practice introduction course