python搜索excel数据_Python在多个Excel中搜索对应的数据并粘贴到新的Excel工作表中...

i have some excel files in a folder, there's already a worksheet call "service" in each file

Notes_111.xlsx

Notes_222.xlsx

Notes_888.xlsx

Workflow : I want to open each .xlsx file, for example, Notes_111.xlsx, then add a new worksheet, name as "code_city", then based on file's name 111, extract only the code = 111 data from the master dataframe and paste to the new worksheet. then save.

Sample master dataframe in another excel file

code city

0 111 NY

1 111 CA

2 222 NJ

3 888 WE

4 888 TL

i don't know how to write a logic within a loop to search corresponding data

import pandas as pd

import numpy as np

import glob

from openpyxl import load_workbook

for f in glob.glob(path + "Notes_*.xlsx"):

wb = load_workbook(f)

ws = wb.create_sheet('code_city')

ws['A1'] = 'how to search corresponding data and paste here???'

wb.save(f)

please help.

解决方案

Use pandas its much easier to manipulate, I believe it uses openpyxl under the hood anyway.

import glob

import pandas as pd

import os

for f in glob.glob('Notes_*.xlsx'):

dda = re.findall('\d+', f) #matches digits in the filename

df_each = pd.read_excel(f) # have to save the data first, coz ExcelWriter will clear up and create a new excel, so, you paste the saved data back to new sheet

df_1_dda = df_master[df_master['code'] == int(dda[0])] #select only those records with code in the filename

writer = pd.ExcelWriter(f)

df_each.to_excel(writer, 'service', index = False) # paste the saved data back to new sheet

df_1_dda.to_excel(writer, 'code_city', index = False)

writer.close()

Hope that helps!

using python 3.6.4 Anaconda - 32-bit