Skip to main content  
        iSeries home   |   Easy400     |   CGIDEV2     |   MMAIL  
Public-Source
 
Introduzione
 
 XLSCONVERT
XLSTABLE
XLSTABLE2
Alcuni programmi CGI
 
 PDF di questo manuale
 
 Scarica questo tool
 
 

 
XLSCONVERT
Converti un foglio Excel XLS o XLSX in un file database
  1. Il motivo
  2. Il comando XLSCONVERT
  3. Fogli contenenti formule
  4. Un certo caso di errore
  5. Il file di output QTEMP/XLSOUTF
  6. Stampare da IBM i un foglio Excel
  7. Leggere su IBM i un foglio XLS o XLSX

1- Il motivo

Excel è estremamente diffuso nelle piccole aziende e nei reparti di grandi compagnie per elaborazioni locali. Consentire di ricevere in input dei fogli Excel aumenta la integrazione di IBM i. Su IBM i è possibile ricevere input spediti per e-mail: La utility MMAIL fornisce il modo per ricevere tali messaggi e per staccare gli allegati Excel come singoli file di flusso IFS.

Sull'IBM i è poi necessario avere una procedura capace di convertire qualunque foglio Excel XLS o XLSX in un file database standard, file che può quindi essere elaborato da applicazioni di IBM i.

Il comando XLPARSE2/XLSCONVERT è proprio lo strumento che serve a questo scopo.

2- Il comando XLSCONVERT

Il comando XLPARSE2/XLSCONVERT converte un foglio di un dato workbook XLS o XLSX - che risieda sukll'IFS come file di flusso - in un file database nella libreria QTEMP. Una volta terminata la conversione, il file database di QTEMP può essere letto da una applicazione locale.

  • E' importante che l'ambiente esecutivo Java del lavoro non sia già stato predisposto (con il comando ADDENVVAR ENVVAR(CLASSPATH) ...). Se infatti viene trovato un classpath diverso da quello atteso, viene emesso un messaggio Java di errore in cui si dice che una classe Java non è stata trovata.
  • Il profilo utente del lavoro che utilizza il comando XLSCONVERT deve possedere la autorità speciale *JOBCTL. Tale autorità è richiesta dal comando ADDENVVAR che viene eseguito dal programma.
  • Se il comando XLSCONVERT viene lanciato da un programma, è opportuno che tale programma venga creato con ACTGRP(XLPARSE2).
  • Il file IFS deve essere un workbook XLS o XLSX in codifica ASCII e deve contenere almeno un foglio di lavoro Excel.
  • Il file database che riceve i dati convertiti è il file fisico QTEMP/XLSOUTF.
                    Convert an Excel spreadsheet (XLSCONVERT)

 Type choices, press Enter.
                           
 .xls/xlsx stream file. . . . . XLS                                             
                                                                                  
 Sheet sequence number. . . . . SHEETNBR    1             1-999, *ALL
 Display the database file. . . DSPDBF      *NO           *YES, *NO
 Print results. . . . . . . . . PRINT       *NO           *YES, *NO
 Decimal places. . . . . . . . . DEC         2             0-6
 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     *             *, :, :, ., ,, -
 Run in a sync submitted job. . SBMJOB      *YES          *YES, *NO
 Type of failure message. . . . FAILMSG     *ESCAPE       *ESCAPE, *DIAG

                           Additional Parameters
                                                                      
