Excel Import Service

The Excel Import Service in the Platon platform allows dynamic importing of Excel files, processing data, and saving it to the database.
It reads the uploaded Excel file, sends each row to configured handlers, transforms the data, and persists it.

---

Overview

The Excel Import Service provides the following capabilities:

• Importing and processing Excel files
• Executing configured handlers for each row (SQL or JSON based)
• Executing pre-import and post-import SQL scripts (beforeImportSql and afterImportSql)
• Applying user session context during the import
• Logging errors and processing steps to a central system (via NATS)
• Caching import configurations for faster access
• Managing transactions during the import process

---

Architecture Components

• Excel Import Engine: Sends each row to configured handlers and processes data
• DataBase Manager: Starts transactions, executes SQL statements, handles commit/rollback
• Cache Manager: Stores Excel import configurations and row handlers in cache
• Spel Component: Checks handler execution conditions
• Logging Service: Sends errors and process status to the central system (via NATS)
• ThreadPoolTaskScheduler: Periodically refreshes cache and import configurations
• Rest Session Context: Manages user session and request parameters

---

Import Process

1. User uploads an Excel file
2. The service retrieves the import configuration from cache or database
3. beforeImportSql is executed (if defined)
4. The Excel file is read row by row:
   • All columns for each row are read and cleaned
   • The appropriate handler is applied for each row (SQL or JSON)
   • Execution conditions (SpEL) are checked
5. Row results are aggregated into a return data structure
6. afterImportSql is executed (if defined)
7. Transaction is committed, or rolled back in case of errors
8. The result is returned to the user and the process is logged

---

Database Interactions

The Excel Import Service performs the following database operations:

• Retrieve Config: Fetch import configurations from cache or database
• Execute SQL: Run pre-import and post-import SQL scripts
• Row Handlers Execution: Process each row through handlers and execute database operations
• Transactional Execution: The entire import process runs in a transactional context

---

Cache and Performance

• Import configurations and row handlers are stored in cache
• Improves response time and reduces server load
• Cache is periodically refreshed based on configured intervals
• New or updated configurations are automatically added to the cache
• Expired or unnecessary configurations are removed from cache

---

Logging and Monitoring

• User requests and import process events are logged
• Errors and successful import operations are sent to a central system via NATS
• Execution status of each row and handler is monitored

---

Summary

The Excel Import Service in the Platon platform provides modular, configuration-based Excel import, processes user data, persists it in the database, and ensures fast access via caching.
It supports transactional management, handler execution, conditional execution (SpEL), pre- and post-import SQL scripts, and centralized logging of errors and operations.