python - 如何使用 Python 添加新行并将其填充到现有 excel 文件中

Question

我需要创建一个 Excel 文件，其中包含不同函数的输出，例如日期、重复变量的数量...我设法创建一个包含第一个函数的输出的 Excel 文件，但是当我尝试将其 append 到第二个函数的输出时，它不起作用。我不知道如何将不同函数的输出添加到同一个 Excel 文件中。

示例:功能1:

import xlsxwriter 
import openpyxl
import pandas as pd

def NGTP():

    VIN_FFF = 0
    N_Alert =0


    for x in flat_list:
        if "NGTPSM HTTP request" in str(x):
           VIN_FFF+=1
           N_Alert =5
    workbook = xlsxwriter.Workbook('Results.xlsx') 
    worksheet = workbook.add_worksheet("sheet") 
    parametres = ( 
        ['VIN_FFF ', VIN_FFF],
        ['Nombre d alertes ', N_Alert],    
    ) 

    # Start from the first cell. Rows and 
    # columns are zero indexed. 
    row = 0
    col = 0

    # Iterate over the data and write it out row by row. 
    for name, parametres in (parametres): 
        worksheet.write(row, col, name) 
        worksheet.write(row, col + 1, parametres) 
        row += 1

    workbook.close() 

函数2:

import xlsxwriter 
from openpyxl import load_workbook
from openpyxl import Workbook
import pandas as pd

filename = "Results.xlsx"
def Date2Roulage():
    file_name='OutputDLT.xlsx'
    df = pd.read_excel(file_name ,header=0, index= False)
    d= df.iloc[0,2]
    #d='2015-01-08 22:44:09' 
    date=pd.to_datetime(d).date()
    date=list(date)
    print('Date de roulage: ', date)
    workbook = xlsxwriter.Workbook('Results.xlsx') 
    worksheet = workbook.add_worksheet("sheet") 
    parametres = ( 
        ['Date de roulage ', date],

    ) 

    # Start from the first cell. Rows and 
    # columns are zero indexed. 
    row = 2
    col = 0

    # Iterate over the data and write it out row by row. 
    for name, parametres in (parametres): 
        worksheet.write(row, col, name) 
        worksheet.write(row, col + 1, parametres) 
        row += 1
    for row_ in parametres:
        worksheet.append(row_)

    workbook.save(filename)
    workbook.close()

它不会产生错误，但我猜它不起作用，因为我试图同时创建同一文件两次。

这是我的主要代码:

from Date_de_roulage_Fct  import *
from Navco_Fct  import *
Date2Roulage()
Navco()

我已经根据建议的解决方案更新了第二个函数，但出现错误:

TypeError: 'datetime.date' object is not iterable

我尝试了这个解决方案date=list(date)但它不起作用

必须创建生成的 Excel 文件，如下图所示:

Answer 1

您无法使用 xlsxwriter 将新行追加到现有文件中。您可以做的是使用 native 具有此选项的 openpyxl (您已导入的):

from openpyxl import Workbook
from openpyxl import load_workbook

filename = "Results.xlsx"
new_row = ['a', 'b', 'c']

# Confirm file exists. 
# If not, create it, add headers, then append new data
try:
    wb = load_workbook(filename)
    ws = wb.worksheets[0]  # select first worksheet
except FileNotFoundError:
    # excel header if the file does not exists
    headers_row = ['Header 1', 'Header 2', 'Header 3']
    wb = Workbook()
    ws = wb.active
    ws.append(headers_row)

ws.append(new_row)
wb.save(filename)

如果您更喜欢使用xlsxwriter，您可以读取前面的所有行，并用新数据重写它们。

对于您的输入，您需要如下:

for row_ in parametres: 
        ws.append(row_)
wb.save(filename)