How can I export all data to an Excel file using openpyxl?

Question

I am trying to write all the data inputted by my user to an Excel file, with each new entry being entered underneath the most recent one.

It should do this in my GUI once I click submit.

I managed to get the headings to show.

I am using the openpyxl library, I googled and this was the first to come up.

My GUI for reference:

Here is the section of my code:

filepath = "C:\\xxxx\\xxxx\\xxxx\\xxxx\\xxxx\\Collecting Data.xlsx"
            
if not os.path.exists(filepath):
    xlsx_open = openpyxl.Xlsx_openxlsx_open()
    sheet = xlsx_open.active
    table_headings = ["Card Type", "Staff Number", "Staff Name", "Staff Title", "Department", "Safety Function", "Safety Standards", "Brief Description of the Incident"]
    sheet.append(table_headings)
    xlsx_open.save(filepath)
    xlsx_open = openpyxl.load_xlsx_open(filepath)
    sheet = xlsx_open.active
    sheet.append([staff_name_entry, staff_id_dropdown, staff_title_entry, department_entry, 
                  card_type_dropdown, safety_function_dropdown, safety_standard_dropdown, manualy_text_entry])
    xlsx_open.save(filepath)

window.mainloop()

This is the error I get.

Error: submit_form sheet.append([CardTypeDropdown, StaffIdDropdown, StaffNameEntry, StaffTitleEntry, DepartmentEntry, ^^^^^^^^^^^^ AttributeError: 'NoneType' object has no attribute 'append'

Please provide the detailed error so others can better understand the problem. — shamnad sherief, Feb 13 '23 at 17:38
@shamnadsherief this is the error I get, its something to do with the .append() function Error: submit_form sheet.append([CardTypeDropdown, StaffIdDropdown, StaffNameEntry, StaffTitleEntry, DepartmentEntry, ^^^^^^^^^^^^ AttributeError: 'NoneType' object has no attribute 'append' — The Clueless Engineer, Feb 13 '23 at 18:08

The Clueless Engineer · Accepted Answer · 2023-02-14T05:14:23.470

Alright so I ended up figuring it out myself through the following changes:

I made a new variable called "entries" containing a list of the inputs so that the append function would recognize it as a function.
I changed the double '\' to a single '/' in the 'filepath' variable to resolve the 'NoneType' error.

I'm not sure if a combination of these 2 solutions fixed it or it was a single solution that fixed it, but nonetheless it's done the job. I'll leave it to the people who are more experienced with python to answer that :)

P.S: I'm pretty sure the code can look a lot better and simpler, but again, I'm a python newbee... at least it works :D New and Working Code:

#Libraries that were used
import tkinter, os, openpyxl
from openpyxl import *
from tkinter import *
from tkinter import ttk
from tkinter import messagebox

def submit_form():# submit form

    StaffIdDropdown = staff_id_dropdown.get()
    StaffNameEntry = staff_name_entry.get()
    StaffTitleEntry = staff_title_entry.get()
    DepartmentEntry = department_entry.get()
    CardTypeDropdown = card_type_dropdown.get()
    SafetyFunctionDropdown = safety_function_dropdown.get()
    SafetyStandardDropdown = safety_standard_dropdown.get()
    ManualTextEntry = manualy_text_entry.get("1.0",'end-1c')   
    entries = [CardTypeDropdown, StaffIdDropdown, StaffNameEntry, StaffTitleEntry, DepartmentEntry, SafetyFunctionDropdown, SafetyStandardDropdown, ManualTextEntry]
    
    print("The form has been submitted successfully with the following contents:\n", StaffNameEntry.upper(), " with the staff ID of ", StaffIdDropdown.upper(), 
        " and a title of ", StaffTitleEntry.upper(), " belonging to the ", DepartmentEntry.upper(), " Department.")
    print("-------------------------------------------------------------------------------------------------------")

    main_filepath = "C:/Users/JadJackHanna/OneDrive - Terminals Holding/Terminals Holding LLC/Application Python/ApplicationData.xlsx"

    if not os.path.exists(main_filepath):
        workbook = openpyxl.Workbook()
        sheet = workbook.active
        form_headings = ["Card Type", "Staff ID", "Staff Name", "Staff Title", "Department",
                            "Safety Function", "Safety Standard", "Brief Description of the Incident"]
        sheet.append(form_headings)
        sheet.append(entries)
        workbook.save(main_filepath)
    else:
        workbook = openpyxl.load_workbook(main_filepath)
        sheet = workbook.active
        sheet.append(entries)
        workbook.save(main_filepath)

    staff_id_dropdown.set('')
    staff_name_entry.delete(0, 'end')
    staff_title_entry.delete(0, 'end')
    department_entry.delete(0, 'end')
    card_type_dropdown.set('')
    safety_function_dropdown.set('')
    safety_standard_dropdown.set('')
    manualy_text_entry.delete('1.0', tkinter.END)
    
    # Confirmation Box
    messagebox.showinfo(title="Success!", message="The form as been submitted and saved!")

How can I export all data to an Excel file using openpyxl?

1 Answers1