Una delle esigenze richieste più comuni vengono fatte a chi sviluppa applicazioni di natura gestionale è la possibilità di poter importare i dati da un file Excel.

Da questo punto di vista, l’avvento della release 19.1 di Oracle APEX è stata accolta con un certo entusiasmo perché è stata introdotta la possibilità di usare la libreria PL/SQL APEX_DATA_PARSER che è in grado di leggere i dati da file in diversi formati: CSV, ovviamente, ma anche XLSX, XML e JSON.

Insomma, una manna dal cielo per tutti quegli sviluppatori che devo quotidianamente implementare applicazioni di che prevedono operazioni di data load.

In questo tutorial ti spiegherò come sviluppare una applicazione in Oracle APEX che è in grado di leggere un file Excel.

Come usare la libreria APEX_DATA_PARSER

Nei prossimo paragrafi ti spiegherò come creare una pagina APEX in grado di leggere un file Excel e mostrare il suo contenuto in un Interactive Report.

Upload Excel Page

• Dall’ App Builder e clicca sulla tua applicazione APEX.
• Clicca su Create Page

• Seleziona Blank Page

• Dai un nome alla pagina e clicca Next

• Crea la voce di menù e clicca Next

File Upload Item

• Apri la definizione della pagina Upload Excel Page
• Clicca su Content Body con il pulsante destro del mouse e seleziona Create Region.

• Impostare le proprietà di questa area
  • Nome: Upload File
  • Type: Static Content

• Aggiungere 3 Page Item a questa regione:
  • PX_FILE: necessario per fare l’upload del file
  • PX_FILE_NAME: visualizza in nome del file che è stato caricato
  • PX_UPLOAD_DATE: visualizza il time-stamp

Page Item PX_FILE

• Name: PX_FILE, dove X è il numero della pagina APEX
• Type: File Browse
• Label: Select File
• Storage Type: Table APEX_APPLICATION_TEMP_FILES
• File Types: .xlsx

Page Item PX_FILE_NAME

• Name: PX_FILE_NAME
• Type: Display Only
• Label: File Name

Page Item PX_UPLOAD_DATE

• Name: PX_FILE_UPLOAD_DATE
• Type: Display Only
• Label: Upload Date

Button Items

Creiamo i bottoni che l’utente dovrà utilizzare per leggere i dati dal file Excel.

• PARSE Button: serve a lanciare il parsing del file Excel
• CLEAR Button: resetta la pagina

Parse Button

• Button Name: PARSE
• Label: PARSE FILE
• Region: Upload File
• Button Position: Top of Region
• Hot Button: Yes
• Action: Submit Page

Clear Button

• Button Name: CLEAR
• Label: CLEAR
• Region: Upload File
• Button Position: Top of Region
• Hot Button: No
• Action: Submit Page

Page Processes

Dobbiamo creare due Page Processes che devono essere eseguiti rispettivamente quando si preme il bottone PARSE FILE e il bottone CLEAR.

Per creare un processo APEX, clicca sul tab Processes

APEX Process PROCESS_FILE

• Name: PROCESS_FILE
• Type: Execute Code
• Location: Local Database
• Language: PL/SQL

• PL/SQL Code: legge il file e memorizza intestazione del file excel
  • ricordati di sostituire i nomi dei page item (es p10_file_name) con quelli che tu hai creato nella tua applicazione.

declare
   l_file_blob  blob;
   l_file_name  varchar2(500);
   l_status     varchar2(50);
   l_message    varchar2(500);
begin
   l_file_name        := :p10_file;
   :p10_file_name     := null;
   :p10_upload_date   := null;
   if l_file_name is null then
      apex_error.add_error(p_message => 'No File selected', p_display_location => apex_error.c_inline_in_notification);
      return;
   end if;
    
    --retrieve blob content from apex_application_temp_files
   begin
      select blob_content
        into l_file_blob
        from apex_application_temp_files
       where name = l_file_name;
      l_status := 'S';
   exception
      when others then
         l_status := 'E';
   end;
   if l_status = 'S' then
      l_message                                   := 'File ' || l_file_name || ' uploaded successfully!';
      apex_application.g_print_success_message    := '<span style="color:white">' || l_message || '</span>';
      :p10_file_name                              := l_file_name;
      :p10_upload_date                            := to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS');
   end if;
   if l_status = 'E' then
      l_message := 'File NOT Uploaded Successfully! Contact System Administrator.';
      apex_error.add_error(p_message => l_message, p_display_location => apex_error.c_inline_in_notification);
   end if;
end;

