Adding Dependencies

To manipulate Excel spreadsheets in Java, you need to include the appropriate libraries. Apache POI handles both legacy and modern Excel formats, while JXL provides an alternative specifically for older .xls files. Add the following to you're Maven pom.xml:

<!-- Apache POI for .xls and .xlsx support -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.17</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.17</version>
</dependency>

<!-- JXL for legacy .xls processing -->
<dependency>
    <groupId>net.sourceforge.jexcelapi</groupId>
    <artifactId>jxl</artifactId>
    <version>2.6.12</version>
</dependency>

Writing Data to Excel with Apache POI

The following class demonstrates how to export data into an Excel file. It checks the file extension to instantiate the correct Workbook implementation (HSSFWorkbook for .xls or XSSFWorkbook for .xlsx). Before writing new data, it clears any existing rows below the header, then populates the sheet with the provided records.

package com.example.excel;

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

import java.io.*;
import java.util.*;

public class ExcelDataExporter {
    private static final String XLS_EXT = ".xls";
    private static final String XLSX_EXT = ".xlsx";

    public static void main(String[] args) {
        List<Map<String, String>> records = new ArrayList<>();
        Map<String, String> entry = new HashMap<>();
        entry.put("Institution", "Global Bank");
        entry.put("Location", "New York");
        entry.put("Contact", "555-0199");
        records.add(entry);

        writeToExcel(records, "D:/outputData.xlsx");
    }

    public static void writeToExcel(List<Map<String, String>> records, String filePath) {
        FileOutputStream fos = null;
        try {
            File targetFile = new File(filePath);
            Workbook workbook = createWorkbook(targetFile);
            Sheet sheet = workbook.getSheetAt(0);

            // Remove existing rows below the header
            int lastRow = sheet.getLastRowNum();
            for (int i = 1; i <= lastRow; i++) {
                Row row = sheet.getRow(i);
                if (row != null) {
                    sheet.removeRow(row);
                }
            }

            // Write new records to the sheet
            int rowIndex = 1;
            for (Map<String, String> record : records) {
                Row row = sheet.createRow(rowIndex++);
                row.createCell(0).setCellValue(record.get("Institution"));
                row.createCell(1).setCellValue(record.get("Location"));
                row.createCell(2).setCellValue(record.get("Contact"));
            }

            fos = new FileOutputStream(filePath);
            workbook.write(fos);
            System.out.println("Data successfully exported.");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (fos != null) {
                    fos.flush();
                    fos.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    private static Workbook createWorkbook(File file) throws IOException {
        if (!file.exists()) {
            // Create a new workbook if the file does not exist
            if (file.getName().endsWith(XLSX_EXT)) {
                return new XSSFWorkbook();
            } else {
                return new HSSFWorkbook();
            }
        }
        FileInputStream fis = new FileInputStream(file);
        if (file.getName().endsWith(XLSX_EXT)) {
            return new XSSFWorkbook(fis);
        } else {
            return new HSSFWorkbook(fis);
        }
    }
}

Reading Data from Excel with JXL

For reading legacy .xls files, the JXL library provides a straightforward API. The class below iterates through the workbook sheets, extracting cell contents into a nested List structure. Empty cells are ignored during the parsing process.

package com.example.excel;

import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

public class ExcelDataReader {
    public static void main(String[] args) {
        ExcelDataReader reader = new ExcelDataReader();
        File sourceFile = new File("D:/inputData.xls");
        List<List<String>> data = reader.parseFile(sourceFile);

        System.out.println("Extracted Data:");
        for (List<String> row : data) {
            for (String cell : row) {
                System.out.print(cell + "\t");
            }
            System.out.println();
        }
    }

    public List<List<String>> parseFile(File sourceFile) {
        List<List<String>> allData = new ArrayList<>();
        try {
            FileInputStream fis = new FileInputStream(sourceFile);
            Workbook workbook = Workbook.getWorkbook(fis);

            for (int sheetIdx = 0; sheetIdx < workbook.getNumberOfSheets(); sheetIdx++) {
                Sheet sheet = workbook.getSheet(sheetIdx);
                for (int rowNum = 0; rowNum < sheet.getRows(); rowNum++) {
                    List<String> rowData = new ArrayList<>();
                    for (int colNum = 0; colNum < sheet.getColumns(); colNum++) {
                        String content = sheet.getCell(colNum, rowNum).getContents();
                        if (content != null && !content.trim().isEmpty()) {
                            rowData.add(content);
                        }
                    }
                    allData.add(rowData);
                }
                // Only processing the first sheet for this example
                break;
            }
        } catch (BiffException | IOException e) {
            e.printStackTrace();
        }
        return allData;
    }
}