用Python语言写入Excel文件 [英] Writing to Excel file in Python

问题描述

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文件中的登记患者选项卡中。我想在每次运行代码时添加标题和8个id。我的问题是使用df_to_EXCEL与startrow和startcol.一起运行它。

推荐答案

您可以尝试如下重构代码：

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

这篇关于用Python语言写入Excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！