Easy way read Excel sheets through Java Application

Easy way read Excel sheets through Java Application

In this article I’m going to introduce a easy way to convert  Excel spread sheets to XML files using  Java language. Suppose you are having a Excel sheet with this template.

                                   

Step 1

In my case I have an application GUI to browse tho location of the Excel sheet. You can use your own way to import Excel sheets to your appliation and it doesn’t matter whatever the way you use. Now you have the location of the excel sheet.

Step 2

To read the whole Excel sheet data you can use the following java (method)code segment.

public Vector readExecl(String fileName) {

        Vector cellVector = new Vector();

           try {

            FileInputStream myInput = new FileInputStream(fileName);

            POIFSFileSystem myfileSystem = new POIFSFileSystem(myInput);

            HSSFWorkbook myworkbook = new HSSFWorkbook(myfileSystem);

            Workbook myworkbook = WorkbookFactory.create(new File("file.xlsx"))

            HSSFSheet mySheet = myworkbook.getSheetAt(0);

            Iterator rowIter = mySheet.rowIterator();

 while (rowIter.hasNext()) {

HSSFRow myRow = (HSSFRow) rowIter.next();

Iterator cellIter = myRow.cellIterator();

                                    Vector cellStoreVector = new Vector();

while (cellIter.hasNext()) {

HSSFCell myCell = (HSSFCell) cellIter.next();

{

                                                                        String s = myCell.toString();

                                                                        cellStoreVector.addElement(myCell);

                                                            }

}

                                    cellVector.addElement(cellStoreVector);

}

        } catch (Exception ex) {

            System.out.println(ex);

        }

return cellVector;

}

To use this method you have to add a jar file(“Apache POI”) to your project.

Click here to download the latest version of the jar

The latest development release is Apache POI 3.10 Beta 1

Then right click on your projects “Libraries” and select “Add JAR/Folder” and select the location of your jar file.

This method need the location of the Excel sheet as the input parameter. The output of this method is a Vector that contain all the data in the Excel sheet row vice. Vector is a data type that can keep any kind of data. It’s most like to as array data type.

In this method it read the excel sheet row vice for each row it create a Vector and each cell of that row also create a vector.

<cellVector   <cellStoreVector>  <cellStoreVector>  >    this is an entire row with two cells(Columns).

Vector data type is not consider the data type that it going to store, you can store Integer values, String values, Double values in a same vector.

The “Iterator” object goes row by row of the excel sheet and read the data of the row until the iterator has next.

“HSSFRow” objects can find the rows from the  “Iterator” object.

“HSSFCell” objects can identify the cells of a given “cellIterator” object.

When all the cells of a given row has been finished it add that  “cellStoreVector” to the “cellVector”

Finally the method returns the   “cellVector”    that contain all the data in the Excel sheet.

Now all the data is stored in that vector. If  you want to do some filtering of  the data you need to read the vector data again.

To read the stored Vector data

·         This following method can be used for filter the data according to your requirement. In here I want to get the data of the row 6 of the Excel sheet.

·         The created vector should be pass to the method.

·         Then the inside vectors are detached from the main vector and read them in a sorted order.

·         Finally that method will return a vector with filtered data.

 

 

public Vector showExeclStuDetailsHeaders(Vector execldata) {

            Vector v = new Vector();

 for (int i = 0; i < execldata.size(); i++) {

                       if (i == 6) {

                        Vector celVec = (Vector) execldata.elementAt(i);

                        for (int j = 0; j < celVec.size(); j++) {

HSSFCell cell = (HSSFCell) celVec.elementAt(j);

                                    String c = cell.toString();

                                    v.add(j, c);

}

}

        }

        return v;

    }