This time, when the intern changed the existing Excel file to the form he wanted to use this time, he automated it using the python library, so I will write it here.
I already have personal information in multiple spreadsheets, but it's a hassle to pull one by one from the child each time, so I put that information together in one spreadsheet and handed it to the client at once. It's easier to do. In that case, it is necessary to copy the specified parts to a new spreadsheet one by one, but the manual work is quite troublesome. So I want to automate it with python.
openpyxl (https://note.nkmk.me/python-openpyxl-usage/)
--Create one array by extracting the specified value for each sheet --Put that array in a new array to create a two-dimensional array --Write based on the two-dimensional array
sample.py
def write_list_2d(sheet, l_2d, start_row, start_col):
for y, row in enumerate(l_2d):
for x, cell in enumerate(row):
sheet.cell(row=start_row + y,
column=start_col + x,
value=l_2d[y][x])
l_2d = [['four', 41, 42, 43], ['five', 51, 52, 53]]
write_list_2d(sheet, l_2d, 5, 1)
Don't forget to save at the end. If you do not do this, it will not be reflected.
sample.py
wb_to.save('hoge.xlsx')
If you want to further divide the extracted characters, use a regular expression. What I wanted to do this time is 「hogehogehoge(fugafuga)」 I wanted to divide it into two parts, one in parentheses and the other. The conclusion is as follows.
sample.py
list = re.match(r"(?P<comment>.*?)(?:[\(（](?P<name>.*?)[）\)])?$",sentence)
temp.append(list['comment'])
temp.append(list['name'])
Recommended Posts