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);

}

}

/**

*

* @author Alessandro

*/

public class ReaderExcel {

public static void main(String[] args)  {

ExcelReaderGUI gui = new ExcelReaderGUI();

gui.setLocationRelativeTo(null);

gui.setVisible(true);

}

}

*/

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

}