Populate a template Excel sheet with DataFrame data.

excel_populator.py

#!/usr/bin/env python3
import time

import pandas as pd
from openpyxl import load_workbook
from openpyxl.utils import coordinate_to_tuple
from openpyxl.workbook.views import BookView


def populate_template_excel(
        source_dataframe,
        template_workbook,
        template_worksheet,
        new_worksheet_name,
        start_cell='A1'):
    """Populate a template Excel sheet with DataFrame data.
    Args:
        template_workbook (str): Path to the template_workbook.
        new_worksheet_name (str): Name of the target sheet to be created
        source_dataframe (pd.DataFrame):
        template_worksheet (str): Template worksheet name
        start_cell (str): The starting cell to fill in the target sheet.

    Returns:
        openpyxl.workbook.workbook.Workbook: Resultant Workbook object

    References:
      OpenPyXL:
      https://openpyxl.readthedocs.io/en/stable/tutorial.html#create-a-workbook
      https://openpyxl.readthedocs.io/en/3.1/api/openpyxl.utils.cell.html#openpyxl.utils.cell.coordinate_to_tuple

      Set window size in Python openpyxl:
      https://stackoverflow.com/a/68362795/11042987
    """
    # Set up a workbook object.
    wb = load_workbook(template_workbook)
    view = [BookView(xWindow=0, yWindow=0, windowWidth=18140, windowHeight=15540)]
    wb.views = view
    # Duplicate the template sheet.
    source = wb[template_worksheet]
    ws = wb.copy_worksheet(source)
    ws.title = new_worksheet_name
    # Fill the target cells.
    row_len, col_len = source_dataframe.shape
    row_start, col_start = coordinate_to_tuple(start_cell)
    for row in range(row_len):
        for col in range(col_len):
            value = source_dataframe.iat[row, col]
            ws.cell(row + row_start, col + col_start, value)

    return wb


if __name__ == '__main__':
    print()
    wb_obj = populate_template_excel(source_dataframe=pd.DataFrame(
        {'col_1': [11, 21, 31, 41, 51],
         'col_2': [12, 22, 32, 42, 52],
         'col_3': [13, 23, 33, 43, 53]}), template_workbook=input('Template Excel? >> '),
        new_worksheet_name=input('Name the new sheet to be created. >> '),
        template_worksheet=input('Template sheet? >> '), start_cell=input('Start cell? (e.g. A1, BC106, etc. >> '))
    out_filepath = f'./filled_{int(time.time())}.xlsx'
    wb_obj.save(out_filepath)
    print(f'Saved: {out_filepath}\n')