How to read and write Excel files

java

file-io

excel

poi

byAlex Kataev·Dec 18, 2024

The standard approach for handling Excel files in Java leverages the Apache POI library. The XSSFWorkbook class assists in creating or modifying .xlsx workbook, and the XSSFSheet class performs sheet operations. Here's a quick example of writing to an Excel file:

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

import java.io.FileOutputStream;

public class ExcelExample {
    public static void main(String[] args) throws Exception {
        try (Workbook workbook = new XSSFWorkbook()) {
            Sheet sheet = workbook.createSheet("Example");
            Row row = sheet.createRow(0);
            row.createCell(0).setCellValue("Hello");  // Suddenly cell A1 exclaims: "Hello"
            row.createCell(1).setCellValue("World");  // And then cell B1 quickly chimed in: "World"

            FileOutputStream out = new FileOutputStream("Example.xlsx");
            workbook.write(out);
            out.close();  // Done with the file, let's close it (Don't you love clean-up code?)
        }
    }
}

In this "next Hollywood script", we're generating a single sheet with "Hello" in cell A1 and "World" in cell B1, then the master script is written to Example.xlsx. Apache POI, of course, must be on stage (i.e., in your build path) for the show to go on.

Taking it to the next level

Reading and writing files like a pro

Apache POI lets you manipulate Excel files with precision. The Sheet's getLastRowNum() method, for example, lets you dynamically fetch the row count. Handy, right?

Heavy-duty file operations

If your Excel files are packing on the pounds (i.e., they're quite large), Apache POI has got you covered. The SXSSFWorkbook and SXSSFSheet classes are optimized for large files, reducing your memory footprint. Neat!

Like it never happened: Error handling

Wrap your I/O operations in try-catch blocks and always close your resources. Remember: code cleanliness is next to godliness.

Reading an Excel file: A walk-through

You'll need an example, of course. Let's read an Example.xlsx:

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

import java.io.FileInputStream;
import java.util.Iterator;

public class ExcelReadExample {
    public static void main(String[] args) throws Exception {
        try (FileInputStream fis = new FileInputStream("Example.xlsx");
             Workbook workbook = new XSSFWorkbook(fis)) {
            Sheet sheet = workbook.getSheetAt(0);
            Iterator<Row> rowIt = sheet.iterator();
            
            while (rowIt.hasNext()) {
                Row row = rowIt.next();
                Iterator<Cell> cellIt = row.cellIterator();
                
                while (cellIt.hasNext()) {
                    Cell cell = cellIt.next();
                    // Here's where you'd handle different cell types – to be or not to be (a DataFormatter)
                    System.out.print(cell.toString() + "; ");
                }
                System.out.println();  // Print a new line because... it's just polite, you know?
            }
        }
    }
}

Crossing the t's and dotting the i's

Data types: The good, the bad, and the 'How does Excel even hold that?'

The type of the data stored in an Excel cell could be anything – numeric, string, boolean, or formula. DataFormatter is your personal stylist - it styles data to appear just as it does in Excel.

Making your Excel "POP"

To create visually appealing Excel documents, Apache POI provides a CreationHelper. You can create RichTextString instances for handled styled text within the cells.

Other libraries in the park

When your requirements are less intricate, consider JExcelApi or Xcelite. They augment your toolset with a simplified, annotation-driven approach to Excel file manipulation.

Don't miss out these libraries

Straightforward tasks with JExcelAPI

For less complex needs, the JExcelApi trims down feature-fat for ease of use.

Xcelite to the rescue

The Xcelite library uses simple annotations to link Java objects to Excel columns, simplifying CRUD like operations on spreadsheets. It's great if you're dealing with POJOs that map to an Excel schema.

explain-codes / Java / How to read and write Excel files

Linked

Using Pandas to pd.read_excel() for multiple worksheets of the same workbook



Google spreadsheet "=QUERY" join() equivalent function?



How do I analyze a .hprof file?



Cannot import XSSF in Apache POI



Export html table data to Excel using JavaScript / JQuery is not working properly in chrome browser

