1. Il motivo
2. Il comando XLSTABLE
3. Un certo caso di errore
4. Come lavora XLSTABLE
5. Fogli contenenti formule
6. Il file di destinazione
7. Suggerimenti per un uso ripetuto dello stesso modello di foglio Excel

1- Il motivo

A un certo punto mi sono reso conto che gli utilizzatori di XLSCONVERT avrebbero voluto avere qualcosa di più.
Non era più sufficiente che avessero accesso ad una immagine del foglio Excel (come quella creata in QTEMP dal comando XLSCONVERT) con normali porogrammi RPG e la procedura xlsgetcell().
Costoro volevano semplicemente che il foglio Excel venisse trasformato in una TABLE: cioè in un comunissimo file fisico IBM i che potesse essere letto da un qualunque programma su IBM i.
Questo è dunque l'obiettivo del comando XLSTABLE..

2- Il comando XLSTABLE

Il comando XLPARSE2/XLSTABLE genera e carica un file fisico ("target file") con i dati pervenuti da una foglio Excel di tipo XLS o XLSX..

• E' importante che l'ambiente esecutivo Java del lavoro non sia già stato predisposto (con il comando ADDENVVAR ENVVAR(CLASSPATH) ...). Se è stato predisposto un classpath diverso da quello atteso, Java emette un messaggio di errore nel quale si dice che una classe Java non è stata trovata.
• Il profilo utente che esegue il comando XLSTABLE deve avere l'autorità speciale *JOBCTL. Tale autorità è richiesta dal comando ADDENVVAR che viene eseguito dal programma.
• Il foglio Excel XLS/XLSX deve essere in un file di flusso IFS e deve essere in caratteri ASCII (CCSID 819-ASCII ISO Latin 1, 1212-PC USA, o 1252-IBM PC).
• Anche se il workbook contiene più fogli Excel, il foglio elaborato è uno solo.

                        Generate PF from XLS/XLSX (XLSTABLE)

 Type choices, press Enter.

 .xls/xlsx stream file. . . . . XLS                                             
                                                                                  
 Spreadsheet sequence number. . SHEET       1             1-999
 Target file. . . . . . . . . . FILE                      Name
   Library. . . . . . . . . . .                           Name
 Target member. . . . . . . . . FILEMBR     *FIRST        Name, *FIRST
 Replace or add records. . . . . MBROPT      *REPLACE      *ADD, *REPLACE
 (Re)create target file. . . . . CRTFILE     *YES          *YES, *NO
 Source file. . . . . . . . . . SRCFILE     QDDSSRC       Name
   Library. . . . . . . . . . .               *FILELIB    Name, *FILELIB
 Source member. . . . . . . . . SRCMBR      *FILE         Name, *FILE
 Header lines. . . . . . . . . . HLINES      0             0-99
 Date columns. . . . . . . . . . DATECOL     *AUTO         Number, *AUTO, *NONE
                + for more values                  
 Date format. . . . . . . . . . DATEFMT     *YMD          *YMD, *MDY, *DMY
 Time columns. . . . . . . . . . TIMECOL     *NONE         Number, *NONE
                + for more values                  
 Time separator. . . . . . . . . TIMESEP     *             *, :, :, ., ,, -
 Display target file. . . . . . DSPFILE     *YES          *YES, *NO
 Run in a sync submitted job. . SBMJOB      *YES          *YES, *NO
 Type of failure message. . . . FAILMSG     *ESCAPE       *ESCAPE, *DIAG

• .xls/xlsx stream file (XLS) - Nome qualificato del del file di flusso (CCSID 819-ASCII ISO Latin 1, 1212-PC USA, or 1252-IBM PC) contenente il foglio Excel che deve essere trasformato nel file fisico specificato nel parametro FILE.
  L'indirizzario IFS '/xlparse2/examples' contiene un certo numero di file di flusso fogli Excel che possono essere utilizzati per provare il comando XLSTABLE.
