Converting a Spreadsheet into a Web Application using Oracle APEX

Introduction

This blog demonstrates how I converted a spreadsheet containing school data into a fully functional web application using Oracle APEX 24.2. The application includes a dashboard, interactive reports, faceted search, and a map page with search functionality.

Spreadsheet Data

The spreadsheet contained the following columns:

  • Borough

  • School Name

  • Neighborhood

  • Interest

  • Method

  • Total Students

  • Graduation Rate

  • Attendance Rate

  • College Career Rate

  • Latitude

  • Longitude

All data is uploaded directly using the Import Data option in Oracle APEX 24.2, which automatically reads and maps the spreadsheet columns.

Step 1: Import Spreadsheet Using APEX 24.2

Navigation:

  • App Builder → Utilities → Import Data

  • Select the spreadsheet file

  • Map columns automatically detected by APEX

  • APEX creates an internal table to store the data for application use

This approach eliminates manual table creation and allows immediate use of spreadsheet data.

Step 2: Create Application with Dashboard

Using Create Application Wizard:

  • App Builder → Create → New Application → Use Data from Imported Spreadsheet

Application components generated:

  • Dashboard page summarizing metrics like Total Students, Graduation Rate, Attendance Rate, and College Career Rate

  • Faceted search page to filter by Borough, Neighborhood, Interest, and Method

  • Interactive Report page to view detailed data

  • Map page displaying school locations based on Latitude and Longitude


Step 3: Interactive Report Configuration

Interactive Reports allow users to:

  • Filter, sort, and search data

  • Export to CSV or PDF



Step 4: Faceted Search Page

Faceted search improves data exploration:

  • Facets: Borough, Neighborhood, Interest, Method

  • Automatic filtering updates report and map dynamically


Step 5: Map Page with Faceted Search

The Map page displays school locations using Latitude and Longitude.

Configuration:

  • Page Designer → Create Region → Map

  • Source Type: SQL Query on imported spreadsheet data

Example SQL query:

select ID,
       BOROUGH,
       SCHOOL_NAME,
       NEIGHBORHOOD,
       INTEREST,
       METHOD,
       TOTAL_STUDENTS,
       GRADUATION_RATE,
       ATTENDANCE_RATE,
       COLLEGE_CAREER_RATE,
       SAFE,
       SEATS,
       APPLICANTS,
       DBN,
       LATITUDE,
       LONGITUDE,
       LANGUAGE_CLASSES,
       ADVANCED_PLACEMENT_COURSES,
       SCHOOL_SPORTS,
       CASE WHEN GRADUATION_RATE BETWEEN 0 and 50 then 'red'
       WHEN GRADUATION_RATE BETWEEN 51 and 75 then 'yellow'
       WHEN GRADUATION_RATE BETWEEN 76 and 100 then 'green' END as PIN_COLOR
  from NYC_HIGHSCHOOLS

Map attributes:

  • Latitude Column: latitude

  • Longitude Column: longitude

  • Primary Key: "School Name"

Faceted search filters are linked to the map so that markers update dynamically.


Step 6: Dashboard Enhancements

The Dashboard provides visual summaries:

  • Total Students by Borough

  • Average Graduation Rate, Attendance Rate, College Career Rate

  • Interactive charts connected to faceted search


Challenges Faced

  1. Null latitude or longitude values

    • Filtered data to avoid map errors

  2. Map and faceted search synchronization

    • Configured dynamic actions to update map markers with filter changes

  3. Dashboard formatting

    • Adjusted chart settings and labels for clarity

Outcome

Using Oracle APEX 24.2 and the Import Data option, the spreadsheet was converted into a web application featuring:

  • Interactive Report with filtering and sorting

  • Faceted Search for efficient exploration

  • Map page with school location markers and dynamic filters

  • Dashboard summarizing key metrics

The low-code environment enabled rapid development with minimal manual effort.

Conclusion

Oracle APEX 24.2 allows direct spreadsheet import and automatic application generation. With dashboards, interactive reports, faceted search, and maps, it provides a complete low-code solution for transforming spreadsheet data into web applications for reporting, analysis, and visualization.

Comments

Post a Comment

Popular posts from this blog

Barcode and QR Code Generation in Oracle APEX

Image
  Introduction In modern applications, Barcodes and QR Codes are widely used to identify products, track inventory, enable quick access to information, and simplify scanning operations. Oracle APEX does not provide built-in barcode or QR generation components, but with the help of JavaScript libraries and Interactive Reports , we can implement this feature efficiently. In this blog, I explain how I implemented Barcode Generator and QR Code Generator in Oracle APEX 24.2 , how data is stored and fetched from database tables, and how barcodes and QR codes are rendered dynamically in reports. Why Barcode and QR Code Are Needed Unique identification of products Faster scanning compared to manual entry Reduced human errors Easy integration with scanners and mobile devices Useful for inventory, billing, logistics, and asset tracking   Flow Create a Form Page to enter product details Save product data into database tables Display data using Interactive...
Read more

Interactive Grid Customization in Oracle APEX

Image
Tweaking the Toolbar for Better Control and Usability Introduction: Oracle APEX Interactive Grid provides a rich, feature-packed interface for working with tabular data. While the default toolbar offers many capabilities, not all of them are required in every application. In real-world projects, simplifying the toolbar and adding targeted custom actions improves usability and keeps the interface aligned with business requirements. This blog explains two Interactive Grid toolbar customizations implemented in Oracle APEX 24.2. The first customization removes the Finder Drop Down from the toolbar. The second customization adds a custom button to the Interactive Grid search bar and links it to a JavaScript action. All changes were implemented using standard Oracle APEX features without plugins. Topic 1: Tweak the Toolbar – Remove “Finder Drop Down” The Finder Drop Down in the Interactive Grid toolbar allows users to search, filter, and apply conditions on the grid data. In controlled ...
Read more

Hybrid Wizard Application with UI Enhancements in Oracle APEX

Image
  Introduction: Customer onboarding is one of the most critical workflows in any enterprise application. A long or complex form often leads to poor user experience, incomplete submissions, and data inconsistency. To address this, I designed and implemented a Hybrid Wizard–based Customer Onboarding Application using Oracle APEX , combining guided wizard navigation with flexible UI components such as popups, dynamic actions, and reusable lists. The goal of this application was to provide a smooth, step-by-step onboarding experience while maintaining visual clarity and ease of interaction for end users. Why a Hybrid Wizard Approach Traditional wizard pages strictly follow a linear, multi-page flow, while modern UI patterns demand flexibility such as popups, dynamic menus, and contextual forms. Instead of using a fully rigid wizard, I implemented a hybrid wizard model where structured steps guide the user, but individual steps can leverage custom pages, popup dialogs, and dynami...
Read more