How to read XLS and XLSX Excel files in Java
Where need to convert each column and row values in Java Objects using POJO class.
To make this project we need a POJO class of columns present in the CSV file.
In this tutorial, we are going to use the Customer details XLS and XLSX file as below.
Here we have a Customer ID, Name, City, Pincode, and State Code.
We may have more than that data and might be there are multiple fields that are blank.
So ID-103 having Customer Name - Geetha having blank City name, same with others as well for other fields.
We are going to handle these scenarios as well using the try-catch block.
In the beginning, we need to add some dependencies in pom.xml for XLS and XLSX files.
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>ReadExcelFiles</groupId>
<artifactId>ReadExcelFiles</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>ExcelFileReading</name>
<build>
<sourceDirectory>src</sourceDirectory>
<plugins>
<plugin>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.1</version>
<configuration>
<source>1.8</source>
<target>1.8</target>
</configuration>
</plugin>
</plugins>
</build>
<dependencies>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.12</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.12</version>
</dependency>
</dependencies>
</project>
|
Now we will create a Customer Pojo class as below.
File Location: src/com/slash/code/Customer.java
package com.slashcode.pojo;
public class Customer {
private int custId;
private String custName;
private String custCity;
private int pinCode;
private String stateCode;
public Customer(){}
public Customer(int custId, String custName, String custCity, int pinCode,
String stateCode) {
super();
this.custId = custId;
this.custName = custName;
this.custCity = custCity;
this.pinCode = pinCode;
this.stateCode = stateCode;
}
@Override
public String toString() {
return "Customer [custId=" + custId + ", custName=" + custName
+ ", custCity=" + custCity + ", pinCode=" + pinCode
+ ", stateCode=" + stateCode + "]";
}
public int getCustId() {
return custId;
}
public void setCustId(int custId) {
this.custId = custId;
}
public String getCustName() {
return custName;
}
public void setCustName(String custName) {
this.custName = custName;
}
public String getCustCity() {
return custCity;
}
public void setCustCity(String custCity) {
this.custCity = custCity;
}
public int getPinCode() {
return pinCode;
}
public void setPinCode(int pinCode) {
this.pinCode = pinCode;
}
public String getStateCode() {
return stateCode;
}
public void setStateCode(String stateCode) {
this.stateCode = stateCode;
}
}
|
So here in Customer Pojo, we will have the required properties, their getters, and setters, default constructor, parameterized constructor. toString() is for printing objects using System.out.println() function.
Now we will have our main function where the program gets the start, and execute required functionality.
For XLS file:
File Location: src/com/slashcode/excel/ReadXLSFile.java
|
Now in ReadXLSFile.java file, we have three functions as below:
- main()
- To start the execution of the program, where we are passing file location and getting results in a list of the customers.
- We are iterating customers list one by one here as well.
- readXLSFile(String file)
- It will take file location as input and returns the list of customers present in that file.
- Here we are going to use HSSF from poi jar, to get sheet and rows. Using the same we will fetch rows records one by one.
- We have handled each blank value using a try-catch block.
For XLSX file:
File Location: src/com/slashcode/excel/ReadXLSX.java
package com.slashcode.excel;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import com.slashcode.pojo.Customer;
public class ReadXLSX {
public static void main(String[] args) {
List<Customer> custList = readXLSXFile("D:\\Test\\CustDetailsXLSX.xlsx");
for(Customer cust : custList){
System.out.println(cust);
}
}
private static List<Customer> readXLSXFile(String file) {
List<Customer> listCust = new ArrayList<Customer>();
try {
XSSFWorkbook work = new XSSFWorkbook(new FileInputStream(file));
XSSFSheet sheet = work.getSheet("Customer");
XSSFRow row = null;
int i=0;
while((row = sheet.getRow(i))!=null){
int custId,pinCode;
String custName,custCity,stateCode;
try{
custId = (int) row.getCell(0).getNumericCellValue();
}
catch(Exception e){custId = 0;}
try{
custName = row.getCell(1).getStringCellValue();
}
catch(Exception e){custName = null;}
try{
custCity = row.getCell(2).getStringCellValue();
}
catch(Exception e){custCity = null;}
try{
pinCode = (int) row.getCell(3).getNumericCellValue();
}
catch(Exception e){pinCode = 0;}
try{
stateCode = row.getCell(4).getStringCellValue();
}
catch(Exception e){stateCode = null;}
Customer cust = new Customer(custId,custName,custCity,pinCode,stateCode);
listCust.add(cust);
i++;
}
work.close();
} catch (IOException e) {
System.out.println("Exception is Customer fetch data :: "+e.getMessage());
e.printStackTrace();
}
return listCust;
}
}
|
Now in ReadXLSX.java file, we have three functions as below:
- main()
- To start the execution of the program, where we are passing file location and getting results in a list of the customers.
- We are iterating customers list one by one here as well.
- readXLSXFile(String file)
- It will take file location as input and returns the list of customers present in that file.
- Here we are going to use XSSF from poi jar, to get sheet and rows. Using the same we will fetch rows records one by one.
- We have handled each blank value using a try-catch block.
Youtube video link for your reference:
To load XLS file:
To load XLSX file:
Comments
Post a Comment