Interview Questions

Read Contents from Excel using JXL

This article deals with reading from an Excel file using JXL.

In the article Write to Excel Sheet Using POI it has been discussed about how to write "EMPLOYEE DETAILS" into an Excel file using POI. We can use the same Employee Details Excel sheet to read using the JXL API.

Consider the following class which is used to read the contents of an Excel file using JXL.

JxlExcelReader.java

/**
 * THIS SOFTWARE IS PROVIDED BY THE AUTHOR AND CONTRIBUTORS ``AS IS''
 * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED
 * TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
 * PURPOSE ARE DISCLAIMED.  IN NO EVENT SHALL THE AUTHOR OR CONTRIBUTORS
 * BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
 * CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
 * SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
 * INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
 * CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
 * ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
 * POSSIBILITY OF SUCH DAMAGE.
 */

package in.techdive.java.excel;

import java.io.File;
import java.io.IOException;

import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

public class JxlExcelReader
{
        private String          excelFilePath;
        private File            exceFile;
        private Workbook        excelWorkBook;
        private String          excelSheetName;
        private Sheet           excelSheet;

        public JxlExcelReader()
        {
                super();
                excelFilePath = "C:\\emp.xls";
        }

        public static void main(String[] args)
        {
                JxlExcelReader jxlReader = new JxlExcelReader();
                jxlReader.getContentsFromExcel();
        }

        public void getContentsFromExcel()
        {
                exceFile = new File(excelFilePath);

                if (!exceFile.exists())
                {
                        throw new RuntimeException("Unable to find the file : " + excelFilePath);
                }
                try
                {
                        excelWorkBook = Workbook.getWorkbook(exceFile);
                        if (excelSheetName == null)
                        {
                                excelSheet = excelWorkBook.getSheet(0);
                        }
                        else
                        {
                                excelSheet = excelWorkBook.getSheet(excelSheetName);
                        }

                }
                catch (BiffException biffe)
                {
                        biffe.printStackTrace();
                }
                catch (IOException ioe)
                {
                        ioe.printStackTrace();
                }

                int columns = excelSheet.getColumns();

                int rows = excelSheet.getRows();

                System.out.println("Excel Sheet Name -> " + excelSheet.getName());
                System.out.println();
                System.out.println("Excel Sheet Contents ");

                for (int r = 0; r < rows; r++)
                {

                        for (int col = 0; col < columns; col++)
                        {
                                String attributeName = excelSheet.getCell(col, r).getContents().trim();
                                if (!attributeName.equalsIgnoreCase(""))
                                {
                                        System.out.println(attributeName);
                                }
                        }
                }
        }
}

The above code reads the Excel file; create a workbook out of it. Then it extracts the Excel sheet from the workbook. It then iterates the rows & columns in the Excel sheet and prints the contents of the sheet.

Here is the sample output

Excel Sheet Name -> EMPLOYEE DETAILS

Excel Sheet Contents
EMPLOYEE DETAILS
EMPLOYEE ID
EMPLOYEE NAME
AGE
SALARY
DEPARTMENT
1001
George
35
50000
IT
1002
George
41
60000
FINANCE
1003
George
32
45000
HR
1004
George
29
40000
MARKETING
1005
George
38
54000
SALES