• Spreadsheet sequence number (SHEET) - Numero di sequenza (da 1 a 99) del foglio (del workbook) che va elaborato.
• Target file (FILE) - Nome qualificato del file che viene creato/caricato dalla elaborazione.
  Se il file fisico non esiste e si è specificato CRTFILE(*YES), viene creato un membro origine DDS il quale viene usato per create il file fisico. Il file fisico viene creato con MAXMBRS(*NOMAX).
• Target member (FILEMBR) - Nome del membro del file fisico creato dalla elaborazione.. Se tale membro non esiste,
  • se il file è definito con MAXMBRS(*NOMAX), il membro viene aggiunto al file
  • altrimenti viene generato un messaggio di errore.
• Replace or add records (MBROPT) - Specifica se i nuovi record sono in sostituzione dei record esistenti oppure in aggiunta. Scelte possibili:
  • *REPLACE - Il programma pulisce il membro esistente, poi aggiunge i nuovi record.
  • *ADD - Il programma aggiunge i nuovi record in coda a quelli già esistenti.
• (Re)create target file (CRTFILE) - Scelte possibili:
  1. *YES
     • se il file di arrivo non esiste ancora, oppure
     • se il file di arrivo esiste già, ma si vuole che il suo tracciato record venga ricalcolato sulla base del tracciato del foglio Excel,.
  2. *NO
     se il file di arrivo esiste già e si vuole mantenere il tracciato record esistente. Se si sceglie *NO, occorre essere coscienti che eventuali variazioni del formato del foglio Excel possono causare l'incapacità di caricare correttamente i dati nel file di arrivo esistente.
• Source file (SRCFILE) (only for CRTFILE(*YES)) - Nome del file origine che deve contenere le DDS generate per il file di arrivo. Se ancora non esiste, il file origine viene creato automaticamente.
• Source member (SRCMBR) (only for CRTFILE(*YES)) - Nome del membro origine che conterrà le DDS generate per il file di arrivo. Si può usare *FILE per indicare lo stesso nome utilizzato nel nparametro FILE come nome del file di arrivo.
• Header lines (HLINES) - Numero di righe di testata da ignorare nella costruzione del file di arrivo.
  Molte volte una o più righe iniziali contengono delle testate. Ovviamente le intestazioni non vanno prese in considerazione in quanto non contengono dati validi per i record del file di arrivo. Ecco quindi che questo parametro viene utilizzato per indicare quante righe iniziali, dedicate a testate, debbano essere ignorate.
• Date columns (DATECOL) - Nei fogli Excel la data è memorizzata come numero floating point. Si tratta del numero di giorni trascorsi dall' 1 Gennaio 1900. Per avere tali valori numerici convertiti in data, esistono le alternative seguenti:
  • Immettere *NONE per evitare di convertire in data a casaccio tutti i valori numerici.
    Si raccomanda questa scelta, a meno che si sappia che il foglio contiene almeno una colonna di date.
  • Immettere fino a 50 numeri di colonna per identificare le colonne in cui i valori numerici vanno trasformati in date.
    Si raccomanda questa scelta quando si sa con esattezza quali colonne contengono delle date.
  • Immettere *AUTO per lasciare che il programma di conversione trasformi in date i valori numerici ritenuti ragionevoli per tale decisione.
    Questa scelta può portare a risultati bizzarri e quindi la si suggerisce per dei test intesi a trovare se nel foglio esistono delle reali colonne di date.
• Date format (DATEFMT) - I campi data vengono generati come stringhe di 10 caratteri. Il carattere separatore di data è '-'.
  Scegliere tra i formati seguenti:
  • *YMD - la data rappresentata ha il formato is aaaa-mm-gg
  • *MDY - la data rappresentata ha il formato mm-gg-aaaa
  • *DMY - la data rappresentata ha il formato gg-mm-aaaa
  Nota. Questo parametro non è disponibile quando nel parametro DATECOL si specifica *NONE
