CODEWITHSUNDEEP
pythonpython-3.xpandasxlsxwriter
Aram Maliachi
Aram Maliachi

Reputation: 223

Append row to top of excel sheet python

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

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)

Upvotes: 4

Views: 1746

Answers (2)

jmcnamara
jmcnamara

Reputation: 41554

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')

Upvotes: 3

Roméo Després
Roméo Després

Reputation: 2163

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)

Upvotes: 1

Related Questions