How can I add a new row and fill it to an exsiting excel file using Python

Question

I need to create an excel file which contains the output of different functions, such as dates, the number of duplicated variables... I managed to create an excel file that contains the output of the first function but when I tried to append it with the output of the 2nd funcion, it did not work. I don't know how to add the outputs of my different functions to the same excel file.

Example: function1:

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

function2:

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

It does not produce an error, but I guess it's not working because i'm trying to create the same file twice simultaneously.

this is my main code:

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

I've updated the second function, according to the suggested solution, but I got an error:

TypeError: 'datetime.date' object is not iterable

I trie this solution date=list(date) and it did not work

The generated excel file must be vreated as shown is the image below:

Nikaido · Accepted Answer · 2019-09-12T16:39:17.473

1

You cannot append new rows to an exsisting file with xlsxwriter. What you can do is use openpyxl (the which you have imported) that natively has this option:

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)

If you prefer to use xlsxwriter you could read all the previous rows, and rewrite them all with the new data.

For your input, you need to as follow:

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

edited Sep 12 '19 at 16:39

answered Sep 12 '19 at 16:19

Nikaido

3,671
5
29
40

you need to unpack them as lists – Nikaido Sep 12 '19 at 16:33
@A.khou check the last part of my answer. There is an update – Nikaido Sep 12 '19 at 16:40
I've updated my function but a Type error appeared, as shown in my updated post – newbie Sep 13 '19 at 08:05
1

you need to delete this for statement -> `for name, parametres in (parametres)`, and leave only the one I suggested you – Nikaido Sep 13 '19 at 08:50
and leave the d value as string, without doing transformation – Nikaido Sep 13 '19 at 08:53
I got this error AttributeError: `'Worksheet' object has no attribute 'append' ` . I don't know why it appeared – newbie Sep 13 '19 at 09:08
1

it's because you are using mixed classes. You don't need anymore to use xlswriter lib – Nikaido Sep 13 '19 at 09:09
Hum.. Should I delete the line `import xlsxwriter` from my second function? – newbie Sep 13 '19 at 09:13
no, all the code that use that lib. You need to change the functions that use xlsxwriter with openpyxl – Nikaido Sep 13 '19 at 09:33
this is how I updated my code `print('Date de roulage: ', date) wb = load_workbook(filename) ws = wb.worksheets[0] parametres = ( ['Date de roulage ', date], ) for row_ in parametres: ws.append(row_ ) wb.save(filename)` No error, but it didn't work – newbie Sep 13 '19 at 10:01

How can I add a new row and fill it to an exsiting excel file using Python

1 Answers1