Lavorare con Excel utilizzando l’api Jexcel®

di - 16 maggio 2012 in Programmazione

 

Nel precedente articolo si è descritto in che modo fosse possibile interfacciarsi con un foglio Excel® utilizzando una connessione jdbc, ora vedremo come, la medesima operazione possa essere realizzata utilizzando la libreria  JExcel®, la quale consente di operare con le versioni di Excel® fino alla 2003.

Elenchiamo di seguito alcune delle sue caratteristiche principali:

  • Lettura dei dati a partire da Excel® fino alla versione 2003;
  • Possibilità di inserire formule, questa operazione è però applicabile solo a partire dalla versione 97;
  • Possibilità di creare fogli compatibili in formato Excel® 2000;
  • Supporta ai font, numeri e date;
  • Possibilità di modificare fogli esistenti;

Detto questo la prima operazione da svolgere riguarda il download dell’api, la quale può essere scaricata a questo link:

Excel_Certificazione

A questo punto creiamo un nuovo progetto, per il nostro articolo utilizzeremo Netbeans®, dopo di che nella sezione relative alle librerie includiamo l’archivio jar associato alla nostra api:

 

 

 

 

 

 

 

 

 

 

 

Il file da importare è jxl.jar. Al completamento di questa operazione ci dovremmo trovare di fronte ad una situazione simile a quella descritta dalla prossima immagine:

 

 

 

 

 

 

 

A questo punto siamo pronti a realizzare la nostra prima applicazione, in particolare cercheremo  di creare un nuovo workbook, all’interno del quale inseriremo anche alcuni dati in particolare di una prima serie faremo la somma mentre la seconda serie di dati diventerà l’input per la funzione media.

 

package exceltest;import java.io.File;import java.io.IOException;

import java.util.Locale;

import java.util.Scanner;

 

import jxl.CellView;

import jxl.Workbook;

import jxl.WorkbookSettings;

import jxl.format.UnderlineStyle;

import jxl.write.Formula;

import jxl.write.Label;

import jxl.write.Number;

import jxl.write.WritableCellFormat;

import jxl.write.WritableFont;

import jxl.write.WritableSheet;

import jxl.write.WritableWorkbook;

import jxl.write.WriteException;

import jxl.write.biff.RowsExceededException;

/**

*

* @author Alessandro

*/

