Batch Import & Export with Spring Batch

public interface RowMapper<T> {
    T mapRow(RowSet rs) throws Exception;
}

package com.bookshelf.batch;

import java.util.Locale;

import org.springframework.batch.core.configuration.annotation.StepScope;
import org.springframework.batch.extensions.excel.mapping.BeanWrapperRowMapper;
import org.springframework.batch.extensions.excel.streaming.StreamingXlsxItemReader;
import org.springframework.batch.extensions.excel.support.rowset.DefaultRowSetFactory;
import org.springframework.batch.extensions.excel.support.rowset.StaticColumnNameExtractor;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Profile;
import org.springframework.core.io.FileSystemResource;

@Configuration
@Profile("streaming")
public class StreamingBooksImportConfig {

    @Bean
    @StepScope
    public StreamingXlsxItemReader<BookRow> booksImportReader(
            @Value("#{jobParameters['input.file']}") String inputFile,
            BeanWrapperRowMapper<BookRow> bookRowMapper) {
        // a streaming reader cannot look back at row 0, so the column names are given instead of read
        var rowSetFactory = new DefaultRowSetFactory();
        rowSetFactory.setColumnNameExtractor(new StaticColumnNameExtractor(
                new String[] {"isbn", "title", "author", "copies", "price", "published"}));

        var reader = new StreamingXlsxItemReader<BookRow>();
        reader.setName("booksImportReader");
        reader.setResource(new FileSystemResource(inputFile));
        reader.setRowSetFactory(rowSetFactory);
        reader.setLinesToSkip(1);
        reader.setUserLocale(Locale.US);
        reader.setRowMapper(bookRowMapper);
        return reader;
    }
}

package com.bookshelf.batch;

import java.io.IOException;
import java.io.OutputStream;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.StandardCopyOption;
import java.util.List;

import org.apache.poi.ss.SpreadsheetVersion;
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;
import org.springframework.batch.core.ExitStatus;
import org.springframework.batch.core.BatchStatus;
import org.springframework.batch.core.listener.StepExecutionListener;
import org.springframework.batch.core.step.StepExecution;
import org.springframework.batch.infrastructure.item.Chunk;
import org.springframework.batch.infrastructure.item.ExecutionContext;
import org.springframework.batch.infrastructure.item.ItemStreamException;
import org.springframework.batch.infrastructure.item.ItemStreamWriter;

/**
 * Writes items as rows of an .xlsx file with SXSSF. The file is built in a temporary workbook and published to its
 * final name only when the step has completed, so a failed run never leaves a half-written report behind.
 * It is not restartable from the middle: a restart rebuilds the whole file.
 */
public class SxssfItemWriter<T> implements ItemStreamWriter<T>, StepExecutionListener {

    /** Fills one data row of the current sheet from one item. */
    @FunctionalInterface
    public interface RowFiller<T> {
        void fill(T item, Row row, Styles styles);
    }

    /** The cell styles of the workbook, created once per workbook. */
    public record Styles(CellStyle header, CellStyle date, CellStyle money) {
    }

    private final Path target;
    private final String sheetName;
    private final List<String> headers;
    private final RowFiller<T> filler;
    private final int window;

    private SXSSFWorkbook workbook;
    private Styles styles;
    private SXSSFSheet sheet;
    private int sheetNumber;
    private int nextRow;

    public SxssfItemWriter(Path target, String sheetName, List<String> headers, RowFiller<T> filler, int window) {
        this.target = target;
        this.sheetName = sheetName;
        this.headers = List.copyOf(headers);
        this.filler = filler;
        this.window = window;
    }

    @Override
    public void open(ExecutionContext executionContext) throws ItemStreamException {
        closeWorkbook();                       // a restart in the same JVM must not reuse the old workbook
        try {
            Files.createDirectories(target.toAbsolutePath().getParent());   // fail now, not after an hour of work
        }
        catch (IOException ex) {
            throw new ItemStreamException("Cannot create the directory of " + target, ex);
        }
        workbook = new SXSSFWorkbook(window);
        workbook.setCompressTempFiles(true);   // gzip the temporary row files
        styles = createStyles(workbook);
        sheetNumber = 0;
        startSheet();
    }

    @Override
    public synchronized void write(Chunk<? extends T> chunk) {
        for (T item : chunk) {
            if (nextRow >= SpreadsheetVersion.EXCEL2007.getMaxRows()) {
                startSheet();                  // a sheet is full: continue on a new one
            }
            filler.fill(item, sheet.createRow(nextRow++), styles);
        }
    }

    /**
     * Called after the last chunk and before close(). A completed step publishes the file; if that fails, the step is
     * marked FAILED here, because an exception thrown from close() would leave the step COMPLETED.
     */
    @Override
    public ExitStatus afterStep(StepExecution stepExecution) {
        if (workbook == null || stepExecution.getStatus() != BatchStatus.COMPLETED) {
            return null;                           // failed or stopped: publish nothing
        }
        try {
            publish();
            return null;                           // null keeps the step's own exit status
        }
        catch (IOException | RuntimeException ex) {
            stepExecution.addFailureException(ex);
            stepExecution.setStatus(BatchStatus.FAILED);
            return ExitStatus.FAILED.addExitDescription(ex);
        }
    }

