Creating an Excel File in Java Using Apache POI

 

Creating an Excel File in Java Using Apache POI

Apache POI is a powerful Java library for working with Microsoft Office documents, including Excel. In this tutorial, we’ll demonstrate how to create an .xlsx file, add a header row, and populate it with data using Apache POI.

---

Prerequisites

Before diving into the code, ensure that your project includes the Apache POI library. If you're using Maven, add the following dependencies to your pom.xml:

<dependencies>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>5.2.3</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>5.2.3</version>
    </dependency>
</dependencies>

---

Java Program to Create and Write an Excel File

Here is a complete Java program that demonstrates how to create an Excel file, add a header row, and populate it with sample data:

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

import java.io.FileOutputStream;
import java.io.IOException;

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

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

        // Create a header row
        Row headerRow = sheet.createRow(0);
        String[] headers = {"ID", "Name", "Age", "Department"};

        // Add headers to the first row
        for (int i = 0; i < headers.length; i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(headers[i]);
        }

        // Add sample data
        Object[][] data = {
            {1, "John Doe", 28, "Engineering"},
            {2, "Jane Smith", 34, "Marketing"},
            {3, "Samuel Green", 45, "HR"}
        };

        int rowNum = 1; // Start from the second row
        for (Object[] rowData : data) {
            Row row = sheet.createRow(rowNum++);
            for (int colNum = 0; colNum < rowData.length; colNum++) {
                Cell cell = row.createCell(colNum);
                if (rowData[colNum] instanceof String) {
                    cell.setCellValue((String) rowData[colNum]);
                } else if (rowData[colNum] instanceof Integer) {
                    cell.setCellValue((Integer) rowData[colNum]);
                }
            }
        }

        // Autosize the columns
        for (int i = 0; i < headers.length; i++) {
            sheet.autoSizeColumn(i);
        }

        // Write to a file
        try (FileOutputStream fos = new FileOutputStream("SampleData.xlsx")) {
            workbook.write(fos);
            System.out.println("Excel file written successfully.");
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            // Close the workbook
            try {
                workbook.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
}

---

Explanation of the Code

1. Creating the Workbook and Sheet:

   • Workbook: Represents the entire Excel workbook.
   • Sheet: Represents a sheet in the workbook where data will be written.

2. Adding a Header Row:

   • A header row is created at index 0 with column names like "ID", "Name", "Age", and "Department".

3. Populating Data:

   • Sample data is defined as a 2D array and written row by row, starting from the second row (index 1).

4. Autosizing Columns:

   • Columns are automatically resized to fit their content for better readability using sheet.autoSizeColumn().

5. Writing to a File:

   • The workbook is saved to a file named SampleData.xlsx using a FileOutputStream.

6. Closing Resources:

   • Resources are closed properly to prevent memory leaks.

---

Output

Running this program generates an Excel file SampleData.xlsx with the following content:

ID	Name	Age	Department
1	John Doe	28	Engineering
2	Jane Smith	34	Marketing
3	Samuel Green	45	HR

---

If you want to insert data explicitly into specific cells, you can modify the code to directly reference each cell by its row and column indices. Here's the updated program

Java Program to Create and Write an Excel File with Explicit value insertion in cell

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

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileOutputStream;

import java.io.IOException;

public class ExcelWriterExplicit {

    public static void main(String[] args) {

        // Create a workbook

        Workbook workbook = new XSSFWorkbook();

        // Create a sheet

        Sheet sheet = workbook.createSheet("Data");

        // Create a header row

        Row headerRow = sheet.createRow(0);

        // Set headers explicitly

        headerRow.createCell(0).setCellValue("ID");

        headerRow.createCell(1).setCellValue("Name");

        headerRow.createCell(2).setCellValue("Age");

        headerRow.createCell(3).setCellValue("Department");

        // Insert data explicitly into cells

        Row row1 = sheet.createRow(1); // Second row

        row1.createCell(0).setCellValue(1);              // ID

        row1.createCell(1).setCellValue("John Doe");     // Name

        row1.createCell(2).setCellValue(28);             // Age

        row1.createCell(3).setCellValue("Engineering");  // Department

        Row row2 = sheet.createRow(2); // Third row

        row2.createCell(0).setCellValue(2);              // ID

        row2.createCell(1).setCellValue("Jane Smith");   // Name

        row2.createCell(2).setCellValue(34);             // Age

        row2.createCell(3).setCellValue("Marketing");    // Department

        Row row3 = sheet.createRow(3); // Fourth row

        row3.createCell(0).setCellValue(3);              // ID

        row3.createCell(1).setCellValue("Samuel Green"); // Name

        row3.createCell(2).setCellValue(45);             // Age

        row3.createCell(3).setCellValue("HR");           // Department

        // Autosize the columns

        for (int i = 0; i < 4; i++) {

            sheet.autoSizeColumn(i);

        }

        // Write to a file

        try (FileOutputStream fos = new FileOutputStream("ExplicitSampleData.xlsx")) {

            workbook.write(fos);

            System.out.println("Excel file written successfully.");

        } catch (IOException e) {

            e.printStackTrace();

        } finally {

            // Close the workbook

            try {

                workbook.close();

            } catch (IOException e) {

                e.printStackTrace();

            }

        }

    }

}

Key Changes

1. Explicit Data Insertion:

   • Each cell is accessed and set individually using Row.createCell(index).setCellValue(value).
   • For example, row1.createCell(0).setCellValue(1) explicitly sets the value 1 in the first cell of the second row.

2. Rows and Columns Defined:

   • Each row is created explicitly, and cells within the row are defined for specific data points.

3. Output File:

• Saves the data into a file named ExplicitSampleData.xlsx

Why Use Apache POI?

• Cross-Platform: Works seamlessly on all Java-supported platforms.
• Feature-Rich: Supports advanced features like formulas, formatting, and charts.
• Widely Used: Trusted by developers for handling Excel files programmatically.

---

Conclusion

This tutorial demonstrates how to create and write data to an Excel file using Java and Apache POI. By following this approach, you can easily generate Excel files for reporting, data export, or any other purpose.