Set output record ID. . . . . . SETOUTID     *NO           *YES, *NO
  • .xls/xlsx stream file (XLS) - Nome qualificato del file di flusso Excel Workbook.
    L'indirizzario IFS '/xlparse2/samples' contiene vari workbook Excel che si possono utilizzare per fare prove del comando XLSCONVERT.
  • Sheet sequence number - Il numero di sequenza del foglio che si vuole elaborare. Specificare *ALL se si vuole convertire in un colpo solo tutti i fogli Excel presenti nel workbook.
  • Display the database file (DSPDBF) - Scegliere tra:
    • *YES per visualizzare il file QTEMP/XLSOUTF a conversione completata. Questa opzione è utile durante le prove.
    • *NO per evitare di visualizzare il file QTEMP/XLSOUTF a conversione completata.
  • Print results (PRINT) - Specifica se si vuole una stampa del foglio una volta convertito.
  • Decimal places (DEC) - Numero di cifre decimali dopo la virgola. Quando una cella contiene un valore numerico (per esempio 142,27), il valore viene ricevuto come numero floating point (per esempio 1.422700000000E+002). Quresto non dice quante cifre decimali vanno presentate dopo la conversione.
    Per questo motivo il numero delle cifre decimali va fornito tramite questo parametro.
    Il parametro ti applica a tutte le celle contenenti valori numerici.
  • 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 sonoi 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. Se si specifica *NONE per il parametro DATECOL, questo parametro per il formato della data non viene presentato.
  • 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).
  • 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 XLSCONVERT faccia partire una Java Virtual Machine nel lavoro attuale, OPPURE
    • non si vuole eseguire XLSCONVERT 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 XLSCONVERT viene eseguito in un altro lavoro batch sincronizzato con il lavoro attuale.
    Può però avvinire che il comando XLSCONVERT 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.
  • Set output record ID (SETOUTID) - I record del file di output QTEMP/XLSOUTF hanno un campo denominato OUTID. Il valore di qtesto campo identifica il tipo dati del record:
    • B significa "Titolo del foglio"
    • H significa "Testata di colonna"
    • D significa "dato della colonna"
    Se si specifica SETOUTID(*NO), non viene fatta più differenza tra i diversi tipi record; a tutti i record viene assegnato il valore D nel campo OUTID.
    DSe invece si specifica SETOUTID(*YES) i record dati vengono differenziati nei tre tipi record di cui sopra.

3- Fogli Excel contenenti formule

XLSCONVERT è in grado di fare il parsing di tre tipi di celle: testo, numero, formula.
Le formule sono espressioni Excel che vengono calcolate da Exel a tempo di esecuzione.
Quando si guarda un folglio Excel, è impossibile dire se il valore di una cella deriva o no da una formula. Per saperlo, occorre agire sulla "tool bar" di Excel:

  1. premere l'etichetta formule
  2. quindi il bottone Mostra formule
XLSCONVERT è in grado di sapere se il risultato di una formula è un testo oppute un numero, ma - ad eccezione di pochi casi - non è in grado di calcolare il risultato della formula. In questi casi, il risultato della formula si presenta in bianco.

Pertanto, quando si ha un foglio Excel contenente delle formule, la cosa migliore da fare è quella di copiare tutte le celle del foglio nella clipboard e quindi "incollarle" sulle celle di un altro foglio vuoto.
Per i dettagli di questa operazione, vedi la pagina di Microsoft Office Support Copy cell values, not formulas.
I comandi XLSCONVERT, XLSTABLE e XLSTABLE2 non hanno problemi nel convertire fogli Excel risultanti da questa operazione.

4- Un certo caso di errore

In alcuni casi può avvenire che XLSCONVERT invii un messaggio di tipo escape con il testo "No significant cells detected in this sheet".
Questo avviene quando XLSCONVERT non riesce ad identificare nessuna cella di tipo testo, numero o cella. In tal caso bisogna fare così:
  • Aprire il foglio con Excel
  • Salvare il foglio. In questo modo Excel aggiungerà i tipi dati mancanti.
Per far ciò in una procedura, basterà usare il comando
HSSFCGI/CLONEBOOK('bookname')
dove bookname è il nome (completo di path) del file di flusso contenente il foglio che ha problemi.
Questo comando utilizza il POI per fare ciò che dovreste fare a mano per ridare alle celle le loro proprietà di "tipo dati".. Se non è in grado di espletare la richiesta, invia un messaggio di tipo escape che va monitorato dal programma chiamante.
Naturalmente occorre avere l'utility HSSFCGI..

5- Il file di output QTEMP/XLSOUT

Questo file database contiene i dati di un foglio Excel XLS o XLSX convertito con il comando XLSCONVERT. Esiste un record per ogni riga del foglio. Il tracciato record è il seguente:

  1. Campo OUTSHEET (50A) - Il nome del foglio di appartenenza.
  2. Campo OUTSEQ (5S 0) - Il numero riga nell'ambito del foglio.
  3. Campo OUTID (1A) - Tipo di contenuto della riga. Valori possibili:
    • B- Titolo del foglio
    • H- Testate delle colonne
    • D- Dati delle colonne
  4. Campo OUTNBRCOL (3S 0) - Numero di colonne in questa riga.
  5. Campo OUTDTA (5000A) - Contenuto di tutte le colonne di questa riga.
    I dati di una colonna hanno sempre la stessa dimensione per tutte le righe del foglio. Il tipo dati e la lunghezza di ciascuna colonna sono documentati nei prossimi due campi.
  6. Campo OUTCOLLEN (una schiera di 500 sottocampi, tutti 4B 0)- Ogni voce della schiera contiene la dimensione in byte della colonna corrispondente.
  7. Campo OUTCOLTYPE (una schiera di 500 sottocampi, tutti 1A) - Ogni voce contiene il tipo dati della colonna corrispondente. Valori possibili:
    • C - Carattere
    • N - Numero rappresentato in caratteri;
      Si noti che il carattere di separazione dei decimali qui è il punto, carattere ".".
    • S- Valore nullo

