Java Excel – Reading Excel Sheet in Java

Add a comment December 19th, 2008

For some reason i found Apache POI difficult to use for reading excel sheet, in terms of naming conventions and the code so i was searching for another Library for reading Excel Sheet in Java and i stopped at JXL.

Java Excel API namely JXL is an Java based API that allows us to read/write/edit the Excel Sheet. The main think i liked about it was its simplicity over Apache POI Package. This article will show you how we can read Excel Sheet using JXL.

Now here i am going to read an excel sheet having content:

EmpId Name Designation
1 ABC Software Engineer
2 DFG Sr Software Engineer
3 LOI Team Leader
4 LKJ Project Manager
5 QWE Software Architech

Your email:

 


I have uploaded the same excel sheet on the server you can download it from here: Download Excel Sheet

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Locale;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.read.biff.BiffException;
import jxl.DateCell;
 
public class ReadXLSheet {
 
	public void init(String filePath) {
		FileInputStream fs = null;
		try {
			fs = new FileInputStream(new File(filePath));
			contentReading(fs);
		} catch (IOException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			try {
				fs.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}
 
	//Returns the Headings used inside the excel sheet
	public void getHeadingFromXlsFile(Sheet sheet) {
		int columnCount = sheet.getColumns();
		for (int i = 0; i < columnCount; i++) {
			System.out.println(sheet.getCell(i, 0).getContents());
		}
	}
 
	public void contentReading(InputStream fileInputStream) {
		WorkbookSettings ws = null;
		Workbook workbook = null;
		Sheet s = null;
		Cell rowData[] = null;
		int rowCount = '0';
		int columnCount = '0';
		DateCell dc = null;
		int totalSheet = 0;
 
		try {
			ws = new WorkbookSettings();
			ws.setLocale(new Locale("en", "EN"));
			workbook = Workbook.getWorkbook(fileInputStream, ws);
 
			totalSheet = workbook.getNumberOfSheets();
			if(totalSheet > 0) {
				System.out.println("Total Sheet Found:" + totalSheet);
				for(int j=0;j<totalsheet ;j++) {
					System.out.println("Sheet Name:" + workbook.getSheet(j).getName());
				}
			}
 
			//Getting Default Sheet i.e. 0
			s = workbook.getSheet(0);
 
			//Reading Individual Cell
			getHeadingFromXlsFile(s);
 
			//Total Total No Of Rows in Sheet, will return you no of rows that are occupied with some data
			System.out.println("Total Rows inside Sheet:" + s.getRows());
			rowCount = s.getRows();
 
			//Total Total No Of Columns in Sheet
			System.out.println("Total Column inside Sheet:" + s.getColumns());
			columnCount = s.getColumns();
 
			//Reading Individual Row Content
			for (int i = 0; i < rowCount; i++) {
				//Get Individual Row
				rowData = s.getRow(i);
				if (rowData[0].getContents().length() != 0) { // the first date column must not null
					for (int j = 0; j < columnCount; j++) {
						switch (j) {
						case 0:
							System.out.println("Employee Id:" + rowData[j].getContents());
						case 1:
							System.out.println("Employee Name:" + rowData[j].getContents());
						case 2:
							System.out.println("Employee Designation:" + rowData[j].getContents());
						default:
							break;
						}
					}
				}
			}
			workbook.close();			
		} catch (IOException e) {
			e.printStackTrace();
		} catch (BiffException e) {
			e.printStackTrace();
		}
	}
 
		public static void main(String[] args) {
		try {
			ReadXLSheet xlReader = new ReadXLSheet();
			xlReader.init("/home/hitesh/Desktop/test.xls");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}
Total Sheet Found:3
----------------------------------------------
Sheet Name:Sheet1
Sheet Name:Sheet2
Sheet Name:Sheet3
----------------------------------------------
Column Heading:EmpId
Column Heading:Name
Column Heading:Designation
----------------------------------------------
Total Rows inside Sheet:6
----------------------------------------------
Total Column inside Sheet:3
----------------------------------------------
Employee Id:EmpId
Employee Name:Name
Employee Designation:Designation
----------------------------------------------
Employee Id:1
Employee Name:ABC
Employee Designation:Software Engineer
----------------------------------------------
Employee Id:2
Employee Name:DFG
Employee Designation:Sr Software Engineer
----------------------------------------------
Employee Id:3
Employee Name:LOI
Employee Designation:Team Leader
----------------------------------------------
Employee Id:4
Employee Name:LKJ
Employee Designation:Project Manager
----------------------------------------------
Employee Id:5
Employee Name:QWE
Employee Designation:Software Architect

With respect to the following code written i am able to perform the following operation:

  • Calculate total no of Sheets inside the Excel Sheet
  • List all the Sheet Name used inside Excel Sheet
  • Read Individual Cell for specifying the cell position – in code have read the column heading i.e. 1st row
  • Read Total Modified Rows in the Excel Sheet
  • Read Total Modified Columns in the Excel Sheet
  • Read Individual Row Content


Popular Articles:

Subscribe to my RSS feed.

  1. December 26th, 2008 at 10:05 | #1

    If I have to write this code using POI, to accomplish the tasks that you have performed, the code would have been pretty similar with very similar API calls. I see the difficulty level as more or less the same when compared with POI. My question is how is this different from POI?

  2. December 28th, 2008 at 22:36 | #2

    Hi Surya,
    Difference comes with the package naming convention used. JXL is purely used for Excel in Java. Where as in Apache POI there are different projects made for different Microsoft Office Files, E.g HWPF, HSSF, HPSF etc. Now as a developer you have to check which one if for Excel Parsing, download the package and then implement it.

  3. January 2nd, 2009 at 11:58 | #3

    Hi ,
    I liked your tutorial.
    Manish

  4. January 6th, 2009 at 04:10 | #4
    Avijit

    there is a problem while having multiple sheets in xls file. i want to accsess multiple sheet values at once, but it returns a ovelap value of two sheets.

    but the tutorial is too good.
    plz solve the problem

  5. January 6th, 2009 at 04:44 | #5

    Hi Avijit,
    Can you paste the source code you are using.

    Hitesh agarwal

  6. January 11th, 2009 at 14:08 | #6
    Vivek Mittal

    Hi Hitesh,
    Very nice tutorial man. Keep it up

  7. February 18th, 2009 at 07:55 | #7
    Abhishek

    Hi Hitesh,

    Nice work man. I had a question. Is it possible to write the macros of Excel in Java and execute those macros while working on the spreadsheet.

    Thanks
    Abhi

  8. May 29th, 2009 at 02:41 | #8
    seeraz

    Great tutorial , thx a lottt …

  9. June 2nd, 2009 at 07:38 | #9
    Moha

    Hi,

    First of all, thank you for the tutorial.
    I’ve a problem. I’ve downloaded the api but I don’t know where to save it in eclipse. The reason is that I putted the unzipped files of the api into plugin folder but the program you gave as an example still asks for jxl.
    Could you please help?
    Thanks.

  1. No trackbacks yet.
Comments feed

Spam protection by WP Captcha-Free