Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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)
like image 545
Aram Maliachi Avatar asked Oct 20 '25 16:10

Aram Maliachi


2 Answers

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

like image 104
jmcnamara Avatar answered Oct 23 '25 05:10

jmcnamara


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)
like image 42
Roméo Després Avatar answered Oct 23 '25 06:10

Roméo Després



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!