With my previous blog I introduced a way to upload Excel xls files of unknown size to Web Dynpro using POI.
If you wish to read in Excel's new formatted xlsx files, you will have to use Apache POI 3.5 onwards. The
problem however is that these jars require JDK1.5 or higher. But what if your system still runs on JDK1.4???
Well, then you simply cannot use POI to help you read in the new Excel files. I therefore sat for some time
to investigate Excel's new file format to see if I can do the job under JDK1.4.
A word of caution: The project I developed is not bullet-proof. If you need to use it, check all the formats
you are going to use in your project and see if my code meets your requirement.
If not, you can debug my code and extend it to match your formats.
First let me show you how it looks:
And then how my Web Dynpro application displays it:
1. The first thing to understand is that the new Excel xlsx file format is very different from the previous xls format.
In building the new file format, Microsoft has yielded to the international community that demanded xml-based
format. Thus the new file format Microsoft introduced is nothing more than a zip file containing numerous xml files along
with some other files, as shown here:
So basically, to read in a new Excel file all you have to do is to be able to read all the xml files in this folder,
analyze them at runtime and exteract the information you need and display it.
2. Analysis: each worksheet is also an xml that contains both the data and the formatting information. So reading in
the data without understanding what format to apply to it is insufficient. Accordingly, a number such as 36936 may be
regarded as a plain number or as a date. Dates, as youb probably know, are evaluated at runtime and calculated
from 01/01/1900. So to be able to format the input you have to refer to two more files: sharedStrings.xml and
styles.xml.
Furthermore, some formats like fractions are lists as decimal numbers or decimal fractions. To convert these numbers
I had to resort to what is known in math as 'Continued Fractions'. For example a number like 7/11 is displayed as
0.636363636363635.
3. Writing such a code took a long time mainly because of the trial and error process involved in it. The information I
found on the net was fragmeneted and incomplete. It took long debugging sessions to fully understand the
structure Microsoft uses in these files.
4. To go along with my previous blog, here also I applied the technique of dynamically creating UI elements to support
every Excel table, size beforehand: unknown.
5. How to use: as you can see two files are web dynpro files: ExcelComp - is the project's component controller.
ExcelCompView is the view, of course. From the component controller I call the class Unzip which in turn
calls ReadXMLFile.
The code can be found in Code Exchange. To download it, click here (Additional login may be required).
I hope that the solution I present here will prove to be beneficial to all members of SCN.
Yuval Peery
- SAP Managed Tags:
