Exporting Reports from a Server

package bookshelf.export;

import java.io.IOException;
import java.io.OutputStream;

/** A report that knows how to write itself to any stream: a response, a temp file or a byte array. */
@FunctionalInterface
public interface ReportWriter {
    void writeTo(OutputStream out) throws IOException;
}

package bookshelf.export;

import java.time.LocalDate;

/** One line of loans-report.xlsx: the mapping step between the data query and the workbook. */
public record LoanRow(long id, String bookTitle, String borrower, LocalDate loanedOn, LocalDate dueOn,
                      boolean returned) {
}

package bookshelf.export;

import java.sql.Date;
import java.time.LocalDate;
import java.util.function.Consumer;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

/** The data query of the report: rows are pushed to the caller one by one and never collected into a list. */
@Component
public class LoanSource {

    private final JdbcTemplate jdbc;

    public LoanSource(DataSource dataSource) {
        this.jdbc = new JdbcTemplate(dataSource);
        this.jdbc.setFetchSize(1_000);   // rows per round trip; the driver decides whether it honours it
    }

    /** The transaction keeps one connection (and, on PostgreSQL, one server-side cursor) open for the whole export. */
    @Transactional(readOnly = true)
    public void forEachBetween(LocalDate from, LocalDate to, Consumer<LoanRow> consumer) {
        jdbc.query("""
                select id, book_title, borrower, loaned_on, due_on, returned
                from loan_entity where loaned_on between ? and ? order by id""",
                ps -> {
                    ps.setDate(1, Date.valueOf(from));
                    ps.setDate(2, Date.valueOf(to));
                },
                (RowCallbackHandler) rs -> consumer.accept(new LoanRow(
                        rs.getLong("id"),
                        rs.getString("book_title"),
                        rs.getString("borrower"),
                        rs.getDate("loaned_on").toLocalDate(),
                        rs.getDate("due_on").toLocalDate(),
                        rs.getBoolean("returned"))));
    }

    @Transactional(readOnly = true)
    public long countSince(LocalDate from) {
        Long count = jdbc.queryForObject("select count(*) from loan_entity where loaned_on >= ?", Long.class,
                Date.valueOf(from));
        return count == null ? 0 : count;
    }
}

package bookshelf.export;

import java.io.IOException;
import java.io.OutputStream;
import java.time.LocalDate;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

/** Writes loans-report.xlsx: query rows go through a 200-row SXSSF window into the output stream. */
public class LoansWorkbookWriter implements ReportWriter {

    private static final String[] HEADERS = {"Id", "Book", "Borrower", "Loaned on", "Due on", "Returned"};

    private final LoanSource source;
    private final LocalDate from;
    private final LocalDate to;

    public LoansWorkbookWriter(LoanSource source, LocalDate from, LocalDate to) {
        this.source = source;
        this.from = from;
        this.to = to;
    }

    @Override
    public void writeTo(OutputStream out) throws IOException {
        try (SXSSFWorkbook workbook = new SXSSFWorkbook(200)) {
            workbook.setCompressTempFiles(true);
            CreationHelper helper = workbook.getCreationHelper();

            Font bold = workbook.createFont();
            bold.setBold(true);
            CellStyle headerStyle = workbook.createCellStyle();
            headerStyle.setFont(bold);
            CellStyle dateStyle = workbook.createCellStyle();
            dateStyle.setDataFormat(helper.createDataFormat().getFormat("yyyy-mm-dd"));

            SXSSFSheet sheet = workbook.createSheet("Loans");
            Row header = sheet.createRow(0);
            for (int c = 0; c < HEADERS.length; c++) {
                header.createCell(c).setCellValue(HEADERS[c]);
                header.getCell(c).setCellStyle(headerStyle);
            }
            sheet.createFreezePane(0, 1);
            sheet.setColumnWidth(1, 40 * 256);
            sheet.setColumnWidth(2, 24 * 256);

            int[] next = {1};
            source.forEachBetween(from, to, loan -> {
                Row row = sheet.createRow(next[0]++);
                row.createCell(0).setCellValue(loan.id());
                row.createCell(1).setCellValue(loan.bookTitle());
                row.createCell(2).setCellValue(loan.borrower());
                row.createCell(3).setCellValue(loan.loanedOn());
                row.getCell(3).setCellStyle(dateStyle);
                row.createCell(4).setCellValue(loan.dueOn());
                row.getCell(4).setCellStyle(dateStyle);
                row.createCell(5).setCellValue(loan.returned());
            });

            workbook.write(out);   // flushes the rest of the window and finishes the zip; does not close out
        }
    }
}

<dependencies>
  <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-webmvc</artifactId>
  </dependency>
  <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
  </dependency>
  <dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.5.1</version>
  </dependency>
</dependencies>

dependencies {
    implementation(platform("org.springframework.boot:spring-boot-dependencies:4.1.1"))
    implementation("org.springframework.boot:spring-boot-starter-webmvc")
    implementation("org.springframework.boot:spring-boot-starter-jdbc")
    implementation("org.apache.poi:poi-ooxml:5.5.1")
}

package bookshelf.export;

import java.io.IOException;
import java.io.InputStream;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Path;
import java.time.LocalDate;
import java.time.YearMonth;
import java.util.List;
import java.util.UUID;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.core.io.ByteArrayResource;
import org.springframework.core.io.Resource;
import org.springframework.http.ContentDisposition;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.server.ResponseStatusException;
import org.springframework.web.servlet.mvc.method.annotation.StreamingResponseBody;

@RestController
public class ReportController {

    private final LoanSource loans;
    private final Path spoolDirectory;   // the reports directory, so the purge also removes a spool file never sent

    public ReportController(LoanSource loans, @Value("${reports.directory}") Path spoolDirectory) {
        this.loans = loans;
        this.spoolDirectory = spoolDirectory;
    }

    /** Small report: build it in memory, so the length is known and the client gets Content-Length. */
    @GetMapping("/reports/books-inventory.xlsx")
    public ResponseEntity<Resource> booksInventory() {
        byte[] bytes = ReportBytes.toByteArray(new BooksInventoryWriter(List.of(
                new BooksInventoryWriter.Book("978-0-00-000001-0", "El camino", 3),
                new BooksInventoryWriter.Book("978-0-00-000002-7", "Don Quijote", 5))));
        return ResponseEntity.ok()
                .contentType(ReportMediaTypes.XLSX)
                .header(HttpHeaders.CONTENT_DISPOSITION, attachment("books-inventory.xlsx"))
                .body(new ByteArrayResource(bytes));
    }

    /** Large report: the workbook is written straight to the response, so the length is unknown. */
    @GetMapping("/reports/loans.xlsx")
    public ResponseEntity<StreamingResponseBody> loansReport(
            @RequestParam(defaultValue = "2020-01-01") LocalDate from) {
        if (from.isAfter(LocalDate.now())) {
            throw new ResponseStatusException(HttpStatus.BAD_REQUEST, "'from' is in the future");
        }
        ReportWriter writer = new LoansWorkbookWriter(loans, from, LocalDate.now());
        StreamingResponseBody body = writer::writeTo;
        return ResponseEntity.ok()
                .contentType(ReportMediaTypes.XLSX)
                .header(HttpHeaders.CONTENT_DISPOSITION, attachment("loans-report.xlsx"))
                .body(body);
    }

    /** Same report, spooled to a file first: a failure still gives a 500, and the length is known. */
    @GetMapping("/reports/loans-spooled.xlsx")
    public ResponseEntity<StreamingResponseBody> loansSpooled(
            @RequestParam(defaultValue = "2020-01-01") LocalDate from) throws IOException {
        Path file = ReportBytes.toFile(new LoansWorkbookWriter(loans, from, LocalDate.now()),
                spoolDirectory, "spool-" + UUID.randomUUID() + ".xlsx");
        long length = Files.size(file);
        StreamingResponseBody body = out -> {
            try (InputStream in = Files.newInputStream(file)) {
                in.transferTo(out);
            } finally {
                Files.deleteIfExists(file);
            }
        };
        return ResponseEntity.ok()
                .contentType(ReportMediaTypes.XLSX)
                .contentLength(length)
                .header(HttpHeaders.CONTENT_DISPOSITION, attachment("loans-report.xlsx"))
                .body(body);
    }

    /** Word report: XWPF is not streaming, so build it in memory behind a row cap. */
    @GetMapping("/reports/monthly-report.docx")
    public ResponseEntity<Resource> monthlyReport(@RequestParam YearMonth month) {
        List<LoanRow> rows = MonthlyReportWriter.collect(loans, month, 2_000);   // may throw before any byte is sent
        byte[] bytes = ReportBytes.toByteArray(new MonthlyReportWriter(month, rows));
        return ResponseEntity.ok()
                .contentType(ReportMediaTypes.DOCX)
                .header(HttpHeaders.CONTENT_DISPOSITION, attachment("monthly-report-" + month + ".docx"))
                .body(new ByteArrayResource(bytes));
    }

