时间戳

首页 > 解决方案 > 在 Python 中写入 Excel 文件

python - 在 Python 中写入 Excel 文件

问题描述

import pandas as pd
import openpyxl

filename="Tests.xlsx"

def createWorkBook():
    wrkbk = openpyxl.Workbook()
    ws = wrkbk.active
    Sheets=["Rostered Patient","Non-Rostered Patient","Email","Error Handling"]
    for sheet in Sheets:
        if not sheet in wrkbk.sheetnames:
            print("Created sheet: "+ sheet)
            wrkbk.create_sheet(sheet)
    wrkbk.save(filename)

def main():
    Columns=[[""],["ID","Testing Procedure:","Pass/Fail","Issue#","Date"],[""],[""],[""]]

    createWorkBook()
xl_writer = pd.ExcelWriter(filename, engine='openpyxl')
    df = pd.DataFrame([["1","2","3","4","5",'6','7','8']],columns = Columns[1])
    df.to_excel(xl_writer, 'Rostered Patient', index=False, startcol=1, startrow=1)

if __name__ == "__main__":
    main()

我只是想将一个数据框附加到一个 Excel 文件到 Rostered Patient 选项卡。每次运行代码时，我都想附加标题和 8 个 ID。我的问题是使用 df_to_excel 与 startrow 和 startcol 一起运行它。

在此处输入图像描述

标签： pythonexcelpandasopenpyxl

解决方案

您可以尝试像这样重构您的代码：

from pathlib import Path

import openpyxl
import pandas as pd

filename="Tests.xlsx"

...

def main():
    Columns = [
        [""],
        ["ID", "Testing Procedure:", "Pass/Fail", "Issue#", "Date"],
        [""],
        [""],
        [""],
    ]
    # Check if the WorkBook exists and get its length
    if not Path(filename):
        createWorkBook()
        previous_df_length = 0
    else:
        previous_df = pd.read_excel(io=filename, sheet_name="Rostered Patient")
        previous_df_length = previous_df.shape[0] + 2

    # As recommended in pandas documentation, use ExcelWriter in a context manager
    with pd.ExcelWriter(filename, engine="openpyxl", mode="a") as xl_writer:
        df = pd.DataFrame(
            [["1", "2", "3", "4", "5", "6", "7", "8"]], columns=Columns[1]
        )
        df.to_excel(
            xl_writer,
            "Rostered Patient",
            index=False,
            startrow=previous_df_length,
            startcol=1,
        )

推荐阅读