Recently I have been asked to write a Web Dynpro program that enables our administrator to upload any excel table and show it within our portal.
Here is an example of the excel table file we had to upload:
And here is my Web Dynpro rendering:
To read any excel into a java program you need to use an external library such as Apache POI , JXL, or OpenXLS. I opted for Apache POI. To add an external jar to a DC, please use this forum Web Dynpro - External Jar (JXL) for Downloading Excel files ISSUE posted by Mr. Walter Kahn. The steps you need to take are:
1. Add the following jars to your .ear file: poi-3.1-FINAL-20080629.jar,
poi-contrib-3.1-FINAL-20080629.jar, poi-scratchpad-3.1-FINAL-20080629.jar.
2. Add your .ear project as dependency to your DC.
3. Go to src and add a new package to hold two java files: Excel.java and
ReadExcel.java. These files are responsible for extracting the information from
the excel file while storing it at runtime before turning it into a nice Web
Dynpro table.
4. Note: to the project files I attach here, I already added all the necessary jars.
There are two points I would like to focus on: one is the way you upload the file, the second the way you dynamically create a Web Dynpro table to reflect the structure and contents of the excel table you just uploaded.
Reading the table
1. Please note: in the attached code I hard-code refer to excel sheet number 0.
Under real circumstances this should not necessarily be mandatory. Just read
in the number of sheets this excel has, and decide which one you wish to
upload.
2. In my code, under remarks, I also put the input types that might be present when
reading the excel table. To store the contents of the table you just read in, I use a java
class I call, how odd, Excel. This file can be omitted altogether and preferably replaced
by a local node with cardinality of 0:n.
Dynamically creating UI elements
1. To respond to the structure and data just uploaded, we have to dynamically create a
new table. Technically, manipulation of UI elements at runtime is done in the method
wdDoModify(). When designing your application you should consider the way you want your
rendering mechanism to work. If you want to create UI elements only when your
application loads, then your code should be placed within the condition : if (firstTime) {}.
In our case, where we give the user the choice of uploading different files again and
again, our code should of course be placed outside the scope of if(firstTime){}.
2. Another important thing we should remember when designing the application is that
each time we upload a new file, the last table we dynamically created should be removed
altogether to be entirely replaced by a newly constructed one. This is achieved by using
the next statement: view.getElement(id).destroy();
3. Furthermore, you should always remember to name each dynamically
created column differently. As developers we often tend to use the name of the column
we derived from the excel table. Beware! for it may happen that the excel you are
reading has two columns that share the same name. So use a distinct name for each
column you create!
Part Two - reading in the excel table
Part Three - creating a table UI element to host the excel
You may download the source code here.
I hope you like it.
Yuval Peery
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
36 | |
7 | |
5 | |
5 | |
5 | |
4 | |
4 | |
4 | |
3 | |
3 |