Apache POI is a Java API to read and write Documents in .xls and .xlsx formats. It contains classes and interfaces. An Apache POI library provides two implementations to read an excel file:

• Horrible Spreadsheet Format (HSSF) Implementation: It’s an API that works with Excel 2003 or earlier versions.
• XML Spreadsheet Format (XSSF) Implementation: It’s an API that works with Excel 2007 or later versions.

Interfaces In Apache POI

• Workbook: It represents an Excel Workbook. This is an interface implemented by HSSFWorkbook and XSSFWorkbook.
• Sheet: This is an interface that represents an Excel worksheet. A sheet is a structure of a workbook, which represents the grid of cells. A Sheet interface extends java.lang.Iterable.
• Row: It is an interface that represents the row of the spreadsheet. The Row interface extends java.lang.Iterable
• Cell: It is an interface. It is the representation of the cell in a row of a spreadsheet. HSSFCell and XSSFCell implement a Cell interface.

Classes In Apache POI

• Apache POI has divided into the below classes:

1. HSSF
2. XSSF

• XLS Classes

• HSSFWorkbook: It’s a class that represents the XLS file.
• HSSFSheet:  It’s a class that represents the sheet in an XLS file.
• HSSFRow:  It’s a class that represents a row in the sheet of the XLS file.
• HSSFCell: It’s a class that represents a cell in a row of XLS files.

• XLSX Classes

• XSSFWorkbook: It’s a class that represents the XLSX file.
• XSSFSheet: It’s a class that represents the sheet in an XLSX file.
• XSSFRow: It’s a class that represents a row in the sheet of the XLSX file.
• XSSFCell: It’s a class that represents a cell in a row of an XLSX file.

Steps to Read Data From Excel file

1st Step: Create a simple Java project in eclipse.

2nd Step: Now, create a library folder in the project.

3rd Step: Download and add the following jar files in the library folder:

• commons-collections4-4.1.jar
• poi-3.17.jar
• poi-ooxml-3.17.jar
• poi-ooxml-schemas-3.17.jar
• xmlbeans-2.6.0.jar

4th Step: Set the Class Path:

Right-click on the project ->Build Path ->Add External JARs -> select all the jar files -> Apply and close.

5th Step: Now create a class file with the name ReadFile and write the following code in the file.

6th Step: Create an excel file with the name “student.xls” and input some data in it.

7th Step: Save and run the program.

Read Also:- How to Process Java Script Executor in Selenium Test Automation

Example of Reading Excel File

Read Also:-  How to Import Excel Data to SQLite DB Using Java

Writing Data In Excel File

 

 

 

 

 

 

 

 

 

 

 

 

 

FAQs

• What does POI stand for Apache POI?

Apache POI is a powerful collection of java libraries that can do so much more than just “poor obfuscation implementation” – it allows Java applications to interact with Microsoft files like Excel, Powerpoint, and Word. With Apache POI in your arsenal, you can alter the content of these documents quickly and easily!

• What is Apache POI in selenium?

Apache POI is an invaluable open-source java library, empowering users with the ability to modify and create Microsoft Office files like Excel seamlessly. Its versatile functions allow for speedy display and read operations on a variety of file formats – elevating productivity!

• What is Apache POI used for?

Apache POI gives Java developers the ability to read and write Microsoft Office files – from Word documents, and Excel Spreadsheets, to Powerpoint presentations. It is run by Apache Software Foundation as a sub-project of the Jakarta Project for hassle-free development.

• What is the POI formula?

The POI formula evaluation code allows you to effortlessly calculate the outcome of even the most complex Excel formulas, with ease. Learn more about this incredible API and gain a better understanding of how it can help automate your workflows today!

• Can we read an excel file in Java without Apache POI?

Don’t be discouraged – the answer is no, you have to use it but there’s nothing wrong with that! Plus, it will help further your skills as a developer; digging in and doing some research by yourself is key for honing your abilities. So go on then – get learning!

• How to set the date format in Apache POI?

Add the current date to any cell with ease! With Apache POI, you can use a style feature to apply the desired format. The set date format () method allows for fast and convenient customization of your cells’ dates.

• What is POI mapping?

POI mapping allows businesses to get the lay of the land and strategically place stores or ads for maximum customer attraction. It makes it easier for companies to determine which area will be most profitable, helping them maximize their presence in an ever-changing landscape.

If you are interested in even more software testing-related articles and information from us here at Devstringx, then we have a lot to choose from for you.