THIS IS A TEST INSTANCE. ALL YOUR CHANGES WILL BE LOST!!!!
| Panel | ||||
|---|---|---|---|---|
| ||||
|
Prerequisites
This
...
HOW-TO
...
assumes
...
that
...
you
...
have
...
some
...
working
...
knowledge
...
of
...
Wicket,
...
Models
...
and
...
some
...
basic
...
usage
...
of
...
JExcel
...
API.
...
JExcel
...
API
...
can
...
be
...
downloaded
...
at
...
...
...
...
In this HOW-TO,
...
we
...
will
...
upload
...
an
...
Excel
...
File
...
using
...
wicket
...
File
...
upload
...
and
...
then
...
render
...
the
...
Excel
...
spreadsheet
...
as
...
an
...
HTML
...
table
...
of
...
labels.
...
1.
...
Create
...
a
...
POJO
...
to
...
represent
...
the
...
basic
...
attributes
...
of
...
the
...
uploaded
...
Excel
...
Spreadsheet.
...
The
...
Number
...
of
...
columns
...
and
...
rows.
...
This
...
makes
...
binding
...
operations
...
within
...
Wicket
...
Easy
...
and
...
also
...
makes
...
the
...
Display
...
Table
...
auto
...
re
...
render
...
with
...
new
...
uploaded
...
sheets.
| Code Block |
|---|
} private class SheetMetaData implements Serializable { {panel} private int cols = 0; private int rows = 0; public int getCols() { return cols; } public void setCols(int cols) { this.cols = cols; } public int getRows() { return rows; } public void setRows(int rows) { this.rows = rows; } } {panel} {code} |
2.
...
Create
...
a
...
WebPage
...
holding
...
the
...
file
...
upload
...
form
...
and
...
the
...
expected
...
table
| Code Block |
|---|
{code} {panel} transient Sheet sheet; // an instance of an Excel WorkSheet {panel} {panel} Excel WorkSheet private SheetMetaData meta; public UploadExcel() { meta = new SheetMetaData (); //init Sheet Meta Data {panel} {panel} //create a file upload field {panel} {panel} final FileUploadField fup = new FileUploadField("fileUp"); {panel} {panel} //create a form {panel} {panel} Form f = new Form("uploadform"){ {panel} {panel} public void onSubmit(){ {panel} {panel} //JExcel API Sheet Processing Logic {panel} {panel} Workbook wkb; {panel} {panel} FileUpload fupload = fup.getFileUpload(); {panel} {panel} if(fupload != null) if(fupload.getContentType().equalsIgnoreCase("application/vnd.ms-excel")) try { /* *Streams directly from File upload into Workbook.getWorkbook(Inputstream) */ wkb = Workbook.getWorkbook(fupload.getInputStream()); sheet = wkb.getSheet(0); //get First Work Sheet /* *Sets Sheet meta data. The HTML table creation needs this object to know about the rows and columns */ meta.setRows(sheet.getRows()); meta.setCols(sheet.getColumns()); } catch (Exception ex) { } } }; f.setMultiPart(true); f.add(fup); {panel} {panel}); f.setMaxSize(Bytes.megabytes(5)); {panel} {panel} add(f); {panel} {panel} add(createMainGrid()); {panel} {panel} add(createHeadings()); {panel} } {code} |
4.
...
Create
...
the
...
Loop
...
that
...
will
...
generate
...
the
...
table
...
from
...
the
...
Uploaded
...
Spreadsheet
...
Data
...
Grid.
...
Actually
...
Wicket
...
Models
...
works
...
magic
...
and
...
using
...
models
...
makes
...
sure
...
that
...
your
...
table
...
re-draws
...
each
...
time
...
another
...
Excel
...
Spreadsheet
...
is
...
uploaded
...
within
...
the
...
same
...
session.
...
We
...
will
...
wrap
...
the
...
POJOs
...
we
...
have
...
created
...
above
...
into
...
models
...
to
...
use
...
for
...
creating
...
the
...
table.
...
HTML
...
for
...
rendering
...
the
...
Table
...
based
...
on
...
Wicket
...
Loop
...
Class:
| Code Block | ||||
|---|---|---|---|---|
| ||||
{code} <nowiki> {panel} <form wicket:id="uploadform"> <input wicket:id="fileUp" type="file" name="file" /> <input id="submit" type="submit" value="Upload Excel File" /> </form> {panel} <table> <!-- the Heading Part --> {panel} <tr> <td> <label>No.</label> </td> <td wicket:id="heading"> <label wicket:id="cellHead" ></label> </td> </tr> {panel} <!-- the Excel Grid Body --> {panel} <tr wicket:id="rows"> <td> <!-- the Row Numbering Part --> <label wicket:id="rowNo"></label> </td> {panel} <!-- the Cell Data Part --> {panel} <td wicket:id="cols"> <label wicket:id="cellData"></label> </td> </tr> </table> {panel} </nowiki> {code} In |
In Java:
...
For
...
the
...
main
...
data
...
grid:
| Code Block |
|---|
} /*generating rows using the Loop class and the PropertyModel with SheetMetaData instance works magic {panel} *We bound the numbers of rows stored in SheetMetaData instance to the Loop using PropertyModel. No table will * be displayed before an upload. */ private Loop createMainGrid(){ //We create a Loop instance and uses PropertyModel to bind the Loop iteration to ExcelMetaData "rows" value return new Loop("rows", new PropertyModel(meta,"rows")) { public void populateItem(LoopItem item) { final int row = item.getIteration(); //creates the row numbers {panel} {panel} item.add(new Label("rowNo" , new Model(String.valueOf(row)))); //We create an inner Loop instance and uses PropertyModel to bind the Loop iteration to ExcelMetaData "cols" value item.add(new Loop("cols", new PropertyModel(meta,"cols")) { {panel} {panel})) { public void populateItem(LoopItem item) { final int col = item.getIteration(); /*this model used for Label component gets data from cell instance * Because we are interacting directly with the sheet instance which gets updated each time * we upload a new Excel File, the value for each cell is automatically updated */ IModel model=new Model() { {panel} {panel} public Object getObject(Component c) { Cell cell = sheet.getCell(col, row); {panel} {panel} return cell.getContents(); {panel} {panel} }}; Label cellData = new Label("cellData", model); item.add(cellData); } }); } }; } {panel} {code} Creating Headings for the table to follow Spreadsheet naming convention A, B, C, . Z, AA, AB, etc. {code} {panel} |
Creating Headings for the table to follow Spreadsheet naming convention A, B, C, . Z, AA, AB, etc.
| Code Block |
|---|
private Loop createHeadings(){
return new Loop("heading", new PropertyModel(meta,"cols")) {
public void populateItem(final LoopItem item) {
int column = item.getIteration();
{panel}
{panel}
/** Copied from javax.swing.table.AbstractTableModel,
* to name columns using spreadsheet conventions:
* A, B, C, . Z, AA, AB, etc.
*/
String colheading = "";
for (; column >= 0; column = column / 26 - 1) {
colheading = (char)((char)(column%26)+'A') + colheading ;
}
item.add(new Label("cellHead", new Model(colheading )));
}
};
}
{panel}
{code}
h1. To Make SpreadSheet Editable
To use textfields and another form around the Excel HTML Table so that Instant Editing and persistence can take place on Submit. The basic construct of every spreadsheet is to store data (Numbers, String, Dates etc) in rows and columns. So we create class to model each cell in the spreadsheet. Simply a POJO. You can enhance this object to suit your own persistence requirements or Object data processing schemes
{code} |
To Make SpreadSheet Editable
To use textfields and another form around the Excel HTML Table so that Instant Editing and persistence can take place on Submit. The basic construct of every spreadsheet is to store data (Numbers, String, Dates etc) in rows and columns. So we create class to model each cell in the spreadsheet. Simply a POJO. You can enhance this object to suit your own persistence requirements or Object data processing schemes
| Code Block |
|---|
public class XCell implements Serializable { {panel} public XCell() { data = null; persist = Boolean.TRUE; } public XCell(long rowNo, long colNo, Object cellData) { setRowId(rowNo); setColId(colNo); setData(cellData); setPersist(Boolean.TRUE); } private long rowId; //cell row location private long colId; //cell column location private Object data; //cell data //create getters and setters {panel} } {panel} {code} {panel} |
2.
...
In
...
the
...
ExcelUpload
...
WebPage
...
discuessed
...
above,
...
add
...
this
...
to
...
the
...
class
...
members
| Code Block |
|---|
} private XCell[][] values; {code} |
This
...
is
...
a
...
multi
...
dimensional
...
array
...
that
...
will
...
automatically
...
fill
...
up
...
as
...
we
...
move
...
through
...
the
...
Excel
...
file.
...
3.
...
You
...
will
...
create
...
an
...
IModel
...
for
...
each
...
textfield
...
that
...
will
...
be
...
created
...
into
...
the
...
Table
...
Grid.
...
The
...
IModel
...
as
...
used
...
above
...
will
...
be
...
updated
...
to
...
be
...
backed
...
by
...
the
...
XCell
...
instance.
...
So
...
that
...
when
...
you
...
hit
...
submit,
...
the
...
XCell
...
instance
...
that
...
was
...
changed
...
will
...
be
...
updated
...
In
...
Java:
...
For
...
the
...
main
...
data
...
grid
...
with
...
TextFields
...
instead
...
of
...
Labels:
| Code Block |
|---|
{code} {panel} private Loop createMainGrid(){ return new Loop("rows", new PropertyModel(meta,"rows")) { public void populateItem(LoopItem item) { final int row = item.getIteration(); //creates the row numbers {panel} {panel} item.add(new Label("rowNo" , new Model(String.valueOf(row)))); //generating columns for each row and binding the inner cells of the Excel table to XCell instances in the array item.add(new Loop("cols", new PropertyModel(meta,"cols")) { {panel} {panel} public void populateItem(LoopItem item) { final int col = item.getIteration(); IModel model=new Model() { {panel} {panel} /*We will bind this IModel to each TextField and overide the setObjects and getObjects method *When the form is submitted, setObject is called, and then we assign the edited data. * */ public void setObject(Component c, Object o) { {panel} {panel} /* basically there is no need for this conditional statement here because the getObject is called * first which must have initiated the XCell instance within the array */ if(values[row][col] != null) {panel} {panel} values[row][col].setData(o); {panel} {panel} else {panel} {panel} values[row][col] = new XCell(row, col,o); } /* The getObject is what determines what you will see in each textfield when it renders as HTML. * It is called for each Textfield Creation.It is where we will create the instances within the XCell * array. We will retreive the Cell content from the sheet.getCell() and then assign its data into * XCell during XCell initialization. */ public Object getObject(Component c) { //at first creation before any edit and submit, the XCell instance in the array is null {panel} {panel} is null if(values[row][col] != null) {panel} {panel} return values[row][col].getData(); {panel} {panel} Cell cell = sheet.getCell(col, row); {panel} {panel} values[row][col] = new XCell(row, col,cell.getContents()); {panel} {panel} return values[row][col].getData(); } }; TextField tx = new TextField("cellField", model); item.add(tx); } }); } }; } {panel} {code} |
3.
...
Modify
...
the
...
HTML
...
of
...
the
...
Loop
...
and
...
add
...
a
...
TextField
...
instead
...
of
...
Label
| Code Block | ||||
|---|---|---|---|---|
| ||||
{code} <nowiki> {panel} <form wicket:id="uploadform"> <input wicket:id="fileUp" type="file" name="file" /> <input id="submit" type="submit" value="Upload Excel File" /> </form> {panel} <form wicket:id="gridform"> <table> <!-- the Heading Part --> {panel} <tr> <td> <label>No.</label> </td> <td wicket:id="heading"> <label wicket:id="cellHead" ></label> </td> </tr> {panel} <!-- the Main Grid Part --> {panel} <tr wicket:id="rows"> <td> <label wicket:id="rowNo"></label> </td> <td wicket:id="cols"> <!-- <label wicket:id="cellData"></label> --> <input type="text" wicket:id="cellField" /> </td> </tr> {panel} <!-- Submit Button added --> {panel} <tr> <td> <input wicket:id="submit" type="submit" value="Submit" /> </td> </tr> </table> {panel} </form> </nowiki> {code} |
4.
...
Modify
...
the
...
Java
...
Code
...
of
...
the
...
WebPage
...
Component
...
initialization
...
to
...
add
...
up
...
the
...
new
...
form
...
components.
...
Remember
...
to
...
re
...
create the
XCell array for each upload of a new Excel Sheet. This will be done in the onSubmit of the FileUpload Form.
| Code Block |
|---|
the {panel} XCell array for each upload of a new Excel Sheet. This will be done in the onSubmit of the FileUpload Form. {panel} {code} {panel} transient Sheet sheet; // an instance of an Excel WorkSheet {panel} /** *This multi dimension array will be used to map every generated textfield to a XCell instance. */ {panel} private XCell[][] values; {panel} {panel} private SheetMetaData meta; public UploadExcel() { meta = new SheetMetaData (); //init Sheet Meta Data values = new XCell[0][0]; //no-upload-yet init {panel} {panel} Form f = new Form("uploadform"){ {panel} {panel} public void onSubmit(){ /** *The multi dimension array is re instantiated again based on the new sheet attributes */ values = new XCell[sheet.getRows()][sheet.getColumns()]; {panel} {panel} submitGrid.setVisible(true); } catch (Exception ex) { } } }; {panel} {panel} //This new form component will hold the textfield gird Form grid = new Form("gridform"){ {panel} {panel} public void onSubmit(){ {panel} {panel}(){ for(int i = 0; i < meta.getRows() ; i++) for(int j = 0; j < meta.getCols() ; j++) {panel} {panel} /*Delegate each cell processing class here e.g Move to Database, Persist with hibernate or something *On Submit, the XCell instances stored in the multi dimension array will be made available here */ {panel} {panel} System.out.println(values[i][j]); {panel} {panel} } }; {panel} {panel} grid.add(createHeadings()); {panel} {panel} grid.add(createLoop()); {panel} {panel} submitGrid = new Button("submit"); {panel} {panel} //if nothing is uploaded yet, the submit button wont show if(meta.getCols() == 0){ {panel} {panel} submitGrid.setVisible(false); {panel} {panel} } {panel} {panel } grid.add(submitGrid); {panel} {panel} add(grid); {panel} } {code} What we have just done is to work with Excel Spreadsheets in Wicket using JExcelAPI You can download the source for these article at [Example Source|http://www.dabarobjects.com/jdev/src.zip]. Just attach to any existing Wicket samples --[dabar|User__Dabar] 15:20, 31 Aug 2006 (BST) |
What we have just done is to work with Excel Spreadsheets in Wicket using JExcelAPI
You can download the source for these article at Example Source. Just attach to any existing Wicket samples
--dabar 15:20, 31 Aug 2006 (BST)