How to Get Java to Read a Sheet File

Details

Written by

Terminal Updated on 30 May 2019   | Print E-mail

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

You lot know, Excel is the very pop file format created by Microsoft. Although information technology is non an opened file format, Coffee applications can withal read and write Excel files using the Apache POI - the Java API for Microsoft Documents , considering the development team uses reverse-technology to sympathise the Excel file format. Hence the proper noun POI stands for Poor Obfuscation Implementation.

This tutorial shows you lot how simple and easy it 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 before) and XLSX (Excel 2007 and later). To use Apache POI in your Java project:

• For not-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 projection'southward classpath:- If you are reading and writing merely Excel 2003 format, only the file poi-VERSION.jar is enough.- If y'all are reading and writing Excel 2007 format, you have 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 projection'south 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.xi (at the time of writing this tutorial).

ii. The Apache POI API Basics

In that location are two chief prefixes which yous 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 later.

And to go started the Apache POI API, you lot just need to understand and use the following four interfaces:

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

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

three. Reading from Excel File Examples

Suppose you want to read an Excel file whose content looks similar the post-obit screenshot:

This spreadsheet contains data most books (championship, author and price).

A Simple Example to Read Excel File in Java

Hither's a muddy example that reads every cell in the first sail of the workbook and prints out values in every cell, row by row:

package internet.codejava.excel;  import java.io.File; import java.io.FileInputStream; import java.io.IOException; import coffee.util.Iterator;  import org.apache.poi.ss.usermodel.Prison 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 dirty elementary program that reads an Excel file.  * @author www.codejava.internet  *  */ public grade SimpleExcelReaderExample { 	 	public static void main(Cord[] 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.next(); 			Iterator<Cell> cellIterator = nextRow.cellIterator(); 			 			while (cellIterator.hasNext()) { 				Cell cell = cellIterator.next(); 				 				switch (jail cell.getCellType()) { 					instance Cell.CELL_TYPE_STRING: 						System.out.print(prison cell.getStringCellValue()); 						break; 					instance Cell.CELL_TYPE_BOOLEAN: 						System.out.print(cell.getBooleanCellValue()); 						break; 					instance Cell.CELL_TYPE_NUMERIC: 						System.out.print(prison cell.getNumericCellValue()); 						break; 				} 				System.out.print(" - "); 			} 			System.out.println(); 		} 		 		workbook.shut(); 		inputStream.shut(); 	}  }

Output:

Head First Coffee - Kathy Serria - 79.0 - Effective Coffee - Joshua Bloch - 36.0 - Clean Lawmaking - 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 class ( Book.java ) with the following code:

package net.codejava.excel;  public grade Volume { 	private String championship; 	individual Cord writer; 	individual float toll;  	public Book() { 	}  	public String toString() { 		return Cord.format("%due south - %southward - %f", championship, author, toll); 	}  	// getters and setters }

Write a method that reads value of a cell every bit following:

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

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

public List<Book> readBooksFromExcelFile(String excelFilePath) throws IOException { 	List<Book> 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.next(); 		Iterator<Jail cell> cellIterator = nextRow.cellIterator(); 		Book aBook = new Book();  		while (cellIterator.hasNext()) { 			Prison cell nextCell = cellIterator.adjacent(); 			int columnIndex = nextCell.getColumnIndex();  			switch (columnIndex) { 			case one: 				aBook.setTitle((Cord) getCellValue(nextCell)); 				interruption; 			case 2: 				aBook.setAuthor((Cord) getCellValue(nextCell)); 				break; 			example 3: 				aBook.setPrice((double) getCellValue(nextCell)); 				break; 			}   		} 		listBooks.add together(aBook); 	}  	workbook.close(); 	inputStream.close();  	render 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 Lawmaking - Robert Martin - 42.000000, Thinking in Coffee - Bruce Eckel - 35.000000]

How to Read both Excel 2003 and 2007 format in Java

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

individual Workbook getWorkbook(FileInputStream inputStream, Cord excelFilePath) 		throws IOException { 	Workbook workbook = nothing;  	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 not Excel file"); 	}  	return workbook; }

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

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

Reading Other Data

• Get a specific canvass:

  Sheet thirdSheet = workbook.getSheetAt(2);

• Go sail proper name:

  Cord sheetName = sheet.getSheetName();

• Get total number of sheets in the workbook:

  int numberOfSheets = workbook.getNumberOfSheets();

• Get all sail names in the workbook:

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

• Go comment of a specific cell:

  Annotate cellComment = canvass.getCellComment(2, two); System.out.println("comment: " + cellComment.getString());

  For reading other data, see the getXXX() methods of the Workbook , Sheet , Row and Cell interfaces.

That's how to read Excel files in Java programmatically. I recommend y'all to accept this Java course 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 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

Near the Author:

Nam Ha Minh is certified Java programmer (SCJP and SCWCD). He started programming with Java in the time of Coffee i.four 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

fritzgoiderearsur.blogspot.com

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

Share this post