public class ExcelTest {

 

private WritableCellFormat timesBoldUnderline;

private WritableCellFormat arial;

private String inputFile;

public void setOutputFile(String inputFile) {

this.inputFile = inputFile;

}

 

public void write() throws IOException, WriteException {

File file = new File(inputFile);

if(!file.exists())

file.createNewFile();

WorkbookSettings wbSettings = new WorkbookSettings();

wbSettings.setLocale(new Locale(“it”, “IT”));

WritableWorkbook workbook = Workbook.createWorkbook(file, wbSettings);

/*

* Creo un workbook con 4 fogli denominati rispettivamente

* Foglio 1, Foglio 2, Foglio 3 e Foglio 4

*/

for(int index =0; index <4; index++)

workbook.createSheet(“Foglio “+(index+1), index); // crea un nuovo foglio all’interno del workbook, il secondo valore indica il numero del foglio

WritableSheet foglioExcel = workbook.getSheet(0);

createLabel(foglioExcel);

createContenutoNumerico(foglioExcel);

foglioExcel = workbook.getSheet(1);

creaContenutoTestuale(foglioExcel);

workbook.write();

workbook.close();

}

 

private void createLabel(WritableSheet sheet) throws WriteException {

// Lets create a times font

WritableFont wfont = new WritableFont(WritableFont.ARIAL, 10);

// Define the cell format

arial = new WritableCellFormat(wfont);

// A capo automatico

arial.setWrap(true);

WritableFont times10ptBoldUnderline = new WritableFont(

WritableFont.TIMES, 10, WritableFont.BOLD, false,

UnderlineStyle.SINGLE);

timesBoldUnderline = new WritableCellFormat(times10ptBoldUnderline);

// Lets automatically wrap the cells

timesBoldUnderline.setWrap(true);

CellView cv = new CellView();

cv.setFormat(arial);

cv.setFormat(timesBoldUnderline);

cv.setAutosize(true);

 

// Scrittura delle intestazioni

addCaption(sheet, 0, 0, “Prima Serie di Valori”);

addCaption(sheet, 1, 0, “Seconda Serie di Valori”);

 

}

 

/**

* Metdo deputato al popolamento di uno dei fogli inseriti all’interno del workbook

* @param sheet foglio in cui inserire i nuovi dati

* @throws WriteException

* @throws RowsExceededException

*/

private void createContenutoNumerico(WritableSheet sheet) throws WriteException,

RowsExceededException {

// inserisco una serie di valori

StringBuffer buf = new StringBuffer();

buf.append(“RAND()*100”); //genero numeri nell’intervallo [0..100]

System.out.println(buf.toString());

Formula f;

for (int i = 1; i < 10; i++) {

f = new Formula(0,i,buf.toString());

// First column

sheet.addCell(f);

//addNumber(sheet, 0, i, generatoreCasuale.nextInt(365));

// Second column

addNumber(sheet, 1, i, i * i);

}

// calcolo la somma dei valori generati

buf = new StringBuffer();

buf.append(“SUM(A2:A10)”);

addLabel(sheet,0,10,”Somma dei valori”);

f = new Formula(0, 11, buf.toString());

sheet.addCell(f);

buf = new StringBuffer();

buf.append(“AVERAGE(B2:B10)”);

addLabel(sheet,1,10,”Media dei valori”);

f = new Formula(1, 11, buf.toString());

sheet.addCell(f);

 

}

/**

* Metodo deputato all’inserimento del solo contenuto testuale all’interno di un foglio che vine

* indicato come argomento del metodo

* @param sheet

* @throws WriteException

* @throws RowsExceededException

*/

private void creaContenutoTestuale(WritableSheet sheet) throws WriteException,RowsExceededException {

// Come inserire del testo all’interno delle celle

for (int i = 1; i < 20; i++) {

// Prima colonna

addLabel(sheet, 0, i, “Salve Salvino”);

// Second column

addLabel(sheet, 1, i, “Certo certosino”);

}

}

private void addCaption(WritableSheet sheet, int column, int row, String s)

throws RowsExceededException, WriteException {

Label label;

label = new Label(column, row, s, timesBoldUnderline);

sheet.addCell(label);

}

 

private void addNumber(WritableSheet sheet, int column, int row,

Integer integer) throws WriteException, RowsExceededException {

Number number;

number = new Number(column, row, integer, arial);

sheet.addCell(number);

}

 

private void addLabel(WritableSheet sheet, int column, int row, String s)

throws WriteException, RowsExceededException {

Label label;

label = new Label(column, row, s, arial);

sheet.addCell(label);

}

 

public static void main(String[] args) throws WriteException, IOException {

String nomeFile =””;

Scanner lettore = new Scanner(System.in);

do {

System.out.print(“Indicare il nome del file da utilizzare per memorizzare i dati:”);

nomeFile = lettore.nextLine();

} while(nomeFile.isEmpty());

ExcelTest test = new ExcelTest();

test.setOutputFile(nomeFile+”.xls”);

test.write();

System.out.printf(“Per verificare il risultato prodotto aprire il file %s.xls\n”,nomeFile);

}

}

 

Se il codice precedente ha consentito di inserire una serie di dati all’interno di un workbook, con il prossimo esempio descriveremo il modo in cui è possibile eseguire la lettura di una serie di dati da un foglio precedentemente salvato.

Launcher dell’applicazione

package readerexcel;

/**

*

* @author Alessandro

*/

