My objective is to enter/type in values into Excel cells if the column names and indices match between Excel and dataframe.

So, my code does the below

  1. Gets the ID values from the dataframe

  2. for each ID, see whether it is present in excel file

  3. If they are present, get the column name list for those corresponding IDs (from dataframe)

  4. Filter only non-NA columns

  5. Check whether those columns names are present in excel file

  6. If both ID and column names (between dataframe and excel) match, key in the column value (from dataframe) into the excel sheet at appropriate position

You can refer this post for more details on the problem.

My code looks like below:

sales = xlwings.book('file1.xlsx')
df_value = region_1['ID'].tolist()
for val in df_value:
    for a_cell in sales.sheets[0]['B5:B8']:
        if a_cell.value == val:
            rn1 = a_cell.row
            temp = region_1.loc[[val]]
            temp = temp.dropna(axis=1, how='all')
            colu = temp.columns.tolist()
            for col in colu:
                for b_cell in sales.sheets[0]['G3:J3']:
                    if b_cell.value == col:
                        rn2 = b_cell.row
                        data_entry_loc = str(b_cell.address[1]) + str(rn1)
                        enter_val = temp[col].values
                        sales.sheets[0][data_entry_loc].value = enter_val

However, am not sure whether this code is elegant and efficient enough to handle big data. While it worked for sample data provided in the above post, I would like to seek your opinion/suggestions on how it can be improved. You can please write the improved version of the code as an answer below.

In case, if you wish to try out the sample data, you can find below

Sample dataframe


region_1 = pd.read_clipboard(sep=',') 

Input excel file

enter image description here

I expect my output to be like as shown below

enter image description here

  • \$\begingroup\$ @Reinderien - updated tye code \$\endgroup\$
    – The Great
    Mar 23, 2022 at 0:08
  • \$\begingroup\$ xlwings appears to a runtime Excel interface. Is it possible for you to just generate an offline file? If not, why not? \$\endgroup\$
    – Reinderien
    Mar 23, 2022 at 13:02
  • \$\begingroup\$ @Reinderien - I don't understand what do you mean by offline file. My excel files are offline placed in shared drive every week (and used by multiple departments). So, my code has to do some checks between dataframe and excel file. If checks are successful, then type in/enter the data in the excel file \$\endgroup\$
    – The Great
    Mar 23, 2022 at 14:12


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.