• Time columns (TIMECOL) - Nei fogli Excel il valore interno che rappresenta l'ora (minuti e secondi) (esempio: 16:48:56) è un numero (esempio: 700648). Questo numero rappresenta l'ora in unità "milionesimi di giorno" (esempio di calcolo; in 24 ore ci sono 84.600 secondi; l'ora 16:48:56 è 69,536 secondi; pertanto in Excel la rappresentazione interna dell'ora16:48:56 è (69,536/84,600)*1,000,000=700648 .
  Per avere i valori numerici dell'ora in Excel convertiti al formato convenzionale hh.mm.ss, si scelga una delle opzioni seguenti:
  • *NONE per evitare di convertire in valore orario qualunque valore numerico. Questra è la scelta consigliata quando non si sa se esista almeno una colonna di orari.
  • Immettere fino a 50 numeri di colonna identificanti le colonne contenenti orari. Si raccomanda questa scelta quando si sa quali colonne contengono degli orari.
• Time separator (TIMESEP) - Questo parametro non viene presentato quando si specifica *NONE per il parametro TIMECOL.
  Scelte possibili:

  	*		per usare il separatore specificato nel valore di sistema QTIMSEP.
  	:		per usare i due punti come separatore (esempio di risultato: 16:48:56)
  	.		per usare il punto come separatore (esempio di risultato: 16.48.56)
  	,		per usare la virgola come separatore (esempio di risultato: 16,48,56)
  	-		per usare uno spazio come separatore (esempio di risultato: 16 48 56).

• Display target file (DSPFILE) - indica se il file di arrivo debba essere visualizzato alla fine della elaborazione.
• Run in a sync submitted job (SBMJOB) - Specificare SBMJOB(*YES) per fare in modo che il comando XLSCONVERT venga eseguito in un altro lavoro che sarà fatto partire in batch. Il lavoro attuale attenderà che il lavoro batch immesso sia completato, dopo di che riprenderà il suo corso.
  Quanto sopra può essere utile in due casi:
  • Non si vuole che XLSTABLE faccia partire una Java Virtual Machine nel lavoro attuale, OPPURE
  • non si vuole eseguire XLSTABLE nel lavoro attuale perchè potrebbe essere in conflitto con le predisposizioni della Java Virtual machine già attiva nel lavoro attuale.
• Type of failure message (FAILMSG) - Quando si specifica SBMJOB(*YES), il comando XLSTABLE viene eseguito in un altro lavoro batch sincronizzato con il lavoro attuale.
  Può però avvinire che il comando XLSTABLE fallisca durante la sua esecuzione. In tal caso verrà inviato un messaggio al programma in attesa nel lavoro attuale. Con questo parametroi si può scegliere se il messaggio debba essere di tipo diagnostico (*DIAG) oppure di tipo *ESCAPE.

3- Un certo caso di errore

• Aprire il foglio con Excel
• Salvare il foglio. In questo modo Excel aggiungerà i tipi dati mancanti.

4- Come lavora XLSTABLE

Può essere utile sapere che cosa avviene sotto coperta:

1. Si fanno alcuni controlli iniziali.
2. Si esegue il comando XLPARSE2/XLSCONVERT creando così il file QTEMP/XLSOUTF a partide dal foglio Excel.
   Di fatto, XLPARSE2/XLSCONVERT legge ad una ad una tutte le celle del foglio Excel, utilizzando, nel service program XLPARSE4 di Scott, la classe Java xlparse di scott e le classi Java POI.
   Da ciò ne risulta il file QTEMP/XLSOUTF, il quale a questo punto contiene una rappresentazione su database ti tuttli i fogli Excel del workbook.
3. Se viene specificato CRTFILE(*YES),
   1. si utilizza la procedura xlsgetcell per ricevere dal file QTEMP/XLSOUTF tutte le celle del foglio desiderato (vedi il parametro SHEET).
      Alla fine dell processo è possibile calcolare il tipo dati e la dimensione di ogni colonna del foglio, viene generato un membro DDS, (ogni precedente versione del membro viene sostituita dall'ultima versione (!!!) ed il file di arrivo viene creato.
   2. Viene creato un programma apposito ("ad hoc") nella libreria QTEMP. Questo programma sarà poi usato per caricare i dati nel file..
4. Se si specifica CRTFILE(*NO)
   1. le DDS non vengono rigenerate ed il file non viene ricreato.
   2. Se non viene trovato in QTEMP, il programma "ad hoc" viene ricreato.
5. Successivamente ha luogo il caricamento dei record nel file di arrivo:
   • Si recuperano le descrizioni dei campi del tracciato record del file di arrivo. Esse vengono usate per mappare il buffer di input da passare al programma "ad hoc".
   • Viene usata la procedura xlsgetcell per ricevere dal file QTEMP/XLSOUTF (per il foglio Excel specificato nel parametro SHEET) tutte le celle, una per una. Ogni cella viene formattata nel buffer di input
     • ai dati di tipo carattere vengono aggiunti spazi sulla destra fino a raggiungere la lunghezza del campo sul database di arrivo
     • i dati numerici vengono convertiti nel formato "zonato (30 6)".
     Non appena un record è completo, il buffer di input vienre passato al programma "ad hoc", che provvede a scriverlo sul file di arrivo.
6. Infine, se si è specificato DSPFILE(*YES), viene visualizzato il contenuto del file di arrivo.
   Una visione ancora migliore del file di arrivo si può avere con la utilità (di easy400.net) CGI_WRKDBF.

5- Fogli contenenti formule

I valori calcolati da formule possono avere problemi di conversione. Un modo semplice per superare tali problemi è documentato in questo paragrafo.

6- Il file di destinazione

Ogni campo del tracciato record del file di arrivo corrisponde ad una colonna del foglio XLS/XLSX.
Ci sono due tipi di campi:

• Campi numerici - Una colonna del foglio Excel la quale contenga solo dati numerici (le formule sono considerate dati numerici) genera un campo numerico.
  Un campo numerico è sempre generato come zonato a 30 cifre, di cui 6 sono cifre decimali.
• Campi carattere - Una colonna del foglio Excel, la quale non contenga solo dati numerici, genera campi carattere.
  I campi carattere hano un fattore critico, la loro lunghezza. La lunghezza di un campo carattere viene calcolato sulla base del massimo numero di caratteri trovato in una cella della colonna.
  Questo comporta che, se se si elaborano con XLSTABLE dei fogli Excel tra loro simili (stesso tipo e numero di colonne), il tracciato trecord del file di arrivo può risultare diverso semplicemente perchè il valor massimo di caratteri per cella di una colonna è variato rispetto a quello calcolato per la stessa colonna nel precedente caso di un foglio dello stesso tipo.

I nomi dei campi sono generati in modo automatico come FLD1, FLD2, FLD3, ..., FLDn.

7- Suggerimenti per un uso ripetuto dello stesso modello di foglio Excel

1. Un dato foglio Excel abbia sempre lo stesso numero di colonne
2. Tutte le colonne del foglio Excel mantengano il loro tipo dati (Carattere/Numerico; vedi tuttavia la NOTA 1 più avanti)
3. Nel tracciato trecord del file di arrivo, i campi carattere siano dimensionati in modo da poter contenere il caso più esteso. Ciò si può fare, una volta per tutte, nel modo seguente:
   1. rivedendo le DDS generate automaticamente:
      1. E' possibile assegnare ai campi nomi che si ritengano appropriati per le elaborazioni locali.
      2. E' possibile assegnare ai campi delle keyword.
      3. E' possibile definire campi chiave.
      4. Si raccomanda caldamente di aumentare la lunghezza dei campi carattere per contenere il caso più esteso.
      5. NON SI DEVE variare il tipo dati, nè il numero di cifre, nè il numero di posizioni decimali dei campi numerici. Essi devenon sempre specificare 30S 6.
         Vedi tuttavia la NOTA 1 più avanti.
      6. NON SI DEVE variare l'ordine dei campi.
   2. ricreando il file di arrivo
   3. eseguendo il comando clrlib qtemp
   4. utilizzando il comando xlstable ... crtfile(*no) ...
4. Ci si assicuri che nel nel comando XLSTABLE sia specificato CRTFILE(*NO). Se invece si specifica CRTFILE(*YES) il membro DDS viene ripulito e vengono generate nuove DDS !!!

1. ridefinendo con le DDS i corrispondenti campi nel tracciato record del file di arrivo come campi carattere lunghi 30
2. eseguendo il comando clrlib qtemp
3. utilizzando il comando xlstable ... crtfile(*no) ...