public class ReaderExcel {

public static void main(String[] args)  {

ExcelReaderGUI gui = new ExcelReaderGUI();

gui.setLocationRelativeTo(null);

gui.setVisible(true);

}

 

}

 

Classe deputata alla costruzione dell’interfaccia grafica

/** To change this template, choose Tools | Templates* and open the template in the editor.

*/

package readerexcel;

 

import java.io.File;

import java.io.IOException;

import java.util.logging.Level;

import java.util.logging.Logger;

import javax.swing.JFileChooser;

import jxl.Cell;

import jxl.CellType;

import jxl.Sheet;

import jxl.Workbook;

import jxl.read.biff.BiffException;

 

/**

*

* @author Alessandro

*/

public class ExcelReaderGUI extends javax.swing.JFrame {

 

/**

* Creates new form ExcelReaderGUI

*/

public ExcelReaderGUI() {

initComponents();

int returnVal = selettoreFile.showOpenDialog(null);

 

if (returnVal == JFileChooser.APPROVE_OPTION) {

setInputFile(selettoreFile.getSelectedFile().getPath());

}

}

private String inputFile;

 

private void setInputFile(String inputFile) {

this.inputFile = inputFile;

}

/**

* This method is called from within the constructor to initialize the form.

* WARNING: Do NOT modify this code. The content of this method is always

* regenerated by the Form Editor.

*/

@SuppressWarnings(“unchecked”)

// <editor-fold defaultstate=”collapsed” desc=”Generated Code”>

private void initComponents() {

 

jLabel1 = new javax.swing.JLabel();

jButton1 = new javax.swing.JButton();

jScrollPane1 = new javax.swing.JScrollPane();

pannelloDati = new javax.swing.JTextArea();

 

setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE);

 

jLabel1.setText(“Dati Letti da foglio excel”);

 

jButton1.setText(“Carica Dati”);

jButton1.addActionListener(new java.awt.event.ActionListener() {

public void actionPerformed(java.awt.event.ActionEvent evt) {

jButton1ActionPerformed(evt);

}

});

 

pannelloDati.setColumns(20);

pannelloDati.setRows(5);

jScrollPane1.setViewportView(pannelloDati);

 

javax.swing.GroupLayout layout = new javax.swing.GroupLayout(getContentPane());

getContentPane().setLayout(layout);

layout.setHorizontalGroup(

layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)

.addGroup(layout.createSequentialGroup()

.addContainerGap()

.addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)

.addGroup(layout.createSequentialGroup()

.addComponent(jScrollPane1)

.addContainerGap())

.addGroup(layout.createSequentialGroup()

.addComponent(jLabel1)

.addContainerGap())

.addGroup(javax.swing.GroupLayout.Alignment.TRAILING, layout.createSequentialGroup()

.addGap(0, 565, Short.MAX_VALUE)

.addComponent(jButton1)

.addGap(100, 100, 100))))

);

layout.setVerticalGroup(

layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)

.addGroup(layout.createSequentialGroup()

.addContainerGap()

.addComponent(jLabel1)

.addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)

.addComponent(jScrollPane1, javax.swing.GroupLayout.PREFERRED_SIZE, 353, javax.swing.GroupLayout.PREFERRED_SIZE)

.addGap(41, 41, 41)

.addComponent(jButton1)

.addContainerGap(31, Short.MAX_VALUE))

);

 

pack();

}// </editor-fold>

 

private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {

try {

read();

} catch (IOException ex) {

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

}

}

public void read() throws IOException  {

File inputWorkbook = new File(inputFile);

Workbook w;

try {

w = Workbook.getWorkbook(inputWorkbook);

// Recupero il primo foglio del workbook

Sheet sheet = w.getSheet(0);

for (int j = 0; j < sheet.getColumns(); j++) {

for (int i = 0; i < sheet.getRows(); i++) {

Cell cell = sheet.getCell(j, i);

CellType type = cell.getType();

if (type == CellType.LABEL)

pannelloDati.setText(pannelloDati.getText()+” – “+cell.getContents()+” – “);

if (type == CellType.NUMBER)

pannelloDati.setText(pannelloDati.getText()+’\n’+cell.getContents()+’\n’);

}

}

pack();

} catch (BiffException e) {

}

}

