Quick start

Spring Boot application

Step 1. Add the starter as shown in section 1.5. No configuration is required: the auto-configuration registers a Sheetsmith bean.

Step 2. Describe the sheet with a sheet class. A record is the most compact form:

import cloud.baldilorenzo.sheetsmith.annotation.ExcelColumn;
import cloud.baldilorenzo.sheetsmith.annotation.ExcelSheet;
import cloud.baldilorenzo.sheetsmith.style.TablePreset;

import java.math.BigDecimal;
import java.time.LocalDate;

@ExcelSheet(title = "Customers", preset = TablePreset.MEDIUM, accentColor = "#1F4E79", autoFilter = true)
public record CustomerRow(
        @ExcelColumn(header = "Code", order = 10, width = 12) String code,
        @ExcelColumn(header = "Name", order = 20) String name,
        @ExcelColumn(header = "Customer since", order = 30, format = "dd/mm/yyyy") LocalDate since,
        @ExcelColumn(header = "Revenue", order = 40, format = "#,##0.00") BigDecimal revenue) {
}

Step 3. Inject the bean and generate the file:

import cloud.baldilorenzo.sheetsmith.SheetData;
import cloud.baldilorenzo.sheetsmith.Sheetsmith;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class CustomerExportService {

    private final Sheetsmith sheetsmith;
    private final CustomerRepository customers;

    public CustomerExportService(Sheetsmith sheetsmith, CustomerRepository customers) {
        this.sheetsmith = sheetsmith;
        this.customers = customers;
    }

    public byte[] export() {
        List<CustomerRow> rows = customers.findAll().stream()
                .map(c -> new CustomerRow(c.getCode(), c.getName(), c.getSince(), c.getRevenue()))
                .toList();
        return sheetsmith.generate(List.of(SheetData.of("Customers", CustomerRow.class, rows)));
    }
}

The resulting workbook has one sheet named Customers, with a merged title row, a header filled with the accent colour and bold contrasting text, a light grid, light zebra rows, a frozen header, an auto-filter and columns sized to their content, except Code, which is 12 characters wide.

Plain Java application

Without Spring, create the generator with its builder. Create it once and reuse it: it is immutable, thread-safe and caches the metadata of each sheet class.

import cloud.baldilorenzo.sheetsmith.SheetData;
import cloud.baldilorenzo.sheetsmith.Sheetsmith;

import java.nio.file.Files;
import java.nio.file.Path;
import java.util.List;

public final class Exports {

    private static final Sheetsmith SHEETSMITH = Sheetsmith.builder().build();

    public static void writeCustomers(List<CustomerRow> rows, Path target) throws Exception {
        byte[] file = SHEETSMITH.generate(List.of(SheetData.of("Customers", CustomerRow.class, rows)));
        Files.write(target, file);
    }
}

To write directly to a file without holding the bytes, use the stream variant:

try (OutputStream out = Files.newOutputStream(target)) {
    SHEETSMITH.generate(List.of(SheetData.of("Customers", CustomerRow.class, rows)), out);
}

Download from a Spring MVC controller

import org.springframework.http.ContentDisposition;
import org.springframework.http.HttpHeaders;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.servlet.mvc.method.annotation.StreamingResponseBody;

@RestController
public class CustomerExportController {

    private static final MediaType XLSX =
            MediaType.parseMediaType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

    private final Sheetsmith sheetsmith;
    private final CustomerQueries queries;

    public CustomerExportController(Sheetsmith sheetsmith, CustomerQueries queries) {
        this.sheetsmith = sheetsmith;
        this.queries = queries;
    }

    @GetMapping("/exports/customers.xlsx")
    public ResponseEntity<StreamingResponseBody> customers() {
        List<SheetData<?>> sheets = List.of(SheetData.of("Customers", CustomerRow.class, queries.rows()));
        StreamingResponseBody body = out -> sheetsmith.generate(sheets, out);
        return ResponseEntity.ok()
                .contentType(XLSX)
                .header(HttpHeaders.CONTENT_DISPOSITION,
                        ContentDisposition.attachment().filename("customers.xlsx").build().toString())
                .body(body);
    }
}

The data is loaded before the response body is produced, so a database error still produces a regular error response. The stream variant writes nothing to the stream when a configuration or generation error occurs, because the workbook is complete before serialisation starts: see section 5.2.2. With a streaming body, however, the servlet container may already have committed the status and the headers when the generation runs, so an exception at that point can no longer become a clean error response. When a guaranteed error response matters, generate the bytes first and return them:

@GetMapping("/exports/customers.xlsx")
public ResponseEntity<byte[]> customers() {
    byte[] file = sheetsmith.generate(List.of(SheetData.of("Customers", CustomerRow.class, queries.rows())));
    return ResponseEntity.ok()
            .contentType(XLSX)
            .contentLength(file.length)
            .header(HttpHeaders.CONTENT_DISPOSITION,
                    ContentDisposition.attachment().filename("customers.xlsx").build().toString())
            .body(file);
}

The two approaches use practically the same memory: see section 5.2.3. Validating the sheet classes at startup (section 10.4) removes configuration errors from the request path in both cases.