    static String attachment(String filename) {
        return ContentDisposition.attachment().filename(filename, StandardCharsets.UTF_8).build().toString();
    }
}

package bookshelf.export;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.UncheckedIOException;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.StandardCopyOption;

/** Helpers that turn a ReportWriter into bytes or into a file, for the variants that need a length first. */
public final class ReportBytes {

    private ReportBytes() {
    }

    public static byte[] toByteArray(ReportWriter writer) {
        ByteArrayOutputStream buffer = new ByteArrayOutputStream();
        try {
            writer.writeTo(buffer);
        } catch (IOException e) {
            throw new UncheckedIOException(e);
        }
        return buffer.toByteArray();
    }

    /** Writes next to the final name and renames at the end, so a reader never sees a half-written file. */
    public static Path toFile(ReportWriter writer, Path directory, String name) throws IOException {
        Files.createDirectories(directory);
        Path partial = Files.createTempFile(directory, name, ".part");
        try {
            try (var out = Files.newOutputStream(partial)) {
                writer.writeTo(out);
            }
            Path target = directory.resolve(name);
            return Files.move(partial, target, StandardCopyOption.REPLACE_EXISTING, StandardCopyOption.ATOMIC_MOVE);
        } finally {
            Files.deleteIfExists(partial);
        }
    }
}

package bookshelf.export;

import org.springframework.http.MediaType;

public final class ReportMediaTypes {

    public static final MediaType XLSX =
            MediaType.parseMediaType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    public static final MediaType DOCX =
            MediaType.parseMediaType("application/vnd.openxmlformats-officedocument.wordprocessingml.document");

    private ReportMediaTypes() {
    }
}

curl -s -D - -o inventory.xlsx http://localhost:8080/reports/books-inventory.xlsx

HTTP/1.1 200
Content-Disposition: attachment; filename="books-inventory.xlsx"; filename*=UTF-8''books-inventory.xlsx
Accept-Ranges: bytes
Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Content-Length: 3478

curl -s -D - -o loans.xlsx 'http://localhost:8080/reports/loans.xlsx?from=2026-01-01'

HTTP/1.1 200
Content-Disposition: attachment; filename="loans-report.xlsx"; filename*=UTF-8''loans-report.xlsx
Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Transfer-Encoding: chunked

HTTP/1.1 200
Content-Disposition: attachment; filename="loans-report.xlsx"; filename*=UTF-8''loans-report.xlsx
Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Content-Length: 3431428

package bookshelf.export;

import java.nio.charset.StandardCharsets;
import org.springframework.http.ContentDisposition;

public class ContentDispositionDemo {

    public static void main(String[] args) {
        String[] names = {"loans-report.xlsx", "Informe de préstamos – septiembre.xlsx", "a\"b\\c;d.xlsx", "日本語.xlsx"};
        for (String name : names) {
            System.out.println(ContentDisposition.attachment().filename(name, StandardCharsets.UTF_8).build());
            System.out.println(ContentDisposition.attachment().filename(name).build());
        }
    }
}

attachment; filename="loans-report.xlsx"; filename*=UTF-8''loans-report.xlsx
attachment; filename="loans-report.xlsx"
attachment; filename="Informe de prestamos _ septiembre.xlsx"; filename*=UTF-8''Informe%20de%20pr%C3%A9stamos%20%E2%80%93%20septiembre.xlsx
attachment; filename="Informe de préstamos – septiembre.xlsx"
attachment; filename="a\"b\\c;d.xlsx"; filename*=UTF-8''a%22b%5Cc%3Bd.xlsx
attachment; filename="a\"b\\c;d.xlsx"
attachment; filename="___.xlsx"; filename*=UTF-8''%E6%97%A5%E6%9C%AC%E8%AA%9E.xlsx
attachment; filename="日本語.xlsx"

package bookshelf.export;

import java.nio.charset.StandardCharsets;

/** Builds Content-Disposition values after RFC 6266 and RFC 8187, for stacks that have no helper. */
public final class ContentDispositions {

    private static final String ATTR_CHARS = "!#$&+-.^_`|~";

    private ContentDispositions() {
    }

    public static String attachment(String filename) {
        return "attachment; filename=\"" + asciiFallback(filename) + "\"; filename*=UTF-8''" + encode(filename);
    }

    /** What an old client shows: ASCII only, no quote, no backslash, no control character. */
    static String asciiFallback(String filename) {
        StringBuilder sb = new StringBuilder();
        for (char c : filename.toCharArray()) {
            sb.append(c < 0x20 || c > 0x7e || c == '"' || c == '\\' ? '_' : c);
        }
        return sb.toString();
    }

