将 pandas.DataFrame 添加到现有 Excel 文件

我有一个网络抓取工具，可以为本月的抓取创建一个 Excel 文件。我想在每次运行时将今天的刮擦和该月的每次刮擦添加到该文件中作为新工作表。然而，我的问题是，它仅用新工作表覆盖现有工作表，而不是将其添加为单独的新工作表。我尝试使用 xlrd、xlwt、pandas 和 openpyxl 来完成此操作。

对于 Python 来说还是全新的，所以简单性值得赞赏！

下面只是处理写入 Excel 文件的代码。

# My relevant time variables
ts = time.time()
date_time = datetime.datetime.fromtimestamp(ts).strftime('%y-%m-%d %H_%M_%S')
HourMinuteSecond = datetime.datetime.fromtimestamp(ts).strftime('%H_%M_%S')
month = datetime.datetime.now().strftime('%m-%y')

# Creates a writer for this month and year
writer = pd.ExcelWriter(
    'C:\\Users\\G\\Desktop\\KickstarterLinks(%s).xlsx' % (month), 
    engine='xlsxwriter')

# Creates dataframe from my data, d
df = pd.DataFrame(d)

# Writes to the excel file
df.to_excel(writer, sheet_name='%s' % (HourMinuteSecond))
writer.save()

Update:

此功能已添加到熊猫0.24.0 http://pandas.pydata.org/pandas-docs/version/0.24/whatsnew/v0.24.0.html#other-enhancements:

ExcelWriter 现在接受mode作为关键字参数，在使用 openpyxl 引擎时启用附加到现有工作簿（GH3441 https://github.com/pandas-dev/pandas/issues/3441)

以前的版本：

熊猫有一个打开功能请求 https://github.com/pandas-dev/pandas/issues/3441为此。

同时，这里有一个函数添加了一个pandas.DataFrame到现有工作簿：

Code:

def add_frame_to_workbook(filename, tabname, dataframe, timestamp):
    """
    Save a dataframe to a workbook tab with the filename and tabname
    coded to timestamp

    :param filename: filename to create, can use strptime formatting
    :param tabname: tabname to create, can use strptime formatting
    :param dataframe: dataframe to save to workbook
    :param timestamp: timestamp associated with dataframe
    :return: None
    """
    filename = timestamp.strftime(filename)
    sheet_name = timestamp.strftime(tabname)

    # create a writer for this month and year
    writer = pd.ExcelWriter(filename, engine='openpyxl')
    
    try:
        # try to open an existing workbook
        writer.book = load_workbook(filename)
        
        # copy existing sheets
        writer.sheets = dict(
            (ws.title, ws) for ws in writer.book.worksheets)
    except IOError:
        # file does not exist yet, we will create it
        pass

    # write out the new sheet
    dataframe.to_excel(writer, sheet_name=sheet_name)
    
    # save the workbook
    writer.save()

测试代码：

import datetime as dt
import pandas as pd
from openpyxl import load_workbook

data = [x.strip().split() for x in """
                   Date  Close
    2016-10-18T13:44:59  2128.00
    2016-10-18T13:59:59  2128.75
""".split('\n')[1:-1]]
df = pd.DataFrame(data=data[1:], columns=data[0])

name_template = './sample-%m-%y.xlsx'
tab_template = '%d_%H_%M'
now = dt.datetime.now()
in_an_hour = now + dt.timedelta(hours=1)
add_frame_to_workbook(name_template, tab_template, df, now)
add_frame_to_workbook(name_template, tab_template, df, in_an_hour)

(Source https://github.com/pandas-dev/pandas/issues/3441#issuecomment-165611726)