Wie schreibe ich in eine vorhandene Excel-Datei, ohne Daten zu überschreiben (mit Pandas)?

Hier ist eine Hilfsfunktion:

def append_df_to_Excel(filename, df, sheet_name='Sheet1', startrow=None,
                       truncate_sheet=False, 
                       **to_Excel_kwargs):
    """
    Append a DataFrame [df] to existing Excel file [filename]
    into [sheet_name] Sheet.
    If [filename] doesn't exist, then this function will create it.

    Parameters:
      filename : File path or existing ExcelWriter
                 (Example: '/path/to/file.xlsx')
      df : dataframe to save to workbook
      sheet_name : Name of sheet which will contain DataFrame.
                   (default: 'Sheet1')
      startrow : upper left cell row to dump data frame.
                 Per default (startrow=None) calculate the last row
                 in the existing DF and write to the next row...
      truncate_sheet : truncate (remove and recreate) [sheet_name]
                       before writing DataFrame to Excel file
      to_Excel_kwargs : arguments which will be passed to `DataFrame.to_Excel()`
                        [can be dictionary]

    Returns: None
    """
    from openpyxl import load_workbook

    import pandas as pd

    # ignore [engine] parameter if it was passed
    if 'engine' in to_Excel_kwargs:
        to_Excel_kwargs.pop('engine')

    writer = pd.ExcelWriter(filename, engine='openpyxl')

    # Python 2.x: define [FileNotFoundError] exception if it doesn't exist 
    try:
        FileNotFoundError
    except NameError:
        FileNotFoundError = IOError


    try:
        # try to open an existing workbook
        writer.book = load_workbook(filename)

        # get the last row in the existing Excel sheet
        # if it was not specified explicitly
        if startrow is None and sheet_name in writer.book.sheetnames:
            startrow = writer.book[sheet_name].max_row

        # truncate sheet
        if truncate_sheet and sheet_name in writer.book.sheetnames:
            # index of [sheet_name] sheet
            idx = writer.book.sheetnames.index(sheet_name)
            # remove [sheet_name]
            writer.book.remove(writer.book.worksheets[idx])
            # create an empty sheet [sheet_name] using old index
            writer.book.create_sheet(sheet_name, idx)

        # copy existing sheets
        writer.sheets = {ws.title:ws for ws in writer.book.worksheets}
    except FileNotFoundError:
        # file does not exist yet, we will create it
        pass

    if startrow is None:
        startrow = 0

    # write out the new sheet
    df.to_Excel(writer, sheet_name, startrow=startrow, **to_Excel_kwargs)

    # save the workbook
    writer.save()

HINWEIS: Für Pandas <0.21.0, sheet_name durch sheetname ersetzen!

Anwendungsbeispiele:

append_df_to_Excel('d:/temp/test.xlsx', df)

append_df_to_Excel('d:/temp/test.xlsx', df, header=None, index=False)

append_df_to_Excel('d:/temp/test.xlsx', df, sheet_name='Sheet2', index=False)

append_df_to_Excel('d:/temp/test.xlsx', df, sheet_name='Sheet2', index=False, startrow=25)

Mit openpyxlversion 2.4.0 und pandasversion 0.19.2 wird der von @ski entwickelte Prozess etwas einfacher:

import pandas
from openpyxl import load_workbook

with pandas.ExcelWriter('Masterfile.xlsx', engine='openpyxl') as writer:
    writer.book = load_workbook('Masterfile.xlsx')
    data_filtered.to_Excel(writer, "Main", cols=['Diff1', 'Diff2'])
#That's it!

Alte Frage, aber ich vermute, dass einige Leute immer noch danach suchen - also ...

Ich finde diese Methode nett, weil alle Arbeitsblätter in ein Wörterbuch mit den Namen von Blattnamen und Datenrahmen geladen werden, die von Pandas mit der Option Blattname = Keine erstellt wurden. Es ist einfach, Arbeitsblätter hinzuzufügen, zu löschen oder zu ändern, indem Sie das Arbeitsblatt in das Diktierformat einlesen und aus dem Diktat zurückschreiben. Für mich funktioniert der xlsxwriter in Bezug auf Geschwindigkeit und Format besser als openpyxl.

Hinweis: In zukünftigen Versionen von Pandas (0.21.0+) wird der Parameter "sheetname" in "sheet_name" geändert.

