How to read an excel file in java

Microsoft excel is the most widely used program to maintain data. Hence it becomes vital that the data from an excel sheet be read directly to a java program so that it may be used further to dump into a database or written to an XML file. Java itself does not provide any features to perform excel related operations but there are external libraries written in java which support this. Two of the common libraries are Apache POI and jexcel (or jxl). Both of them provide convenient methods to handle excel files. This post will be using jexcel (or jxl) api for its demonstration.

Following is a snapshot of excel file which is to be read by the java program :

Code to read the above excel file using jxl follows :

     private static void readExcel() {
		String filePath = "d:\\employee.xls";
		try {
			Workbook workbook = Workbook.getWorkbook(new File(filePath));
			//get first sheet
			Sheet sheet = workbook.getSheet(0);
			//get all rows in the sheet
			int rows = sheet.getRows();
			//iterate over rows
			for (int rowNum = 0; rowNum < rows; rowNum++) {
				//get all columns in each row
				Cell[] row = sheet.getRow(rowNum);
				//iterate over cells in each row
				for (int colNum = 0; colNum < row.length; colNum++) {
					//print cell contents
					System.out.print(row[colNum].getContents());
					//give a space of 2 tabs
					System.out.print("\t\t");
				}
				//change line after each row
				System.out.println();
			}
		} catch (BiffException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
   }

 

Details :

Keeping in mind the hierarchy of components, first we read a workbook. This is done using static getWorkbook()method of jxl.Workbookclass which takes the path of the file to read as an argument and returns an instance of jxl.Workbook. Using Workbook, we get a jxl.Sheetobject using its getSheet()method which takes the index or name of the sheet to be read as argument. We need to read the contents of this sheet.

A sheet is composed of rows and a row is composed of columns (or cells). Hence, in order to read the contents of the sheet, we need to iterate over each row and in turn, over each cell of the row being iterated.

To iterate over the rows in a sheet, we need the number of rows in that sheet. We get this number by calling getRows()method on the sheet instance. In each iteration, we get the row being iterated by calling getRow()method of sheet which takes the index of the row. getRow()method returns an array of cells in the row. Each cell represents a column of this row.

Finally, we iterate over this cell array using for loop and in each iteration we access individual cell of the row being iterated. Contents of the cell are retrieved by calling getContents()method on the cell object.

Let’s tweak in :

1. Column and row indices in jxl start with 0.
2. jxl does not support reading and writing excel files with “.xlsx” extension.
3. jxl.Sheetalso has a method getCell()to directly access a cell. This method takes the column and row index of the column.
4. Instead of getting the total number of columns in a row using the getRow(rowNum) method of jxl.Sheetas used in the code above, we can also use getColumns()method of jxl.Sheetclass.
5. File being read should be present at the given location otherwise an error will be raised as :

   java.io.FileNotFoundException: D:\employee.xls (The system cannot find the file specified)

Liked this post !!! Don’t forget to share…