How to Read Excel Sheet Data in Java?

Details

Written by

Last Updated on 30 May 2019   | Impress Email

In this tutorial, I will share with you lot how to read Excel files programmatically using Java.

You lot know, Excel is the very pop file format created by Microsoft. Although it is not an opened file format, Java applications tin still read and write Excel files using the Apache POI - the Java API for Microsoft Documents , because the evolution squad uses reverse-engineering to understand the Excel file format. Hence the name POI stands for Poor Obfuscation Implementation.

This tutorial shows you how uncomplicated and easy it is to read Excel files using Apache POI's API.

i. Getting Apache POI library

Apache POI is the pure Coffee API for reading and writing Excel files in both formats XLS (Excel 2003 and earlier) and XLSX (Excel 2007 and subsequently). To use Apache POI in your Java project:

• For non-Maven projects:
  • Download the latest release of the library here: Apache POI - Download Release ArtifactsExtract the zip file and add the appropriate JAR files to your project's classpath:- If you are reading and writing only Excel 2003 format, only the file poi-VERSION.jar is enough.- If you are reading and writing Excel 2007 format, you lot have to include the following files:
    • poi-ooxml-VERSION.jar
    • poi-ooxml-schemas-VERSION.jar
    • xmlbeans-VERSION.jar
• For Maven projects: Add together the following dependency to your project's pom.xml file:
  • For Excel 2003 format simply:

    <dependency> 	<groupId>org.apache.poi</groupId> 	<artifactId>poi</artifactId> 	<version>VERSION</version> </dependency>

  • For Excel 2007 format:

    <dependency> 	<groupId>org.apache.poi</groupId> 	<artifactId>poi-ooxml</artifactId> 	<version>VERSION</version> </dependency>

     The latest stable version of Apache POI is 3.11 (at the time of writing this tutorial).

two. The Apache POI API Basics

There are ii chief prefixes which you will encounter when working with Apache POI:

• HSSF : denotes the API is for working with Excel 2003 and earlier.
• XSSF : denotes the API is for working with Excel 2007 and afterwards.

And to go started the Apache POI API, y'all merely need to sympathize and use the post-obit 4 interfaces:

• Workbook : loftier level representation of an Excel workbook. Concrete implementations are: HSSFWorkbook and XSSFWorkbook .
• Sheet : high level representation of an Excel worksheet. Typical implementing classes are HSSFSheet and XSSFSheet .
• Row : high level representation of a row in a spreadsheet. HSSFRow and XSSFRow are ii physical classes.
• Cell : high level representation of a cell in a row. HSSFCell and XSSFCell are the typical implementing classes.

At present, let's walk through some real-life examples.

3. Reading from Excel File Examples

Suppose you desire to read an Excel file whose content looks like the following screenshot:

This spreadsheet contains information almost books (championship, writer and price).

A Simple Example to Read Excel File in Java

Here'southward a dirty example that reads every jail cell in the first sheet of the workbook and prints out values in every cell, row past row:

parcel cyberspace.codejava.excel;  import coffee.io.File; import java.io.FileInputStream; import java.io.IOException; import java.util.Iterator;  import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.usermodel.XSSFWorkbook;  /**  * A muddied unproblematic programme that reads an Excel file.  * @author www.codejava.net  *  */ public class SimpleExcelReaderExample { 	 	public static void main(String[] args) throws IOException { 		String excelFilePath = "Books.xlsx"; 		FileInputStream inputStream = new FileInputStream(new File(excelFilePath)); 		 		Workbook workbook = new XSSFWorkbook(inputStream); 		Sheet firstSheet = workbook.getSheetAt(0); 		Iterator<Row> iterator = firstSheet.iterator(); 		 		while (iterator.hasNext()) { 			Row nextRow = iterator.side by side(); 			Iterator<Cell> cellIterator = nextRow.cellIterator(); 			 			while (cellIterator.hasNext()) { 				Cell cell = cellIterator.next(); 				 				switch (cell.getCellType()) { 					case Cell.CELL_TYPE_STRING: 						Organization.out.print(cell.getStringCellValue()); 						interruption; 					instance Cell.CELL_TYPE_BOOLEAN: 						System.out.print(cell.getBooleanCellValue()); 						break; 					case Cell.CELL_TYPE_NUMERIC: 						System.out.print(cell.getNumericCellValue()); 						suspension; 				} 				Arrangement.out.impress(" - "); 			} 			System.out.println(); 		} 		 		workbook.shut(); 		inputStream.close(); 	}  }

Output:

Caput Beginning Java - Kathy Serria - 79.0 - Constructive Java - Joshua Bloch - 36.0 - Clean Code - Robert Martin - 42.0 - Thinking in Coffee - Bruce Eckel - 35.0 -

A More Object-Oriented Example to read Excel File

For nicer and more object-oriented program, let's create a model form ( Volume.java ) with the post-obit code:

