Converting Excel Data Into a List Of Java Objects

Understanding data mapping holds significant importance in software development. Given Excel’s widespread usage in data management, Java developers must learn and explore the process of mapping data between Excel and Java objects. Let us delve into understanding various libraries to convert Excel data into a list of Java objects.

1. Comparison of Excel Data Processing Libraries

Let’s take a look at the different libraries to convert Excel data into a collection of Java objects.

2. Converting Excel Data into a List of Java Objects using Apache POI

Apache POI is a popular Java library for working with Microsoft Office documents. It provides extensive support for reading, writing, and manipulating Excel files, catering to both .xls and .xlsx formats. With its rich feature set, Apache POI enables developers to handle various Excel operations efficiently. However, its complex API and memory-intensive nature for large files might pose challenges for beginners and resource-constrained environments.

2.1 Include Apache POI Dependency

First, ensure that the Apache POI library is included in your project’s dependencies. You can add the following Maven dependency to your project’s pom.xml file:

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>5.2.4</version>
</dependency>

2.2 Read Excel File

Create a class that reads data from an Excel file using Apache POI. The following code snippet demonstrates how to read an Excel file and convert its data into a list of Java objects:

package com.jcg.example.apachepoi;

import org.apache.poi.ss.usermodel.*;

public class ExcelReader {
    public static List<YourObject> readExcelData(File excelFile) {
        List<YourObject> dataList = new ArrayList<>();
        try (Workbook workbook = WorkbookFactory.create(excelFile)) {
            Sheet sheet = workbook.getSheetAt(0);
            for (Row row : sheet) {
                YourObject obj = new YourObject();
                obj.setField1(row.getCell(0).getStringCellValue());
                obj.setField2(row.getCell(1).getNumericCellValue());
                // Set other fields accordingly
                dataList.add(obj);
            }
        } catch (IOException | InvalidFormatException e) {
            e.printStackTrace();
        }
        return dataList;
    }
}

2.3 Define Your Java Object

Create a Java class representing the structure of your data. Define fields for each column in your Excel sheet:

package com.jcg.example.apachepoi;

public class YourObject {
    private String field1;
    private double field2;
    
    // Define other fields, constructors, getters, and setters
}

2.4 Convert Excel Data into List of Java Objects

Finally, create a main class to execute the conversion process. Specify the path to your Excel file and call the ExcelReader class to convert the data:

package com.jcg.example.apachepoi;

import java.io.File;
import java.util.List;

public class ExcelToJavaConverter {
    public static void main(String[] args) {
        File excelFile = new File("path/to/your/excel/file.xlsx");
        List<YourObject> dataList = ExcelReader.readExcelData(excelFile);
        // Process dataList as needed
    }
}

By following these steps, you can effectively convert Excel data into a list of Java objects using Apache POI.

3. Converting Excel Data into a List of Java Objects using Poiji

Poiji is a lightweight Java library for reading Excel files into a collection of Java objects. With its simple and easy-to-use API, Poiji excels in the automatic mapping of Excel columns to Java object fields, alleviating the need for explicit cell-to-field mapping. While Poiji provides convenience and ease of use, it lack some advanced features present in Apache POI, such as support for formulas or advanced formatting.

3.1 Include Poiji Dependency

Begin by including the Poiji dependency in your project. You can add the following Maven dependency to your project’s pom.xml file:

<dependency>
    <groupId>com.github.kireebloo</groupId>
    <artifactId>poiji</artifactId>
    <version>4.1.0</version>
</dependency>

3.2 Define Your Java Object

Create a Java class representing the structure of your data. Define fields for each column in your Excel sheet:

package com.jcg.example.poiji;

import lombok.Data;

@Data
public class YourObject {
    private String field1;
    private double field2;
    
    // Define other fields as necessary
}

3.3 Read Excel File and Convert to List of Java Objects

Instantiate the Poiji class and call the method to read the Excel file and convert it into a list of Java objects:

package com.jcg.example.poiji;

import com.poiji.bind.Poiji;

import java.io.File;
import java.util.List;

public class ExcelToJavaConverter {
    public static void main(String[] args) {
        File excelFile = new File("path/to/your/excel/file.xlsx");
        List<YourObject> dataList = Poiji.fromExcel(excelFile, YourObject.class);
        
        // Process dataList as needed
    }
}

3.4 Processing the List of Java Objects

After converting the Excel data into a list of Java objects, you can manipulate the data as required by your application:

for (YourObject obj : dataList) {
    System.out.println(obj.getField1());
    System.out.println(obj.getField2());
    
    // Output other fields as necessary
}

