I. Finding operations
The modules xlrd, xlwt, and xlutils are responsible for reading, writing, and read/write conversion of Excel files, respectively!
You can read and write directly to Excel files!
You can read and write directly to Excel files!
Install the openpyxl module
pip install openpyxl
III. Reading and screening your own information in the duty roster
1. Read all duty information;
2. As a result of the general situation excel will have part of the form is empty, save all None of the excel line string data;
3. Loop through all of the shift data to form a data string from the current line of data;
4. Determine whether the current duty message string contains your name;
5. Store key information (duty time, name) in data containing your own information;
6. Then determine whether the current string contains all None data;
7. Since there are no empty rows in the duty list, check None and jump out of the loop directly.
dutys = []
book = openpyxl.load_workbook('',data_only=True)
sheet =
all_data = book.get_sheet_by_name("Daily overtime")
none_str = ''.join([str(None).ljust(20) for c in range(1,all_data.max_column+1)])
for r in range(1,all_data.max_row + 1):
cur_str = ''.join([str(all_data.cell(row=r,column=c).value).ljust(20) for c in range(1,all_data.max_column+1)])
if cur_str.find("***") >= 0:
({
"date": all_data.cell(row=r,column=2).value,
"name": all_data.cell(row=r,column=3).value
})
elif cur_str.find(none_str) >= 0:
break
return dutys
IV. Creating your own duty information sheet
1. Create an excel of the duty information sheet;
2. Circulate your duty information;
3. Fill in the information on the created form.
book = ()
sheet =
for i in range(len(dutys)):
(row=1 + i, column=1).value = dutys[i].get("name")
(row=1 + i, column=2).value = f'{dutys[i].get("date")}'
('my_duty.xlsx')
V. All codes
#!/usr/bin/env python
"""
@Author ：Rattenking
@Date ：2021/06/02 10:19
@CSDN ：/m0_38082783
"""
import openpyxl
import time
def get_my_duty_date():
dutys = []
book = openpyxl.load_workbook('',data_only=True)
sheet =
all_data = book.get_sheet_by_name("Daily overtime")
none_str = ''.join([str(None).ljust(20) for c in range(1,all_data.max_column+1)])
for r in range(1,all_data.max_row + 1):
cur_str = ''.join([str(all_data.cell(row=r,column=c).value).ljust(20) for c in range(1,all_data.max_column+1)])
if cur_str.find("***") >= 0:
({
"date": all_data.cell(row=r,column=2).value,
"name": all_data.cell(row=r,column=3).value
})
elif cur_str.find(none_str) >= 0:
break
return dutys
def create_my_duty_list(dutys):
book = ()
sheet =
for i in range(len(dutys)):
(row=1 + i, column=1).value = dutys[i].get("name")
(row=1 + i, column=2).value = f'{dutys[i].get("date")}'
('my_duty.xlsx')
if __name__ == "__main__":
start_time = int(round(() * 1000))
dutys = get_my_duty_date()
create_my_duty_list(dutys)
end_time = int(round(() * 1000))
print(f'Time of this withdrawal duty roster：{end_time - start_time}ms')
VI. Results of implementation
VII. Summary
Familiarize yourself with the various functions of the openpyxl module to facilitate the operation of excel; filter and extract the key information you are concerned about, and rebuild the table; the next article according to the duty time, use python to automatically send messages to their WeChat for reminders!
This article on Python can also play so that only 30 lines of code from excel to extract the personal duty list of the article is introduced to this, more related Python from excel to extract the personal duty list of the contents of the search for my previous posts or continue to browse the following related articles I hope that you will support me in the future more!