How to Append Data Using Openpyxl Python to Excel File from a Specified Row

how to append data using openpyxl python to excel file from a specified row?

Try using:

sheet.max_row 

It will return the last row value, you can start writing the new values from there:

max = ws.max_row
for row, entry in enumerate(data1, start=1):
   st.cell(row=row+max, column=1, value=entry)

Insert row into Excel spreadsheet using openpyxl in Python

Answering this with the code that I'm now using to achieve the desired result. Note that I am manually inserting the row at position 1, but that should be easy enough to adjust for specific needs. You could also easily tweak this to insert more than one row, and simply populate the rest of the data starting at the relevant position.

Also, note that due to downstream dependencies, we are manually specifying data from 'Sheet1', and the data is getting copied to a new sheet which is inserted at the beginning of the workbook, whilst renaming the original worksheet to 'Sheet1.5'.

EDIT: I've also added (later on) a change to the format_code to fix issues where the default copy operation here removes all formatting: new_cell.style.number_format.format_code = 'mm/dd/yyyy'. I couldn't find any documentation that this was settable, it was more of a case of trial and error!

Lastly, don't forget this example is saving over the original. You can change the save path where applicable to avoid this.

    import openpyxl

    wb = openpyxl.load_workbook(file)
    old_sheet = wb.get_sheet_by_name('Sheet1')
    old_sheet.title = 'Sheet1.5'
    max_row = old_sheet.get_highest_row()
    max_col = old_sheet.get_highest_column()
    wb.create_sheet(0, 'Sheet1')

    new_sheet = wb.get_sheet_by_name('Sheet1')

    # Do the header.
    for col_num in range(0, max_col):
        new_sheet.cell(row=0, column=col_num).value = old_sheet.cell(row=0, column=col_num).value

    # The row to be inserted. We're manually populating each cell.
    new_sheet.cell(row=1, column=0).value = 'DUMMY'
    new_sheet.cell(row=1, column=1).value = 'DUMMY'

    # Now do the rest of it. Note the row offset.
    for row_num in range(1, max_row):
        for col_num in range (0, max_col):
            new_sheet.cell(row = (row_num + 1), column = col_num).value = old_sheet.cell(row = row_num, column = col_num).value

    wb.save(file)

Exporting to Excel Using Openpyxl - specific rows and columns

I solved the problem like this:

from openpyxl import Workbook
wb = Workbook()
wb = load_workbook ('OFERTA_SZABLON.xlsx')
# ws1 = wb.sheetnames()
ws1 = wb["DETALE wyceniane osobno"]

# for r in dataframe_to_rows(df, index=False, header=False):
#     ws1.append(r)
offset_row = 5        
offset_col = 0

row = 1
for row_data in dataframe_to_rows(df, index=False, header=False):
    col = 1
    for cell_data in row_data:

        ws1.cell(row + offset_row, col + offset_col, cell_data)

        col += 1

    row += 1



wb.save('OFERTA_SZABLON.xlsx')

Openpyxl - Appending data from an Excel workbook to another

I found the solution and will post it here in case anyone will have the same problem. Although the cells below looked empty, they had apparently, weird formatting. That's why the Python script saw the cells as Non-empty and appended/shifted the data in another place(the place where there was no formatting).

The Solution would be to format every row below your data as empty cells. (Just copy a range of empty cells from a new Workbook and paste it below your data)

Hope that helps! ;)

how to append data frame to existed formulated excel file

If you write to the file by cells, you can do it. Below is the code...
Assuming that, by formulated, you mean the cells have format (color, font, etc.) and you want to write data without changing the format of the cells)

import numpy as np
#Create random 10x3 dataframe
df = pd.DataFrame(np.random.randint(0,100,size=(10, 3)), columns=list('ABC'))

wb = openpyxl.load_workbook('output.xlsx', read_only=False)
ws=wb['Sheet2']

#Write header
for col in range(df.shape[1]):
    ws.cell(1, col+1).value = df.columns[col]

#Write the data
for row in range(df.shape[0]):
    for column in range(df.shape[1]):
        ws.cell(row=row+2, column=column+1).value = df.iloc[row,column]
wb.save('output.xlsx')
wb.close()

Output - Before writing

After writing your data

Option 2 - based on Charlie Clark's input

This does the same thing as above, but a little faster/better....

import numpy as np
from openpyxl.utils.dataframe import dataframe_to_rows

df = pd.DataFrame(np.random.randint(0,100,size=(10, 3)), columns=list('ABC'))

wb = openpyxl.load_workbook('output.xlsx', read_only=False)
ws=wb['Sheet2']
rows = dataframe_to_rows(df, index=False)
for r_idx, row in enumerate(rows, 1):
    for c_idx, value in enumerate(row, 1):
        ws.cell(row=r_idx, column=c_idx, value=value)
        
wb.save('output.xlsx')
wb.close()

---

Related Topics