Inserting data into a variable number of Excel sheets depending on data size using Python

Question

I'm appending data to an existing, pre-formatted Excel Template. The problem is, depending on user input the data size could vary and could overflow beyond the available cells of the template. Is there a way to make it so that if the data size is larger than, let's say 8 entries, a new sheet is appended to the previous one and data is added to the new sheet, then it's all combined into a PDF in the end? The only part I'm having trouble with is how to make it so that Python keeps multiple instances of the sheet open at the same time and only as much as needed. I'm using Openpyxl and this is the code that I currently use that handles one excel sheet only up to 8 rows

# Open Template file
quotation = openpyxl.load_workbook("Path/Quotation_Template.xlsx")
sheet = quotation.active

# Add the product data to the quotation template and assign the cells to add data to
    rows = sheet.iter_rows(
        min_row=14, max_row=14 + 7, min_col=1, max_col=10
    )
# Insert Data into Template by looping over rows and inserting data into corresponding columns
for i, row in enumerate(rows):
        if i == len(product_data):
            break
        row[6].value = product_data["Quantity"][i]
        if product_data["Description"][i] == None:
            row[0].value = product_data["Product_Name"][i]
        else:
            row[0].value = product_data["Description"][i]
        row[7].value = product_data["Price"][i]
        row[8].value = product_data["Total"][i]
        row[9].value = product_data["Product_Name"][I]

# Direct all information to the function that creates PDF from the single Excel sheet
submit_quotation_to_db(session["user_id"], quotation, sheet)

Answer 1

You can try below code. I made up some random product data and set num_rows_per_sheet = 3

In the output file result.xlsx, you can see there are 4 sheets (one original, 3 copied), and there are 3 rows per sheet.

import openpyxl
import pandas as pd
import math

# Open Template file
quotation = openpyxl.load_workbook("template.xlsx")
sheet = quotation.active

product_data = pd.DataFrame({
    "Quantity": [1, 2, 3, 4, 5, 6, 7, 8, 9, 10],
    "Description": [1, 2, 3, 4, 5, 6, 7, 8, 9, 10],
    "Product_Name": [1, 2, 3, 4, 5, 6, 7, 8, 9, 10],
    "Price": [1, 2, 3, 4, 5, 6, 7, 8, 9, 10],
    "Total": [1, 2, 3, 4, 5, 6, 7, 8, 9, 10],
})

num_rows_per_sheet = 3
min_row = 14
max_row = min_row + num_rows_per_sheet - 1
min_col = 1
max_col = 10

num_products = len(product_data)
num_sheets_to_copy = math.ceil(num_products / num_rows_per_sheet) - 1

sheets = [sheet]
for _ in range(num_sheets_to_copy):
    ws = quotation.copy_worksheet(sheet)
    sheets.append(ws)

# Insert Data into Template by looping over rows and inserting data into corresponding columns
for j in range(len(sheets)):
    ws = sheets[j]
    rows = ws.iter_rows(min_row=min_row, max_row=max_row, min_col=min_col, max_col=max_col)
    for n, row in enumerate(rows):
        i = j * num_rows_per_sheet + n
        if i == len(product_data):
            break
        row[6].value = product_data["Quantity"][i]
        if product_data["Description"][i] is None:
            row[0].value = product_data["Product_Name"][i]
        else:
            row[0].value = product_data["Description"][i]
        row[7].value = product_data["Price"][i]
        row[8].value = product_data["Total"][i]
        row[9].value = product_data["Product_Name"][i]

quotation.save('result.xlsx')