# read a single or multi-sheet Excel file
# (returns dict of sheetname(s), dataframe(s))
ws_dict = pd.read_Excel(excel_file_path,
                        sheetname=None)

# all worksheets are accessible as dataframes.

# easy to change a worksheet as a dataframe:
mod_df = ws_dict['existing_worksheet']

# do work on mod_df...then reassign
ws_dict['existing_worksheet'] = mod_df

# add a dataframe to the workbook as a new worksheet with
# ws name, df as dict key, value:
ws_dict['new_worksheet'] = some_other_dataframe

# when done, write dictionary back to Excel...
# xlsxwriter honors datetime and date formats
# (only included as example)...
with pd.ExcelWriter(Excel_file_path,
                    engine='xlsxwriter',
                    datetime_format='yyyy-mm-dd',
                    date_format='yyyy-mm-dd') as writer:

    for ws_name, df_sheet in ws_dict.items():
        df_sheet.to_Excel(writer, sheet_name=ws_name)

Für das Beispiel in der Frage 2013:

ws_dict = pd.read_Excel('Masterfile.xlsx',
                        sheetname=None)

ws_dict['Main'] = data_filtered[['Diff1', 'Diff2']]

with pd.ExcelWriter('Masterfile.xlsx',
                    engine='xlsxwriter') as writer:

    for ws_name, df_sheet in ws_dict.items():
        df_sheet.to_Excel(writer, sheet_name=ws_name)

Ich weiß, dass dies ein älterer Thread ist, aber dies ist das erste Element, das Sie beim Suchen finden. Die oben genannten Lösungen funktionieren nicht, wenn Sie Diagramme in einer bereits erstellten Arbeitsmappe beibehalten müssen. In diesem Fall ist xlwings die bessere Option - Sie können in das Excel-Buch schreiben und die Diagramme/Diagrammdaten beibehalten.

einfaches Beispiel:

import xlwings as xw
import pandas as pd

#create DF
months = ['2017-01','2017-02','2017-03','2017-04','2017-05','2017-06','2017-07','2017-08','2017-09','2017-10','2017-11','2017-12']
value1 = [x * 5+5 for x in range(len(months))]
df = pd.DataFrame(value1, index = months, columns = ['value1'])
df['value2'] = df['value1']+5
df['value3'] = df['value2']+5

#load workbook that has a chart in it
wb = xw.Book('C:\\data\\bookwithChart.xlsx')

ws = wb.sheets['chartData']

ws.range('A1').options(index=False).value = df

wb = xw.Book('C:\\data\\bookwithChart_updated.xlsx')

xw.apps[0].quit()

Ab Pandas 0.24 können Sie dies mit dem mode-Schlüsselwortargument von ExcelWriter vereinfachen:

import pandas as pd

with pd.ExcelWriter('the_file.xlsx', engine='openpyxl', mode='a') as writer: 
     data_filtered.to_Excel(writer) 

def append_sheet_to_master(self, master_file_path, current_file_path, sheet_name):
    try:
        master_book = load_workbook(master_file_path)
        master_writer = pandas.ExcelWriter(master_file_path, engine='openpyxl')
        master_writer.book = master_book
        master_writer.sheets = dict((ws.title, ws) for ws in master_book.worksheets)
        current_frames = pandas.ExcelFile(current_file_path).parse(pandas.ExcelFile(current_file_path).sheet_names[0],
                                                               header=None,
                                                               index_col=None)
        current_frames.to_Excel(master_writer, sheet_name, index=None, header=False)

        master_writer.save()
    except Exception as e:
        raise e

Das funktioniert einwandfrei, nur die Formatierung der Master-Datei (Datei, zu der wir ein neues Blatt hinzufügen) geht verloren.

book = load_workbook(xlsFilename)
writer = pd.ExcelWriter(self.xlsFilename)
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)
df.to_Excel(writer, sheet_name=sheetName, index=False)
writer.save()

Es gibt eine bessere Lösung in Pandas 0.24:

with pd.ExcelWriter(path, mode='a') as writer:
    s.to_Excel(writer, sheet_name='another sheet', index=False)

vor:

nach dem:

so rüste deine Pandas jetzt auf:

pip install --upgrade pandas

writer = pd.ExcelWriter('prueba1.xlsx'engine='openpyxl',keep_date_col=True)

Die "keep_date_col" -Hoffnung hilft Ihnen dabei