Come esportare dati in Excel con JXLS

Descriviamo una tecnica per esportare i dati in excel a partire da una vista senza ricorrere a csv o macro vba attraverso strumenti gratitui e open source.

Spesso ci siamo imbattuti nell'esigenza di esportare i dati visualizzati in una vista in excel. In quanto ad integrazione con strumenti desktop ed office, il client di lotus notes continua a non brillare nonostante i grandi cambiamenti della versione 8.
Le classiche soluzioni di esportare in csv o in lotus 123 (!?) hanno gli svantaggi di offrire una esportazione nuda e cruda, priva di formattazione e comunque oggi comincia ad essere impresentabile oltre che incomprensibile ai nuovi utenti; si può ricorrere ad una macro vba che però ha lo svantaggio di aprire un processo excel.exe sul server o sul client e presenta la difficoltà di dover scrivere ogni volta molte righe di codice per arrivare ad un risultato dignitoso.

Recentemente abbiamo utlizzato con successo una libreria java open source (LGPL) che offre notevole flessibilità ed è facilmente gestibile anche da chi ha scarsa familiarità con il linguaggio java: la libreria è JXLS, di cui trovate tutta la documentazione nel sito http://jxls.sourceforge.net/. Tra le tante cose presenta il grande vantaggio di poter creare il template di esportazione direttamente in excel e di dover scrivere pochissimo codice.
Tutti gli strumenti che vedrete sotto sono open source e distribuiti in maniera gratuita come spesso avviene nella community java (l'unica cosa che non è gratis è Excel !).


Riportiamo un esempio banale di come utilizzare la libreria e come ottenere in tempi rapidi una esportazione in excel.
Nel nostro caso partiamo da una vista in cui i singoli documenti rappresentano dei dati statistici di vendita sui singoli articoli.



Il risultato finale della nostra esportazione, ottenuto in pochi minuti di lavoro è questo:



Associamo alla Action "Esporta Excel" il codice necessario per esportare i dati attraverso un agent.
Questa scelta ci permette di far eseguire il codice e il processo di esportazione dal server (nulla vieta che sia eseguito dal client) .

     'Definizione variabili
     Dim s As New NotesSession        
     Dim db As NotesDatabase
     Dim agent As NotesAgent
     Set db = s.CurrentDatabase
     Set agent = db.GetAgent("PrintXls")    'Definisco l'agent che effettua l'esportazione
     agent.RunOnServer                             'Eseguiamo il processo lato server


A questo punto, configuriamo il nostro database per poter utilizzare la libreria jxls.
A tal fine è sufficiente crearsi una script library in java che chiameremo "Commons" (potete ovviamente utilizzare un nome qualsiasi) ed inserire all'interno i  .jar (librerie java) necessari all'applicazione (sono tutti strumenti open source).
La lista la trovate nel sito http://jxls.sourceforge.net/reference/installation.html.
Per inserire i jar basta cliccare il pulsante edit project all'interno della library.



Con questa operazione abbiamo "installato" e configurato la libreria jxls che è pronta per essere utilizzata.

Non ci rimane che scrivere l'agent che recupera i dati dalla vista e li esporta in excel.
Creiamo un agent e con il punsante edit project indichiamo all'agent di utilizzare la script library in java appena creata.



Dopo di che scriviamo l'agent vero e proprio.

import lotus.domino.*;
import java.util.*;
import java.io.*;
import com.ankg.vendite.*;

import net.sf.jxls.transformer.*;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class JavaAgent extends AgentBase {

     public void NotesMain() {

             try {
                     Session session = getSession();
                     AgentContext agentContext = session.getAgentContext();
             
                     //Recupero i documenti dalla vista
                      Database db = agentContext.getCurrentDatabase();
                           View view = db.getView("uStat");
                      Document doc = view.getFirstDocument();

                      //Popolo un Vector con i singoli documenti della vista
                      Vector  coll=new Vector();
                              while (doc != null) {
                                //Creo un oggetto per ogni doc
                                StatVendita st=StatVendita.loadFromDoc(doc);
                          coll.addElement(st);        //inserisco l'oggetto nel Vector
                                  doc = view.getNextDocument(doc);  //ciclo
                     }
               
                     //Utilizzo la libreria JXLS utilizzando un template di excel statTemplate.xls
                             Map beans = new HashMap();
                                beans.put("stats", coll);        
                                              HSSFWorkbook wb=XLSTrasform("c:/statTemplate.xls",beans);    
     
                     // creo il file di output
                     FileOutputStream out = new FileOutputStream("c:/stat.xls");
             
                     //Scrivo il file e lo chiudo
                     wb.write(out);
                     out.close();

             } catch(Exception e) {
                     e.printStackTrace();
             }
     }
     
     
     /**
      * Trasforma il template in xls finale
      * NON MODIFICARE QUESTO CODICE
      *
      * @param xlsTemplatename   template del file xls da riempire
      * @param beans                         mappa degli oggetti che usa il XLSTrasformer
      * @return                                       file excel
      * @throws Exception
      */
     private static HSSFWorkbook XLSTrasform(String xlsTemplatename,Map beans) throws Exception{
           XLSTransformer transformer = new XLSTransformer();
           BufferedInputStream bfTemplate = new BufferedInputStream(new java.io.FileInputStream(xlsTemplatename));
                   HSSFWorkbook wb=transformer.transformXLS(bfTemplate, beans);
           return wb;
     }
}

Per poter utilizzare questo codice in altre viste o altri database è sufficiente cambiare pochissime righe.

 StatVendita st=StatVendita.loadFromDoc(doc);   -> questo passaggio che descriviamo sotto trasforma il documento Notes in una classe che ne rappresenta il contenuto, tipico in java
 HSSFWorkbook wb=XLSTrasform("c:/statTemplate.xls",beans);      -> indicare il relativo template
 FileOutputStream out = new FileOutputStream("c:/stat.xls");             -> indicare il percorso di output

Tutto il resto rimane invariato.
In sostanza abbiamo inviato ad un "template" un elenco di oggetti (che sono i documenti notes) con questa riga di codice:
 beans.put("stats", coll);  

Ed ecco il nostro template. Come viene indicato nella documentazione di JXLS è sufficiente creare un file excel, formattarlo come voluto ed indicare dove reperire i dati.




Nel nostro caso è sufficiente indicare di ciclare sull'elenco "stats":
     
             ......................
     

Queste due righe indicano alla libreria di ciclare. Per ogni singolo elemento (identificato dalla parola stat ) vengono  riportati questi dati.



E' facile immaginare che stat.codiceArticolo rappresenti un attributo dell'oggetto java, e lo stesso vale per le altre informazioni.

Per completare il tutto riportiamo la classe che "immagazzina" i dati del documento Notes in un oggetto.
Questa operazione non è di per se necessaria, potevamo anche inviare al template un elenco di Document (classe di lotus domino).
Per chi lavora in java, tuttavia è prassi separare gli elementi di back end da quelli di front end e quindi abbiamo realizzato una banale classe che rappresenta la singola statistica di vendita (nella realtà lavoriamo spesso ad oggetti in lotus notes, quindi questa classe era già presente nel      nostro database e viene utilizzata in molti altri contesti dell'applicazione).

Nel codice trovete un banale bean java che recupera i dati dal Document e valorizza le variabili dell'oggetto.


package com.ankg.vendite;

import java.math.BigDecimal;
import lotus.domino.*;

public class StatVendita {

 // variabili
 private String codiceArticolo;                            //Codice articolo
 private String descrizioneArt;                           //Descrizione articolo
 private BigDecimal qtVendita;                           //qt di vendita
 private BigDecimal valoreFatturatoLordo;        //valore fatturato lordo
 private BigDecimal valoreFatturatoLordoTp;    //valore fatturato lordo trim.precedente
 private BigDecimal delta;                                  //delta fatturato
 
 
         /**
      *   Valorizza un oggetto da un documento di tipo Stat
      */
     public static StatVendita loadFromDoc(Document doc)        throws Exception{

             if (doc==null)  return null;
                     
             if (doc.getItemValueString("Form")==null  || ! doc.getItemValueString("Form").equalsIgnoreCase("Stat"))  return null;
                     
                     StatVendita st=new StatVendita();                        
                     st.setCodiceArticolo(doc.getItemValueString("cdart"));
                     st.descrizioneArt=doc.getItemValueString("descr");

                     st.setQtVendita(new BigDecimal(doc.getItemValueDouble("qt")));                
                     st.valoreFatturatoLordo=new BigDecimal(doc.getItemValueDouble("ft"));
                     st.valoreFatturatoLordoTp=new BigDecimal(doc.getItemValueDouble("ftp"));
                     st.delta=new BigDecimal(doc.getItemValueDouble("delta"));
                     return st;
          }
 
 
 // Get and Set ************************************************************************************************
 
 public String getCodiceArticolo() {
     return codiceArticolo;
 }
 public void setCodiceArticolo(String codiceArticolo) {
     this.codiceArticolo = codiceArticolo;
 }
 public String getDescrizioneArt() {
     return descrizioneArt;
 }
 public void setDescrizioneArt(String descrizioneArt) {
     this.descrizioneArt = descrizioneArt;
 }
    public BigDecimal getQtVendita() {
     return qtVendita;
 }
 public void setQtVendita(BigDecimal qtVendita) {
     this.qtVendita = qtVendita;
 }
 
 public BigDecimal getValoreFatturatoLordo() {
     return valoreFatturatoLordo;
 }
 public void setValoreFatturatoLordo(BigDecimal valoreFatturatoLordo) {
     this.valoreFatturatoLordo = valoreFatturatoLordo;
 }
 
    public BigDecimal getValoreFatturatoLordoTp() {
     return valoreFatturatoLordoTp;
 }
 public void setValoreFatturatoLordoTp(BigDecimal valoreFatturatoLordoTp) {
     this.valoreFatturatoLordoTp = valoreFatturatoLordoTp;      
 }
 public BigDecimal getDelta() {
     return delta;
 }
 public void setDelta(BigDecimal delta) {
     this.delta = delta;
 }
     
}

In pratica se si volesse realizzare la stessa tecnica in un altro contesto è sufficiente adattare il codice scritto in grassetto che di per se non presenta difficoltà.

Uno degli aspetti più piacevoli è quello che si può realizzare il template direttamente in excel, ottenendo una formattazione (numeri, testi, colori, bordi) in pochi click, difficilmente ottenibile con poche righe di codice. Se date una occhiata alle potenzialità della libreria scoprirete che è semplice ottenere risultati anche più complessi come esportazione su più fogli, totali, formule,  raggruppamenti e subtotali; sempre partendo dal template senza aggiungere nulla al codice java dell'agent o del bean. Tra l'altro allo stesso template si possono passare molte più informazioni rispetto al nostro caso, come parametri, altri elenchi, elenchi di elenchi....

L'esempio riportato è banale  e rappresenta una base su cui costurire facilmente un engine per le esportazioni in excel (nel nostro esempio si esporta brutalmente su file system, ma è ovvio che in produzione si può esportare su documenti notes che vengono aperti dall'agent, o reperiti su una apposita vista dall'utente o qualsiasi altra soluzione tradizionale notes).

Volutamente nel nostro caso il tutto viene eseguito centralmente dal server. In questo modo il carico della procedura è nella macchina server ed è centralizzato. La stessa tecnica utilizzata ovviamente può essere facilmente estesa ad un form, ad embedded view. alle folder e può essere (ovviamente) gestita dai web services dello stesso database.