By following these steps, you can effectively convert Excel data into a list of Java objects using Poiji.

4. Converting Excel Data into a List of Java Objects using FastExcel

FastExcel is a Java library designed for efficient reading and writing of Excel files with a low memory footprint. It offers streaming capabilities for handling large Excel files efficiently, making it suitable for scenarios where performance is critical. While FastExcel provides excellent performance and a straightforward API, it does not offer the same level of feature richness as Apache POI.

4.1 Include FastExcel Dependency

Begin by including the FastExcel dependency in your project. You can add the following Maven dependency to your project’s pom.xml file:

<dependency>
    <groupId>com.github.kumarshantanu</groupId>
    <artifactId>java-excel-fast-export</artifactId>
    <version>2.4.7</version>
</dependency>

4.2 Define Your Java Object

Create a Java class representing the structure of your data. Define fields for each column in your Excel sheet:

package com.jcg.example.fastexcel;

import lombok.Data;

@Data
public class YourObject {
    private String field1;
    private double field2;
    
    // Define other fields as necessary
}

4.3 Read Excel File and Convert to List of Java Objects

Instantiate the FastExcel class and call the method to read the Excel file and convert it into a list of Java objects:

package com.jcg.example.fastexcel;

import com.github.kumarshantanu.javaexcelexporter.model.Exportable;
import com.github.kumarshantanu.javaexcelexporter.ExcelExporter;

import java.io.File;
import java.util.List;

public class ExcelToJavaConverter {
    public static void main(String[] args) {
        File excelFile = new File("path/to/your/excel/file.xlsx");
        List<YourObject> dataList = new ExcelExporter().read(YourObject.class).from(excelFile);
        
        // Process dataList as needed
    }
}

4.4 Processing the List of Java Objects

After converting the Excel data into a list of Java objects, you can manipulate the data as required by your application:

for (YourObject obj : dataList) {
    System.out.println(obj.getField1());
    System.out.println(obj.getField2());
    
    // Output other fields as necessary
}

By following these steps, you can effectively convert Excel data into a list of Java objects using FastExcel.

5. Converting Excel Data into a List of Java Objects using JExcelApi (Jxl)

JExcelApi, also known as Jxl, is a lightweight library for basic Excel operations in Java. It offers a simple API for reading, writing, and manipulating Excel files, making it suitable for small to medium-sized tasks. However, Jxl’s development has ceased, and it lacks support for newer Excel formats (.xlsx), which limit its applicability in modern Java projects.

5.1 Include Jxl Dependency

Begin by including the Jxl dependency in your project. You can add the following Maven dependency to your project’s pom.xml file:

<dependency>
    <groupId>net.sourceforge.jexcelapi</groupId>
    <artifactId>jxl</artifactId>
    <version>2.6.12</version>
</dependency>

5.2 Define Your Java Object

Create a Java class representing the structure of your data. Define fields for each column in your Excel sheet:

package com.jcg.example.jxl;

public class YourObject {
    private String field1;
    private double field2;
    
    // Define other fields as necessary
}

5.3 Read Excel File and Convert to List of Java Objects

Instantiate the Workbook class from Jxl and iterate through the rows and columns of the Excel file to convert it into a list of Java objects:

package com.jcg.example.jxl;

import jxl.*;
import java.io.File;
import java.util.ArrayList;
import java.util.List;

public class ExcelToJavaConverter {
    public static void main(String[] args) {
        File excelFile = new File("path/to/your/excel/file.xls");
        List<YourObject> dataList = new ArrayList<>();
        
        try {
            Workbook workbook = Workbook.getWorkbook(excelFile);
            Sheet sheet = workbook.getSheet(0); // Assuming the first sheet is to be read
            
            for (int row = 0; row < sheet.getRows(); row++) {
                YourObject obj = new YourObject();
                obj.setField1(sheet.getCell(0, row).getContents());
                obj.setField2(Double.parseDouble(sheet.getCell(1, row).getContents()));
                // Set other fields accordingly
                dataList.add(obj);
            }
            
            workbook.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        
        // Process dataList as needed
    }
}

5.4 Processing the List of Java Objects

After converting the Excel data into a list of Java objects, you can manipulate the data as required by your application:

for (YourObject obj : dataList) {
    System.out.println(obj.getField1());
    System.out.println(obj.getField2());
    
     // Output other fields as necessary
}

By following these steps, you can effectively convert Excel data into a list of Java objects using JExcelApi (Jxl).

Sign up