How to Read Excel File xlsx in Java using Apache Poi?: Excel files are now two different versions, Microsoft Excel 97-2003 Worksheet (.xls) and Microsoft Excel Worksheet (.xlsx). In the below post we are going to see how to read the microsoft excel worksheet xlsx file using apache poi jars. All the required jars, sample excel(.xlsx) files are attached with the complete working source code below.
Table of Contents
Update on August 6th 2018:
If we use poi-ooxml latest jar 3.17 then you will see the deprecated issues near this snippet:
[java]
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC:
excelRowCols.add(Double.toString(cell.getNumericCellValue()));
break;
case Cell.CELL_TYPE_STRING:
excelRowCols.add(cell.getStringCellValue());
break;
[/java]
Solution:
[java]
switch (cell.getCellTypeEnum()) {
case NUMERIC:
excelRowCols.add(Double.toString(cell.getNumericCellValue()));
break;
case STRING:
excelRowCols.add(cell.getStringCellValue());
break;
[/java]
poi-ooxml 3.17 maven entry for your quick reference:
[xml]
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
[/xml]
Requirements:
dom4j-1.6.1.jar
poi-3.9.jar
poi-ooxml-3.9.jar
poi-ooxml-schemas-3.9.jar
xmlbeans-2.3.0.jar
All the above jars with the working java project attached at the last of this post.
Steps in overview:
- Create new XSSFWorkbook object by giving the input xlsx file.
- Retrieve the XSSFSheet by passing the worksheet number to it. In the below program I mentioned 0 to retrieve the first worksheet from the workbook.
- Iterate the xssfSheet object and create the row object.
- Iterate the row object and read each column, cell content is checked with celltype method to avoid typecast exceptions.
- All the iterated things are stored in the arraylist.
- Iterate the array list and retrieve from it based on the column number. (A column in excel mapped with 0 element in the arraylist.)
Input Excel (.xlsx) File:
Click here to download the sampleexcel.xlsx file.
Read Excel File (.xlsx) – Java Source Code:
Change the path of the input xml in the program before running it.
[java]
package in.javadomain;
import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.Iterator;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ReadExcelFile {
public static void main(String[] args) {
System.out.println(“Reading Excel File .xlsx Starts”);
ArrayList excelRowCols = new ArrayList();
boolean skipHeaderRow = false;
String affiliateURL = “”;
String couponCode = “”;
String couponShop = “”;
String couponTitle = “”;
String expiryDate = “”;
String startDate = “”;
String couponDesc = “”;
String dealType = “”;
try {
FileInputStream xlsxfile = new FileInputStream(new File(
“E:/samplexlsx.xlsx”));
// Creating XSSFWorkbook
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(xlsxfile);
// Getting the contents from the first sheet of the workbook
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
// Iterating each row from the xlsx file
Iterator xlsxRowIterator = xssfSheet.iterator();
while (xlsxRowIterator.hasNext()) {
Row row = xlsxRowIterator.next();
// Iterating the columns in each row
Iterator cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
// cell content type check
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC:
excelRowCols.add(Double.toString(cell
.getNumericCellValue()));
break;
case Cell.CELL_TYPE_STRING:
excelRowCols.add(cell.getStringCellValue());
break;
default:
excelRowCols.add(“”);
break;
}
}
if (skipHeaderRow) {
if (excelRowCols != null && excelRowCols.size() == 10) {
if (excelRowCols.get(1) != null
&& !excelRowCols.get(1).trim().isEmpty()) {
affiliateURL = excelRowCols.get(7).trim();
couponCode = excelRowCols.get(6).trim();
couponShop = excelRowCols.get(1).trim();
couponTitle = excelRowCols.get(2).trim();
startDate = excelRowCols.get(8).trim();
expiryDate = excelRowCols.get(9).trim();
couponDesc = excelRowCols.get(3).trim();
dealType = excelRowCols.get(5).trim();
System.out.println(“Affiliate URL ==> “+affiliateURL);
System.out.println(“Coupon code ==> “+couponCode);
System.out.println(“Coupon shop ==> “+couponShop);
System.out.println(“Coupon Title ==> “+couponTitle);
System.out.println(“Start Date ==> “+startDate);
System.out.println(“Expiry Date ==> “+expiryDate);
System.out.println(“Coupon Desc ==> “+couponDesc);
System.out.println(“Deal Type ==> “+dealType);
excelRowCols = new ArrayList();
} else {
excelRowCols = new ArrayList();
}
}
} else {
skipHeaderRow = true;
excelRowCols = new ArrayList();
}
}
xlsxfile.close();
} catch (Exception e) {
e.printStackTrace();
}
System.out.println(“Reading Excel File .xlsx Completed”);
}
}
[/java]
Console Output:
[plain]
Reading Excel File .xlsx Starts
Affiliate URL ==> http://tracking.icubeswire.com/aff_c?offer_id=681&aff_id=13585&url_id=15498
Coupon code ==> MUNCH200
Coupon shop ==> Paytm
Coupon Title ==> Get 100% Cashback on Movie tickets and F&B
Start Date ==> Nov 17, 2016
Expiry Date ==> Dec 01, 2016
Coupon Desc ==> This code is only applicable for booking movie tickets and F&B on Paytm.com and Paytm App. The maximum Cashback amount that can be redeemed is INR 200. This is a limited period offer valid on a minimum transaction value of INR 500. Offer is limited to 3 transactions per user during the offer period. Please make sure to apply the Promocode before clicking on “Proceed to Pay”.
Cashback will be credited within 24 hours of a successful transaction.
Deal Type ==> Coupon
Affiliate URL ==> http://tracking.icubeswire.com/aff_c?offer_id=681&aff_id=13585&url_id=15498
Coupon code ==> FIRSTMOVIE
Coupon shop ==> Paytm
Coupon Title ==> Get Flat INR 100 Cashback
Start Date ==> Nov 17, 2016
Expiry Date ==> Dec 16, 2016
Coupon Desc ==> Max Cashback INR 100. Applicable on. Minimum 1 ticket. Maximum usage is 1 per user. Offer valid for new Users only.
Deal Type ==> Coupon
Reading Excel File .xlsx Completed
[/plain]
Download the complete Java project.
What it contains ?
Feel free to share any thoughts/comments in the below comments section.