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).

6. Conclusion

Excel is a ubiquitous tool in various industries for data management, analysis, and reporting. In Java development, the need to interact with Excel files is common, whether it’s for importing data into applications, exporting reports, or performing data analysis tasks. To address this need, developers have access to several libraries tailored for Excel data processing. One such library is Apache POI, which stands as a comprehensive solution offering extensive functionalities for reading, writing, and manipulating Excel files in both .xls and .xlsx formats. With Apache POI, developers can handle complex Excel operations efficiently, albeit at the cost of a somewhat steep learning curve due to its complex API and potential memory overhead for large datasets.

In contrast, Poiji offers a lightweight alternative focusing on simplicity and ease of use. It excels in automatically mapping Excel columns to Java object fields, streamlining the process of reading Excel data into Java objects. Poiji’s straightforward API and automatic mapping capabilities make it an attractive choice for developers seeking a quick and hassle-free solution for basic Excel data extraction tasks. However, Poiji lacks some advanced features present in Apache POI, limiting its suitability for more complex Excel processing requirements.

FastExcel, on the other hand, emphasizes performance and efficiency. It is designed to handle large Excel files with minimal memory usage, leveraging streaming capabilities to process data efficiently. FastExcel’s high-performance capabilities make it well-suited for scenarios where processing speed is paramount, such as handling massive datasets or generating Excel reports in real time. While FastExcel offers impressive performance, it does not provide the same level of feature richness as Apache POI, which could be a trade-off depending on the project’s requirements.

Lastly, JExcelApi (Jxl) represents a lightweight library for basic Excel operations in Java. Although it offers a simple API for reading, writing, and manipulating Excel files, its development has ceased, and it lacks support for newer Excel formats like .xlsx. While Jxl suffices for small to medium-sized tasks requiring basic Excel functionality, its limitations in terms of ongoing development and support for modern Excel formats make it less suitable for larger, more complex projects.

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

and many more ....

I agree to the Terms and Privacy Policy

Sign up

Thank you!

We will contact you soon.