CODEWITHSUNDEEP
pythonexcelopenpyxl
Rick Zhang
Rick Zhang

Reputation: 89

How to append a row after certain rows in Excel using Python

I am new to Python. Just playing openpyxl these days. I want to write a spreadsheet using Python, however the format and styles of the cells are complex and not necessarily right every time.

So I am thinking of making a template excel file and always reading from the the file and saving it as the new file. To do this I have avoid overwriting some rows. Currently I use the code to append rows in Excel as I do not the size is going to be appended.

Is there any way I can append rows from certain row like from 7th row?

My code is:

def Output(message):

    wb = Workbook()
    ws = wb.active
    ws.title = 'Quotation'
    Row_size = len(message)
    for i in range (Row_size):
        message_line = message[i]

        ws.append(message_line)

    wb.save('Quatation.xlsx')

Upvotes: 6

Views: 10734

Answers (1)

Martin Evans
Martin Evans

Reputation: 46779

Rather than using ws.append(), use the ws.cell() function to specify the required row and column. enumerate() can be used to give you a row number starting from 7 for each entry in the passed list.

from openpyxl import Workbook

def Output(message):
    wb = Workbook()
    ws = wb.active
    ws.title = 'Quotation'
    
    for row, text in enumerate(message, start=7):
        ws.cell(column=1, row=row, value=text)

    wb.save('Quotation.xlsx')

Output(['hello', 'world'])

Giving you:

excel screenshot

Note, if you are passing multiple cells per row, you will need a second loop. If you want to append to an existing file, you can also use ws.max_row to give you where the last existing row is:

import openpyxl

xlsx = 'Quotation.xlsx'
wb = openpyxl.load_workbook(xlsx)
ws = wb.active

data = [['a', '1'], ['b', '2']]     # new rows to append to existing xlsx

for rowy, row in enumerate(data, start=ws.max_row):
    for colx, value in enumerate(row, start=1):
        ws.cell(column=colx, row=rowy, value=value)

wb.save(xlsx)

In this case, ws.append() could also be used, but by using ws.cell() it would give you greater control over the formatting of the cell if needed. e.g.

import openpyxl

xlsx = 'Quotation.xlsx'
wb = openpyxl.load_workbook(xlsx)
ws = wb.active

data = [['a', '1'], ['b', '2']]     # new rows to append to existing xlsx

for row in data:
    ws.append(row)

wb.save(xlsx)

Upvotes: 5

Related Questions