Java excel tutorial

Following is a simple excel sheet reading tutorial.

For this tutorial we will use the Apache POI library. This product is used to parse/write Micorsoft popular document formats, such as Excel, Power Point, etc., using Java. This is a common case for projects that need to produce reporting.

Prerequisites:
1) JDK installed
3) POI library from Apache
2) eclipse

If you do not have JDK, please download the lates stable release and install it:
http://www.oracle.com/technetwork/java/javase/downloads/index.html

Once you download it, just follow the instructions and keep the default installation.

Next is to get the POI library, which you can download from here:
http://poi.apache.org/download.html

Download the binary distribution and save it a location on your PC. I usually have a folder for Apache products, which I call "Apache", in there I keep all the downloaded pieces that wear its trend. Now extract the zip to the same folder. Here is the location I got:

C:\Apache\poi-3.10-FINAL

Now lets create a simple Excel sheet with one sheet in it:

I saved this sheet as "testBook.xlsx" under:

C:\Temp

Now we will create a Java project in eclipse, import required POI libraries, and write a simple class to parse this workbook.

1) Open your eclipse .If you do not have eclipse installed, download it from:
http://www.eclipse.org/downloads/ - I suggest to get the one for Java EE developers. Once you get it, open it up and create workspace. I have my workspace under:

C:\workspace

2) Choose a java perspective:

3) Create a java project. This can be done in several ways, but i usually just press Ctrl+N. Then in filter type java and click next:

I called the project ExcelParser:

Click finish.

Now we have an empty project. Lets place the POI libraries in this project.

1) Create a folder in your new project and call it lib (right-click on project and choose new --> folder):

call it lib.

Now we will copy all files required jar files from POI we downloaded into this lib folder (just use copy paste - programmer's best lucky move)

C:\Apache\poi-3.10-FINAL\poi-3.10-FINAL-20140208.jar
C:\Apache\poi-3.10-FINAL\poi-examples-3.10-FINAL-20140208.jar
C:\Apache\poi-3.10-FINAL\poi-excelant-3.10-FINAL-20140208.jar
C:\Apache\poi-3.10-FINAL\poi-ooxml-3.10-FINAL-20140208.jar
C:\Apache\poi-3.10-FINAL\poi-ooxml-schemas-3.10-FINAL-20140208.jar
C:\Apache\poi-3.10-FINAL\poi-scratchpad-3.10-FINAL-20140208.jar
C:\Apache\poi-3.10-FINAL\lib\commons-codec-1.5.jar
C:\Apache\poi-3.10-FINAL\lib\commons-logging-1.1.jar
C:\Apache\poi-3.10-FINAL\lib\junit-4.11.jar
C:\Apache\poi-3.10-FINAL\lib\log4j-1.2.13.jar
C:\Apache\poi-3.10-FINAL\ooxml-lib\dom4j-1.6.1.jar
C:\Apache\poi-3.10-FINAL\ooxml-lib\stax-api-1.0.1.jar
C:\Apache\poi-3.10-FINAL\ooxml-lib\xmlbeans-2.3.0.jar

Now we will add all these libraries to the classpath of the project so that our program may use them.
Right-click on the project ---> choose properties. Then on the left select Java Build Path and on the right click on libraries tab. Then click add jars button --> choose all the jars from the lib folder of our project and click ok, then ok on the build path:

Now we will create our Java program. For that we will need to create a package and a Java source file in it.

1) Right click on src folder and choose new --> package. I chose name as "com.test.processor".
2) Now lets create a parser class. Right click on a package and choose new -- > Class. I gave it a name of ExcelParser.

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

package com.test.processor; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.IOException; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class ExcelParser { public static void main(String[] args) { try { XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream("C:/Temp/testBook.xlsx")); // Get sheet. XSSFSheet sheet = wb.getSheetAt(0); for (int r = 0; r < sheet.getPhysicalNumberOfRows(); r++) { // Get row XSSFRow row = sheet.getRow(r); if (row == null) { continue; } // Parse cells for (int c = 0; c < row.getPhysicalNumberOfCells(); c ++) { XSSFCell cell = row.getCell(c); System.out.print(cell.getStringCellValue() + " | "); } System.out.println(); } } catch (FileNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } }

Now run your program.

Here is the output i got:

Username | First Name | Last Name | Depatment | Location | 
jmellow | Josh | Mellow | IT | New York | 
aspalleti | Adriano | Spalleti | IT | Rome | 
jsorrow1 | Jake | Sorrow | IT | Rome | 
jsorrow | Jake | Sorrow | QA | London | 
msmith | Mary | Smith | IT | London | 
tbrath | Tony | Brath | IT | New York | 
klindsay | Kate | Lindsay | IT | New York |