    @Override
    public void close() throws ItemStreamException {
        closeWorkbook();                           // always: removes the temporary row files
    }

    private void publish() throws IOException {
        Path part = target.resolveSibling(target.getFileName() + ".part");
        try {
            try (OutputStream out = Files.newOutputStream(part)) {
                workbook.write(out);
            }
            Files.move(part, target, StandardCopyOption.REPLACE_EXISTING, StandardCopyOption.ATOMIC_MOVE);
        }
        finally {
            Files.deleteIfExists(part);        // after a failed write or move; a no-op once the move has succeeded
        }
    }

    private void closeWorkbook() {
        if (workbook != null) {
            try {
                workbook.close();
            }
            catch (IOException ex) {
                throw new ItemStreamException("Cannot close the workbook", ex);
            }
            finally {
                workbook = null;               // close() has already deleted the temporary row files
            }
        }
    }

    private void startSheet() {
        sheetNumber++;
        sheet = workbook.createSheet(sheetNumber == 1 ? sheetName : sheetName + " " + sheetNumber);
        Row headerRow = sheet.createRow(0);
        for (int i = 0; i < headers.size(); i++) {
            var cell = headerRow.createCell(i);
            cell.setCellValue(headers.get(i));
            cell.setCellStyle(styles.header());
        }
        sheet.createFreezePane(0, 1);
        nextRow = 1;
    }

    private static Styles createStyles(SXSSFWorkbook wb) {
        CreationHelper helper = wb.getCreationHelper();
        Font bold = wb.createFont();
        bold.setBold(true);
        CellStyle header = wb.createCellStyle();
        header.setFont(bold);
        CellStyle date = wb.createCellStyle();
        date.setDataFormat(helper.createDataFormat().getFormat("yyyy-mm-dd"));
        CellStyle money = wb.createCellStyle();
        money.setDataFormat(helper.createDataFormat().getFormat("#,##0.00"));
        return new Styles(header, date, money);
    }
}

package com.bookshelf.batch;

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

/** One row of loans-report.xlsx. */
public record LoanRow(long id, String reader, String title, LocalDate loanedOn, LocalDate dueOn, BigDecimal fine) {
}

package com.bookshelf.batch;

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

import javax.sql.DataSource;

import org.apache.poi.ss.usermodel.Row;
import org.springframework.batch.core.configuration.annotation.StepScope;
import org.springframework.batch.core.job.Job;
import org.springframework.batch.core.job.builder.JobBuilder;
import org.springframework.batch.core.repository.JobRepository;
import org.springframework.batch.core.step.Step;
import org.springframework.batch.core.step.builder.ChunkOrientedStepBuilder;
import org.springframework.batch.infrastructure.item.database.JdbcCursorItemReader;
import org.springframework.batch.infrastructure.item.database.builder.JdbcCursorItemReaderBuilder;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.DataClassRowMapper;
import org.springframework.transaction.PlatformTransactionManager;

@Configuration
public class LoansExportJobConfig {

    @Bean
    public JdbcCursorItemReader<LoanRow> loansReader(DataSource dataSource) {
        return new JdbcCursorItemReaderBuilder<LoanRow>()
                .name("loansReader")
                .dataSource(dataSource)
                .sql("SELECT id, reader, title, loaned_on, due_on, fine FROM loan ORDER BY id")
                .rowMapper(new DataClassRowMapper<>(LoanRow.class))
                .fetchSize(1000)
                .saveState(false)          // the file is rebuilt from scratch, so the reader must start from row 1 too
                .build();
    }

    @Bean
    @StepScope
    public SxssfItemWriter<LoanRow> loansWorkbookWriter(@Value("#{jobParameters['output.file']}") String outputFile) {
        return new SxssfItemWriter<>(Path.of(outputFile), "Loans",
                List.of("Id", "Reader", "Title", "Loaned on", "Due on", "Fine"),
                LoansExportJobConfig::fillRow, 200);
    }

    private static void fillRow(LoanRow loan, Row row, SxssfItemWriter.Styles styles) {
        row.createCell(0).setCellValue(loan.id());
        row.createCell(1).setCellValue(loan.reader());
        row.createCell(2).setCellValue(loan.title());
        var loaned = row.createCell(3);
        loaned.setCellValue(loan.loanedOn());
        loaned.setCellStyle(styles.date());
        var due = row.createCell(4);
        due.setCellValue(loan.dueOn());
        due.setCellStyle(styles.date());
        var fine = row.createCell(5);
        fine.setCellValue(loan.fine().doubleValue());
        fine.setCellStyle(styles.money());
    }

    @Bean
    public Step loansExportStep(JobRepository jobRepository, PlatformTransactionManager transactionManager,
                                JdbcCursorItemReader<LoanRow> loansReader,
                                SxssfItemWriter<LoanRow> loansWorkbookWriter) {
        return new ChunkOrientedStepBuilder<LoanRow, LoanRow>("loansExportStep", jobRepository, 500)
                .reader(loansReader)
                .writer(loansWorkbookWriter)
                .listener(loansWorkbookWriter)        // afterStep tells the writer whether to publish the file
                .transactionManager(transactionManager)
                .build();
    }

    @Bean
    public Job loansExportJob(JobRepository jobRepository, Step loansExportStep) {
        return new JobBuilder("loansExportJob", jobRepository).start(loansExportStep).build();
    }
}