packet net.codejava.excel;  public class Book { 	private String title; 	private String author; 	private float toll;  	public Volume() { 	}  	public Cord toString() { 		return String.format("%s - %due south - %f", title, author, cost); 	}  	// getters and setters }

Write a method that reads value of a cell as following:

private Object getCellValue(Cell cell) { 	switch (cell.getCellType()) { 	case Cell.CELL_TYPE_STRING: 		return cell.getStringCellValue();  	case Cell.CELL_TYPE_BOOLEAN: 		return cell.getBooleanCellValue();  	case Prison cell.CELL_TYPE_NUMERIC: 		return cell.getNumericCellValue(); 	}  	return null; }

Next, implement a method that reads an Excel file and returns a list of books:

public Listing<Book> readBooksFromExcelFile(String excelFilePath) throws IOException { 	List<Volume> listBooks = new ArrayList<>(); 	FileInputStream inputStream = new FileInputStream(new File(excelFilePath));  	Workbook workbook = new XSSFWorkbook(inputStream); 	Sheet firstSheet = workbook.getSheetAt(0); 	Iterator<Row> iterator = firstSheet.iterator();  	while (iterator.hasNext()) { 		Row nextRow = iterator.adjacent(); 		Iterator<Cell> cellIterator = nextRow.cellIterator(); 		Book aBook = new Book();  		while (cellIterator.hasNext()) { 			Cell nextCell = cellIterator.next(); 			int columnIndex = nextCell.getColumnIndex();  			switch (columnIndex) { 			instance ane: 				aBook.setTitle((String) getCellValue(nextCell)); 				intermission; 			case two: 				aBook.setAuthor((String) getCellValue(nextCell)); 				break; 			instance 3: 				aBook.setPrice((double) getCellValue(nextCell)); 				intermission; 			}   		} 		listBooks.add(aBook); 	}  	workbook.shut(); 	inputStream.close();  	return listBooks; }

And here is the testing code:

public static void main(String[] args) throws IOException { 	String excelFilePath = "Books.xlsx"; 	ExcelReaderExample2 reader = new ExcelReaderExample2(); 	List<Book> listBooks = reader.readBooksFromExcelFile(excelFilePath); 	System.out.println(listBooks); }

Output:

[Head First Java - Kathy Serria - 79.000000, Effective Java - Joshua Bloch - 36.000000, 	Make clean Code - Robert Martin - 42.000000, Thinking in Java - Bruce Eckel - 35.000000]

How to Read both Excel 2003 and 2007 format in Coffee

For better supporting both users using Excel 2003 and 2007, it's recommended to write a split factory method that returns an XSSFWorkbook or HSSFWorkbook depending on the file extension of the file (.xls or .xlsx):

private Workbook getWorkbook(FileInputStream inputStream, Cord excelFilePath) 		throws IOException { 	Workbook workbook = zippo;  	if (excelFilePath.endsWith("xlsx")) { 		workbook = new XSSFWorkbook(inputStream); 	} else if (excelFilePath.endsWith("xls")) { 		workbook = new HSSFWorkbook(inputStream); 	} else { 		throw new IllegalArgumentException("The specified file is non Excel file"); 	}  	return workbook; }

And here's a usage example of this factory method:

Cord excelFilePath = "Books.xlsx"; // tin can be .xls or .xlsx  FileInputStream inputStream = new FileInputStream(new File(excelFilePath));  Workbook workbook = getWorkbook(inputStream, excelFilePath);

Reading Other Information

• Become a specific sheet:

  Sheet thirdSheet = workbook.getSheetAt(2);

• Get sheet proper name:

  String sheetName = sheet.getSheetName();

• Become total number of sheets in the workbook:

  int numberOfSheets = workbook.getNumberOfSheets();

• Become all sheet names in the workbook:

  int numberOfSheets = workbook.getNumberOfSheets();  for (int i = 0; i < numberOfSheets; i++) { 	Sheet aSheet = workbook.getSheetAt(i); 	Organisation.out.println(aSheet.getSheetName()); }

• Get annotate of a specific jail cell:

  Comment cellComment = canvas.getCellComment(2, 2); Organization.out.println("comment: " + cellComment.getString());

  For reading other information, see the getXXX() methods of the Workbook , Sheet , Row and Prison cell interfaces.

That's how to read Excel files in Java programmatically. I recommend you to have this Coffee form to fully larn Java programming.

Related Coffee Excel Tutorials:

• How to Write Excel Files in Java using Apache POI
• Java Example to Read Password-protected Excel Files Using Apache POI
• Java Case to Update Existing Excel Files Using Apache POI
• Working with Formula Cells in Excel using Apache POI

References:

• Apache POI - the Coffee API for Microsoft Documents
• POI API Documentation (Javadocs)
• Apache POI Quick Guide
• Apache POI HOWTO

About the Author:

Nam Ha Minh is certified Coffee programmer (SCJP and SCWCD). He started programming with Java in the time of Java ane.4 and has been falling in love with Java since then. Make friend with him on Facebook and watch his Java videos you YouTube.

Add comment

dangthertat.blogspot.com

Source: https://www.codejava.net/coding/how-to-read-excel-files-in-java-using-apache-poi

Share this post