We can read data from a Microsoft Excel CSV file, where all the values will be column separated, to do this we will use a third party jar i.e OpenCSV.
OpenCSV
OpenCSV is a lightweight java CSV parser. OpenCSV provides most of the basic features for CSV parsing. Some of the important classes in OpenCSV parser are;
CSVReader: This is the most important class in OpenCSV. CSVReader class is used to parse CSV files. We can parse CSV data line by line or read all data at once.
CSVWriter: CSVWriter class is used to write CSV data to Writer implementation.
CsvToBean: CsvToBean is used when you want to convert CSV data to java objects.
BeanToCsv: BeanToCsv is used to export Java beans to CSV file.
OpenCSV Maven Dependency
<dependency>
<groupId>com.opencsv</groupId>
<artifactId>opencsv</artifactId>
<version>3.8</version>
</dependency>
There are two ways to read a csv file using OpenCSV
- Reading into an array of strings
- Reading into beans
Sample data :
Create java bean class to hold CSV data and all fields mention in POJO class should match the column name of CSV file and they are case sensitive.
package com.Excel;
import com.opencsv.bean.CsvBindByName;
public class Employee {
@CsvBindByName
private String Empid;
@CsvBindByName
private String name;
@CsvBindByName
private String salary;
@CsvBindByName
private String valid;
public String getEmpid() {
return Empid;
}
public void setEmpid(String empid) {
Empid = empid;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSalary() {
return salary;
}
public void setSalary(String salary) {
this.salary = salary;
}
public String getValid() {
return valid;
}
public void setValid(String valid) {
this.valid = valid;
}
}
we are using @CsvBindByName annotation which identify all columns by name in CSV file.
Main class to read our CSV file.
package com.Excel;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.util.List;
import com.opencsv.bean.CsvToBeanBuilder;
public class ReadEXcel {
@SuppressWarnings({ "unchecked", "rawtypes" })
public static void main(String[] args) {
List<Employee> beans;
try {
beans = new CsvToBeanBuilder(new FileReader("test.csv")).withType(Employee.class).build().parse();
for (Employee employee : beans) {
System.out.println(employee.getEmpid());
System.out.println(employee.getName());
System.out.println(employee.getSalary());
}
} catch (IllegalStateException e) {
e.printStackTrace();
} catch (FileNotFoundException e) {
e.printStackTrace();
}
}
}
This jar gives us freedom of type conversions (wrapped and unwrapped primitives and Strings) occur automatically which is a big relief as a programmer you don't bother about the type in excel as it will handle by your POJO class which you have created.
