Excel in salsa Java

di admin - 24 Agosto 2012 in Programmazione

In questo articolo descriveremo in che modo è possibile integrare all’interno delle nostre applicazioni Java® i dati memorizzati all’interno di fogli Excel®, nello specifico saranno descritte il seguente tipo di operazioni:

Importazione dei dati
Esportazioni dei dati verso fonti esterni in particolare:
- Database
- File CSV
- File XML

Al fine di rendere il più semplice possibile la nostra trattazione si farà uso di un bridge jbdc-odbc in questo modo sarà possibile utilizzare i workbook e i fogli di Excel alla stregue di un database all’interno del quale sono state definite una serie di tabelle, inoltre il contenuto della prima riga di ogni foglio identifica i nomi dei diversi campi della tabella ; per prima cosa quindi, descriviamo in che modo definire il nostro bridge.

Creazione del bridge JDBC-ODBC

Cominciamo innanzitutto con il precisare che per Excel® è sempre presente un driver ODBC, inoltre al fine di rendere il più snello possibile il processo di definizione si farà uso del driver JDBC-ODBC definito all’interno del SDK, vediamo quindi tramite una serie di schermate come definire una fonte ODBC.

Come prima operazione è necessario definire una nuova “Origine dei dati”, questa opzione è localizzata all’interno degli “Strumenti di amministrazione”:

Il secondo passo nel nostro processo di definizione consiste nel definire il DSN utente per portare a termine questa operazione si devno eseguire i seguenti passi:

Selezionare la voce “Aggiungi” nella finestra che viene visualizzata dopo aver cliccato due volte sulla voce origine dati:

A questo punto selezionare la voce Microsoft Excel Driver (*.xls,*.xlsx,*xlsm, *xlsb):

Dopo aver premuto, il pulsante “Fine” sarà visualizzata la finestra grazie alla quale sia il nome dell’origine dei dati, questa particolare label sarà quella che utilizzeremo all’interno del codice ogni qualvolta si deciderà di accedere ai dati, è chiaramente indispensabile selezionare la cartella di lavoro a cui associare la connessione in particolare è possibile optare per una connessione che consenta la sola operazione di lettura, oppure anche quella di scrittura, nella prossima immagine mostriamo come sarà popolata la finestra di definizione dopo aver definito tutti gli elementi cui si è appena fatto cenno, chiaramente la versione di Excel dipenderà da quella installata sul sistema target, nel nostro caso si tratta di Excel 2010:

A questo punto dopo aver selezionato “Ok” si ritornerà alla scheda “DSN utente” in particolare notiamo la presenza della connessione appena definita

Dalla prossima sezione descriveremo in che modo utilizzare la fonte appena definita.

Tips&Trick.

Nel caso in cui si selezionando la voce Aggiungi non fosse visualizzata la finestra descritta al punto 2, ma la seguente

si può ricorrere a questo trucco:

Dal prompt dei comandi digitare il seguente comando: c:\windows\sysWOW64\odbcad32.exe, sarà visualizzata la stessa finestra del punto 1 a questo putno cliccando su “Aggiungi” si dovrebbe vedere visualizzata la finestra completa per la selezione del driver. Il problema appena descritto in genere si verifica su sistemi Windows® a 64 bit.

Lettura dei dati

In questa sezione della nostra guida descriveremo in che modo è possibile accedere ai dati presenti all’interno di un foglio Excel® utilizzando il bridge definito nella sezione precedente.

L’operazione in se non richiede molti chiarimenti, l’aspetto più interessante che salterà immediatamente all’attenzione del lettore sarà il fatto di aver potuto utilizzare una normale query SQL per recuperare i dati, altro elemento interessante sarà poi, la notazione utilizzata per specificare il nome del foglio all’interno della cartella di lavoro.

A questo punto poniamo termine agli indugi e presentiamo un codice completo grazie al quale accedere ad una serie di dati relativi ai contatti memorizzati all’interno di una rubrica telefonica.

package excelreader;

import java.sql.*;

