Store Data in Excel Sheets with Java Easily

Excel spreadsheets are a staple in data management across various industries due to their versatility and user-friendliness. Java, as one of the most popular programming languages, offers robust libraries to interact with Excel files. This post will walk you through how to use Java to manipulate and store data in Excel sheets, focusing on ease of use and efficiency.

Understanding Excel Libraries for Java

Before diving into the coding aspect, it’s crucial to understand which Java libraries are commonly used for Excel file operations:

• Apache POI: Perhaps the most well-known library, it supports both .xls and .xlsx formats. Apache POI provides extensive functionality for reading, writing, and modifying Excel files.
• JExcelApi: An alternative library that only supports .xls files but is lighter in size, making it suitable for projects where memory footprint is a concern.
• OpenCSV: While not designed specifically for Excel, it can handle CSV files, which are widely compatible with Excel applications.

💡 Note: This guide will focus on Apache POI as it’s widely used and supports both Excel formats.

Setup for Apache POI

To start working with Excel files in Java, you’ll first need to add Apache POI to your project:

• Add dependencies in your pom.xml for Maven projects:

    org.apache.poi
    poi
    5.2.0


    org.apache.poi
    poi-ooxml
    5.2.0

<li>Or include the JARs directly in your classpath for other build systems.</li>

Creating an Excel Workbook

Here’s how you can create a simple Excel workbook and add data:

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import java.io.FileOutputStream;

public class ExcelDemo {
    public static void main(String[] args) throws Exception {
        // Create a Workbook
        Workbook workbook = new XSSFWorkbook();

    // Create a Sheet
    Sheet sheet = workbook.createSheet("Employee Data");

    // Create rows and cells, set values
    Row headerRow = sheet.createRow(0);
    headerRow.createCell(0).setCellValue("Name");
    headerRow.createCell(1).setCellValue("Position");
    headerRow.createCell(2).setCellValue("Age");

    Row dataRow = sheet.createRow(1);
    dataRow.createCell(0).setCellValue("John Doe");
    dataRow.createCell(1).setCellValue("Developer");
    dataRow.createCell(2).setCellValue("32");

    // Write the output to a file
    FileOutputStream fileOut = new FileOutputStream("employee.xlsx");
    workbook.write(fileOut);
    fileOut.close();
    workbook.close();

    System.out.println("Excel file has been created successfully.");
}


}

This code snippet demonstrates how to:

• Create a workbook.
• Set up a worksheet with headers.
• Add rows and cells with data.
• Write to an Excel file on disk.

Advanced Operations with Excel

After setting up your workbook, let’s explore some advanced operations:

Adding Formulas

You can add Excel formulas directly into cells:

// In the dataRow, add a formula to calculate years to retirement
Cell retirementCell = dataRow.createCell(3);
retirementCell.setCellFormula(“65 - C2”);

💡 Note: Make sure you reference cells correctly as Excel does, starting from 1, not 0.

Styling Cells

Enhance your Excel sheets with custom styles:

CellStyle headerStyle = workbook.createCellStyle();
Font font = workbook.createFont();
font.setBold(true);
headerStyle.setFont(font);
// Apply style to header row
for(int i = 0; i < 3; i++){
    headerRow.getCell(i).setCellStyle(headerStyle);
}

Reading Excel Data

Reading data from an Excel file can also be achieved using Apache POI:

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

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;

public class ExcelReadDemo {
    public static void main(String[] args) throws IOException {
        FileInputStream file = new FileInputStream(new File(“employee.xlsx”));
        Workbook workbook = new XSSFWorkbook(file);
        Sheet sheet = workbook.getSheetAt(0);

    Iterator<Row> rowIterator = sheet.iterator();
    // Iterate over rows and cells to read data
    while (rowIterator.hasNext()) {
        Row row = rowIterator.next();
        Iterator<Cell> cellIterator = row.cellIterator();
        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();
            switch (cell.getCellType()) {
                case STRING:
                    System.out.print(cell.getStringCellValue() + "\t");
                    break;
                case NUMERIC:
                    System.out.print(cell.getNumericCellValue() + "\t");
                    break;
                case BOOLEAN:
                    System.out.print(cell.getBooleanCellValue() + "\t");
                    break;
                default:
                    System.out.print("Unknown Cell Type: " + cell.getCellType() + "\t");
            }
        }
        System.out.println();
    }
    workbook.close();
}


}

💡 Note: Remember to handle different cell types appropriately when reading data.

As we wrap up this guide on managing Excel files with Java, we've explored several key functionalities provided by Apache POI. These include creating workbooks, adding and styling data, integrating formulas, and reading from existing files. This toolkit not only simplifies Excel manipulation in Java but also enhances data management in numerous applications.

What are the advantages of using Apache POI over other libraries?

+

Apache POI supports both .xls and .xlsx formats, provides extensive documentation, and has a large community, which contributes to its wide adoption. Its rich feature set makes it suitable for complex Excel operations.

Can I read password-protected Excel files with Apache POI?

+

Yes, but you will need to provide the password when opening the workbook. Apache POI has methods to decrypt password-protected files.

How can I optimize the memory usage when dealing with large Excel files?

+

Apache POI offers SXSSFWorkbook for streaming large datasets. This class keeps only a small number of rows in memory and writes the rest to disk, reducing memory consumption.