JFileChooser selettoreFile = new JFileChooser();

// Variables declaration – do not modify

private javax.swing.JButton jButton1;

private javax.swing.JLabel jLabel1;

private javax.swing.JScrollPane jScrollPane1;

private javax.swing.JTextArea pannelloDati;

// End of variables declaration

}

Share

Promozioni

Potrebbe interessarti



Commenti

  • Carmelo

    Ciao, complimenti per l’articolo.

    Solo una cosa però non mi è chiara: come faccio a leggere e poi inserire in un database i dati presenti in un foglio Excel?

    Scusa per il disturbo

    Carmelo

  • Alessandro

    Ciao Carmelo
    Dato che il metodo getContents() ti restituisce il contenuto della cella corrente, l’unico problema è quello di comporre la querystring,
    quindi ad esempio la query iniziale potrebbe essere
    String query= “INSERT INTO NOME_TABELLA [LISTA_FIELD] VALUES (“;
    A questo punto all’interno del ciclo in cui reuperi i dati puoi scrivere qualcosa del tipo
    query+=cell.getContents();
    Al termine del ciclo inserisci la
    sequenza di comandi
    query+=”)”;
    stmt.executeUpdate(sql);
    dove la prima istruzione ti completa la scrittura della query mentre la seconda fa riferimento ad una variabile di tipo Statement associata ad un connessione JDBC.
    Spero di aver chiarito in parte il dubbio

  • Alessandro

    Ciao Carmelo,
    per completare la risposta precedente puoi anche pensare di utilizzare una connessione odbc per il foglio excel, questo ti da la possibilità di utilizzare il workbook alla stregua di un db ricorda solo che per accedere ai fogli devi usare la seguente scrittura [nomeFoglio$], di seguito comunque ti riporto un codice di esempio:
    package excelreader;

    import java.sql.*;

    /**
    *
    * @author Alessandro
    */
    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:rubrica”, “”, “”);
    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 );
    }
    }
    }
    }
    A questo punto all’interno del ciclo while in cui leggo i dati tu puoi comporre la query con i dati estratti dal foglio dopo di che tramite una executeUpdate puoi effettuare la scrittura sul db dei dati che hai estratto dal foglio.
    Spero di essere riuscito a chiarire al meglio i tuoi dubbi.
    Alessandro

    • Andrea

      Ciao Alessandro e complimenti per l’utile articolo scritto in maniera semplice ed efficace.
      Volevo da te un suggerimento…se possibile. Vorrei scrivere un’applicazione java che legga un file excel in cui è presente un grafico e possa modificare determinati valori, su cui dipende il grafico, in modo da modificare il grafico stesso. Sapresti darmi dei suggerimenti? Tra l’altro credo che jexcel non supporti la creazione diretta di grafici da java, vero?
      Ti ringrazio in anticipo.
      Ciao!

      • Alessandro

        Ciao Andrea,
        in primis grazie per i commenti, per quanto riguarda le tue richieste, passo in breve a descriverti come puoi effettuare la modifica dei contenuti di un foglio excel.
        Come prima operazione è necessario aprire il foglio che desideri modificare in modalità scrittura, questo si realizza mediante la seguente sequenza di comandi:
        Workbook workbook = Workbook.getWorkbook(new File(“myfile.xls”));
        con questo prima operazione apri il file, per renderlo utilizzabile in modoalità scrittura il comando da scrivere sarà:
        WritableWorkbook copy = Workbook.createWorkbook(new File(“output.xls”), workbook);
        ora dopo aver ottenuta una copia scrivibile del file possiamo ottenere il foglio su cui vogliamo operare, ti ricordo che la numerazione parte da 0, quindi il comando seguente ti consente di ottenere il secondo foglio del workbook;
        WritableSheet sheet2 = copy.getSheet(1);
        Ora dopo avere ottenuto l’intero foglio per accedere ad una specifica cella se ne dovrà fornire numero di riga e colonna anche in questo caso gli indici partono da 0.
        WritableCell cell = sheet2.getWritableCell(1, 2);
        Il comando appena riportato ti consente di ottenere l’elemento di posizione B3
        A questo punto dopo avere ottenuto la cella puoi invocare il metodo setValue per modificare il valore della cella è importate però prima di questa operazione capire il tipo di cella con cui si opera pertanto è necessario che si effettui prima il seguente test
        if(cell.getType()== CellType.XXXX)
        dopo di che si deve effettuare un’operazione di cast sulla cella quindi se ad esempio voglio modificare il contenuto di una cella numerica allora scriverò la seguente serie di istruzioni
        if(cell.getType()==CellType.Number)
        {
        Number numero = (Number)cell;
        numero.setValue();
        }
        Discorso simile vale anche per gli altri tipi questo quindi significa che si ha anche la possibilità di modificare una formula già esistente
        A questo punto per salvare le le modifiche invocheremo in sequenza i metodi write e close sull’oggetto WritableWorkbook che si era creato subito dopo avere aperto lo strem vero il foglio excel.
        Infine per quanto rigurda la tua seconda richiesta, ossia quella relativa al supporto dei grafici, al momento il supporto risulta estremamente rudimentale e quindi personalmente ti suggerisco di non utilizzarlo.
        Per concludere in altrenativa alla libreria jexcel per modificare i dati contenuti all’interno di un foglio puoi utilizzare anche una connessione odbc in questo modo tutte le operazioni sul foglio possono essere espresse per mezzo di espressioni sql.
        Spero che questa mia spiegazione ti sia stata utile.
        Ciao Alessandro

        • Andrea

          Ti ringrazio per la tua risposta, chiara e veloce. Un’ulteriore domanda, se posso, riguardo alla possibilità di sfruttare un JDBC-ODBC bridge per collegarmi ad un file excel. Se in un file ho n fogli ed in ognuno avessi dei dati da voler modificare dinamicamente, dovrei registrare un datasource (assegnando un nome) per ogni foglio?
          Ciao e grazie ancora!

  • Alessandro

    Ciao Andrea,
    Devi definire il datasource solo per il workbook per accedere ai foglio nella query dovrai inserire il riferimento al foglio
    Ciao Alessandro

    • Andrea

      Gentilissimo Alessandro, tutto chiaro.

      Grazie!
      Ciao.

  • Alessandro

    É stato un piacere

  • Buongiorno sono un programmatore cobol ed uso un compilatore che crea delle classi java.
    Ora dovrei interagire con excel usando jexcelapi . Le funzioni che mi occorrono sono elementari ma non so dove mettere le mani , qualcuno puo aiutarmi.
    grazie
    Antonio

  • Alessandro

    Salve Antonio dovresti essere un pò più specifico, in quanto se hai bisogmo solo di inserire/cancellare/modificare dei dati all’interno di un foglio, la scelta più semplice potrebbe essere quella di utilizzare una connessione odbc/jdbc e quindi interagire con il foglio utilizzando direttamente sql, in questo modo eviteresti anche di utilizzare api esterne.
    Alessandro

  • Alessandro

    Da quello che ho potuto capire, per conseguire la certificazione SCJP ora chiamata OCPJP non sono necessari i due esmi per la certificazione OCA

  • Andrea

    Salve a tutti, io ho un problema con la classe, non so se mi potete aiutare, quando importo molte stringhe hanno un carattere strano fatto con un ? nel centro. Ho letto hce è un carattere non riconosciuto, sapete come posso risolverla? Grazie.