I need to fill the data in one xlsx file to another xlsm file using Python xlwings, Pandas, numpy ..-CodePudding

I am new to Python. I want to automate manual Excel tasks. Please help me.

I have one master xlsm file and another xlsx file with multiple sheets.

I am taking the values from multiple sheets in xlsx file and filling required cells in xlsm file.

Master xlsm file contains:

Item no, Item Name, Jan              
10,       aaa,      data_to_be_filled
20,       bbb,      data_to_be_filled
30,       ccc,      data_to_be_filled

Source xlsx file:

Item no, Item Name, Amount
10,       aaaa       1000
20,       bbbb       2000
30,       cccc       3000

I want the amount column data to be filled in corresponding cells in Master file 'Jan' column.

Edit, I tried the following:

pd.read_excel. df1 = pd.read_excel(master_file, sheet_name='Sheet1', header=52) 
df2 = pd.read_excel(source_file, sheet_name='Sheet1', header=2)
Left_join = pd.merge(df1, df2, on ='Item no.', how ='left') 
right_join = pd.merge(df1, df2, on = 'Item no.', how='right') 
left_right_join = pd.merge(df1, df2, left_on = 'Item no.', right_on='Item no.', how='right') 
inner_join = pd.merge(df1, df2, on = 'Item no.', how='inner') 
outer_join = pd.merge(df1, df2, on='Item no.', how='outer')

CodePudding user response：

Is this what you wanted?

df_final = pd.merge(df1, df2[["Item no", "Amount"]], on="Item no", how="left")
df_final["Jan"] = df_final["Amount"]
df_final.drop("Amount", axis=1, inplace=True)

Output:

    Item no Item Name   Jan
0   10      aaa         1000
1   20      bbb         2000
2   30      ccc         3000

CodePudding user response：

Thanks for your answer.

I solved with following code.

list_item_no = [10,20,30...]

x = 0 y = 0

def item_no_in_source_file(): for item_no in list_item_no: for row_source in range(1,55): for col_source in range(1, 10): if ws2.range((row_source,col_source)).value == item_no:
ns_col = 2
global x, y x = item_no y = (int(round(ws2.range(row_source, ns_col).value))) item_no_in_master_file(x, y) else: continue

def item_no_in_master_file(a, b):
for row in range(1, 200): for col in range(0, 2):
if ws1.range((row,col)).value == a: jan_col = 2 ws1.range(row, jan_col).value = y else: continue

item_no_in_source_file()