• Server Side Condition
  • When Button Pressed: PARSE

APEX Process CLEAR_PAGE

• Name: CLEAR_PAGE
• Type: Execute Code
• Location: Local Database
• Language: PL/SQL

• PL/SQL Code: esegue il reset della pagina
  • ricordati di sostituire i nomi dei page item (es p10_file_name) con quelli che tu hai creato nella tua applicazione.

begin
   :p10_file          := null;
   :p10_file_name     := null;
   :p10_upload_date   := null;
   if apex_collection.collection_exists(p_collection_name => 'FILE_PARSER_COLLECTION') then
      apex_collection.delete_collection(p_collection_name => 'FILE_PARSER_COLLECTION');
   end if;
end;

• Server Side Condition
  • When Button Pressed: CLEAR

Come visualizzare i dati dell’Excel

In questo paragrafo ti spiegherò come visualizzare il contenuto dell’Excel.

Per comodità ci limiteremo a creare un Interactive Report che mostra le prime 7 colonne del nostro file.

Tuttavia, devi sapere che APEX_DATA_PARSER è in grado di leggere file Excel che hanno fino a 300 colonne.

• Aggiungi alla pagina una nuova regione di tipo Interactive Report
• Puoi chiamare questa regione Excel Content

• Configurazione dell’Interactive Report
  • Title: Excel Content
  • Type: Interactive Report
  • Type: SQL Query
  • Page Items to Submit: PX_FILE
  • SQL Query: in questo esempio ci limiteremo a mostrare le prima 7 colonne (col001…col007) tuttavia puoi aggiungerne fino a 300 (col008…col300)

select line_number, col001, col002, col003, col004,  col005, col006, col007
  from apex_application_temp_files f,
   table ( apex_data_parser.parse(p_content => f.blob_content, p_add_headers_row => 'Y', p_store_profile_to_collection => 'FILE_PARSER_COLLECTION', p_file_name => f.filename) )                  p
 where f.name = :p10_file_name

Come convertire i dati del file Excel

Nel paragrafo precedente abbiamo utilizzato la funzione apex_data_parser.parse restituisce un oggetto di tipo table che rappresenta il contenuto del file Excel.

Il parametro P_STORE_PROFILE_TO_COLLECTION permette di salvare la definizione dell’excel (numero di colonne e relativo data-type) una una APEX_COLLECTION.

Nell’esempio che ti sto spiegando questa collection si chiama FILE_PARSER_COLLECTION.

Puoi utilizzare questa collection per effettuare le validazioni delle colonne dell’Excel e per applicare le opportune conversioni.

Ad esempio, se il tuo Excel contiene una colonna con un campo formattato come Data, potrai recuperare il formato corretto e riuscirai, in questo modo, a convertire il valore nel campo DATE di Oracle.

Per vedere la definizione dell’Excel ho creato una regione di tipo Classic Report con la query seguente.

select column_position, column_name, data_type, format_mask
  from apex_collections c, 
       table( apex_data_parser.get_columns( p_profile => c.clob001 ) )
 where c.collection_name = 'FILE_PARSER_COLLECTION' 
   and c.seq_id = 1

Questo è il risultato: come vedi, APEX_DATA_PARSER è in grado di capire il tipo di dato della colonna Excel (DATE, VARCHAR2, ecc.)

Come salvare i dati nel database

Grazie a questo tutorial, dovresti aver acquisito una certa competenza su come funziona il pacchetto APEX_DATA_PARSER.

Come hai visto, ti da la possibilità leggere il contenuto di un file Excel in modo semplice ed immediato, usando una semplice query.

Inoltre, è in grado di recuperare le informazioni su colonne e tipi di dati: per fare questa operazione abbiamo usato una collection.

Tuttavia esistono alcune funzioni interessanti come APEX_DATA_PARSER.DISCOVER e APEX_DATA_PARSER.GET_COLUMNS. Per maggiori dettagli guarda la documentazione ufficiale.

Una volta che hai caricato i dati nella tua applicazione APEX, puoi salvare i record nel database sfruttando la stessa query che abbiamo usato per alimentare l’Interactive Report.

Ovviamente, sta a te implementare tutte le logiche di validazione necessarie sui dati presenti nell’Excel.

Ad esempio, potresti verificare che il tracciato dell’ Excel sia quello previsto (nome delle colonne e tipo dati) piuttosto che controllare che tutti i campi obbligatori siano stati popolati dall’utente.

Ricorda che APEX_DATA_PARSER può essere utilizzato con altri tipi di sorgenti come XML, CSV o servizi JSON.