5 Ways to Create Excel Sheets in Java Using Mkyong

Java is a versatile programming language that provides developers with numerous libraries and tools to interact with various file formats, including spreadsheets like those in Microsoft Excel. In this blog post, we explore five different methods to create Excel sheets in Java using resources inspired by Mkyong, a reputable site known for its tutorials on Java and more. Here, we'll dive into various approaches, each offering unique features and use cases, ensuring that you can find the most fitting solution for your Excel needs in Java projects.

1. Apache POI

Apache POI, standing for Poor Obfuscation Implementation, is a popular open-source library for working with Microsoft Office document formats. Here’s how you can use Apache POI to create an Excel sheet:

• Add Dependency: Include Apache POI in your project dependencies.
• Create Workbook: Start by creating a new workbook.
• Create Sheet: Add a sheet to the workbook.
• Populate Sheet: Add rows and cells to the sheet with your data.

Example:

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileOutputStream;
import java.io.IOException;

public class ExcelExample {
    public static void main(String[] args) throws IOException {
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("Employee Data");
        
        // Create a row and put some cells in it. Rows are 0 based.
        Row row = sheet.createRow(0);
        row.createCell(0).setCellValue("Employee ID");
        row.createCell(1).setCellValue("Name");
        
        // Save the workbook to an output stream
        FileOutputStream fileOut = new FileOutputStream("workbook.xlsx");
        workbook.write(fileOut);
        fileOut.close();
        workbook.close();
    }
}

📝 Note: Apache POI can handle both .xls (HSSFWorkbook) and .xlsx (XSSFWorkbook) file formats, providing extensive formatting options but at the cost of larger binary size due to its comprehensive feature set.

2. JExcelApi

JExcelApi, though less updated, remains a straightforward option for working with Excel files:

• Add JExcelApi: Ensure your project includes JExcelApi.
• Create Workbook: Initialize a workbook instance.
• Create Sheet: Add a sheet to the workbook.
• Add Data: Populate cells with data.

Example:

import jxl.*;
import jxl.write.*;

public class JExcelApiExample {
    public static void main(String[] args) throws Exception {
        WritableWorkbook workbook = Workbook.createWorkbook(new File("example.xls"));
        WritableSheet sheet = workbook.createSheet("Sheet 1", 0);
        
        // Add data
        Label label = new Label(0, 0, "Hello Excel!");
        sheet.addCell(label);

        workbook.write();
        workbook.close();
    }
}

📝 Note: While JExcelApi offers an older, less feature-rich interface compared to Apache POI, it can still be useful for simple Excel file creation with a smaller footprint.

3. JXLS

JXLS stands for Java Excel Library Services, which leverages templates for creating Excel files:

• Include JXLS: Ensure your project includes JXLS and POI libraries.
• Create Template: Design your Excel template.
• Load Template: Load the template into your application.
• Fill Data: Populate the template with your data.
• Output Results: Generate and save the new Excel file.

import org.jxls.common.Context;
import org.jxls.util.JxlsHelper;

public class JXLSTemplate {
    public static void main(String[] args) throws Exception {
        // Assuming you have an Excel template at "template.xlsx"
        Context context = new Context();
        context.putVar("name", "John");
        
        JxlsHelper.getInstance().processTemplate("template.xlsx", "output.xlsx", context);
    }
}

📝 Note: JXLS is particularly useful for reporting, as it can manage complex Excel sheet designs without having to code extensive cell positioning.

4. OpenCSV

While primarily designed for CSV files, OpenCSV can be employed to create Excel-compatible files:

• Include OpenCSV: Add OpenCSV to your project dependencies.
• Create Data: Prepare your data in Java.
• Write to CSV: Use OpenCSV to write your data to a CSV file.
• Excel Compatibility: Ensure the CSV is formatted for Excel import.

import com.opencsv.CSVWriter;

public class OpenCSVExample {
    public static void main(String[] args) throws IOException {
        String[] header = {"Name", "Age"};
        String[] row = {"John", "25"};
        
        CSVWriter writer = new CSVWriter(new FileWriter("output.csv"));
        writer.writeNext(header);
        writer.writeNext(row);
        writer.close();
    }
}

📝 Note: While this method doesn't directly create Excel files, it produces a file type that Excel can read, making it a quick and lightweight solution for basic data export.

5. EasyXLS

EasyXLS provides an easy interface for working with Excel files, especially suited for .NET developers migrating to Java:

• Include EasyXLS: Ensure your project has the EasyXLS library.
• Create Workbook: Instantiate a workbook object.
• Create Sheet: Add a new sheet.
• Add Data: Insert data into cells.
• Save File: Save your workbook to an Excel file.

import com.easyxls.Workbook;
import com.easyxls.Worksheet;

public class EasyXLSExample {
    public static void main(String[] args) throws Exception {
        Workbook workbook = new Workbook();
        Worksheet sheet = workbook.addSheet("Test");
        
        sheet.setCell(0, 0, "Hello World");
        
        workbook.save("example.xlsx");
    }
}

📝 Note: EasyXLS, although priced, offers simplicity and robust performance, making it an excellent choice for developers needing rapid Excel file manipulation without diving into complexity.

With these methods, Java developers have a plethora of tools at their disposal to create Excel sheets tailored to various needs. Each method provides its own benefits:

• Apache POI: Versatile with extensive Excel functionality.
• JExcelApi: Simple, lightweight for older Excel files.
• JXLS: Perfect for reports and template-based sheets.
• OpenCSV: Quick and dirty for basic data export.
• EasyXLS: Offers simplicity with a performance edge for more commercial or complex tasks.

In conclusion, by understanding these tools and techniques, you can select the right method to generate Excel files in Java, ensuring you have the flexibility to meet project demands, from simple data dumps to complex report generation.

Which library should I use to create Excel files in Java?

+

The choice depends on your requirements:

• For extensive Excel features, use Apache POI.
• For simplicity and smaller footprints, consider JExcelApi.
• JXLS for template-based Excel generation.
• OpenCSV for quick CSV to Excel conversion.
• EasyXLS for paid simplicity with performance.

Can Apache POI handle both .xls and .xlsx formats?

+

Yes, Apache POI can handle both .xls files (HSSFWorkbook) and .xlsx files (XSSFWorkbook).

Is JXLS good for creating reports from templates?

+

Absolutely. JXLS excels in this area by allowing you to design your report in Excel and then fill it with dynamic data from Java.

What is the benefit of using OpenCSV?

+

OpenCSV offers a lightweight solution for data export which can be easily read by Excel, ideal for basic reporting needs without requiring extensive Excel functionality.