/**

* @author Alessandro Grande

public class ExcelReader {

/**

* @param args the command line arguments

public static void main(String[] args) {

Connection excelConnection = null;

Statement stmt = null;

try

{

Class.forName( “sun.jdbc.odbc.JdbcOdbcDriver” );

excelConnection = DriverManager.getConnection(“jdbc:odbc:mio_excel”, “”, “”);

stmt = excelConnection.createStatement();

String query = “SELECT * from [rubrica$];”; //l’argomento tra parentesi rappresenta il nome del foglio

ResultSet rs = stmt.executeQuery(query);

System.out.println( “Sono state individuate le seguenti entry”);

while( rs.next() )

{

System.out.printf( “info per contatto %s %s telefono: %s cellulare %s mail %s\n”,rs.getString(“nome”),rs.getString(“cognome”),rs.getString(“telefono”),

rs.getString(“cellulare”),rs.getString(“email”) );

}

catch( ClassNotFoundException | SQLException e )

{

System.err.println( e );

}

finally

{

try

{

stmt.close();

excelConnection.close();

}

catch( Exception e )

{

System.err.println( e );

}

Nel codice precedente sono state evidenziate tre sezioni di codice particolari:

Nella prima notiamo come si faccia uso del nome da noi scelto quanto si è definite il DSN utente si noti come in questo caso non sia stato necessario specificare né un username, né una password a differenza di quanto avverrebbe nel caso in cui la connessione odbc fosse riferita ad un norma DB;
L’elemento successivo è relativo invece alla formulazione della query per la selezione dei dati, notiamo infatti come si sia reso necessario l’utilizzo di una notazione particolare per specificare il nome del foglio, in particolare questo significa che se non si fosse attribuito un nome specifico al foglio all’interno della cartella di lavoro si sarebbe dovuta adottare la notazione [Foglio1$] in quanto in questa guida si fa uso della versione italiana di Excel per le altre lingue si dovrà, chiaramente, impiegare una label diversa, inoltre presente come l’indicazione del nome per il foglio da utilizzare quale fonte sia case insensitive quindi è possibile scrivere indifferentemente Rubrica$, RubricA$, RUBRICA$ ecc senza incorrere in errori,

Esportare dati da un foglio Excel

Nel paragrafo precedente è stato descritto in che modo è possibile leggere dei dati da un foglio Excel® utilizzando un DSN definito in precedenza, nelle prossime sezioni affronteremo le problematiche connesse all’operazione di export, ossia vedremo in che modo è possibile salvare i dati presenti all’interno di un un foglio Excel® rispettivamente all’interno di:

una tabella MySQL,
un file CSV
un file XML

Per quel che concerne il salvataggio dei dati all’interno del DB supporremo l’esistenza di una db denominato “Dati_Esportati” all’interno del quale è stata definita una Tabella denominata “rubrica_excel” la quale presenta la seguente struttura:

id	Int	Chiave primaria
nome	Varchar(50)	Nome del contatto
nognome	Varchar(50)	Cognome del conttatto
telefono	Varcahr(15)	Numero telefono fisso
cellulare	Varchar(15)	Numero telefono cellulare
email	Varchar(60);	Email personale

Per quel che concerne il file CSV si supporrà che i singoli field siano separati l’uno dall’altro mediante l’uso del simbolo tabulazione.

Infine per quel concerne il file XML ogni record sarà organizzato in conformità al seguente schema:

</rubrica>

Da un punto di vista strutturale il progetto è stato organizzato seguendo i dettami del pattern MVC, il. che ci ha garantito di separare la logica di business, da quella di presentazione

Esportazione dei dati all’interno di un Database MySql

Come prima cosa saranno descritte le classi grazie alle quali effettuare la lettura dei dati dal foglio Excel® ed aprire una connessione verso il DB, in ambo i casi per le implementazioni delle classi si farà uso del pattern Singleton.

Classe ExcelReader

La classe riportata in questa sezione ha come scopo il recupero dei dati dal foglio Excel®

* To change this template, choose Tools | Templates

* and open the template in the editor.

package excelexporter.model;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.HashMap;

import java.util.logging.Level;

import java.util.logging.Logger;

/**

* La classe ExcelREader è stata definita seguendo i dettami del design pattern

* Singleton

* @author Alessandro Grande

public class ExcelReader {

private static Connection excelConnection;

private static ExcelReader singleton;

private ExcelReader() {

try {

Class.forName( “sun.jdbc.odbc.JdbcOdbcDriver” );

excelConnection = DriverManager.getConnection(“jdbc:odbc:mio_excel”, “”, “”);

} catch (SQLException | ClassNotFoundException ex) {

Logger.getLogger(ExcelReader.class.getName()).log(Level.SEVERE, null, ex);

}

public static HashMap<Integer,ExcelRecordInfo> getInfo() {

HashMap<Integer,ExcelRecordInfo> excelInfo = new HashMap<>();

if(singleton==null) {

singleton = new ExcelReader();

Statement stmt;

String query = “SELECT * from [rubrica$];”; //l’argomento tra parentesi rappresenta il nome del foglio

try {

stmt = excelConnection.createStatement();

ResultSet rs = stmt.executeQuery(query);

int index=1;

while(rs.next()) {

excelInfo.put(new Integer(index), new ExcelRecordInfo(rs.getString(“nome”),rs.getString(“cognome”),rs.getString(“telefono”),rs.getString(“cellulare”),rs.getString(“email”)));

index++;

}

} catch (SQLException ex) {

Logger.getLogger(ExcelReader.class.getName()).log(Level.SEVERE, null, ex);

}

return excelInfo;

}

Osservando il codice appena riportato, notiamo come questo sia una riproposizione di quanto presentato in precedenza quando si è descritto in che modo è possibile leggere e stampare a video i dati memorizzati all’interno di un foglio Excel, la differenza rispetto a quanto presentato in precedenza è che ora i dati sono stati memorizzati all’interno di un hashmap.

La classe successiva è quella deputata alla creazione della connessione verso il db:

Classe MySqlConnection

* To change this template, choose Tools | Templates

* and open the template in the editor.

package excelexporter.controller;

import excelexporter.model.DBInfo;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.util.logging.Level;

import java.util.logging.Logger;

/**

* Classe deputata alla creazione di una nuova connessione verso il db

* per la sua definizione si è fatto uso del design pattern

* Singleton

* @author Alessandro Grande

public class MySqlConnection {

private static MySqlConnection singleton;

private static Connection dbConnection;

private MySqlConnection() {

try {

Class.forName(“com.mysql.jdbc.Driver”);

dbConnection = DriverManager.getConnection(“jdbc:mysql://”+DBInfo.DBHOST+”/”+DBInfo.DBNAME,DBInfo.DBUSER,DBInfo.DBPASSWORD);

} catch (SQLException | ClassNotFoundException ex) {

Logger.getLogger(MySqlConnection.class.getName()).log(Level.SEVERE, null, ex);

}

public static Connection getConnection() {

if (singleton==null) {

singleton = new MySqlConnection();

}

return dbConnection;

}

Relativamente al codice appena presentato va precisato che all’interno della classe denominata DBINFO sono state inserite tutte le informazioni necessarie alla corretta gestione dell’applicazione, in particolare in sessa sono stati definiti i seguenti campi:

nome del db
nome dell’host su cui è in esecuzione il dbms
nome dell’utente
password dell’utente
nome della tabella target

Il risultato di questa scelta è di definire un unico elemento da modificare laddove sorgesse la necessità di alterare le informazioni necessarie all’applicazione, ad esempio se il db fosse spostato su di un server differente, l’unica modifica da apportare sarebbe fatta sul file DBINFO senza quindi essere costretti ad dover modificare singolarmente i file che utilizzano questa informazione.

Visivamente il file DBINFO si compone nel modo seguente:

* To change this template, choose Tools | Templates

* and open the template in the editor.

package excelexporter.model;

/**

* @author Alessandro Grande

public class DBInfo {

public final static String DBNAME=”dati_esportati”;

public final static String DBUSER=”excel_user”;

public final static String DBPASSWORD=”excel”;

public final static String DBHOST=”localhost”;

public final static String EXCELTABLE=”rubrica_excel”;

}

A questo punto dopo aver definito le classi di supporto all’applicazione veniamo alla definizione di quella deputata ad effettuare la scrittura dei dati sul db da noi definito in precedenza.

Classe DBWriter

* To change this template, choose Tools | Templates

* and open the template in the editor.

package excelexporter.controller;

import excelexporter.model.DBInfo;

import excelexporter.model.ExcelRecordInfo;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.SQLException;

import java.util.HashMap;

import java.util.Iterator;

/**

* @author Alessandro

public class DBWriter {

private static Connection dbConnection;

private static DBWriter manager;

private DBWriter(Connection con) {

DBWriter.dbConnection = con;

}

public static void writer(HashMap<Integer,ExcelRecordInfo> data,Connection conn) throws SQLException {

if (manager==null) {

manager = new DBWriter(conn);

}

PreparedStatement stmt ;

String insertStatement = “INSERT INTO “+DBInfo.EXCELTABLE+”(nome,cognome,telefono,cellulare,email) VALUES(?,?,?,?,?)”;

stmt = dbConnection.prepareStatement(insertStatement);

Iterator<Integer> iter = data.keySet().iterator();

Integer key;

while(iter.hasNext()) {

key = iter.next();

stmt.setString(1, data.get(key).nome);

stmt.setString(2, data.get(key).cognome);

stmt.setString(3, data.get(key).telefono);

stmt.setString(4, data.get(key).cellulare);

stmt.setString(5, data.get(key).email);

stmt.executeUpdate();

}

Il codice appena riportato non presenta particolari difficoltà, l’unica particolarità è rappresentata dal fatto di aver utilizzato i PreparedStatement il cui utilizzo consente di ottenere migliori prestazioni e consente di proteggere le applicazioni da attacchi come le SQL Injection.

Esportazione dei dati all’interno di un file CSV

L’esportazione dei dati all’interno di un file CSV non presenta particolari difficoltà, si tratta infatti di definire una classe il cui unico scopo è quello di leggere i dati memorizzati all’interno dello hashmap costruito grazie al metodo getInfo definito all’interno della classe ExcelReader, per farlo fare uso di un normalissimo Iterator, per quanto riguarda il salvataggio questo avverrà all’interno di un file denominato dati_excel.csv;

* To change this template, choose Tools | Templates

* and open the template in the editor.

package excelexporter.controller;

import excelexporter.model.ExcelRecordInfo;

import java.io.BufferedWriter;

import java.io.File;

import java.io.FileNotFoundException;

import java.io.FileWriter;

import java.io.IOException;

import java.util.HashMap;

import java.util.Iterator;

import java.util.logging.Level;

import java.util.logging.Logger;

/**

* Così come già avvenuto per la classe DBWriter la classe grazie alla quale effettuare

* l’operazione di scrittura sarà definita sarà conforme alle regole del pattern Singleton

* @author Alessandro GRande

public class CSVWriter {

private static CSVWriter writer;

private static BufferedWriter csvFileWriter;

private CSVWriter() throws FileNotFoundException, IOException {

File csvFile = new File(“./dati_excel.csv”);

if(!csvFile.exists()) {

csvFile.createNewFile();

}

csvFileWriter = new BufferedWriter(new FileWriter(csvFile,true));

}

public static void writer(HashMap<Integer,ExcelRecordInfo> data) throws IOException {

if(writer == null) {

writer = new CSVWriter();

}

String record;

try {

Iterator<Integer> iter = data.keySet().iterator();

int key;

while(iter.hasNext()) {

key=iter.next();

record=data.get(key).nome+’\t’+data.get(key).cognome+’\t’+data.get(key).telefono+’\t’+data.get(key).cellulare+’\t’+

data.get(key).email;

csvFileWriter.write(record);

csvFileWriter.newLine();

}

} catch (FileNotFoundException ex) {

Logger.getLogger(CSVWriter.class.getName()).log(Level.SEVERE, null, ex);

} catch (IOException ex) {

Logger.getLogger(CSVWriter.class.getName()).log(Level.SEVERE, null, ex);

} finally {

csvFileWriter.close();

}

Come possiamo osservare l’operazione di scrittura avverrà in modalità append in questo modo eventuali dati salvati in precedenza non andranno perso. Resta a questo punto da esaminare solo l’operazione di salvataggio su di un file XML, cosa quest’ultima di cui ci occuperemo nella prossima sezione.

Esportazione dei dati in formato XML

Il salvataggio dei dati in formato XML nel caso del nostro esempio non presenta particolari difficoltà, non fosse altro che nella nostra formulazione della soluzione si è evitato di definire un file schema, l’output che sarà generato dal nostro codice sarà comunque un file xml valido, il che ci darà la possibilità di importare le nostre informazioni anche in altri applicativi.

Per la realizzazione della classe deputata ad effettuare l’operazione di salvataggio ci avvarremo delle funzionalità offerte dal Parser DOM implementate direttamente all’interno dello SDK, detto questo vediamo in che modo si realizza il nostro codice.

package excelexporter.controller;

import excelexporter.model.ExcelRecordInfo;

import java.io.File;

import java.io.IOException;

import java.util.HashMap;

import java.util.Iterator;

import javax.xml.parsers.DocumentBuilder;

import javax.xml.parsers.DocumentBuilderFactory;

import javax.xml.parsers.ParserConfigurationException;

import javax.xml.transform.Transformer;

import javax.xml.transform.TransformerConfigurationException;

import javax.xml.transform.TransformerException;

import javax.xml.transform.TransformerFactory;

import javax.xml.transform.dom.DOMSource;

import javax.xml.transform.stream.StreamResult;

import org.w3c.dom.Document;

import org.w3c.dom.Element;

/**

* @author Alessandro

public class XMLWriter {

private static XMLWriter writer;

private static Document doc;

private static Element rootElement;

private XMLWriter() throws ParserConfigurationException {

DocumentBuilderFactory docFactory = DocumentBuilderFactory.newInstance();

DocumentBuilder docBuilder = docFactory.newDocumentBuilder();

doc = docBuilder.newDocument();

rootElement = doc.createElement(“rubrica”);

doc.appendChild(rootElement);

}

public static void writer(HashMap<Integer,ExcelRecordInfo> data) throws ParserConfigurationException, TransformerConfigurationException, IOException, TransformerException {

if(writer==null) {

writer = new XMLWriter();

}

Iterator<Integer> iter = data.keySet().iterator();

int key;

while(iter.hasNext()) {

key = iter.next();

Element contatto = doc.createElement(“Contatto”);

contatto.setAttribute(“id”, Integer.toString(key));

contatto.appendChild(createNewNode(“nome”,data.get(key).nome));

contatto.appendChild(createNewNode(“conome”,data.get(key).cognome));

contatto.appendChild(createNewNode(“telefono”,data.get(key).telefono));

contatto.appendChild(createNewNode(“cellulare”,data.get(key).cellulare));

contatto.appendChild(createNewNode(“email”,data.get(key).email));

rootElement.appendChild(contatto);

}

TransformerFactory transformerFactory = TransformerFactory.newInstance();

Transformer transformer = transformerFactory.newTransformer();

DOMSource source = new DOMSource(doc);

File file = new File(“./rubrica_excel.xml”);

if(file.exists()) {

file.delete();

}

file.createNewFile(); //per garantire la validità dei dati si è costretti a creare ogni volta ex-novo il file xml

StreamResult result = new StreamResult(file);

transformer.transform(source, result);

}

/**

* Metodo a cui si assegna il compito di creare una nuova entry all’interno

* del nodo corrente

* @param label etichetta utilizza per identificare l’elemento

* @param value valore asseganto all’elemento

* @return nuovo elemento da insierire all’interno del nodo corrente

private static Element createNewNode(String label,String value) {

Element newElement= doc.createElement(label);

newElement.appendChild(doc.createTextNode(value));

return newElement;

}

Con quest’ultimo codice concludiamo la nostra dissertazione relativa ad alcuni dei modi con cui è possibile esportare una serie di dati memorizzati all’interno di un foglio Excel®.