How to Read Data From Excel Sheet in Java

Details

Written by

Final Updated on 30 May 2019   | Impress E-mail

In this tutorial, I will share with y'all how to read Excel files programmatically using Java.

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

This tutorial shows you lot how elementary and like shooting fish in a barrel information technology is to read Excel files using Apache POI's API.

1. Getting Apache POI library

Apache POI is the pure Java 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 projection:

• For not-Maven projects:
  • Download the latest release of the library here: Apache POI - Download Release ArtifactsExtract the nil file and add the appropriate JAR files to your project'south classpath:- If yous are reading and writing only Excel 2003 format, merely the file poi-VERSION.jar is plenty.- If you are reading and writing Excel 2007 format, you take to include the following files:
    • poi-ooxml-VERSION.jar
    • poi-ooxml-schemas-VERSION.jar
    • xmlbeans-VERSION.jar
• For Maven projects: Add the following dependency to your project's pom.xml file:
  • For Excel 2003 format only:

    <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).

2. The Apache POI API Nuts

There are two primary prefixes which you will encounter when working with Apache POI:

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

And to get started the Apache POI API, you just need to sympathize and use the following iv interfaces:

• Workbook : high level representation of an Excel workbook. Physical 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 two concrete classes.
• Jail cell : high level representation of a cell in a row. HSSFCell and XSSFCell are the typical implementing classes.

At present, permit'south walk through some existent-life examples.

3. Reading from Excel File Examples

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

This spreadsheet contains information about books (title, author and cost).

A Unproblematic Example to Read Excel File in Java

Hither's a muddy example that reads every cell in the offset sheet of the workbook and prints out values in every cell, row past row:

packet net.codejava.excel;  import java.io.File; import java.io.FileInputStream; import java.io.IOException; import coffee.util.Iterator;  import org.apache.poi.ss.usermodel.Jail 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 muddy simple program that reads an Excel file.  * @writer world wide web.codejava.cyberspace  *  */ public form SimpleExcelReaderExample { 	 	public static void principal(Cord[] args) throws IOException { 		String excelFilePath = "Books.xlsx"; 		FileInputStream inputStream = new FileInputStream(new File(excelFilePath)); 		 		Workbook workbook = new XSSFWorkbook(inputStream); 		Sail firstSheet = workbook.getSheetAt(0); 		Iterator<Row> iterator = firstSheet.iterator(); 		 		while (iterator.hasNext()) { 			Row nextRow = iterator.next(); 			Iterator<Prison cell> cellIterator = nextRow.cellIterator(); 			 			while (cellIterator.hasNext()) { 				Cell cell = cellIterator.adjacent(); 				 				switch (cell.getCellType()) { 					example Cell.CELL_TYPE_STRING: 						System.out.print(jail cell.getStringCellValue()); 						interruption; 					case Cell.CELL_TYPE_BOOLEAN: 						Organisation.out.print(cell.getBooleanCellValue()); 						break; 					example Cell.CELL_TYPE_NUMERIC: 						Organisation.out.print(cell.getNumericCellValue()); 						break; 				} 				System.out.impress(" - "); 			} 			Arrangement.out.println(); 		} 		 		workbook.close(); 		inputStream.shut(); 	}  }

Output:

Head Get-go Coffee - Kathy Serria - 79.0 - Effective Java - Joshua Bloch - 36.0 - Clean Code - Robert Martin - 42.0 - Thinking in Java - 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:

package cyberspace.codejava.excel;  public grade Book { 	individual String championship; 	private String author; 	private float price;  	public Book() { 	}  	public Cord toString() { 		return Cord.format("%south - %s - %f", title, writer, price); 	}  	// getters and setters }

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

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

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

public List<Book> readBooksFromExcelFile(String excelFilePath) throws IOException { 	List<Volume> listBooks = new ArrayList<>(); 	FileInputStream inputStream = new FileInputStream(new File(excelFilePath));  	Workbook workbook = new XSSFWorkbook(inputStream); 	Sail firstSheet = workbook.getSheetAt(0); 	Iterator<Row> iterator = firstSheet.iterator();  	while (iterator.hasNext()) { 		Row nextRow = iterator.next(); 		Iterator<Cell> cellIterator = nextRow.cellIterator(); 		Book aBook = new Book();  		while (cellIterator.hasNext()) { 			Cell nextCell = cellIterator.adjacent(); 			int columnIndex = nextCell.getColumnIndex();  			switch (columnIndex) { 			example 1: 				aBook.setTitle((String) getCellValue(nextCell)); 				break; 			case 2: 				aBook.setAuthor((Cord) getCellValue(nextCell)); 				break; 			case iii: 				aBook.setPrice((double) getCellValue(nextCell)); 				pause; 			}   		} 		listBooks.add(aBook); 	}  	workbook.shut(); 	inputStream.shut();  	render listBooks; }

And hither is the testing code:

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

Output:

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

How to Read both Excel 2003 and 2007 format in Java

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

private Workbook getWorkbook(FileInputStream inputStream, String excelFilePath) 		throws IOException { 	Workbook workbook = null;  	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 hither'southward a usage example of this factory method:

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

Reading Other Information

• Get a specific sail:

  Canvas thirdSheet = workbook.getSheetAt(two);

• Get sheet proper name:

  Cord sheetName = sheet.getSheetName();

• Get full 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); 	System.out.println(aSheet.getSheetName()); }

• Become comment of a specific cell:

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

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

That'southward how to read Excel files in Java programmatically. I recommend you to take this Java course to fully learn Coffee programming.

Related Java Excel Tutorials:

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

References:

• Apache POI - the Java 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 1.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 together comment

How to Read Data From Excel Sheet in Java

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

Share this post