Data Import: Excel Room List

Relevant source files

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

• finder/management/commands/load_rooms_excel.py
• finder/views.py
• finder/forms.py
• finder/models.py
• finder/templates/finder/upload_excel.html
• generate_sample_data.py
• README.md

The Campuspal Room Finder application relies on initial data being populated into its database to function. This is achieved by importing data from structured Excel (.xlsx) files. The system supports importing two types of data: a list of all available rooms and their properties, and a comprehensive timetable of all class schedules.

There are two primary mechanisms for performing this data import:

1. A command-line interface via a Django management command, suitable for developers during setup or for automated scripts.
2. A web-based upload interface, accessible to staff members through the application's front end.

This document details the data structures, file formats, and processes involved in both import methods.

Data Models

The imported Excel data populates two core Django models: Room and ClassSchedule. These models form the foundation of the application's search and display logic.

Sources: finder/models.py

Entity Relationship Diagram

The following diagram illustrates the relationship between the Room and ClassSchedule models. A single Room can have many ClassSchedule entries associated with it.

Room Model

The Room model stores details about each physical room.

Field	Type	Description	Constraints
name	CharField(100)	The unique identifier for the room (e.g., "A101").	Part of unique_room_building
building	CharField(100)	The building where the room is located (e.g., "Hephaestus Hall").	Part of unique_room_building
capacity	PositiveIntegerField	The maximum seating capacity of the room.	default=0
amenities	JSONField	A JSON list of amenities (e.g., ["Projector", "Whiteboard"]).	default=list
photo_url	URLField	A URL to an image of the room.	blank=True, null=True

Sources: finder/models.py:7-13

ClassSchedule Model

The ClassSchedule model stores the booking information for each class, linking it to a specific Room.

Field	Type	Description	Constraints
course	CharField(50)	The course code (e.g., "CS101").
subject	CharField(100)	The full subject name (e.g., "Intro to Programming").
day	CharField(3)	The day of the week, chosen from a predefined list ("Mon", "Tue", etc.).	choices=DAY_CHOICES
start_time	TimeField	The start time of the class.
end_time	TimeField	The end time of the class.
room	ForeignKey to Room	The room where the class is held.	on_delete=models.CASCADE
lecturer	CharField(100)	The name of the lecturer.
is_cancelled	BooleanField	A flag to mark if the class is cancelled.	default=False

Sources: finder/models.py:28-49

Excel File Structure

The import system expects two specifically formatted Excel files: rooms.xlsx and timetable.xlsx.

rooms.xlsx

This file contains the master list of all rooms. The management command expects this file to be located at sample_data/rooms.xlsx.

Column	Data Type	Example	Description
Room	Text	HH101	The unique name/number of the room.
Building	Text	Hephaestus Hall	The full name of the building.
capacity	Number	30	The integer seating capacity.
amenities	Text (JSON)	["Projector", "Whiteboard"]	A JSON-formatted string representing a list of amenities.
photo_url	Text (URL)	https://images.unsplash.com/...	A valid URL to a photo of the room.

Sources: finder/management/commands/load_rooms_excel.py:17-19, generate_sample_data.py:40-42

timetable.xlsx

This file contains the schedule for all classes.

Column	Data Type	Example	Description
Course	Text	CS101	The course identifier.
Subject	Text	Intro to Programming	The full name of the course.
Day/Time	Text	Mon 09:00-10:30	A formatted string with day, start time, and end time.
Room	Text	HH101	The name of the room, must match a Room in rooms.xlsx.
Building	Text	Hephaestus Hall	The building name, must match a Building in rooms.xlsx.
Lecturer	Text	Dr. Smith	The name of the instructor.

Sources: finder/views.py:160-166, generate_sample_data.py:65-67

Import Mechanisms

1. Management Command (load_rooms_excel)

This method is designed for developers to quickly populate the Room model from the command line. It does not handle timetable data.

Sources: finder/management/commands/load_rooms_excel.py

Process Flow

The command executes the following steps to load room data:

Key Logic

• Data Deletion: Before importing, the command first deletes all existing records in the Room table to ensure a clean slate. Room.objects.all().delete()
• File Path: It specifically looks for the Excel file at sample_data/rooms.xlsx.
• Column Validation: It checks for the presence of Room, Building, capacity, amenities, and photo_url columns.
• Data Insertion: It uses Room.objects.update_or_create() to add or update rooms, preventing duplicates based on the unique combination of name and building.
• JSON Parsing: The amenities column is expected to be a string, which is parsed into a JSON object before being saved.

Sources: finder/management/commands/load_rooms_excel.py:10, 13, 17, 31, 40

2. Web-Based Upload

A user-friendly interface at the /upload/ URL allows authenticated staff members to upload both rooms.xlsx and timetable.xlsx files.

Sources: finder/views.py:126-253, finder/forms.py:64-106, finder/templates/finder/upload_excel.html

Sequence Diagram

This diagram shows the interaction between the user, browser, and server during the file upload process.

Components

• View (upload_excel): A function-based view in finder/views.py protected by the @staff_member_required decorator. It handles both GET requests (to display the form) and POST requests (to process files).
  Sources: finder/views.py:126-127
• Form (ExcelUploadForm): A Django form in finder/forms.py that defines the file input fields. It includes validation logic to check for file extensions (.xlsx, .xls), file size (5MB for rooms, 10MB for timetable), and ensures at least one file is submitted.
  Sources: finder/forms.py:64-106
• Template (upload_excel.html): An HTML template that renders the upload form. It uses JavaScript from finder/js/scripts.js to provide a drag-and-drop interface for a better user experience. It also displays success or error messages passed from the view.
  Sources: finder/templates/finder/upload_excel.html, finder/static/finder/js/scripts.js:123-171
• Processing Logic: Similar to the management command, the view logic first clears the respective database tables (Room or ClassSchedule) before inserting new data. It uses the pandas library to read the Excel files and iterates through the rows to create model instances. A regular expression is used to parse the complex Day/Time column from the timetable file.
  Sources: finder/views.py:145, 172, 182-187

Summary

The data import system is a critical component for populating the Campuspal application. By offering both a developer-focused command-line tool and a user-friendly web interface, it provides flexible ways to manage room and schedule data. The system relies on strictly formatted Excel files that map directly to the Room and ClassSchedule database models, ensuring data integrity and enabling the application's core room-finding capabilities.

Sources: README.md:20-22, finder/management/commands/load_rooms_excel.py, finder/views.py:126-253