6- Stampare da IBM i un foglio Excel

Il comando XLSCONVERT può stampare fogli Excel di qualunque formato.
Semplicemente si immetta il comando XLSCONVERT XLS(...) PRINT(*YES) ed è fatto!
Si veda il nostro esempio.

7- Leggere su IBM i un foglio XLS o XLSX

Modi in cui è possibile leggere un foglio XLS o XLSX da un file di flusso IFS:

  1. Scrivere il proprio programma utilizzando direttamente le procedure del service program di Scott Klement XLPARSER4 (vedi questo articolo). Io ho fatto così per scrivere il programma XLSCONVERT. Scott fornisce qualche esempio nei programmi XLPDEMO e XLPDEMOF (inclusi nella libreria XLPARSE2).
  2. Utilizzare il comando XLSCONVERT per convertire il foglio Excel nel file QTEMP/XLSOUTF.
    Il programma utente - per leggere il foglio Excel convertito - dovrà
    1. Essere creato con ACTGRP(XLPARSE2)
    2. Invocare una semplice procedura (denominata XlsGetCell) per ricevere via via le celle del foglio.
      Si utilizzi
      • XlsGetCell('STR') per posizionarsi prima della prima cella del foglio convertito in QTEMP/XLSOUTF
      • XlsGetCell('GET') per ricevere le celle, una alla volta
      • XlsGetCell('END') per annullare il posizionamento.
    Per dimostrare la facilità di questo metodo, abbiamo scritto un programma di esempio che fa esattamente quanto descritto. Il programma si chiama GETCELLS. Ecco il suo sorgente:

    *========================================================================
    * Create this program as follow:
    * CRTBNDRPG PGM(XLPARSE2/GETCELLS) SRCFILE(XLPARSE2/QRPGLESRC)
    *           DFTACTGRP(*NO) ACTGRP(XLPARSE2) DBGVIEW(*SOURCE)
    * NOTE that activation group MUST be XLPARSE2
    *========================================================================
    H BNDDIR('XLPARSE2/XLPARSE2')
    H optimize(*NONE)
    H decedit(*JOBRUN)
    H truncnbr(*NO)
    H option(*srcstmt : *nodebugio)
     *Prototype of XLSGETCELL subprocedure
    D XLSGETCELL      PR           565
    D  Action                        3    value  options(*nopass)
     *========================================================================
     * Procedure XLSGETCELL, any time it is called,
     * returns a data structure containing information
     * about the next spreadsheet cell.
     *  This information is retrieved from physical file QTEMP/XLSOUTF.
     * This file contains the database version of the last spreadsheet
     * converted by command XLSCONVERT.
     * The following is the layout of the information data structure
     * returned from this subprocedure:
    D InfoDS          ds
     * Return code: 0=cell found, -1=No more  cells.
    D  rc                           10i 0
     * Sheet name
    D  xsheet                       50
     * Row number of this cell
    D  xrownbr                      11s 0
     * Type of row: B=Sheet title, H=Column headers, D=Data columns
    D  xID                           1
     * Number of columns in this row
    D  xnbrcol                       9s 0
     * Column number of this cell
    D  xcolnbr                       9s 0
     * Type of the data in this cell: C=character, N=numeric (edited), S=null value
    D  xcoldtatyp                    1
     * Estimated column length of this cell
    D  xcollen                      10i 0
     * Data in this cell
    D  xcoldta                     500
     *========================================================================
     * Main line
     *========================================================================
     /free
       rc=0;
    
       InfoDS=xlsgetcell('STR');      //Start process
    
       //loop getting spreadsheet cells, until no more cells (rc=-1)
       dow  rc=0;
            eval InfoDS=xlsgetcell('GET'); //get the info about the next cell
       enddo;
    
       InfoDS=xlsgetcell('END');      //END process
    
       *inlr=*on;
       return;
     /end-free