Feature: Excel Data Upload UI

Relevant source files

The following files were used as context for generating this wiki page:

• finder/views.py
• finder/templates/finder/upload_excel.html
• finder/forms.py
• finder/static/finder/js/scripts.js
• finder/static/finder/css/styles.css
• generate_sample_data.py

The Excel Data Upload feature provides a user interface for staff members to populate and update the application's database with room and class schedule information. This is accomplished by uploading two specifically formatted .xlsx files. The system is designed to completely replace existing data with the contents of the uploaded files, making it a bulk data management tool. Access to this feature is restricted to staff members.

Sources: finder/views.py:112, finder/views.py:141, finder/views.py:173

System Architecture and Flow

The data upload process involves a combination of frontend user interaction, Django form validation, and backend data processing using the pandas library.

High-Level Data Flow

The following diagram illustrates the complete process from file upload to database update.

This diagram shows the user initiating an upload, which is handled by the Django backend. The upload_excel view uses ExcelUploadForm for validation. If valid, it uses pandas to parse the files, clears the relevant database tables, and inserts the new data. Feedback is provided to the user via Django messages.

Sources: finder/views.py:112-259, finder/forms.py:59-93, finder/static/finder/js/scripts.js:158-185

Frontend Components

The user interface for the upload feature is defined in upload_excel.html and enhanced with JavaScript for a better user experience.

Sources: finder/templates/finder/upload_excel.html, finder/static/finder/js/scripts.js, finder/static/finder/css/styles.css

UI Layout and Styling

The upload page (/upload/) presents a clean, card-based interface for uploading rooms.xlsx and timetable.xlsx files.

• Drag-and-Drop Zones: Two distinct drop zones are provided, one for each file type. These zones are styled with dashed borders that change on hover or when a file is dragged over.
• Visual Feedback: When a file is selected or dropped, its name is displayed, and the drop zone's appearance changes to indicate a successful selection.
• Messages: Success, warning, and error messages from the backend are displayed in styled alert boxes above the form.
• Submission Button: An "Upload Files" button initiates the process. JavaScript disables this button and shows a spinner during upload to prevent duplicate submissions.

<!-- finder/templates/finder/upload_excel.html:43-52 -->
<div class="upload-drop-zone" id="rooms-drop-zone">
    <input type="file" name="rooms_file" id="id_rooms_file" accept=".xlsx,.xls" hidden>
    <label for="id_rooms_file" class="drop-zone-label">
        <i class="fas fa-file-excel upload-icon"></i>
        <p>Drag & drop your <strong>Rooms Excel File</strong> here, or click to browse.</p>
        <span class="file-name" id="rooms-file-name">No file selected</span>
    </label>
</div>

Sources: finder/templates/finder/upload_excel.html:43-62, finder/static/finder/css/styles.css:368-428, finder/static/finder/js/scripts.js:158-185

Client-Side Interaction

JavaScript in scripts.js enhances the upload form's usability:

• Drag-and-Drop Handling: Event listeners for dragover, dragleave, and drop are attached to the drop zones to manage visual states and handle file input.
• File Selection: The script updates the UI to show the selected file's name, whether it was dropped or selected via the file browser.
• Form Submission: On submission, the script checks if at least one file has been provided. If not, it shows an alert. It also provides feedback by disabling the submit button and changing its text to "Uploading...".

Sources: finder/static/finder/js/scripts.js:127-185

Backend Processing

The core logic resides in the upload_excel view and the ExcelUploadForm form.

Sources: finder/views.py:112-259, finder/forms.py:59-93

Form Validation

The ExcelUploadForm is responsible for initial validation before any processing occurs.

This diagram shows the ExcelUploadForm class and its validation methods.

Field / Method	Type	Description	Source File
rooms_file	FileField	Optional field for the rooms Excel file.	finder/forms.py:60
timetable_file	FileField	Optional field for the timetable Excel file.	finder/forms.py:68
clean_rooms_file()	Method	Validates that the rooms file is an Excel file (.xlsx, .xls) and does not exceed 5MB.	finder/forms.py:76
clean_timetable_file()	Method	Validates that the timetable file is an Excel file (.xlsx, .xls) and does not exceed 10MB.	finder/forms.py:84
clean()	Method	Ensures that at least one of the two files has been uploaded.	finder/forms.py:92

View Logic (upload_excel)

This view, restricted to staff members, orchestrates the entire file processing workflow.

1. Authentication: The @staff_member_required decorator ensures only authenticated staff can access the view.
2. Form Handling: It instantiates ExcelUploadForm with POST data and files.
3. Data Clearing: Upon successful validation, it deletes all existing Room and ClassSchedule records from the database. This makes the upload a "replace" operation.
4. File Parsing: It uses pandas.read_excel() to read the uploaded file(s) from an in-memory buffer (BytesIO).
5. Column Validation: It checks if the expected columns exist in the DataFrame. If not, an error message is generated.
6. Row Iteration: It iterates through each row of the DataFrame, cleans the data, and creates new model instances.
7. Error Handling: The entire process is wrapped in a try...except block to catch and report any errors during file processing. Warnings are issued for skippable issues like invalid time formats or non-existent rooms.
8. Redirection: After successfully processing the timetable file, the user is redirected to the main search page.

# finder/views.py:112-115
@staff_member_required
def upload_excel(request):
    if request.method == "POST":
        form = ExcelUploadForm(request.POST, request.FILES)
        if form.is_valid():
            # ... processing logic ...

Sources: finder/views.py:112-259

Expected Data Formats

The system expects two specific Excel files. The structure for these files is implicitly defined by the data generation script and the parsing logic in the upload_excel view.

Sources: generate_sample_data.py, finder/views.py:127-248

rooms.xlsx

This file defines the rooms available on campus.

Column	Type	Description	Example
Room	String	The unique name or code for the room.	HH101
Building	String	The name of the building where the room is located.	Hephaestus Hall
capacity	Integer	The seating capacity of the room. (Not used in upload_excel view)	50
amenities	JSON String	A JSON array of strings listing room amenities. (Not used in upload_excel view)	["Projector", "Whiteboard"]
photo_url	URL String	A URL to a photo of the room. (Not used in upload_excel view)	https://images.unsplash.com/...

Note: The upload_excel view in finder/views.py only processes the Room and Building columns. The other columns are defined in generate_sample_data.py but are not currently imported by the upload view.

Sources: finder/views.py:129-131, generate_sample_data.py:38

timetable.xlsx

This file defines the class schedules for the rooms.

Column	Type	Description	Example
Course	String	The course code.	CS101
Subject	String	The full name of the subject.	Intro to Programming
Day/Time	String	A formatted string for the day and time slot. Must match the regex `(\w+)\s+(\d{1,2}:\d{2}\s*(?:AM	PM)?)[-–](https://github.com/Allen739/roomfinder/blob/HEAD/%5Cd%7B1%2C2%7D%3A%5Cd%7B2%7D%5Cs*(?:AM
Room	String	The name of the room, matching a Room from rooms.xlsx.	HH101
Building	String	The name of the building, matching a Building from rooms.xlsx.	Hephaestus Hall
Lecturer	String	The name of the lecturer.	Dr. Smith

The Day/Time column is parsed using a regular expression to extract the day, start time, and end time. The day is then mapped from full names (e.g., "Monday") to three-letter abbreviations (e.g., "Mon").

Sources: finder/views.py:163-171, 182-190, generate_sample_data.py:58