    /** Percent-encodes the UTF-8 bytes of every character that is not an RFC 8187 attr-char. */
    static String encode(String filename) {
        StringBuilder sb = new StringBuilder();
        for (byte b : filename.getBytes(StandardCharsets.UTF_8)) {
            int c = b & 0xff;
            if (Character.isLetterOrDigit(c) && c < 0x80 || ATTR_CHARS.indexOf(c) >= 0) {
                sb.append((char) c);
            } else {
                sb.append('%').append(Character.toUpperCase(Character.forDigit(c >> 4, 16)))
                        .append(Character.toUpperCase(Character.forDigit(c & 0xf, 16)));
            }
        }
        return sb.toString();
    }
}

attachment; filename="loans-report.xlsx"; filename*=UTF-8''loans-report.xlsx
attachment; filename="Informe de pr_stamos _ septiembre.xlsx"; filename*=UTF-8''Informe%20de%20pr%C3%A9stamos%20%E2%80%93%20septiembre.xlsx
attachment; filename="a_b_c;d.xlsx"; filename*=UTF-8''a%22b%5Cc%3Bd.xlsx
attachment; filename="___.xlsx"; filename*=UTF-8''%E6%97%A5%E6%9C%AC%E8%AA%9E.xlsx

package bookshelf.export;

import jakarta.inject.Inject;
import jakarta.ws.rs.BadRequestException;
import jakarta.ws.rs.DefaultValue;
import jakarta.ws.rs.GET;
import jakarta.ws.rs.Path;
import jakarta.ws.rs.Produces;
import jakarta.ws.rs.QueryParam;
import jakarta.ws.rs.core.HttpHeaders;
import jakarta.ws.rs.core.Response;
import jakarta.ws.rs.core.StreamingOutput;
import java.time.LocalDate;
import java.time.format.DateTimeParseException;

@Path("/reports")
public class ReportResource {

    static final String XLSX = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

    @Inject
    LoanSource source;

    @GET
    @Path("/loans.xlsx")
    @Produces(XLSX)
    public Response loans(@QueryParam("from") @DefaultValue("2020-01-01") String from) {
        LocalDate start;
        try {
            start = LocalDate.parse(from);               // validate before the first byte is written
        } catch (DateTimeParseException e) {
            throw new BadRequestException("'from' must be an ISO date such as 2026-01-31");
        }
        LocalDate end = LocalDate.now();
        StreamingOutput body = out -> new LoansWorkbookWriter(source).writeTo(start, end, out);
        return Response.ok(body, XLSX)
                .header(HttpHeaders.CONTENT_DISPOSITION, ContentDispositions.attachment("loans-report.xlsx"))
                .build();
    }
}

<dependency>
  <groupId>jakarta.platform</groupId>
  <artifactId>jakarta.jakartaee-api</artifactId>
  <version>11.0.0</version>
  <scope>provided</scope>
</dependency>

<dependencyManagement>
  <dependencies>
    <dependency>
      <groupId>io.quarkus.platform</groupId>
      <artifactId>quarkus-bom</artifactId>
      <version>3.40.1</version>
      <type>pom</type>
      <scope>import</scope>
    </dependency>
  </dependencies>
</dependencyManagement>
<dependencies>
  <dependency>
    <groupId>io.quarkus</groupId>
    <artifactId>quarkus-rest</artifactId>
  </dependency>
  <dependency>
    <groupId>io.quarkus</groupId>
    <artifactId>quarkus-agroal</artifactId>
  </dependency>
  <dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.5.1</version>
  </dependency>
</dependencies>

package bookshelf.export;

import io.agroal.api.AgroalDataSource;
import jakarta.enterprise.context.ApplicationScoped;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDate;
import java.util.function.Consumer;

/** The data query: plain JDBC, one connection, a fetch size, and no list of rows. */
@ApplicationScoped
public class LoanSource {

    private final AgroalDataSource dataSource;

    public LoanSource(AgroalDataSource dataSource) {
        this.dataSource = dataSource;
    }

