New assignment, new problems, new solutions, and new learnings……This is what I get everytime I start of with a new project.
Coming in to a new project the very first day I was assigned a module which kind of replicates, MDM import manager’s basic functionality. On similar lines, the component to be developed takes an excel spreadsheet as an input and uploads/creates all the line items as different records in MDM repository.
Googling around for a while, I came across out two different API’s (open source) which can be used for reading in data from excel spreadsheets:
- Apache POI – HSSF – Java API to Access Microsoft Excel Format Files
- Java Excel API – A Java API to read, write and modify Excel spreadsheets
Below, I will discuss the approach and the basic steps needed to read data from excel spreadsheet using the Java Excel API(jExcel API).
jExcel API can read a spreadsheet from a file stored on the local file system or from some input stream, ideally the following should be the steps while reading:
- Create a workbook from a file on the local file system, as illustrated in the following code fragment:
Workbook workbook = Workbook.getWorkbook(new File(“test.xls”));
- On getting access to the worksheet, once can use the following code piece to access individual sheets. These are zero indexed – the first sheet being 0, the second sheet being 1, and so on. (You can also use the API to retrieve a sheet by name).
Sheet sheet = workbook.getSheet(0);
- After getting the sheet, you can retrieve the cell’s contents as a string by using the convenience method getContents(). In the example code below, A1 is a text cell, B2 is numerical value and C2 is a date. The contents of these cells may be accessed as follows
// perform operations on strings
However in case we need to access the cell’s contents as the exact data type ie. as a numerical value or as a date, then the retrieved Cell must be cast to the correct type and the appropriate methods called. The code piece given below illustrates how JExcelApi may be used to retrieve a genuine java double and java.util.Date object from an Excel spreadsheet. For completeness the label is also cast to it’s correct type. The code snippet also illustrates how to verify that cell is of the expected type – this can be useful when performing validations on the spreadsheet for presence of correct datatypes in the spreadsheet.
String a1 = null;
Double b2 = 0;
Date c2 = null;
Cell a1 = sheet.getCell(0,0);
Cell b2 = sheet.getCell(1,1);
Cell c2 = sheet.getCell(2,1);
if (a1.getType() == CellType.LABEL)
LabelCell lc = (LabelCell) a1;
stringa1 = lc.getString();
if (b2.getType() == CellType.NUMBER)
NumberCell nc = (NumberCell) b2;
numberb2 = nc.getValue();
if (c2.getType() == CellType.DATE)
DateCell dc = (DateCell) c2;
datec2 = dc.getDate();
// operate on dates and doubles
It is recommended to, use the close() method (as in the code piece below) when you are done with processing all the cells.This frees up any allocated memory used when reading spreadsheets and is particularly important when reading large spreadsheets.
// Finished – close the workbook and free up memory
The API class files are availble in the ‘jxl.jar’, which is available for download along with API documentation. Start with the URL given above and you will be able to locate the javadoc and the relevant jar files.
Folks, do give this a try and let me know your feedback.