Append row to top of excel sheet python

Question

How can I append a row at the top of an excel sheet? Goal as follows:

The file itself is written by using pandas.df.to_excel as follows:

import pandas

with pandas.ExcelWriter(output_filename) as writer:
   for file in files:
      df = pandas.read_csv(file)
      df.to_excel(writer, sheet_name=file.replace(".csv", "").replace("_", " ").title(), index=False)

Looking at [this](https://stackoverflow.com/questions/18002133/xlsxwriter-is-there-a-way-to-open-an-existing-worksheet-in-my-workbook), you cant use XlsWriter to open and modify an existing file. See if it helps. — shahkalpesh, May 18 '21 at 13:56

score 3 · Accepted Answer · answered May 19 '21 at 14:28

Here is one way to do it using XlsxWriter as the Excel engine:

with pandas.ExcelWriter(output_filename, engine='xlsxwriter') as writer:
    for file in files:
        df = pandas.read_csv(file)
        sheet_name = file.replace(".csv", "").replace("_", " ").title()

        df.to_excel(writer, sheet_name=sheet_name, index=False, startrow=1)

        worksheet = writer.sheets[sheet_name]
        worksheet.write('A1', 'Here is some additional text')

score 1 · Answer 2 · answered May 19 '21 at 11:55

You can use openpyxl to edit your Excel file afterwards:

import contextlib

import openpyxl
import pandas as pd


new_row = "THIS ROW IS APPENDED AFTER THE FILE IS WRITTEN BY PANDAS"

with contextlib.closing(openpyxl.open(output_filename)) as wb:
    for file in files:
        sheet_name = file.replace(".csv", "").replace("_", " ").title()
        sheet = wb[sheet_name]
        sheet.insert_rows(0)
        sheet["A1"] = new_row
    wb.save(output_filename)

Append row to top of excel sheet python

2 Answers2