    public void forEachBetween(LocalDate from, LocalDate to, Consumer<LoanRow> consumer) throws SQLException {
        try (Connection connection = dataSource.getConnection()) {
            connection.setAutoCommit(false);           // PostgreSQL only uses a cursor when autocommit is off
            connection.setReadOnly(true);
            try (PreparedStatement statement = connection.prepareStatement("""
                    select id, book_title, borrower, loaned_on, due_on, returned
                    from loan where loaned_on between ? and ? order by id""")) {
                statement.setFetchSize(1_000);
                statement.setDate(1, Date.valueOf(from));
                statement.setDate(2, Date.valueOf(to));
                try (ResultSet rs = statement.executeQuery()) {
                    while (rs.next()) {
                        consumer.accept(new LoanRow(rs.getLong("id"), rs.getString("book_title"),
                                rs.getString("borrower"), rs.getDate("loaned_on").toLocalDate(),
                                rs.getDate("due_on").toLocalDate(), rs.getBoolean("returned")));
                    }
                }
            } finally {
                connection.rollback();                 // read-only: nothing to commit, and the cursor is released
            }
        }
    }
}

package bookshelf.export;

import org.springframework.http.HttpStatus;
import org.springframework.http.ProblemDetail;
import org.springframework.web.bind.annotation.ExceptionHandler;
import org.springframework.web.bind.annotation.RestControllerAdvice;

/** Turns failures that happen before the first byte into a normal error response. */
@RestControllerAdvice
public class ReportProblemAdvice {

    @ExceptionHandler(ReportTooLargeException.class)
    public ProblemDetail tooLarge(ReportTooLargeException e) {
        return ProblemDetail.forStatusAndDetail(HttpStatus.UNPROCESSABLE_ENTITY, e.getMessage());
    }
}

package bookshelf.export;

import java.io.IOException;
import java.io.OutputStream;
import java.time.YearMonth;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.xwpf.usermodel.ParagraphAlignment;
import org.apache.poi.xwpf.usermodel.XWPFDocument;
import org.apache.poi.xwpf.usermodel.XWPFParagraph;
import org.apache.poi.xwpf.usermodel.XWPFRun;
import org.apache.poi.xwpf.usermodel.XWPFTable;
import org.apache.poi.xwpf.usermodel.XWPFTableRow;

/** Writes monthly-report.docx. XWPF keeps the whole document in memory, so the row count is capped by the caller. */
public class MonthlyReportWriter implements ReportWriter {

    private final YearMonth month;
    private final List<LoanRow> loans;

    public MonthlyReportWriter(YearMonth month, List<LoanRow> loans) {
        this.month = month;
        this.loans = loans;
    }

    /** Collects the month's rows; stops with an exception as soon as the cap is exceeded. */
    public static List<LoanRow> collect(LoanSource source, YearMonth month, int maxRows) {
        List<LoanRow> rows = new ArrayList<>();
        source.forEachBetween(month.atDay(1), month.atEndOfMonth(), loan -> {
            if (rows.size() >= maxRows) {
                throw new ReportTooLargeException("More than " + maxRows + " loans in " + month
                        + "; use the Excel export instead");
            }
            rows.add(loan);
        });
        return rows;
    }

    @Override
    public void writeTo(OutputStream out) throws IOException {
        try (XWPFDocument document = new XWPFDocument()) {
            XWPFParagraph title = document.createParagraph();
            title.setStyle("Title");
            title.setAlignment(ParagraphAlignment.LEFT);
            XWPFRun titleRun = title.createRun();
            titleRun.setBold(true);
            titleRun.setFontSize(20);
            titleRun.setText("Monthly loans report: " + month);

            XWPFParagraph summary = document.createParagraph();
            summary.createRun().setText(loans.size() + " loans started in " + month + ".");

            XWPFTable table = document.createTable(loans.size() + 1, 4);
            String[] headers = {"Id", "Book", "Borrower", "Due on"};
            XWPFTableRow headerRow = table.getRow(0);
            for (int c = 0; c < headers.length; c++) {
                XWPFRun run = headerRow.getCell(c).getParagraphArray(0).createRun();
                run.setBold(true);
                run.setText(headers[c]);
            }
            for (int r = 0; r < loans.size(); r++) {
                LoanRow loan = loans.get(r);
                XWPFTableRow row = table.getRow(r + 1);
                row.getCell(0).setText(Long.toString(loan.id()));
                row.getCell(1).setText(loan.bookTitle());
                row.getCell(2).setText(loan.borrower());
                row.getCell(3).setText(loan.dueOn().toString());
            }
            document.write(out);
        }
    }
}

curl -s -D - -o monthly-report.docx 'http://localhost:8080/reports/monthly-report.docx?month=2026-03'

HTTP/1.1 200
Content-Disposition: attachment; filename="monthly-report-2026-03.docx"; filename*=UTF-8''monthly-report-2026-03.docx
Accept-Ranges: bytes
Content-Type: application/vnd.openxmlformats-officedocument.wordprocessingml.document
Content-Length: 4694