Fill out Excel Template with Python

Question

I'm having trouble finding a solution to fill out an excel template using python. I currently have a pandas dataframe where I use openpyxl to write the necessary data to specific Rows and Cells in a for loop. The issue I have is that in my next project several of the cells I have to write are not continuous so for example instead of going A1,A2,A3 it can go A1,A5,A9. However this time if I were to list the cells like I did in the past it would be impractical.

So I was looking for something that would work similar to a Vlookup in excel. Where in the template we have Python would match the necessary Row and Column to drop the information. I know I might need to use different commands.

I added a picture below as an example. So I would need to drop values in the empty cells and ideally Python would read "USA and Revenue" and know to drop that information on cell B2. I know I might need something to map it also I am just not sure on how to start or if it is even possible.

enter image description here

To help find a solution, could you include in your question an example dataframe you are using. — Chris Seeling, Jul 20 '22 at 07:50

score 0 · Answer 1 · answered Apr 03 '23 at 12:25

I guess you have already completed your project now, but for possible future searches:

You can use the formula from here and adapt it for your request.

import pandas as pd
import openpyxl
from openpyxl.utils.dataframe import dataframe_to_rows

def df_to_excel(df, ws, header=False, index=False, startrow=0, startcol=0):
    """Write DataFrame df to openpyxl worksheet ws"""

    rows = dataframe_to_rows(df, header=header, index=index)

    for r_idx, row in enumerate(rows, startrow + 1):
        for c_idx, value in enumerate(row, startcol + 1):
            if value is not None:
                ws.cell(row=r_idx, column=c_idx).value = value
            else:
                pass

In order to use only one data frame, None can be stored in the areas of the formula fields. If the data is drawn from SQL, for example, it can be explicitly stored as NULL in SQL.

wb = openpyxl.Workbook()
df = pd.DataFrame({'USA':[1,2,3],
                   'Mexico':[4,5,6],
                   'Canada':[7,8,9], 
                   'Lating America':[None,None,None], 
                   'London':[1,2,3], 
                   'France':[1,2,3]})
df_to_excel(df, wb.active, startrow=2, startcol=2)

The result would looks like that:

Fill out Excel Template with Python

1 Answers1