Working with Aspose.Cells FOSS

Overview

This guide covers the core workflow for creating, loading, modifying, and saving Excel workbooks with Aspose.Cells FOSS for Java.

Creating and Saving a Workbook

The Workbook class is the entry point for all spreadsheet operations. It implements AutoCloseable for clean resource management:

import com.aspose.cells_foss.Cell;
import com.aspose.cells_foss.Style;
import com.aspose.cells_foss.Workbook;
import com.aspose.cells_foss.Worksheet;

try (Workbook workbook = new Workbook()) {
    WorksheetCollection sheets = workbook.getWorksheets();
    Worksheet sheet = sheets.get(0);
    sheet.setName("Sales");

    sheet.getCells().get("A1").putValue("Product");
    sheet.getCells().get("B1").putValue("Revenue");
    sheet.getCells().get("A2").putValue("Widget A");
    Cell b2 = sheet.getCells().get("B2");
    b2.putValue(42500.75);

    Style style = b2.getStyle();
    style.getFont().setBold(true);
    style.setCustom("#,##0.00");
    b2.setStyle(style);

    workbook.save("sales.xlsx");
}

Loading an Existing Workbook

Pass LoadOptions to configure how the XLSX package is parsed. Set setStrictMode(false) to allow lenient parsing, setTryRepairPackage(true) to recover from structurally invalid ZIP entries, and setTryRepairXml(true) to attempt recovery from malformed XML parts. After loading, call getLoadDiagnostics() to inspect any issues found; iterate getIssues() to log each repair applied during the load.

import com.aspose.cells_foss.LoadIssue;
import com.aspose.cells_foss.LoadOptions;
import com.aspose.cells_foss.Workbook;

LoadOptions options = new LoadOptions();
options.setStrictMode(false);
options.setTryRepairPackage(true);
options.setTryRepairXml(true);

try (Workbook workbook = new Workbook("input.xlsx", options)) {
    if (workbook.getLoadDiagnostics().hasRepairs()) {
        for (LoadIssue issue : workbook.getLoadDiagnostics().getIssues()) {
            System.out.println(issue.getMessage());
        }
    }
    workbook.getDocumentProperties().setAuthor("cells-foss");
    workbook.save("output.xlsx");
}

Working with Multiple Worksheets

A Workbook contains a WorksheetCollection accessed via getWorksheets(). You can add new sheets, rename existing ones, and navigate between them by index:

import com.aspose.cells_foss.Workbook;
import com.aspose.cells_foss.Worksheet;

try (Workbook workbook = new Workbook()) {
    WorksheetCollection sheets = workbook.getWorksheets();
    Worksheet sheet1 = sheets.get(0);
    sheet1.setName("Q1");
    workbook.getWorksheets().add("Q2");
    Worksheet sheet2 = sheets.get(1);
    sheet2.setName("Q2 Summary");
    workbook.save("multi.xlsx");
}

AutoFilter

The Worksheet.getAutoFilter() method returns an AutoFilter instance that defines drop-down filter controls on column headers. Call setRange() to specify the header row range that Excel displays with filter arrows when the file is opened:

import com.aspose.cells_foss.Workbook;
import com.aspose.cells_foss.Worksheet;

try (Workbook workbook = new Workbook()) {
    WorksheetCollection sheets = workbook.getWorksheets();
    Worksheet sheet = sheets.get(0);
    sheet.getCells().get("A1").putValue("Product");
    sheet.getCells().get("B1").putValue("Category");
    sheet.getAutoFilter().setRange("A1:B1");
    workbook.save("filtered.xlsx");
}